| |
 |
|
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. |
 |
|
