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.
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
Points to Ponder
. You can follow any responses to this entry through the
comments feed
.