Adaptive Cursor Sharing was introduced in 11g to deal with the issues related to Bind Variable Peeking.
This post assumes that you have a basic understanding of bind peeking and ACS working.
Goal of this post to demonstrate a key problem with ACS that it detects the problem a little too late.
CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(10));
-- Insert 1 million rows, c2 is skewed
INSERT INTO t1
SELECT LEVEL c1,
CHR(ROUND(DBMS_RANDOM.NORMAL) + 77) c2
FROM DUAL
CONNECT BY LEVEL <= 1e6;
COMMIT;
CREATE INDEX t1_i1 ON t1(c1);
CREATE INDEX t1_i2 ON t1(c2);
-- gather stats at 100% with histograms
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 't1', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 254');
-- data distribution
SELECT c2, COUNT(*) FROM t1 GROUP BY c2 ORDER BY c2;
C2 COUNT(*)
---------- ----------
H 3
I 234
J 5950
K 60656
L 242076
M 382945
N 241556
O 60508
P 5838
Q 230
R 4
VAR b2 varchar2(100)
EXEC :b2 :='M'
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2;
COUNT(DISTINCTC1)
-----------------
382945
/
/
..
-- I execute this statement 20 times.
select * from table(dbms_xplan.display_cursor);
SQL_ID 1cvnvg9f3yrb0, child number 0
-------------------------------------
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2
Plan hash value: 405047221
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1814 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | VW_DAG_0 | 382K| 4861K| | 1814 (2)| 00:00:22 |
| 3 | HASH GROUP BY | | 382K| 2617K| 6016K| 1814 (2)| 00:00:22 |
|* 4 | TABLE ACCESS FULL| T1 | 382K| 2617K| | 491 (3)| 00:00:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C2"=:B2)
Full Table Scan looks good because bind value that we passed (M) is highly un-selective. (selectivity 0.38)
Now I pass the most selective bind value which is 'H' (selectivity 0.000003).
I am expecting ACS to kick in so that index t1_i2 could be used.
Index Scan on t1_i2 would be the optimal plan here because the current bind value 'H' is highly selective.
EXEC :b2 :='H'
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2;
COUNT(DISTINCTC1)
-----------------
3
select * from table(dbms_xplan.display_cursor);
SQL_ID 1cvnvg9f3yrb0, child number 0
-------------------------------------
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2
Plan hash value: 405047221
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1814 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | VW_DAG_0 | 382K| 4861K| | 1814 (2)| 00:00:22 |
| 3 | HASH GROUP BY | | 382K| 2617K| 6016K| 1814 (2)| 00:00:22 |
|* 4 | TABLE ACCESS FULL| T1 | 382K| 2617K| | 491 (3)| 00:00:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C2"=:B2)
Note that ACS did not detect the problem as yet (on the first execution). You still see a full table scan.I execute this statement again few times hoping that ACS would kick in and index would be used.
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2;
/
/
/
select * from table(dbms_xplan.display_cursor);
SQL_ID 1cvnvg9f3yrb0, child number 0
-------------------------------------
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2
Plan hash value: 405047221
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1814 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | VW_DAG_0 | 382K| 4861K| | 1814 (2)| 00:00:22 |
| 3 | HASH GROUP BY | | 382K| 2617K| 6016K| 1814 (2)| 00:00:22 |
|* 4 | TABLE ACCESS FULL| T1 | 382K| 2617K| | 491 (3)| 00:00:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C2"=:B2)
ACS didn't kick in yet !
/
/
<< 8th execution >>
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2;
SQL_ID 1cvnvg9f3yrb0, child number 1
-------------------------------------
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2
Plan hash value: 1964376958
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | VW_DAG_0 | 3 | 39 | 5 (20)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 21 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_I2 | 3 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C2"=:B2)
Finally, I see an index range scan. It seems to me that ACS was working behind the scenes and finally detects the problem on the 8th execution which I think is a little too late.
Now a new child cursor has been created which is bind aware.
SQL> select is_bind_sensitive,is_bind_aware,executions,child_number from v$sql where sql_id='1cvnvg9f3yrb0';
I I EXECUTIONS CHILD_NUMBER
- - ---------- ------------
Y N 27 0
Y Y 1 1
SQL> select SQL_ID,CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH FROM v$sql_cs_selectivity where sql_id='1cvnvg9f3yrb0';
SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------- ------------ ---------------------------------------- ---------- ---------- ----------
1cvnvg9f3yrb0 1 =B2 0 0.000003 0.000003
CONCLUSION
Let me summarise some of the limitations of ACS.
1. So, it takes a while before ACS kicks in. It needs a certain number of executions before ACS identifies a problem
and corrects it. This actually depends on the number of child 0 executions and may be some other factors.
I did some tests and this is what I found.
-----------------------------------------------------------------
CHILD 0 (FTS) CHILD 1 (Index Range Scan)
Executions. Created after n executions.
-----------------------------------------------------------------
1 | 2
10 | 5
20 | 8
50 | 18
100 | 35
500 | 168
1000 | ACS NEVER KICKS IN
-----------------------------------------------------------------
As you can see, there is a linear growth on the number of executions that ACS needs before it can make a cursor bind aware
and create a new child cursor. There is even more interesting thing I noticed in my tests is that when I executed the child 0
cursor 1000 times and then changed my bind value to be most selective, ACS never corrected the problem. I do not know if that is a software bug or an expected behaviour.
2. ACS does not work for SQL statements called from inside the PL SQL block. However, It does seem to work after you set session_cached_cursors to 0.
3. A Cursor is not marked bind sensitive if the number of bind variables exceed 14.
4. ACS does not work for predicates of the form col <op> func(:bind). For e.g the statement below will not be marked bind sensitive.
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = UPPER(:b2)
Despite all the limitations ACS still is a wonderful feature and is a big step forward in dealing with the bind peeking
problems. ACS does not solve all your problems but certainly solves some of them !
This post assumes that you have a basic understanding of bind peeking and ACS working.
Goal of this post to demonstrate a key problem with ACS that it detects the problem a little too late.
CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(10));
-- Insert 1 million rows, c2 is skewed
INSERT INTO t1
SELECT LEVEL c1,
CHR(ROUND(DBMS_RANDOM.NORMAL) + 77) c2
FROM DUAL
CONNECT BY LEVEL <= 1e6;
COMMIT;
CREATE INDEX t1_i1 ON t1(c1);
CREATE INDEX t1_i2 ON t1(c2);
-- gather stats at 100% with histograms
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 't1', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 254');
-- data distribution
SELECT c2, COUNT(*) FROM t1 GROUP BY c2 ORDER BY c2;
C2 COUNT(*)
---------- ----------
H 3
I 234
J 5950
K 60656
L 242076
M 382945
N 241556
O 60508
P 5838
Q 230
R 4
VAR b2 varchar2(100)
EXEC :b2 :='M'
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2;
COUNT(DISTINCTC1)
-----------------
382945
/
/
..
-- I execute this statement 20 times.
select * from table(dbms_xplan.display_cursor);
SQL_ID 1cvnvg9f3yrb0, child number 0
-------------------------------------
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2
Plan hash value: 405047221
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1814 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | VW_DAG_0 | 382K| 4861K| | 1814 (2)| 00:00:22 |
| 3 | HASH GROUP BY | | 382K| 2617K| 6016K| 1814 (2)| 00:00:22 |
|* 4 | TABLE ACCESS FULL| T1 | 382K| 2617K| | 491 (3)| 00:00:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C2"=:B2)
Full Table Scan looks good because bind value that we passed (M) is highly un-selective. (selectivity 0.38)
Now I pass the most selective bind value which is 'H' (selectivity 0.000003).
I am expecting ACS to kick in so that index t1_i2 could be used.
Index Scan on t1_i2 would be the optimal plan here because the current bind value 'H' is highly selective.
EXEC :b2 :='H'
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2;
COUNT(DISTINCTC1)
-----------------
3
select * from table(dbms_xplan.display_cursor);
SQL_ID 1cvnvg9f3yrb0, child number 0
-------------------------------------
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2
Plan hash value: 405047221
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1814 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | VW_DAG_0 | 382K| 4861K| | 1814 (2)| 00:00:22 |
| 3 | HASH GROUP BY | | 382K| 2617K| 6016K| 1814 (2)| 00:00:22 |
|* 4 | TABLE ACCESS FULL| T1 | 382K| 2617K| | 491 (3)| 00:00:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C2"=:B2)
Note that ACS did not detect the problem as yet (on the first execution). You still see a full table scan.I execute this statement again few times hoping that ACS would kick in and index would be used.
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2;
/
/
/
select * from table(dbms_xplan.display_cursor);
SQL_ID 1cvnvg9f3yrb0, child number 0
-------------------------------------
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2
Plan hash value: 405047221
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1814 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | VW_DAG_0 | 382K| 4861K| | 1814 (2)| 00:00:22 |
| 3 | HASH GROUP BY | | 382K| 2617K| 6016K| 1814 (2)| 00:00:22 |
|* 4 | TABLE ACCESS FULL| T1 | 382K| 2617K| | 491 (3)| 00:00:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C2"=:B2)
ACS didn't kick in yet !
/
/
<< 8th execution >>
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2;
SQL_ID 1cvnvg9f3yrb0, child number 1
-------------------------------------
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = :b2
Plan hash value: 1964376958
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | VW_DAG_0 | 3 | 39 | 5 (20)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 21 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_I2 | 3 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C2"=:B2)
Finally, I see an index range scan. It seems to me that ACS was working behind the scenes and finally detects the problem on the 8th execution which I think is a little too late.
Now a new child cursor has been created which is bind aware.
SQL> select is_bind_sensitive,is_bind_aware,executions,child_number from v$sql where sql_id='1cvnvg9f3yrb0';
I I EXECUTIONS CHILD_NUMBER
- - ---------- ------------
Y N 27 0
Y Y 1 1
SQL> select SQL_ID,CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH FROM v$sql_cs_selectivity where sql_id='1cvnvg9f3yrb0';
SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------- ------------ ---------------------------------------- ---------- ---------- ----------
1cvnvg9f3yrb0 1 =B2 0 0.000003 0.000003
CONCLUSION
Let me summarise some of the limitations of ACS.
1. So, it takes a while before ACS kicks in. It needs a certain number of executions before ACS identifies a problem
and corrects it. This actually depends on the number of child 0 executions and may be some other factors.
I did some tests and this is what I found.
-----------------------------------------------------------------
CHILD 0 (FTS) CHILD 1 (Index Range Scan)
Executions. Created after n executions.
-----------------------------------------------------------------
1 | 2
10 | 5
20 | 8
50 | 18
100 | 35
500 | 168
1000 | ACS NEVER KICKS IN
-----------------------------------------------------------------
As you can see, there is a linear growth on the number of executions that ACS needs before it can make a cursor bind aware
and create a new child cursor. There is even more interesting thing I noticed in my tests is that when I executed the child 0
cursor 1000 times and then changed my bind value to be most selective, ACS never corrected the problem. I do not know if that is a software bug or an expected behaviour.
2. ACS does not work for SQL statements called from inside the PL SQL block. However, It does seem to work after you set session_cached_cursors to 0.
3. A Cursor is not marked bind sensitive if the number of bind variables exceed 14.
4. ACS does not work for predicates of the form col <op> func(:bind). For e.g the statement below will not be marked bind sensitive.
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = UPPER(:b2)
Despite all the limitations ACS still is a wonderful feature and is a big step forward in dealing with the bind peeking
problems. ACS does not solve all your problems but certainly solves some of them !
No comments:
Post a Comment