Oracle adaptive shared cursor

Adaptive cursor sharing adaptive cursor sharing or extended cursor sharing (Extended cursor sharing) is one of the new features of Oracle 11g. It is mainly used to solve previous versions
In this example, the SQL statement cannot obtain the best execution plan because of the check of the bound variables. That is, the inefficient cursor (sub-cursor) can be automatically identified and the best execution plan can be selected. Ben
This section describes adaptive cursor sharing in detail and provides an example.
For more information about variable binding, see Oracle variable binding.

I. Example adaptive cursor sharing
1. Create a demo Environment

SQL> select * from V $ version where rownum <2; </P> <p> banner <br/> statement <br/> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-production </P> <p> SQL> Create Table T (ID, owner, object_id) as <br/> 2 select rownum, owner, object_id from all_objects where rownum <= 1000; </P> <p> SQL> alter table t add constraint t_pk primary key (ID); </P> <p> SQL> exec dbms_stats.gather_table_stats ('Scott ', 'T', cascade => true); </P> <p> SQL> select count (ID), count (distinct ID), min (ID), max (ID) from t; </P> <p> count (ID) count (distinctid) min (ID) max (ID) <br/> ---------- ------------------- ---------- <br/> 1000 1000 1 1000 <br/>

2. Use the bound variable to execute the SQL statement and obtain the first execution result.

SQL> var v_id number; <br/> SQL> Exec: v_id: = 9; <br/> SQL> set linesize 180 <br/> SQL> select sum (object_id) from t where ID <: v_id; </P> <p> sum (object_id) <br/> -------------- <br/> 2078 <br/> SQL> select * from table (dbms_xplan.display_cursor (null, null, 'typical-predicate ')); <br/> SQL _id 7qcp6urq1_d2j, Child number 0 <br/> ------------------------------------- <br/> select sum (object_id) from t where ID <: v_id --> when the variable value is 9, the correct execution plan is used, and the estimated number of rows is also accurate </P> <p> plan hash value: 4270555908 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | 2 | table access by index rowid | T | 8 | 64 | 3 (0) | 00:00:01 | <br/> | 3 | index range scan | t_pk | 8 | 2 (0) | 00:00:01 | <br/> Accept </P> <p> SQL> Col SQL _text format a45 --> the following statement obtains the values of three fields shared by the adaptive cursor. <br/> SQL> select SQL _id, SQL _text, child_number, executions, is_bind_sensitive, is_bind_aware, is_retriable <br/> 2 from V $ SQL where SQL _text like '% select sum (object_id) from t where % 'and SQL _text not like' % from V $ SQL % '; </P> <p> SQL _id SQL _text child_number executions I <br/> ------------- certificate ------------ -------------<br/> 7qcp6urq1_d2j select sum (object_id) from t where ID <: v_id 0 1 y n y <br/>

3. external embodiment of adaptive cursor sharing
Adaptive cursor sharing involves three fields: is_bind_sensitive, is_bind_aware, and is_retriable. (Note: The three fields are only in Oracle 11g.
). According to the above query from V $ SQL (no is_retriable exists in V $ sqlarea), the three fields are assigned different values, representing different meanings.

Is_bind_sensitive (whether the binding is sensitive)
Whether the Bind Variable element is used in the child game tag, and the Execution Plan is generated using the BIND peeking method. If the execution plan depends on the prying value, here is y,
Otherwise, it is N.

Is_bind_aware (whether the binding is known)
Indicates whether the child cursor uses the extended cursor sharing technology. If it is Y, otherwise it is n. If it is N, the cursor will be discarded and no longer available.

Is_shareable (whether shared)
Indicates whether the Sub-cursor can be shared by the next soft resolution. If the value is Y, otherwise n indicates that the sub-cursor has lost the shared value and is eliminated by the LRU algorithm.

This SQL statement is the first execution, so we know from the results of the V $ SQL query
Is_bind_sensitive is Y value (BIND peeking is executed for the first time)
Is_bind_aware is set to n (first run, not supported by extended cursor sharing)
The is_retriable value is Y (the execution plan can be shared)

4. view the cursor sharing status after the value is assigned again.

