Bind Variable Peeking  

Posted by Mawahid in

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.

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.

This entry was posted on Thursday, November 3, 2011 at 6:11 AM and is filed under . You can follow any responses to this entry through the comments feed .

0 comments

Post a Comment