November 2003

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

November 2003
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Bind Variable Peeking

You’re invited to submit your Oracle-related questions to us at askdbspecialists11@dbspecialists.com. Include your name, telephone number and problem description. If your question gets published, you’ll receive a free copy of our reference poster on Oracle Wait Events. Peter Koletzke’s Oracle Stuff

This month’s question comes from Tracy F. in Boston, MA: What is “bind variable peeking” and can we now abandon literals when querying data with a skewed distribution?

Ian Jones of the Database Specialists team responds: To answer this question we need a little background. Generally in OLTP systems, i.e. systems characterized by lots of short duration SQL statements, we want to use bind variables so as to minimize hard parses and maximize SQL reuse in the shared pool. Unfortunately, prior to Oracle 9i there was an important exception to this general rule. The exception occurred when we needed to search a column that contained relatively few heavily skewed data values. After indexing such a column and collecting optimizer statistics, Oracle was able to choose different execution plans when searching for different column values, based on their relative occurrence. Let us illustrate this:

CREATE TABLE Check_Status
(
Check_No  INTEGER      NOT NULL,
Status    VARCHAR2(10) NOT NULL
);

CREATE INDEX Skewed_Status ON Check_Status (Status);

BEGIN
  FOR l_check_no IN 1..10000 LOOP
    -- Lets cancel 1 in 100 checks    
    IF MOD (l_check_no, 100) = 0 THEN
      INSERT INTO Check_Status (Check_No, Status) 
      VALUES (l_check_no, 'Cancelled');
    ELSE
      INSERT INTO Check_Status (Check_No, Status)
      VALUES (l_check_no, 'Processed');
    END IF;
  END LOOP;
END;

ANALYZE TABLE Check_Status COMPUTE STATISTICS;
ANALYZE TABLE Check_Status COMPUTE STATISTICS FOR COLUMNS Status;

SELECT Status, COUNT(*) FROM Check_Status GROUP BY Status;

STATUS      COUNT(*)
----------- --------
Cancelled        100
Processed       9900

The optimizer now has enough data available to choose the optimal execution plan:

SELECT * FROM Check_Status WHERE Status = 'Cancelled';

Operation                                   Rows  Bytes  Cost
SELECT STATEMENT Optimizer Mode=CHOOSE       100   1200     2
  TABLE ACCESS BY INDEX ROWID CHECK_STATUS   100   1200     2
    INDEX RANGE SCAN  SKEWED_STATUS          100            1

SELECT * FROM Check_Status WHERE Status = 'Processed';

Operation                                   Rows  Bytes  Cost
SELECT STATEMENT Optimizer Mode=CHOOSE      9900 118800     6
  TABLE ACCESS FULL  CHECK_STATUS           9900 118800     6 

As the above plans show, the optimizer is clever enough to use the index range scan when we are looking for ‘Cancelled’ checks and the full table scan when looking for ‘Processed’ checks. This existing functionality handles skewed data well when literals are used but does not fare so well when bind variables are used instead of literals.

Oracle 9i introduced a new feature known as “bind variable peeking.” This allows the optimizer to look inside a bind variable when a statement is hard parsed (i.e. initially when it is loaded into the shared pool). Unfortunately, this feature does not allow us to eliminate the literals from the above code. In fact, doing so would be a bad idea because we would lose one of our executions plans. To illustrate this we use the following commands in an Oracle 9i environment to query the table using a bind variable set to ‘Cancelled’ and then look at the tkprof output.

 

VARIABLE l_status VARCHAR2(20);
EXECUTE :l_status := 'Cancelled';
SELECT * FROM Check_Status WHERE Status = :l_status;

call     count       cpu    elapsed     disk    query  current      rows
------- ------  -------- ---------- -------- -------- --------  --------
Parse        1      0.36       0.36        0        0        0         0
Execute      1      0.00       0.00        0        0        0         0
Fetch        8      0.00       0.04       29       40        0       100
------- ------  -------- ---------- -------- -------- --------  --------
total       10      0.36       0.40       29       40        0       100

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 57  

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  TABLE ACCESS BY INDEX ROWID OBJ#(32937)
    100   INDEX RANGE SCAN OBJ#(32939)

Looking at the above output, we see that this statement was hard parsed (misses in library cache during parse is 1) and Oracle correctly performed the index range scan based on the value of the bind variable ‘Cancelled’. However if we now query for the ‘Processed’ checks, tkprof shows us the following:

EXECUTE :l_status := 'Processed';
SELECT * FROM Check_Status WHERE Status = :l_status;

call     count       cpu    elapsed     disk    query  current      rows
------- ------  -------- ---------- -------- -------- --------  --------
Parse        1      0.00       0.00        0        0        0         0
Execute      1      0.00       0.00        0        0        0         0
Fetch      661      0.06       0.26       27     1375        0      9900
------- ------  -------- ---------- -------- -------- --------  --------
total      663      0.06       0.26       27     1375        0      9900

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 57

Rows     Row Source Operation
-------  ---------------------------------------------------
   9900  TABLE ACCESS BY INDEX ROWID OBJ#(32937)
   9900   INDEX RANGE SCAN OBJ#(32939)

Looking at the above output, we see that this statement was soft parsed (misses in library cache during parse is 0) and Oracle reused the previous execution plan which is definitely sub-optimal in this case. A full table scan would have been better here, as we saw when literals were used.

In summary, bind variable peeking in Oracle 9i does not help us when we need different execution plans for different values of a bind variable. Bind variable peeking allows the optimizer to take the value of a bind variable into account when first generating an execution plan for a SQL statement. If we know that the data distribution is heavily skewed and that future executions of the SQL statement may involve different data distributions for the bind variable value, we must still resort to using literals to get the best performance.

 

 

Well-known author Peter Koletzke’s website contains information, utilities, and links related to Oracle database design and development. Koletzke is the author of Oracle9i JDeveloper Handbook, Oracle Developer Advanced Forms & Reports, and The Oracle Designer Handbook. On the site, you’ll find lots of resources including conference papers, tips and techniques, and plenty of useful links. Check it out at http://ourworld.compuserve.com/homepages/Peter_Koletzke. Stewart’s Oracle Links, Tips, and Tricks

 

Oracle DBA Stewart McGlaughlin has spent a lot of time developing a handy website with lots of resource links. You’ll find sections on data warehousing, products, utilities, scripts, listservers/forums, and free magazines. It’s all at http://www.stewartmc.com/oracle. OraSnap, a Free Performance Utility

You might also want to check out the scripts he has posted in his Tips section at http://www.stewartmc.com/oracle/tips.html. Here’s a quick sampling:

  • Create primary keys in parallel
  • Using the Export and Import utilities with a named pipe and compress
  • Decreasing the size of a column which contains data
  • Long deletes (committing every x number of records)
  • Identifying and removing duplicate records

 

Also created by Stewart McGlaughlin is OraSnap, a free performance utility. OraSnap is a collection of scripts that run against an Oracle database to display performance information. The scripts are compatible with Oracle from 7.3 on up to 9i, and the information they provide can help you tune and optimize your databases. The scripts are not too different from the typical scripts that experienced Oracle DBAs are already familiar with, but what makes OraSnap different is that the script output is presented in HTML that you view with a web browser.

Each report includes a “Notes” section that presents extra information about the statistics gathered by the script, and also the source code that created the report output.

You’ll find out more information on OraSnap at http://www.stewartmc.com/oracle/orasnap/index.html.

 

Leave a Reply

Your email address will not be published. Required fields are marked *