SQL> Exec: v_id: = 900; <br/> SQL> select sum (object_id) from t where ID <: v_id; </P> <p> sum (object_id) <br/> -------------- <br/> 1826561 </P> <p> SQL> select * from table (dbms_xplan.display_cursor (null, null, 'typical-predicate ')); </P> <p> SQL _id 7qcp6urq1_d2j, Child number 0 <br/> ------------------------------------- <br/> select sum (object_id) from t where ID <: v_id --> the variable value for this execution is 900, and the execution plan where the last variable bit is 9 <br/> --> This is an incorrect execution plan, which is equivalent to Oracle 9i, condition in 10 Gb <br/> plan hash value: 4270555908 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | 2 | table access by index rowid | T | 8 | 64 | 3 (0) | 00:00:01 | <br/> | 3 | index range scan | t_pk | 8 | 2 (0) | 00:00:01 | <br/> cursor </P> <p> --> the values of the three fields shared by the adaptive cursor have not changed. <br/> SQL> select SQL _id, SQL _text, child_number, executions, is_bind_sensitive, is_bind_aware, is_retriable <br/> 2 from V $ SQL where SQL _text like '% select sum (object_id) from t where % 'and SQL _text not like' % from V $ SQL % '; </P> <p> SQL _id SQL _text child_number executions I <br/> ------------- certificate ------------ -------------<br/> 7qcp6urq1_d2j select sum (object_id) from t where ID <: v_id 0 2 y n y </P> <p> SQL> select sum (object_id) from t where ID <: v_id; --> execute the SQL statement whose variable is 900 again </P> <p> sum (object_id) <br/> -------------- <br/> 1826561 </P> <p> SQL> select * from table (dbms_xplan.display_cursor (null, null, 'typical-predicate ')); </P> <p> SQL _id 7qcp6urq1_d2j, Child number 1 <br/> ------------------------------------- <br/> select sum (object_id) from t where ID <: v_id --> at this time, the execution plan is changed from the previous one, and the full table scan is used, rows is close to the actual value <br/> --> adaptive cursor sharing feature can be reflected <br/> plan hash value: 2966233522 </P> <p> --------------------------------------------------------------------------- <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> --------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | 2 | table access full | T | 900 | 7200 | 3 (0) | 00:00:01 | <br/> --------------------------------------------------------------------------- </P> <p> --> the adaptive cursor sharing feature changes several values and generates a new subcursor, its subcursor number is 1 <br/> SQL> select SQL _id, SQL _text, child_number, executions, is_bind_sensitive, is_bind_aware, is_retriable <br/> 2 from V $ SQL where SQL _text like '% select sum (object_id) from t where %' and SQL _text not like '% from V $ SQL % '; </P> <p> SQL _id SQL _text child_number executions I <br/> ------------- certificate ------------ -------------<br/> 7qcp6urq1_d2j select sum (object_id) from t where ID <: v_id 0 2 y n y <br/> 7qcp6urq1_d2j select sum (object_id) from t where ID <: v_id 1 1 y </P> <p> SQL> Exec: v_id: = 800 --> assign different values to the variable </P> <p> SQL> select sum (object_id) from t where ID <: v_id; --> execute an SQL statement using the new variable value </P> <p> sum (object_id) <br/> -------------- <br/> 1548431 </P> <p> SQL> select SQL _id, child_number, executions, is_bind_sensitive, is_bind_aware, is_retriable <br/> 2 from V $ SQL where SQL _text like '% select sum (object_id) from t where %' and SQL _text not like '% from V $ SQL % '; </P> <p> SQL _id child_number executions I <br/> ------------------ -------------<br/> 7qcp6urq1_d2j 0 2 y n y <br/> 7qcp6urq1_d2j 1 y n <br/> 7qcp6urq1_d2j 2 1 y --> A new sub-game ID is 2 </P> <p> SQL> Exec: v_id: = 500; --> assign a new value to the variable </P> <p> SQL> select sum (object_id) from t where ID <: v_id; --> execute an SQL statement using the new variable value </P> <p> sum (object_id) <br/> -------------- <br/> 826694 </P> <p> /********************** * **************************/<br/>/* Author: robinson Cheng */<br/>/* blog: http://blog.csdn.net/robinson_0612 */<br/>/* MSN: robinson_0612@hotmail.com */<br/>/* QQ: 645746311 */<br/> /******************************** * *****************/</P> <p> SQL> select SQL _id, child_number, executions, is_bind_sensitive, is_bind_aware, is_retriable <br/> 2 from V $ SQL where SQL _text like '% select sum (object_id) from t where % 'and SQL _text not like' % from V $ SQL % '; </P> <p> SQL _id child_number executions I <br/> ------------------ -------------<br/> 7qcp6urq1_d2j 0 2 y n y <br/> 7qcp6urq1_d2j 1 y n <br/> 7qcp6urq1_d2j 2 1 y n --> note that the is_retriable value of sub-cursor 1 and 2 is n, indicates that the sub-game cannot be shared <br/> 7qcp6urq1_d2j 3 1 y --> A new sub-game number is 3, </P> <p> --> View All execution plans of different sub-game targets of the SQL statement. <br/> SQL> select * from table (dbms_xplan.display_cursor ('7qcp6urq1_d2j ', null, 'typical-predicate'); </P> <p> plan_table_output <br/> limit <br/> SQL _id 7qcp6urq1_d2j, child number 0 <br/> ----------------------------------- <br/> select sum (object_id) from t where ID <: v_id --> 0 subcursor for index range scanning </P> <p> plan hash value: 4270555908 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | 2 | table access by index rowid | T | 8 | 64 | 3 (0) | 00:00:01 | <br/> | 3 | index range scan | t_pk | 8 | 2 (0) | 00:00:01 | <br/> limit </P> <p> SQL _id 7qcp6urq1_d2j, Child number 1 <br/> ----------------------------------- <br/> select sum (object_id) from t where ID <: v_id --> the subcursor No. 1 is a full table scan, and its estimated number of rows is close to the actual number of affected rows. The value is 900 </P> <p> plan hash value: 2966233522 </P> <p> --------------------------------------------------------------------------- <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> --------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | 2 | table access full | T | 900 | 7200 | 3 (0) | 00:00:01 | <br/> limit </P> <p> SQL _id 7qcp6urq1_d2j, Child number 2 <br/> ----------------------------------- <br/> select sum (object_id) from t where ID <: v_id --> 2 subcursor: full table scan, however, the estimated number of rows is close to the actual number of affected rows, and the value is 800 </P> <p> plan hash value: 2966233522 </P> <p> --------------------------------------------------------------------------- <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> --------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | 2 | table access full | T | 800 | 6400 | 3 (0) | 00:00:01 | <br/> limit </P> <p> SQL _id 7qcp6urq1_d2j, Child number 3 <br/> ----------------------------------- <br/> select sum (object_id) from t where ID <: v_id --> 3 subcursor: full table scan, but the estimated number of rows is equal to 499 of the actual number of rows affected </P> <p> plan hash value: 2966233522 </P> <p> --------------------------------------------------------------------------- <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> --------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | sort aggregate | 1 | 8 | <br/> | 2 | table access full | T | 499 | 3992 | 3 (0) | 00:00:01 | <br/> --------------------------------------------------------------------------- <br/>

Ii. Adaptive cursor sharing related views

1. V $ SQL _cs_statistics
The following query lists the peeking conditions, execution times, number of pre-processed rows, and buffer_gets of each sub-game.

SQL> select child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets <br/> 2 from V $ SQL _cs_statistics where SQL _id = 'hangzhou' <br/> 3 order by 1; </P> <p> child_number bind_set_hash_value P executions rows_processed buffer_gets <br/> ------------ bytes----------- ------------ ----------- <br/> 0 1706589901 Y 1 17 69 --> 17 rows, index range scan <br/> 1 3116944019 Y 1 900 5 --> 900 rows, full table scan <br/> 2 1328865654 Y 1 800 5 --> 800 rows, full table scan <br/> 3 1624350242 Y 1 500 5 --> 500 rows, full table scan <br/>

2. V $ SQL _cs_selecti.pdf
Displays the selective range of each sub-game cursor. The following query lists the predicate, selective range, and column selective values.

SQL> select child_number, predicate, range_id, low, high from V $ SQL _cs_selecti.pdf <br/> 2 where SQL _id = '7qcp6urq1_d2j 'order by 1; </P> <p> child_number predicate range_id low high <br/> ------------ ------------------ ---------- <br/> 1 <v_id 0 0.809910 0.989890 <br/> 2 <v_id 0 0.719820 0.989890 <br/> 3 <v_id 0 0.449550 0.989890 <br/>

3. V $ SQL _cs_histogram
Used to determine whether a query allows adaptive cursor sharing and is stored in a histogram.

SQL> select child_number, bucket_id, count from V $ SQL _cs_histogram where SQL _id = '7qcp6urq1_d2j '<br/> 2 order by 1; </P> <p> child_number bucket_id count <br/> ------------ ---------- <br/> 0 1 1 <br/> 0 0 1 <br/> 0 2 0 <br /> 1 1 0 <br/> 1 0 1 <br/> 1 2 0 <br/> 2 1 0 <br/> 2 0 1 <br/> 2 2 0 <br/> 3 1 0 <br/> 3 0 1 <br/> 3 2 0 <br/>

Iii. Summary
1. Adaptive cursor sharing: When an SQL statement is executed for the first time (bind variables), the system Snoops and records the results. If the same SQL statement is executed subsequently, the system snoops the results.
To determine whether a new execution plan needs to be generated. This indicates whether the bound variable is sensitive.
2. the sensitiveness of the bound variable is used to determine whether the current cursor is scalable. When unknown, the cursor is discarded.
3. The essence of adaptive cursor sharing is that, on the basis of Oracle 10 Gb ago, multiple variable binding spams are implemented, increasing the chance of getting the best execution plan selection.
4. Although the adaptive cursor sharing feature is used, it cannot be ensured that each SQL statement is executed according to the best plan. The second execution of the SQL statement in this demonstration is not the best execution plan.
5. Adaptive cursor sharing does not guarantee that the same SQL statement must be executed according to the same execution plan twice, because adaptive cursor sharing will continuously try peeking.

Iv. Extension reference
Oracle variable binding
Oracle adaptive shared cursor
Bind variables and their advantages and disadvantages

Parent cursor, child cursor, and shared cursor

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan