BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Non-Use of Bind Variables

Oracle Tips by Mike Ault

You’ll hear it from PL/SQL tuners, you’ll hear from SQL tuners and you’ll hear from shared pool tuners, the biggest problem in many applications is the non-use of bind variables.

Why is this an issue? Well, Oracle uses a signature generation algorithm to assign a hash value to each SQL statement based on the characters in the SQL statement. Any change in a statement (generally speaking) will result in a new hash and thus Oracle assumes it is a new statement. Each new statement must be verified, parsed and have an execution plan generated and stored.

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.

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,
   Remote 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
   Remote 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,
   Remote 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
   Remote 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,
   Remote 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
   Remote 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,
   Remote 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
   Remote 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
                            Remote DBAville database                            

users                Non-Shared SQL Shared SQL     Percent Shared
-------------------- -------------- -------------- --------------
Remote DBAVILLAGE                9,601,173     81,949,581         89.513         
PERFSTAT                  2,652,827        199,868          7.006         
Remote 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, Remote DBAVILLAGE and Remote 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.

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, Remote 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”.

SEE CODE DEPOT FOR FULL SCRIPTS


For more information on this topic, I recommend Don Burleson's latest book "Oracle Tuning: The Definitive Reference". 

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

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter