{"id":586,"date":"2009-03-12T07:01:40","date_gmt":"2009-03-12T05:01:40","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=586"},"modified":"2009-03-13T00:11:15","modified_gmt":"2009-03-12T22:11:15","slug":"intelligent-aka-adaptive-cursor-sharing-problem","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2009\/03\/12\/intelligent-aka-adaptive-cursor-sharing-problem\/","title":{"rendered":"Intelligent (aka adaptive) Cursor Sharing Problem"},"content":{"rendered":"

Today, at the last day of Hotsos Symposium 2009<\/a> Ric van Dyke presented on “Intelligent (aka adaptive) Cursor Sharing”, a new feature of Oracle Database 11g. The feature was created to fix the shortcomings of bind peeking on columns with histograms. Tom Kyte presented a few years ago on this topic when he talked about the impact of the wheather on Oracle database performance.<\/p>\n

During the very interesting presentation Ric presented with a demo how this feature works but also showed a nasty side effect. He tested it on a million row table with a column “object type”, which was skewed. He bound several different values to the object_type bind variable causing 2 different execution plans to be used for the same sql_id. The problem with it was that he ended up with dozens of child cursors showing V$SQL.IS_SHARABLE => N. <\/p>\n

It was not known whether this is a bug or whether the whole concept is flawed. I searched on metalink and found the appropriate document:<\/p>\n

\nBug 6644714 – High number of child cursors with adaptive cursor sharing
\n<\/strong>This issue is fixed in\t<\/p>\n

* 11.2 (Future Release)
\n * 11.1.0.7 (Server Patch Set)
\nLots of child cursors may be produced with adaptive cursor sharing enabled,
\nsometimes with overlapping or same bind value selectivity ranges (in v$sql_cs_selectivity).
\nThis problem shows as LOAD_OPTIMIZER_STATS=’Y’ for many of the cursors in V$SQL_SHARED_CURSOR.<\/p>\n

– and – <\/p>\n

Bug 7213010 – Adaptive cursor sharing generates lots of child cursors<\/strong><\/p>\n

This issue is fixed in\t<\/p>\n

* 11.2 (Future Release)
\n * 11.1.0.7 (Server Patch Set) <\/p>\n

Lots of child cursors may be produced for a query with bind variables
\nappearing in at least two simple relational predicates
\n(eg. col :b). <\/p>\n

v$sql_cs_selectivity shows that there are multiple equivalent \/ overlapping
\nranges for a single cursor. The number of ranges gets bigger and bigger
\nas the query is run more times.\n<\/p><\/blockquote>\n

I then tried to verify if the problem indeed is gone in 11.1.0.7 and found out, that it isn\u00b4t.<\/p>\n

Here is my testcase:<\/p>\n

\r\nSQL> @\/tmp\/setup.sql\r\nConnected.\r\nSQL> drop table tab1 purge;\r\n\r\nTable dropped.\r\n\r\nSQL> create table tab1 (a int,\r\n  2                     b varchar2(20),\r\n  3                                     padding varchar2(200))\r\n  4  tablespace USERS;\r\n\r\nTable created.\r\n\r\nSQL>\r\nSQL> drop sequence myid;\r\n\r\nSequence dropped.\r\n\r\nSQL> create sequence myid cache 1000;\r\n\r\nSequence created.\r\n\r\nSQL>\r\nSQL> begin\r\n  2  for i in 1..100000\r\n  3  loop\r\n  4          insert into tab1 values (myid.nextval,\r\n  5                                                           'AAAAAAAAAA',\r\n  6                                                           rpad('*',200,'*'));\r\n  7  end loop;\r\n  8\r\n  9  for i in 1..1000\r\n 10  loop\r\n 11          insert into tab1 values (myid.nextval,\r\n 12                                                           'BBBBBBBBBB',\r\n 13                                                           rpad('*',200,'*'));\r\n 14  end loop;\r\n 15\r\n 16  for i in 1..1000\r\n 17  loop\r\n 18          insert into tab1 values (myid.nextval,\r\n 19                                                           'CCCCCCCCCC',\r\n 20                                                           rpad('*',200,'*'));\r\n 21  end loop;\r\n 22\r\n 23  for i in 1..100\r\n 24  loop\r\n 25          insert into tab1 values (myid.nextval,\r\n 26                                                           'DDDDDDDDDD',\r\n 27                                                           rpad('*',200,'*'));\r\n 28  end loop;\r\n 29\r\n 30  for i in 1..10\r\n 31  loop\r\n 32          insert into tab1 values (myid.nextval,\r\n 33                                                           'EEEEEEEEEE',\r\n 34                                                           rpad('*',200,'*'));\r\n 35  end loop;\r\n 36\r\n 37  insert into tab1 values (myid.nextval,\r\n 38                                                           'FFFFFFFFFF',\r\n 39                                                           rpad('*',200,'*'));\r\n 40\r\n 41  commit;\r\n 42  end;\r\n 43  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>\r\nSQL> create index tab1_idx1 on tab1 (b);\r\n\r\nIndex created.\r\n\r\nSQL>\r\nSQL> begin dbms_stats.gather_table_stats(ownname => 'MDECKER',\r\n  2                                          tabname => 'TAB1',\r\n  3                                          method_opt =>'FOR ALL COLUMNS SIZE 254',\r\n  4                                          cascade=> TRUE,\r\n  5                                          estimate_percent => 100,\r\n  6                                          no_invalidate => FALSE);\r\n  7  end;\r\n  8  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>\r\nSQL> set lines 300\r\nSQL> col table_name format a10\r\nSQL> col owner format a8\r\nSQL> select owner, table_name,num_rows, blocks, round((blocks*8192)\/1024\/1024) as MB\r\n  2  from dba_tables where table_name = 'TAB1' and owner = 'MDECKER';\r\n\r\nOWNER    TABLE_NAME   NUM_ROWS     BLOCKS         MB\r\n-------- ---------- ---------- ---------- ----------\r\nMDECKER  TAB1           102111       3268         26\r\n\r\nSQL>\r\nSQL> prompt \"Press Enter to continue\"\r\n\"Press Enter to continue\"\r\nSQL> pause\r\n\r\nSQL>\r\nSQL> \/*\r\nSQL> OWNER       TABLE_NAME   NUM_ROWS  BLOCKS         MB\r\nSQL> -------- ---------- ---------- ---------- ----------\r\nSQL> MDECKER  TAB1           102111       3268         26\r\nSQL>\r\nSQL> *\/\r\nSQL>\r\nSQL>\r\nSQL>\r\nSQL> select b , count(*) from mdecker.tab1 group by b order by 1 desc;\r\n\r\nB                      COUNT(*)\r\n-------------------- ----------\r\nFFFFFFFFFF                    1\r\nEEEEEEEEEE                   10\r\nDDDDDDDDDD                  100\r\nCCCCCCCCCC                 1000\r\nBBBBBBBBBB                 1000\r\nAAAAAAAAAA               100000\r\n\r\n6 rows selected.\r\n\r\nSQL>\r\nSQL> \/*\r\nSQL> B                      COUNT(*)\r\nSQL> -------------------- ----------\r\nSQL> FFFFFFFFFF                    1\r\nSQL> EEEEEEEEEE                   10\r\nSQL> DDDDDDDDDD                  100\r\nSQL> CCCCCCCCCC                 1000\r\nSQL> BBBBBBBBBB                 1000\r\nSQL> AAAAAAAAAA               100000\r\nSQL>\r\nSQL> 6 rows selected.\r\nSQL>\r\nSQL> *\/\r\nSQL>\r\nSQL> prompt \"Press Enter to continue\"\r\n\"Press Enter to continue\"\r\nSQL> pause\r\n\r\nSQL> select num_buckets, histogram from dba_tab_col_statistics where table_name = 'TAB1' and column_name = 'B';\r\n\r\nNUM_BUCKETS HISTOGRAM\r\n----------- ---------------\r\n          6 FREQUENCY\r\n\r\nSQL>\r\nSQL> \/*\r\nSQL> NUM_BUCKETS HISTOGRAM\r\nSQL> ----------- ---------------\r\nSQL>           6 FREQUENCY\r\nSQL> *\/\r\nSQL>\r\nSQL>\r\nSQL>\r\nSQL>\r\nSQL>\r\nSQL> show user\r\nUSER is \"MDECKER\"\r\nSQL> @\/tmp\/sql1.txt\r\nSQL> set lines 300\r\nSQL> set echo on\r\nSQL> set pages 10000\r\nSQL> col bind format a120\r\nSQL> prompt \"Press enter to run\"\r\n\"Press enter to run\"\r\nSQL> pause\r\n\r\nSQL>\r\nSQL> variable myvar varchar2(20)\r\nSQL> exec :myvar := 'AAAAAAAAAA';\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> set termout off\r\nSQL> select * from table(dbms_xplan.display_cursor());\r\n\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------\r\nSQL_ID  fhtgpcv2m2j4s, child number 0\r\n-------------------------------------\r\nSELECT \/* MD_TESTCASE *\/ * from tab1 where b = :myvar\r\n\r\nPlan hash value: 2211052296\r\n\r\n--------------------------------------------------------------------------\r\n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |\r\n--------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT  |      |       |       |   889 (100)|          |\r\n|*  1 |  TABLE ACCESS FULL| TAB1 |   100K|    20M|   889   (1)| 00:00:11 |\r\n--------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - filter(\"B\"=:MYVAR)\r\n\r\n\r\n18 rows selected.\r\n\r\nSQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from\r\n  2   v$sql where sql_id = 'fhtgpcv2m2j4s';\r\n\r\nSQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER\r\n------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------\r\nfhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0\r\n\r\nSQL>\r\nSQL> prompt \"Press enter to run\"\r\n\"Press enter to run\"\r\nSQL> pause\r\n\r\nSQL> exec :myvar := 'BBBBBBBBBB';\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> set termout off\r\nSQL> select * from table(dbms_xplan.display_cursor());\r\n\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------\r\nSQL_ID  fhtgpcv2m2j4s, child number 0\r\n-------------------------------------\r\nSELECT \/* MD_TESTCASE *\/ * from tab1 where b = :myvar\r\n\r\nPlan hash value: 2211052296\r\n\r\n--------------------------------------------------------------------------\r\n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |\r\n--------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT  |      |       |       |   889 (100)|          |\r\n|*  1 |  TABLE ACCESS FULL| TAB1 |   100K|    20M|   889   (1)| 00:00:11 |\r\n--------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - filter(\"B\"=:MYVAR)\r\n\r\n\r\n18 rows selected.\r\n\r\nSQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from\r\n  2   v$sql where sql_id = 'fhtgpcv2m2j4s';\r\n\r\nSQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER\r\n------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------\r\nfhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0\r\n\r\nSQL>\r\nSQL> exec :myvar := 'CCCCCCCCCC';\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> prompt \"Press enter to run\"\r\n\"Press enter to run\"\r\nSQL> pause\r\n\r\nSQL> set termout off\r\nSQL> select * from table(dbms_xplan.display_cursor());\r\n\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------\r\nSQL_ID  fhtgpcv2m2j4s, child number 1\r\n-------------------------------------\r\nSELECT \/* MD_TESTCASE *\/ * from tab1 where b = :myvar\r\n\r\nPlan hash value: 1918680838\r\n\r\n-----------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-----------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |           |       |       |    36 (100)|          |\r\n|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |  1000 |   210K|    36   (0)| 00:00:01 |\r\n|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |  1000 |       |     4   (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   2 - access(\"B\"=:MYVAR)\r\n\r\n\r\n19 rows selected.\r\n\r\nSQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from\r\n  2   v$sql where sql_id = 'fhtgpcv2m2j4s';\r\n\r\nSQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER\r\n------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------\r\nfhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0\r\nfhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1\r\n\r\nSQL>\r\nSQL> exec :myvar := 'DDDDDDDDDD';\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> prompt \"Press enter to run\"\r\n\"Press enter to run\"\r\nSQL> pause\r\n\r\nSQL> set termout off\r\nSQL> select * from table(dbms_xplan.display_cursor());\r\n\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------\r\nSQL_ID  fhtgpcv2m2j4s, child number 2\r\n-------------------------------------\r\nSELECT \/* MD_TESTCASE *\/ * from tab1 where b = :myvar\r\n\r\nPlan hash value: 1918680838\r\n\r\n-----------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-----------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |           |       |       |     5 (100)|          |\r\n|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |   100 | 21600 |     5   (0)| 00:00:01 |\r\n|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |   100 |       |     1   (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   2 - access(\"B\"=:MYVAR)\r\n\r\n\r\n19 rows selected.\r\n\r\nSQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from\r\n  2   v$sql where sql_id = 'fhtgpcv2m2j4s';\r\n\r\nSQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER\r\n------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------\r\nfhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0\r\nfhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1\r\nfhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2\r\n\r\nSQL>\r\nSQL> exec :myvar := 'EEEEEEEEEE';\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> prompt \"Press enter to run\"\r\n\"Press enter to run\"\r\nSQL> pause\r\n\r\nSQL> set termout off\r\nSQL> select * from table(dbms_xplan.display_cursor());\r\n\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------\r\nSQL_ID  fhtgpcv2m2j4s, child number 3\r\n-------------------------------------\r\nSELECT \/* MD_TESTCASE *\/ * from tab1 where b = :myvar\r\n\r\nPlan hash value: 1918680838\r\n\r\n-----------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-----------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |\r\n|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |    10 |  2160 |     2   (0)| 00:00:01 |\r\n|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |    10 |       |     1   (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   2 - access(\"B\"=:MYVAR)\r\n\r\n\r\n19 rows selected.\r\n\r\nSQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from\r\n  2   v$sql where sql_id = 'fhtgpcv2m2j4s';\r\n\r\nSQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER\r\n------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------\r\nfhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0\r\nfhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1\r\nfhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2\r\nfhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA()))           1918680838            3\r\n\r\nSQL>\r\nSQL> exec :myvar := 'FFFFFFFFFF';\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> prompt \"Press enter to run\"\r\n\"Press enter to run\"\r\nSQL> pause\r\n\r\nSQL> set termout off\r\nSQL> select * from table(dbms_xplan.display_cursor());\r\n\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------\r\nSQL_ID  fhtgpcv2m2j4s, child number 4\r\n-------------------------------------\r\nSELECT \/* MD_TESTCASE *\/ * from tab1 where b = :myvar\r\n\r\nPlan hash value: 1918680838\r\n\r\n-----------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-----------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |\r\n|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |     1 |   216 |     2   (0)| 00:00:01 |\r\n|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |     1 |       |     1   (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   2 - access(\"B\"=:MYVAR)\r\n\r\n\r\n19 rows selected.\r\n\r\nSQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from\r\n  2   v$sql where sql_id = 'fhtgpcv2m2j4s';\r\n\r\nSQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER\r\n------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------\r\nfhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0\r\nfhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1\r\nfhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2\r\nfhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA()))           1918680838            3\r\nfhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'FFFFFFFFFF', ANYDATA()))           1918680838            4\r\n<\/pre>\n
You can see that after parsing for bind CCCCCCCC, the execution plan changes from full table scan to index range scan. Moreover child cursor 1 is marked “is_bind_aware=Y”. When now bind DDDDDDDD is used, there is a new child (#2) and child 1 is set to is_sharable=N. After using bind EEEEEEEE, child #2 is set to sharable=N and a new child #3 is created. This repeats until there are 5 child cursors, 2 of which are set to is_sharable=Y. When the script is executed multiple times, there is even a sixth child cursor. <\/p>\n
\r\nSQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS\r\n------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ ----------\r\nfhtgpcv2m2j4s N Y N N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0          2\r\nfhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1          1\r\nfhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2          1\r\nfhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA()))           1918680838            3          1\r\nfhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'FFFFFFFFFF', ANYDATA()))           1918680838            4         16\r\nfhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            5          3\r\n<\/pre>\n
I wondered why the cursors are not sharable and queried v$sql_shared_cursor:<\/p>\n
\r\nSQL>   select * from v$sql_shared_cursor where sql_id = 'fhtgpcv2m2j4s';\r\n\r\nSQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T R I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H\r\n------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -\r\nfhtgpcv2m2j4s 23F4E438 23F4DAB0            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N\r\nfhtgpcv2m2j4s 23F4E438 23D924D8            1 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N\r\nfhtgpcv2m2j4s 23F4E438 23D7E888            2 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N\r\nfhtgpcv2m2j4s 23F4E438 23D6B8C8            3 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N\r\nfhtgpcv2m2j4s 23F4E438 23D574D8            4 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N\r\nfhtgpcv2m2j4s 23F4E438 23B983B0            5 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N\r\n\r\n6 rows selected.\r\n\r\nSQL> select sql_id, address, child_number, LOAD_OPTIMIZER_STATS, row_level_sec_mismatch from v$sql_shared_cursor where sql_id = 'fhtgpcv2m2j4s';\r\n\r\nSQL_ID        ADDRESS  CHILD_NUMBER L R\r\n------------- -------- ------------ - -\r\nfhtgpcv2m2j4s 245CE53C            0 Y N\r\nfhtgpcv2m2j4s 245CE53C            1 N Y\r\nfhtgpcv2m2j4s 245CE53C            2 N Y\r\nfhtgpcv2m2j4s 245CE53C            3 N Y\r\nfhtgpcv2m2j4s 245CE53C            4 N Y\r\nfhtgpcv2m2j4s 245CE53C            5 N Y\r\n\r\nSQL> select * from v$sql_cs_statistics where sql_id = 'fhtgpcv2m2j4s';\r\n\r\nADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME\r\n-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------\r\n245CE53C 3308340376 fhtgpcv2m2j4s            5          3946114705 N          1              0           2          0\r\n245CE53C 3308340376 fhtgpcv2m2j4s            5          2785428864 Y          1              0           2          0\r\n245CE53C 3308340376 fhtgpcv2m2j4s            4          1680726256 Y          1              2           3          0\r\n245CE53C 3308340376 fhtgpcv2m2j4s            3          1438126164 Y          1             20           5          0\r\n245CE53C 3308340376 fhtgpcv2m2j4s            2           451655377 Y          1            200          21          0\r\n245CE53C 3308340376 fhtgpcv2m2j4s            1          4078512309 Y          1           2000         169          0\r\n245CE53C 3308340376 fhtgpcv2m2j4s            0           479772785 Y          1         100000        9823          0\r\n\r\n7 rows selected.\r\n\r\nSQL> select * from v$sql_cs_selectivity where sql_id = 'fhtgpcv2m2j4s';\r\n\r\nADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH\r\n-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------\r\n245CE53C 3308340376 fhtgpcv2m2j4s            5 =MYVAR                                            0 0.000004   0.010773\r\n245CE53C 3308340376 fhtgpcv2m2j4s            4 =MYVAR                                            0 0.000009   0.010773\r\n245CE53C 3308340376 fhtgpcv2m2j4s            3 =MYVAR                                            0 0.000088   0.010773\r\n245CE53C 3308340376 fhtgpcv2m2j4s            2 =MYVAR                                            0 0.000881   0.010773\r\n245CE53C 3308340376 fhtgpcv2m2j4s            1 =MYVAR                                            0 0.008814   0.010773\r\n\r\n<\/pre>\n
It is not clear, why the cursors are not shared and why this is expressed in the flag “row_level_sec_mismatch”. From these tests we can see that the number of child cursors is still quite high.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, at the last day of Hotsos Symposium 2009 Ric van Dyke presented on “Intelligent (aka adaptive) Cursor Sharing”, a new feature of Oracle Database 11g. The feature was created to fix the shortcomings of bind peeking on columns with histograms. Tom Kyte presented a few years ago on this topic when he talked about […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[14,47,19,5],"tags":[],"class_list":["post-586","post","type-post","status-publish","format-standard","hentry","category-11g","category-bugs","category-metalink","category-oracle-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/586","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=586"}],"version-history":[{"count":18,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/586\/revisions"}],"predecessor-version":[{"id":604,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/586\/revisions\/604"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=586"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=586"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=586"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}