Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Locating similar SQL in Oracle



Oracle Tips by  Michael R. Ault

Oracle bind variables are a super important way to make Oracle SQL reentrant.

The dictionary columns exact_matching_signature, and force_matching_signature exist in v$sqlarea, v$sql, stats$sql_summary and v$active_session_history.
 
The Oracle docs note that these signature columns are "normalized" (e.g. standardized" representations of SQL statements, thereby allowing them to be compared with dictionary queries:
 
The "signature [is] calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings." It also says that FORCE_MATCHING_SIGNATURE is a "signature used when the CURSOR_SHARING parameter is set to FORCE". The signature seems to be just another hash value calculated from SQL statement, but this time, it's a hash of normalized (removed spaces, etc.) SQL statement.
 
FORCE_MATCHING_SIGNATURE is calculated from SQL text as if CURSOR_SHARING would be set to TRUE. (You don't have to set it to TRUE to get the signature values.) CURSOR_SHARING=FORCE forces SQL statements to share cursors by replacing constants with bind variables, so all statements that differ only by constants share the same cursor. Let's have a look at FORCE_MATCHING_SIGNATURE values for the same SQLs"

The activities needed to parse a statement and generate an execution plan are CPU intensive and generate recursive SQL against the data dictionary which may result in physical IO as well. The added statement and parse tree takes up space in the shared pool. I have seen several databases where the shared pool was over a gigabyte in size (one were it was 4 gig!) until bind variables were introduced, reducing the size to a couple of hundred megabytes at most.

A quick method of seeing whether code is being reused (a key indicator of proper bind variable usage) is to look at the values of reusable and non-reusable memory in the shared pool. A SQL for determining this comparison of reusable to non-reusable code is shown in figure 1.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

ttitle 'Shared Pool Utilization'
spool sql_garbage
select 1 nopr,
to_char(a.inst_id) inst_id,
a.users users,
to_char(a.garbage,'9,999,999,999') garbage,
to_char(b.good,'9,999,999,999') good,
to_char((b.good/(b.good+a.garbage))*100,'9,999,999.999') good_percent
from (select
  a.inst_id,
  b.username users,
  sum(a.sharable_mem+a.persistent_mem) Garbage,
  to_number(null) good
from
   sys.gv_$sqlarea a,
   dba_users b
where
  see code depot for full script
  (a.parsing_user_id = b.user_id and a.executions<=1)
group by a.inst_id, b.username
union
select distinct
  c.inst_id,
  b.username users,
  to_number(null) garbage,
  sum(c.sharable_mem+c.persistent_mem) Good
from
   dba_users b,
   sys.gv_$sqlarea c
where
  (b.user_id=c.parsing_user_id and c.executions>1)
group by c.inst_id, b.username
) a, (select
  a.inst_id,
  b.username users,
  sum(a.sharable_mem+a.persistent_mem) Garbage,
  to_number(null) good
from
   sys.gv_$sqlarea a,
   dba_users b
where
  (a.parsing_user_id = b.user_id and a.executions<=1)
group by a.inst_id,b.username
union
select distinct
  c.inst_id,
  b.username users,
  to_number(null) garbage,
  sum(c.sharable_mem+c.persistent_mem) Good
from
   dba_users b,
   sys.gv_$sqlarea c
where
  (b.user_id=c.parsing_user_id and c.executions>1)
group by c.inst_id, b.username
) b
where a.users=b.users
and a.inst_id=b.inst_id
and a.garbage is not null and b.good is not null
union
select 2 nopr,
'-------' inst_id,'-------------' users,'--------------' garbage,'--------------' good,
'--------------' good_percent from dual
union
select 3 nopr,
to_char(a.inst_id,'999999'),
to_char(count(a.users)) users,
to_char(sum(a.garbage),'9,999,999,999') garbage,
to_char(sum(b.good),'9,999,999,999') good,
to_char(((sum(b.good)/(sum(b.good)+sum(a.garbage)))*100),'9,999,999.999') good_percent
from (select
  a.inst_id,
  b.username users,
  sum(a.sharable_mem+a.persistent_mem) Garbage,
  to_number(null) good
from
   sys.gv_$sqlarea a,
   dba_users b
where
  (a.parsing_user_id = b.user_id and a.executions<=1)
group by a.inst_id,b.username
union
select distinct
  c.inst_id,
  b.username users,
  to_number(null) garbage,
  sum(c.sharable_mem+c.persistent_mem) Good
from
   dba_users b,
   sys.gv_$sqlarea c
where
  (b.user_id=c.parsing_user_id and c.executions>1)
group by c.inst_id,b.username
) a, (select
  a.inst_id,
  b.username users,
  sum(a.sharable_mem+a.persistent_mem) Garbage,
  to_number(null) good
from
   sys.gv_$sqlarea a,
   dba_users b
where
  (a.parsing_user_id = b.user_id and a.executions<=1)
group by a.inst_id,b.username
union
select distinct
  c.inst_id,
  b.username users,
  to_number(null) garbage,
  sum(c.sharable_mem+c.persistent_mem) Good
from
   dba_users b,
   sys.gv_$sqlarea c
where
  (b.user_id=c.parsing_user_id and c.executions>1)
group by c.inst_id, b.username
) b
where a.users=b.users
and a.inst_id=b.inst_id
and a.garbage is not null and b.good is not null
group by a.inst_id
order by 1,2 desc
/
spool off
ttitle off
set pages 22

Figure 1: SQL Code to Show Shared verses Non-shared code

An example report is shown in Figure 2 for an instance with poor code reuse characteristics. The names have been changed to protect the innocent.

Date: 03/25/05                                              Page:   1
Time: 17:51 PM            Shared Pool Utilization           SYSTEM
                            whoville database
users                Non-Shared SQL Shared SQL     Percent Shared
-------------------- -------------- -------------- --------------
WHOAPP                  532,097,982      1,775,745           .333
SYS                       5,622,594      5,108,017         47.602
DBSNMP                      678,616        219,775         24.463
SYSMAN                      439,915      2,353,205         84.250
SYSTEM                      425,586         20,674          4.633
-------------        -------------- -------------- --------------
5                       541,308,815      9,502,046          1.725

Figure 2: Example report output for poor code reuse

As you can see from Figure 2 the majority owner in this application, WHOAPP is only showing 0.3 percent of reusable code by memory usage and is tying up an amazing 530 megabytes with non-reusable code! Let's look at a database with good reuse statistics. Look at Figure 3.

Date: 11/13/05                                              Page:   1
Time: 03:15 PM            Shared Pool Utilization           PERFSTAT      
                            dbaville database                                 
                                                                          
users                Non-Shared SQL Shared SQL     Percent Shared         
-------------------- -------------- -------------- --------------         
DBAVILLAGE                9,601,173     81,949,581         89.513         
PERFSTAT                  2,652,827        199,868          7.006         
DBASTAGER                 1,168,137     35,468,687         96.812         
SYS                          76,037      5,119,125         98.536         
-------------        -------------- -------------- --------------         
4                        13,498,174    122,737,261         90.092  

 

Figure 3: Example of Good Sharing of Code

Notice in Figure 3 how the two application owners, DBAVILLAGE and DBASTAGER show 89.513 and 96.812 reuse percentage by memory footprint for code.

So what else can we look at to see about code reusage, the above reports give us a gross indication, how about something with a bit more usability to correct the situation? The V$SQLAREA and V$SQLTEXT views give us the capability to look at the current code in the shared pool and determine if it is using, or not using bind variables. Look at Figure 4.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

set lines 140 pages 55 verify off feedback off
col num_of_times heading 'Number|Of|Repeats'
col SQL heading 'SubString - &&chars Characters'
col username format a15 heading 'User'
@title132 'Similar SQL'
spool rep_out\&db\similar_sql&&chars
select b.username,substr(a.sql_text,1,&&chars) SQL,
count(a.sql_text) num_of_times from v$sqlarea a, dba_users b
where a.parsing_user_id=b.user_id
group by b.username,substr(a.sql_text,1,&&chars) having count(a.sql_text)>&&num_repeats
order by count(a.sql_text) desc
/
spool off
undef chars
undef num_repeats
clear columns
set lines 80 pages 22 verify on feedback on
ttitle off

Figure 4: Similar SQL report code

Figure 4 shows a simple script to determine, based on the first x characters (input when the report is executed) the number of SQL statements that are identical up to the first x characters. This shows us the repeating code in the database and helps us to track down the offending statements for correction. An example output from the similar_sql.sql script is shown in Figure 5.

Date: 02/23/05                                         Page:   1             
Time: 10:20 AM              Similar SQL               SYSTEM        
                          whoville database                                                                
                                                                                                                                            
User            SubString - 120 Characters                                                                                                  
--------------- -------------------------------------------------------
    Number                                                                                                                                 
        Of                                                                                                                                  
   Repeats                                                                                                                                 
----------                                                                                                                                 
WHOAPP         SELECT Invoices."INVOICEKEY", Invoices."CLIENTKEY", Invoices."BUYSTATUS", Invoices."DEBTORKEY", Invoices."INPUTTRANSKEY"   
      1752                                                                                                                                 
WHOAPP         SELECT DisputeCode.DisputeCode , DisputeCode.Disputed , InvDispute."ROWID" , DisputeCode."ROWID"  FROM InvDispute , Disp   
       458                                                                                                                                 
WHOAPP         SELECT Transactions.PostDate , Payments.PointsAmt , Payments.Type_ AS PmtType , Payments.Descr , Payments.FeeBasis , Pay   
       449                                                                                                                                 
SYS             SELECT SUM(Payments.Amt) AS TotPmtAmt , SUM(Payments.FeeEscrow) AS TotFeeEscrow , SUM(Payments.RsvEscrow) AS TotRsvEscro   
       428                                                                                                                                 
WHOAPP         SELECT SUM(Payments.Amt) AS TotPmtAmt, SUM(Payments.FeeEscrow) AS TotFeeEscrow, SUM(Payments.RsvEscrow) AS TotRsvEscrow    
       428                                                                                                                                  
WHOAPP         SELECT Transactions.BatchNo , Payments.Amt , Payments."ROWID" , Transactions."ROWID"  FROM Payments , Transactions WHERE   
       396                                                                                                                                  
WHOAPP         INSERT INTO Payments (PaymentKey, AcctNo, Amt, ChargeAmt, Descr, FeeBasis, FeeEarned, FeeEscrow, FeeRate, FeeTaxAmt, Hol   
       244                                                                                                                                  
WHOAPP         SELECT Clients.Name , Clients.ClientNo , Invoices.InvNo , Invoices.ClientKey AS InvClientKey , Transactions.ClientKey AS   
       244                                                                                                                                  
SYS             SELECT COUNT(*) AS RecCount , INVOICES."ROWID" , TRANSACTIONS."ROWID" , PROGRAMS."ROWID"  FROM INVOICES , TRANSACTIONS ,   
       232                                                                                                                                  
WHOAPP         SELECT COUNT(*) AS RecCount FROM INVOICES, TRANSACTIONS, PROGRAMS WHERE INVOICES.BUYTRANSKEY = TRANSACTIONS.TRANSKEY (+)   
       232                                                                                                                                 

 

Figure 5: Example output from the similar_sql.sql report script.

As you can see from Figure 5, the SQL text is pinpointed that needs fixing. Using a substring from the above SQL the V$SQLTEXT view can be used to pull an entire listing of the code.

Some may be asking: "What is a bind variable?" simply put, a bind variable is a variable inserted into the SQL code in the place of literal values. For example:

SELECT * FROM whousers WHERE first_name='ANNA';

Is not using bind variables. If we issued a second query:

SELECT * FROM whousers WHERE first_name='GRINCH';

Even though the queries are identical until the last bit where we specify the name, the Oracle query engine would treat them as two different queries. By using bind variables, as shown below, we allow Oracle to parse the statement once and reuse it many times.

SELECT * FROM whousers WHERE first_name=:whoname;

The colon in front of the variable "whoname" tells Oracle this is a bind variable that will be supplied at run time.   

So, the proper fix for non-bind variable usage is to re-write the application to use bind variables. This of course can be an expensive and time consuming process, but ultimately it provides the best fix for the problem. However, what if you can't change the code? Maybe you have time, budget or vendor constraints that prevent you from being able to do the "proper" thing. What are your options?

Oracle has provided the CURSOR_SHARING initialization variable that will automatically replace the literals in your code with bind variables. The settings for CURSOR_SHARING are EXACT (the default), FORCE, and SIMILAR.

        EXACT - The statements have to match exactly to be reusable

        FORCE - Always replace literals

        SIMILAR - Perform literal peeking and replace when it makes sense

We usually suggest the use of the SIMILAR option for CURSOR_SHARING. You can tell if cursor sharing is set to FORCE or SIMILAR by either using the SHOW PARAMETER CURSOR_SHARING command or by looking at the code in the shared pool, if you see code that looks like so:

SELECT USERNAME FROM whousers WHERE first_name=:"SYS_B_0"

This tells you that CURSOR_SHARING is set to either FORCE or SIMILAR because of the replacement variable :"SYS_B_O".


If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of my favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational