AdaptiveAdaptive Cursor Sharing cursor sharing or extended cursor sharing ( Extended Cursor Sharing ) is Oracle 11g new characteristic ,is mainly used to solve the previous version due to bind peeking in SQL statements cannot obtain optimal execution plan defects ,i.
e. to inefficient cursor ( sub vernier ) for automatic identification and choose the best execution plan .This paper describes in detail the adaptive cursor sharing and gives examples .
The bind variables snooping refer to :one ,the sample adaptive vernier shared 1 ,create the presentation environment SQL> ;select * from V $version where rownum< ;BANNER 2 ;Oracle Database 11g Enterprise Edition -------------------------------------------------------------------------------- Release 11.
2.0.1.0 - Production SQL> ;create table t ( ID ,owner ,object_id ) as 2 select rownum ,owner ,object_id from all_objects where rownum< = 1000 ;SQL> ;alter ;table t add constraint t_pk primary key ( ID ) ;SQL> ;exec dbms_stats.
gather_table_stats ( , ,cascade = > ;true ;SQL> ) ;select count ( ID ) ,count ( distinct ID ) ,min ( ID ) ,max ( ID from t ) ;COUNT ( ID ) COUNT ( DISTINCTID ) MIN ( ID ) MAX ( ID ) - ----------------- - - 10001000110002 ,use bind variables to execute a SQL statement and was the first implementation of SQL> ;VAR v_id number ;SQL> ;exec :v_id = 9 ;SQL> linesize SQL> ;set 180 ;select sum ( object_id ) from t where id< ;SUM :v_id ;( OBJECT_ID ) on 2078 SQL> ;select * from table ( dbms_xplan.
display_cursor ( null ,null , predicate ) ) ;SQL_ID 7qcp6urqh7d2j ,child number 0 ------------------------------------- Select sum ( object_id ) from t where id< :v_id - > ;variables ;a value of 9 ,using the correct implementation plan ,and estimated rows also accurate Plan hash value: 4270555908 Id Operation Name Rows Bytes Cost ------------------------------------------------------------------------------------- ( %CPU ) Time ------------------------------------------------------------------------------------- 0 SELECT STATEMENT 3 ( 100) 1 SORT AGGREGATE 182 TABLE ACCESS BY INDEX ROWID T 8643 ( 0) 00 : 00: 013 INDEX RANGE SCAN T_PK 82 ( 0) 00 : 00: 01 ------------------------------------------------------------------------------------- SQL> col SQL_TEXT format A45 ;> ;the following statement to access adaptive cursor sharing in a 3 field value SQL> ;select sql_id ,sql_text ,child_number ,executions ,is_bind_sensitive ,is_bind_aware ,is_shareable 2 from V $SQL where sql_text like %select sum ( object_id ) from t where% and sql_text not like %from V $ sql% ;SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I ------------- out - - - - - - - - 7qcp6urqh7d2j select sum ( object_id ) from t where id< ;Y N Y 3 :v_id 01 ,adaptive cursor sharing embodiment of adaptive cursor sharing through three main fields to be reflected ,i.
e. is_bind_sensitive ,is_bind_aware ,is_shareable .( Note: this three field only in Oracle 11g in the presence of ) .Through the above from the V $SQL ( V $sqlarea is_shareable does not exist in the query that ) ,three fields were given different & # 20540 ;,represents a different meaning .
Is_bind_sensitive ( binding is sensitive ) said the child cursor is used to bind variables elements ,and uses the bind peeking method execution plan generation .If the execution plan is dependent on a snooping into & # 20540 ;otherwise ,here for the Y ,N .
Is_bind_aware ( binding is knowable) indicates that the child cursor is used by extended cursor sharing technology ,is for Y ,or N ,such as N ,the cursor will be abandoned ,is no longer available .
Is_shareable ( or shared ) indicates that the child cursor may be next time whether can share the use of soft parse .Can be shared for Y ,Cheap Beats By Dre,or N ,said the child cursor lost share price & # 20540 ,according to LRU algorithm eliminated .
Because the SQL statement for the first time to perform ,so from V $ SQL query results that is_bind_sensitive Y& # ( 20540 ;first run ,the implementation of the bind peeking is_bind_aware N& # ) 20540 ;( first run ,not by the extended cursor sharing support is_shareable Y& # ) 20540 ;( execution plan can be shared ) ,4 to Fu & # 20540 ;after observing the cursor sharing SQL> ;exec :v_id: = 900 ;SQL> ;select sum ( object_id ) from t where id< ;SUM :v_id ;( OBJECT_ID ) on 1826561 * from table SQL> ;select ( dbms_xplan.
display_cursor ( null ,null , predicate ) ) ;SQL_ID 7qcp6urqh7d2j ,child number 0 select sum ------------------------------------- (object_id) from t where id< :v_id - > ;;the implementation of the value of the variable is a variable 900 ,the execution plan last 9 execution plan - > ;this is not the correct implementation plan ,equivalent to the Oracle 9i ,10g Plan hash value: 4270555908 cases Id Operation Name Rows Bytes Cost ------------------------------------------------------------------------------------- ( %CPU ) Time ------------------------------------------------------------------------------------- 0 SELECT STATEMENT 3 ( 100) 1 SORT AGGREGATE 182 TABLE ACCESS BY INDEX ROWID T 8643 ( 0) 00 : 00: 013 INDEX RANGE SCAN T_PK 82 ( 0) 00 : 00: 01 ------------------------------------------------------------------------------------- - > ;adaptive cursor sharing 3 values of a field there are no changes in SQL> ;select sql_id ,sql_text ,child_number ,executions ,is_bind_sensitive ,is_bind_aware ,is_shareable 2 from V $SQL where sql_text like %select sum ( object_id ) from t where% and sql_text not like %from V $sql% SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS ;I I I ------------- out - - - - - - - - 7qcp6urqh7d2j select sum ( object_id ) from t where id< ;Y N Y :v_id 02 SQL> select sum ( object_id ) ;from t where id< :v_id ;> ;- ;again performs variable value for the 900 SQL statement SUM ( OBJECT_ID ) on 1826561 SQL> ;select * from table ( dbms_xplan.
display_cursor ( null ,null , predicate ) ) ;SQL_ID 7qcp6urqh7d2j ,chil D number 1 select sum ------------------------------------- (object_id) from t where id< :v_id - > ;;when the execution plan is lower than the last time changes, using a full table scan ,Rows close to the actual value - > ;adaptive cursor sharing features will be reflected Plan hash value: 2966233522 Id Operation Name Rows Bytes Cost --------------------------------------------------------------------------- ( %CPU Time ----------------------------------------------------------------------- ) 0 SELECT STATEMENT 3 ( 100 ) 1 SORT AGGREGATE 182 TABLE ACCESS FULL T 90072003 ( 0) 00 : 00: 01 --------------------------------------------------------------------------- - > ;adaptive cursor sharing characteristics of several value has changed ,a new generation of child cursor ,the cursor No.
1 SQL> ;select sql_id ,sql_text ,child_number ,executions ,is_bind_sensitive ,is_bind_aware ,is_shareable 2 from V $SQL where sql_text like %select sum ( object_id ) from t where% and sql_text not like %from V $sql% ;SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I ------------- out - - - - - - - - 7qcp6urqh7d2j select sum ( object_id ) from t where id< ;Y N Y 02 :v_id 7qcp6urqh7d2j select sum ( object_id ) from t where id< ;v_id Y Y Y SQL> :11 ;exec :v_id: > = 800 ;variable gives different the value of SQL> select sum ( object_id ) ;from t where id< :v_id ;> ;- ;the use of new value of the variable S QL statement SUM ( OBJECT_ID ) on 1548431 SQL> ;select sql_id ,child_number ,executions ,is_bind_sensitive ,is_bind_aware ,Moncler Down Jackets,is_shareable 2 from V $SQL where sql_text like %select sum ( object_id ) from t where% and sql_text not like %from V $sql% SQL_ID CHILD_NUMBER EXECUTIONS ;I I I ------------- - - - - - - - - 7qcp6urqh7d2j 02 Y N Y 7qcp6urqh7d2j 11 Y Y N 7qcp6urqh7d2j 21 Y Y Y - > ;a new generation of children travel to grade 2 SQL> ;exec :v_id: = 500 ;> ;variable given new value SQL> ;select sum from t where id< ( object_id ) - > :v_id ;;;the use of new values for the variables to execute a SQL statement SUM ( OBJECT_ID ) on 826694 / * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * / / * Author: Robinson Cheng * / * Blog: http: / / blog.
csdn.net / robinson_0612 * / / * MSN: robinson_0612@hotmail.com * / * QQ: 645746311 * / / * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * / SQL> ;select sql_id ,child_number ,executions ,is_bind_sensitive ,Coach Bags,is_bind_aware ,is_shareable 2 from V $SQL where sql_text like %select sum ( object_id ) from t where% and sql_text not like %from V $sql% SQL_ID CHILD_NUMBER EXECUTIONS I I I ;- - - - - - - - 7qcp6urqh7d2j ------------- 02 Y N Y 7qcp6urqh7d2j 11 Y Y N 7qcp6urqh7d2j 21 Y Y N - > ;see child cursor 1, 2 of the is_shareable value for the N ,not 7qcp6urqh7d2j Y Y Y shared 31 - > ;a new generation of child swim numbered 3 ,- > ;see the SQL statement End Sub vernier all the executive plan of SQL> ;select * from table ( dbms_xplan.
display_cursor ( 7qcp6urqh7d2j ,null , predicate PLAN_TABLE_OUTPUT ) ) ;------------------------------------------------------------------------------------------------------------------ SQL_ID 7qcp6urqh7d2j ,UGG Boots Clearance,child number 0 select sum ------------------------------------- (object_id) from t where id< ;> :v_id - ;0 being cursor index range scan Plan hash value: 4270555908 ------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost ( %CPU ) Time ------------------------------------------------------------------------------------- 0 SELECT STATEMENT 3 ( 100) 1 SORT AGGREGATE 182 TABLE ACCESS BY INDEX ROWID T 8643 ( 0) 00 : 00: 013 INDEX RANGE SCAN T_PK 82 ( 0) 00 : 00: 01 ------------------------------------------------------------------------------------- SQL_ID 7qcp6urqh7d2j ,child number 1 - - - - - - - ------------------------------ select sum ( object_id ) from t where id< ;> :v_id - ;1 being the cursor is a full table scan ,its estimation of the number of rows is close to the actual effect of number of rows of the value of 900 Plan hash value: 2966233522 Id Operation Name Rows Bytes Cost --------------------------------------------------------------------------- ( %CPU ) Time 0 SELECT STATEMENT 3 --------------------------------------------------------------------------- ( 100) 1 SORT AGGREGATE 182 TABLE ACCESS FULL T 90072003 ( 0) 00 : 00: 01 --------------------------------------------------------------------------- SQL_ID 7qcp6urqh7d2j ,child number 2 select sum ------------------------------------- (object_id) from t where id< ;> :v_id - ;2 being the cursor is a full table scan ,but its estimation of the number of lines close to the actual effect of number of rows of the value of 800 Plan hash value: 2966233522 Id Operation Name Rows Bytes Cost --------------------------------------------------------------------------- ( %CPU ) Time --------------------------------------------------------------------------- 0 SELECT STATEMENT 3 ( 100) 1 SORT AGGREGATE 182 TABLE ACCESS FULL T 80064003 ( 0) 00 : 00: 01 --------------------------------------------------------------------------- SQL_ID 7qcp6urqh7d2j ,child number 3 select sum ------------------------------------- (object_id) from t where id< ;> :v_id - ;3 being the cursor is a full table scan ,but its estimation of the number of rows is equal to the actual effect of number of rows of the value of 499 Plan hash value: 2966233522 Id Operation Name Rows Bytes Cost --------------------------------------------------------------------------- ( %CPU ) Time --------------------------------------------------------------------------- 0 SELECT STATEMENT 3 ( 100 ) 1 SORT AGGREGATE 182 TABLE ACCESS FULL T 49939923 ( 0) 00 : 00: two ,01 --------------------------------------------------------------------------- adaptive cursor sharing several related view 1 ,V $ sql_cs_statistics to monitor adaptive cursor sharing relevant statistical information .
The following query lists each sub vernier peeking situations ,as well as execution times ,pretreatment line number ,BUFFER_GETS SQL> ;select child_number ,bind_set_hash_value ,peeked ,executions ,rows_processed ,buffer_gets 2 from V $sql_cs_statistics where sql_id = 7qcp6urqh7d2j 3 order by 1 ;CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS - - - - - - on the ------------------- - 01706589901 Y 11769 > ;17 ,the index range scan 13116944019 Y 19005 > ;900 ,full table scan 21328865654 Y 18005 > ;800 ,full table scan 31624350242 Y 15005 - > ;500 ,full table scanning 2 ,V $ sql_cs_selectivity display each cursor cursor for select Selective range .
The following query is listed in the predicate ,selective range ,listed on the selective & # 20540 ;SQL> ;select child_number ,predicate ,range_id ,low ,high from V $sql_cs_selectivity 2 where sql_id = by 1 ;CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH - - - - - - - these 1 < ;0.
9898902 < V_ID 0.809910 0.9898903 < ;V_ID 0.719820 ;V_ID 0.449550 0.9898903 ,V $sql_cs_histogram is used to determine whether a query allows adaptive cursor sharing ,Vibram Sale,in order to form the histogram memory SQL> ;select CHILD_NUMBER ,BUCKET_ID ,COUNT from V $sql_cs_histogram where SQL_ID = 7qcp6urqh7d2j by 1 ;CHILD_NUMBER BUCKET_ID COUNT - - - - - - 01100102 01101011 20210 20122031 0301320 three ,sum 1 ,adaptive cursor sharing in SQL statements ( first performed using bind variables ) ,to snoop ,and record the snoop results ,if the follow-up with the same SQL statement is executed ,the snoop results were compared to judge whether the need to generate new execution plan .
This is not sensitive to bind variables .2 ,bind variable knowability used to judge the current cursor cursor for scalable shared ,when the unknowable ,the cursor is abandoned .3 ,adaptive cursor sharing is the essence of the Oracle 10g previously on the basis of the repeated bind peeking ,added to obtain optimal execution plan choice probability .
4 ,despite the use of adaptive cursor shared characteristics ,but is not guaranteed to execute a SQL statement must according to the plan, the presentation of the second SQL statement execution is not optimal execution plan .
5 ,adaptive cursor sharing can not guarantee two performs the same SQL statement must press the same execution plan execution ,because adaptive cursor sharing will keep trying peeking .
Related articles：
没有评论:
发表评论