In the previous post we looked at a simple scenario to illustrate SQL Monitoring. In this post I want to extend on that example to illustrate two more features of query optimisation Oracle may employ; bind variable peeking and adaptive cursor sharing.
The query we used last time was simply:
SELECT * FROM monitor_test WHERE category = :cat
EXPLAIN PLAN showed that Oracle will choose to perform a full table scan when executing the query:
SQL>EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM monitor_test
5 WHERE category = :cat
6 /
Explained.
SQL>
SQL>SELECT *
2 FROM TABLE(dbms_xplan.display())
3 /
Plan hash value: 2944410690
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 7617K| 1661 (2)| 00:00:20 |
|* 1 | TABLE ACCESS FULL| MONITOR_TEST | 200K| 7617K| 1661 (2)| 00:00:20 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CATEGORY"=TO_NUMBER(:CAT))
13 rows selected.
For the previous post we initially used a bind variable value of 5 and then a value of 1. The choice of values and the order used was actually deliberate. If we were to retry the example but use a bind variable value of 1 as the first value we get the following (you may need to flush the shared pool or restart your database first to clear the library cache):
SQL> SET LINESIZE 132
SQL> SET PAGESIZE 9999
SQL> SET ARRAYSIZE 1000
SQL>
SQL> COLUMN plan_line_id FORMAT 90
SQL> COLUMN plan_operation FORMAT A20
SQL> COLUMN plan_options FORMAT A20
SQL> COLUMN plan_object_name FORMAT A15
SQL> COLUMN plan_object_type FORMAT A10
SQL> COLUMN plan_cost FORMAT 9999990
SQL> COLUMN plan_cardinality FORMAT 9999990
SQL> COLUMN starts FORMAT 9999990
SQL> COLUMN output_rows FORMAT 9999990
SQL>
SQL> VARIABLE cat NUMBER
SQL> VARIABLE client_id VARCHAR2(30)
SQL>
SQL> EXEC :cat := 1
PL/SQL procedure successfully completed.
SQL> EXEC :client_id := 'monitor test category 1'
PL/SQL procedure successfully completed.
SQL>
SQL> EXEC dbms_session.set_identifier (:client_id)
PL/SQL procedure successfully completed.
SQL>
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL>
SQL> SELECT /*+ MONITOR */
2 *
3 FROM monitor_test
4 WHERE category = :cat
5 /
1000 rows selected.
Statistics
----------------------------------------------------------
83 recursive calls
0 db block gets
1092 consistent gets
2347 physical reads
0 redo size
43596 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL>
SQL> SET AUTOTRACE OFF
SQL>
SQL> EXEC dbms_session.set_identifier (NULL)
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT pm.plan_line_id
2 , pm.plan_operation
3 , pm.plan_options
4 , pm.plan_object_name
5 , pm.plan_object_type
6 , pm.plan_cost
7 , pm.plan_cardinality
8 , pm.starts
9 , pm.output_rows
10 FROM v$sql_monitor m
11 , v$sql_plan_monitor pm
12 WHERE m.key = pm.key
13 AND m.client_identifier = :client_id
14 ORDER BY
15 m.sql_exec_start
16 , pm.key
17 , pm.sql_id
18 , pm.plan_line_id
19 /
PLAN_LINE_ID PLAN_OPERATION PLAN_OPTIONS PLAN_OBJECT_NAM PLAN_OBJEC PLAN_COST PLAN_CARDINALITY STARTS OUTPUT_ROWS
------------ -------------------- -------------------- --------------- ---------- --------- ---------------- -------- -----------
0 SELECT STATEMENT 1 1000
1 TABLE ACCESS BY INDEX ROWID MONITOR_TEST TABLE 15 1000 1 1000
2 INDEX RANGE SCAN MONITOR_TEST_I1 INDEX 4 1000 1 1000
So, despite what our EXPLAIN PLAN output said, Oracle has not table scanned MONITOR_TEST and instead has selected to look up the rows via the index MONITOR_TEST_I1. To retrieve 1,000 rows out of 1,000,000 this is understandable but there remains the question of why it chose to do so when the EXPLAIN PLAN said otherwise. The reason is that when hard parsing a query, i.e. creating an execution plan for the first time, Oracle will “peek” at the bind variables used and attempt to optimise the query for those values. Bind variable peeking was introduced in Oracle 9i. In our scenario we used a bind variable of 1 when the query was hard parsed. Since we have a histogram on the CATEGORY column Oracle was able to deduce that it will be returning 1,000 rows (note the plan cardinality in the SQL monitor query) and so it selected an index access path instead of a full table scan. We can see the details of the histogram by querying USER_TAB_HISTOGRAMS:
SQL>COLUMN column_name FORMAT A12 SQL> SQL>SELECT column_name 2 , endpoint_value 3 , endpoint_number 4 FROM user_tab_histograms 5 WHERE table_name = 'MONITOR_TEST' 6 AND column_name = 'CATEGORY' 7 ORDER BY 8 endpoint_value 9 / COLUMN_NAME ENDPOINT_VALUE ENDPOINT_NUMBER ------------ -------------- --------------- CATEGORY 1 1000 CATEGORY 2 6000 CATEGORY 3 56000 CATEGORY 4 156000 CATEGORY 5 1000000
So, when Oracle went to parse the query and peeked at the bind variable it was able to determine that it could expect to retrieve 1,000 rows and hence selected the index access path. If we now rerun the same query but with a bind variable of 5 we get:
SQL> EXEC :cat := 5
PL/SQL procedure successfully completed.
SQL> EXEC :client_id := 'monitor test category 5'
PL/SQL procedure successfully completed.
SQL>
SQL> EXEC dbms_session.set_identifier (:client_id)
PL/SQL procedure successfully completed.
SQL>
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL>
SQL> SELECT /*+ MONITOR */
2 *
3 FROM monitor_test
4 WHERE category = :cat
5 /
844000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9390 consistent gets
5382 physical reads
0 redo size
37277063 bytes sent via SQL*Net to client
9793 bytes received via SQL*Net from client
845 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
844000 rows processed
SQL>
SQL> SET AUTOTRACE OFF
SQL>
SQL> EXEC dbms_session.set_identifier (NULL)
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT pm.plan_line_id
2 , pm.plan_operation
3 , pm.plan_options
4 , pm.plan_object_name
5 , pm.plan_object_type
6 , pm.plan_cost
7 , pm.plan_cardinality
8 , pm.starts
9 , pm.output_rows
10 FROM v$sql_monitor m
11 , v$sql_plan_monitor pm
12 WHERE m.key = pm.key
13 AND m.client_identifier = :client_id
14 ORDER BY
15 m.sql_exec_start
16 , pm.key
17 , pm.sql_id
18 , pm.plan_line_id
19 /
PLAN_LINE_ID PLAN_OPERATION PLAN_OPTIONS PLAN_OBJECT_NAM PLAN_OBJEC PLAN_COST PLAN_CARDINALITY STARTS OUTPUT_ROWS
------------ -------------------- -------------------- --------------- ---------- --------- ---------------- -------- -----------
0 SELECT STATEMENT 1 844000
1 TABLE ACCESS BY INDEX ROWID MONITOR_TEST TABLE 15 1000 1 844000
2 INDEX RANGE SCAN MONITOR_TEST_I1 INDEX 4 1000 1 844000
Hmmm, our subsequent run with a bind variable of 5 still used the same index access path. Note again that the plan cardinality is 1,000 but the actual output rows is 844,000. Clearly the 1,000 estimate is rather amiss and as such the index access path probably isn’t the best choice. This illustrates the key failing of bind variable peeking; the variables are peeked at when the query is hard parsed only and subsequent executions of the query will reuse the same plan regardless of the bind variable values. This in turn may lead to inefficient query execution as our example illustrates.
To rectify the problem with bind variable peeking Oracle introduced adaptive cursor sharing in version 11g. To demonstrate this feature, well simply rerun our previous query with a bind variable value of 5 again:
SQL>VARIABLE cat NUMBER
SQL>VARIABLE client_id VARCHAR2(30)
SQL>
SQL>EXEC :cat := 5
PL/SQL procedure successfully completed.
SQL>EXEC :client_id := 'monitor test category 5a'
PL/SQL procedure successfully completed.
SQL>
SQL>EXEC dbms_session.set_identifier (:client_id)
PL/SQL procedure successfully completed.
SQL>
SQL>SET AUTOTRACE TRACEONLY STATISTICS
SQL>
SQL>SELECT /*+ MONITOR */
2 *
3 FROM monitor_test
4 WHERE category = :cat
5 /
844000 rows selected.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6909 consistent gets
1 physical reads
0 redo size
8578594 bytes sent via SQL*Net to client
9793 bytes received via SQL*Net from client
845 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
844000 rows processed
SQL>
SQL>SET AUTOTRACE OFF
SQL>
SQL>EXEC dbms_session.set_identifier (NULL)
PL/SQL procedure successfully completed.
SQL>
SQL>SELECT pm.plan_line_id
2 , pm.plan_operation
3 , pm.plan_options
4 , pm.plan_object_name
5 , pm.plan_object_type
6 , pm.plan_cost
7 , pm.plan_cardinality
8 , pm.starts
9 , pm.output_rows
10 FROM v$sql_monitor m
11 , v$sql_plan_monitor pm
12 WHERE m.key = pm.key
13 AND m.client_identifier = :client_id
14 ORDER BY
15 m.sql_exec_start
16 , pm.key
17 , pm.sql_id
18 , pm.plan_line_id
19 /
PLAN_LINE_ID PLAN_OPERATION PLAN_OPTIONS PLAN_OBJECT_NAM PLAN_OBJEC PLAN_COST PLAN_CARDINALITY STARTS OUTPUT_ROWS
------------ -------------------- -------------------- --------------- ---------- --------- ---------------- -------- -----------
0 SELECT STATEMENT 1 844000
1 TABLE ACCESS FULL MONITOR_TEST TABLE 1657 844000 1 844000
SQL>
You’ll note that without making any changes whatsoever Oracle has changed the execution plan. The SQL Monitor query not only tells us that Oracle performed a full table scan for the query but it also correctly estimated the number of rows that the query would return. This is adaptive cursor sharing in operation. Essentially, if Oracle encounters a query that is sensitive to the specific bind variable values it will flag it as such. On execution of the query if there is a difference between the estimated cardinality and the actual output rows then, on subsequent execution, Oracle will reparse the query in an attempt to generate a better execution plan.
One downside to adaptive cursor sharing is that Oracle will only do the subsequent optimisation after it has encountered a poor execution of query. In our example, the initial run of the query with a bind variable value of 5 used an index access path, which was clearly suboptimal and Oracle flagged it as such. When the query was rerun Oracle optimised the second exeuction for the bind variable used.
We can see that adaptive cursor sharing has kicked in by looking into the query cursor in the SGA. Oracle will set the IS_BIND_SENSITIVE attribute in V$SQL for queries that it’s noted are sensitive to the values of the bind variables presented and the attribute IS_BIND_AWARE attribute for queries that are subject to adaptive cursor sharing optimisation:
SQL>SET ECHO ON
SQL>SET LINESIZE 132
SQL>SET PAGESIZE 9999
SQL>
SQL>COLUMN is_bind_sensitive FORMAT A17
SQL>COLUMN is_bind_aware FORMAT A13
SQL>
SQL>VARIABLE client_id VARCHAR2(30)
SQL>VARIABLE sql_id VARCHAR2(30)
SQL>
SQL>EXEC :client_id := 'monitor test category 5a'
PL/SQL procedure successfully completed.
SQL>
SQL>EXEC dbms_session.set_identifier (:client_id)
PL/SQL procedure successfully completed.
SQL>
SQL>BEGIN
2 SELECT sql_id
3 INTO :sql_id
4 FROM v$sql_monitor
5 WHERE client_identifier = :client_id
6 AND ROWNUM = 1;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL>SELECT *
2 FROM TABLE(dbms_xplan.display_cursor(:sql_id,NULL))
3 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9a8ttf3bd0sk6, child number 0
-------------------------------------
SELECT /*+ MONITOR */ * FROM monitor_test WHERE category =
:cat
Plan hash value: 1473390912
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| MONITOR_TEST | 1000 | 39000 | 15 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MONITOR_TEST_I1 | 1000 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CATEGORY"=:CAT)
SQL_ID 9a8ttf3bd0sk6, child number 1
-------------------------------------
SELECT /*+ MONITOR */ * FROM monitor_test WHERE category =
:cat
Plan hash value: 2944410690
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1657 (100)| |
|* 1 | TABLE ACCESS FULL| MONITOR_TEST | 844K| 31M| 1657 (1)| 00:00:20 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CATEGORY"=:CAT)
39 rows selected.
SQL>
SQL>SELECT sql_id
2 , plan_hash_value
3 , executions
4 , is_bind_sensitive
5 , is_bind_aware
6 FROM v$sql
7 WHERE sql_id = :sql_id
8 /
SQL_ID PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE
------------- --------------- ---------- ----------------- -------------
9a8ttf3bd0sk6 1473390912 2 Y N
9a8ttf3bd0sk6 2944410690 1 Y Y
From the above we can see that the index access path query was SQL_ID 9a8ttf3bd0sk6, child number 0 and had a plan hash value of 1473390912. The full table scan query was SQL_ID 9a8ttf3bd0sk6, child number 1 and had a plan hash value of 2944410690. We can see that we’ve had two executions of our index access path query and it’s flagged as bind sensitive. We can also see that we’ve had one execution of the table scan query and that this is set to bind aware.
Adaptive cursor sharing is a new feature of Oracle 11g but isn’t documented in the standard database documentation and has a series of restrictions. Oracle will need to be aware of skewed data via histograms in order to determine bind value cardinalities. Furthermore, Oracle will only optimise the query for certain types of simple predicates, such as equality, less/greater than and LIKE (from version 11.2). It will also be disabled if the query runs in parallel or contains more than 14 bind variables. Oracle Support document 740052.1 provides a better listing of the restrictions (you will need an Oracle Support account to access the document).
Both bind variable peeking and adaptive cursor sharing are designed to allow Oracle to execute queries more efficiently by altering “normal” behaviour for details picked up at run time. However, they also complicate matters when it comes to diagnosing performance problems as the execution plan obtained via EXPLAIN PLAN may not actually be the plan Oracle uses when it runs the query. The details presented in V$SQL help determining if adaptve cursor sharing is in effect (as well as some data dictionary views not discussed in this post). However, there’s no similar flag to determine if bind variable peeking influenced the creation of the execution plan. SQL monitoring permits us to look into actual query executions and can be useful diagnosing the impact that these features are having.
Download scripts shown in this post.
Mark Hoxey 