Adaptive Cursor Sharing in Oracle Database 11g Release 1:
Adaptive Cursor Sharing (ACS) has been introduced in Oracle 11g Release1 to address the potential threat of cursor sharing and bind variables: The usage of bind variables can be a possible threat if different values of bind variables are executed using the same execution plan, but actually would require different execution plans to prevent poor performing executions.
So ACS allows Oracle to detect such problems and selectively “unshare” cursors, which means that Oracle creates one or more additional child cursors for different (ranges) of bind values.
In real time environment DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans. This is because the optimizer peeks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.
Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration. The below given practical will provide you an example of adaptive cursor sharing.
Practical Approach:
First we create and populate a test table.
SQL> connect paw_test_user/paw_test_pass
Connected.
SQL> CREATE TABLE paw_acs_test_tab (
id NUMBER,
record_type NUMBER,
description VARCHAR2(50),
CONSTRAINT paw_acs_test_tab_pk PRIMARY KEY (id)
);
Table created.
SQL> CREATE INDEX paw_acs_test_tab_record_type_i ON paw_acs_test_tab(record_type);
Index created.
SQL>
DECLARE
TYPE t_paw_acs_test_tab IS TABLE OF paw_acs_test_tab%ROWTYPE;
l_tab t_paw_acs_test_tab := t_paw_acs_test_tab();
BEGIN
FOR i IN 1 .. 100000 LOOP
l_tab.extend;
IF MOD(i,2)=0 THEN
l_tab(l_tab.last).record_type := 2;
ELSE
l_tab(l_tab.last).record_type := i;
END IF;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := ‘Description for ‘ || i;
END LOOP;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO paw_acs_test_tab VALUES l_tab(i);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, ‘paw_acs_test_tab’, method_opt=>’for all indexed columns size skewonly’, cascade=>TRUE);
PL/SQL procedure successfully completed.
The data in the RECORD_TYPE column is skewed, as shown by the presence of a histogram against the column.
SQL> SELECT column_name, histogram FROM user_tab_cols WHERE table_name = ‘PAW_ACS_TEST_TAB’;
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
ID NONE
RECORD_TYPE HEIGHT BALANCED
DESCRIPTION NONETest Adaptive Cursor Sharing:
Next, we query the table and limit the rows returned based on the RECORD_TYPE column with a literal value of “1”.
SQL> SET LINESIZE 200
SQL> SELECT MAX(id) FROM paw_acs_test_tab WHERE record_type = 1;
MAX(ID)
----------
1SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID ajturnryqf3rw, child number 0
-------------------------------------
SELECT MAX(id) FROM paw_acs_test_tab WHERE record_type = 1
Plan hash value: 1556772097
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PAW_ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PAW_ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RECORD_TYPE"=1)
20 rows selected.This query has used the index as we would expect. Now we repeat the query, but this time use a bind variable.
SQL> VARIABLE l_record_type NUMBER
SQL> EXEC :l_record_type := 1;
PL/SQL procedure successfully completed.
SQL> SELECT MAX(id) FROM paw_acs_test_tab WHERE record_type = :l_record_type;
MAX(ID)
----------
1SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID g38xapnafzxb7, child number 0
SELECT MAX(id) FROM paw_acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 1556772097
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PAW_ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PAW_ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RECORD_TYPE"=:L_RECORD_TYPE)
20 rows selected.So we ran what amounted to the same query, and got the same result and execution plan. The optimizer picked an execution plan that it thinks is optimum for query by peeking at the value of the bind variable. The only problem is, this would be totally the wrong thing to do for other bind values.
SQL> VARIABLE l_record_type NUMBER;
SQL> EXEC :l_record_type := 2;
PL/SQL procedure successfully completed.
SQL> SELECT MAX(id) FROM paw_acs_test_tab WHERE record_type = :l_record_type;
MAX(ID)
----------
100000SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g38xapnafzxb7, child number 0
SELECT MAX(id) FROM paw_acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 1556772097
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PAW_ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PAW_ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RECORD_TYPE"=:L_RECORD_TYPE)
20 rows selected.If we look at the V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as ‘Y’, so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as ‘N’, so Oracle has not acted on this yet.
SQL> SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_text = ‘SELECT MAX(id) FROM paw_acs_test_tab WHERE record_type = :l_record_type’;
SQL_ID CHILD_NUMBER I I
------------- ------------ - -
g38xapnafzxb7 0 Y NIf we run the statement using the second bind variable again, we can see that Oracle has decided to use an alternate, more efficient plan for this statement.
SQL> VARIABLE l_record_type NUMBER;
SQL> EXEC :l_record_type := 2;
PL/SQL procedure successfully completed.
SQL> SELECT MAX(id) FROM paw_acs_test_tab WHERE record_type = :l_record_type;
MAX(ID)
----------
100000SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID g38xapnafzxb7, child number 1
-------------------------------------
SELECT MAX(id) FROM paw_acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3516695453
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 137 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| PAW_ACS_TEST_TAB | 49213 | 432K| 137 (1)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RECORD_TYPE"=:L_RECORD_TYPE)
19 rows selected.This change in behavior is also reflected in the V$SQL view, which now has the IS_BIND_AWARE column maked as “Y”.
SQL> SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_text = ‘SELECT MAX(id) FROM paw_acs_test_tab WHERE record_type = :l_record_type’;
SQL_ID CHILD_NUMBER I I
------------- ------------ - -
g38xapnafzxb7 0 Y N
g38xapnafzxb7 1 Y Y
2 rows selected.Information about the cursor sharing histograms, statistics and selectivity is displayed using the V$SQL_CS_HISTOGRAM V$SQL_CS_STATISTICS and V$SQL_CS_SELECTIVITY views respectively.
SQL> SELECT * FROM v$sql_cs_histogram WHERE sql_id = ‘g38xapnafzxb7’;
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0000000083D97248 351270247 g38xapnafzxb7 1 0 0
0000000083D97248 351270247 g38xapnafzxb7 1 1 1
0000000083D97248 351270247 g38xapnafzxb7 1 2 0
0000000083D97248 351270247 g38xapnafzxb7 0 0 1
0000000083D97248 351270247 g38xapnafzxb7 0 1 1
0000000083D97248 351270247 g38xapnafzxb7 0 2 0
6 rows selected.SQL> SELECT * FROM v$sql_cs_statistics WHERE sql_id = ‘g38xapnafzxb7’;
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
0000000083D97248 351270247 g38xapnafzxb7 1 2064090006 Y 1 50001 500 0
0000000083D97248 351270247 g38xapnafzxb7 0 2342552567 Y 1 3 3 0
2 rows selected.SQL> SELECT * FROM v$sql_cs_selectivity WHERE sql_id = ‘g38xapnafzxb7’;
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
0000000083D97248 351270247 g38xapnafzxb7 1 =L_RECORD_T 0 0.442913 0.541339
1 row selected.Thank you for reading …..This is Airy…Enjoy 🙂