oracle数据库11g新特性-自适应游标

2 11G出现11g新特性之自适应游标共享（Adaptive Cursor Sharing）
窥视+自适应

对于11g以上环境，则可以根据查询

select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_Id='8g00231cdyxus';

来确定产生了自适应的游标详细。

缺点：1 可能导致一定数量的额外的硬解析。

          2  可能导致一定数量的额外的child_cursor挂在同意个parent_cursor下，增加软解析和软软解析的工作量。

           3 sql第一次执行效率很高谓词列(高度不集中）走索引，第2次查询可能缓慢(应该全表仍然走索引），第3次就重新进行解析，（游标v$sql.runtime buffer_gets相差较大，

游标变为is_bind_AWARE） select count(1) from v$sql_cs_selectivity where sql_Id='1pm8c037m91f9';

2.1 自适应游标过程总结。
1 一个游标建立之后会存在3个变量值，runtime，buffer_gets,is_bind_sensitive ,is_shareable , is_bind_aware 

2 第2次执行，直接继承第一次执行计划，并比较runtime，差异较大则is_bind_aware 调整为Y，第一个游标is_shareable为N，并在v$sql_cs_selectivity出现一条范围值。范围之内共享，范围之外硬解析。

SQL> select child_number,buffer_gets,is_bind_sensitive bs,is_shareable sh, is_bind_aware ba from v$sql where sql_id='8g00231cdyxus';

CHILD_NUMBER BUFFER_GETS B S B
------------ ----------- - - -
           0        1786 Y N N
           1        1437 Y Y Y
           2           4    Y Y Y

SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_Id='8g00231cdyxus';

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           2 <X                                                0 0.001194   0.001460
           1 <X                                                0 0.900000   1.100000  ###########没有child为0的范围值。

SELECT ROUND((谓词条数/总数)*0.9，6） low,ROUND((谓词条数/总数)*0.9，6） HIGH FROM DUAL;

 

alter system flush shared_pool;

var x number
exec :x:=100

select object_name,count(1) from t1 where object_Id<:x group by object_name;
select * From table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

exec :x:=100000000000000  --出现第2次就硬解析了，不用连续。

执行计划均为:
SQL> select * From table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8g00231cdyxus, child number 0
-------------------------------------
select object_name,count(1) from t1 where object_Id<:x group by
object_name

Plan hash value: 1125902940

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |       |       |     6 (100)|          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY                       |      |    97 |  3880 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |    97 |  3880 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX1 |    97 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
   3 - SEL$1 / T1@SEL$1

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 100

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_ID"<:X)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], COUNT(*)[22]
   2 - "OBJECT_NAME"[VARCHAR2,128]
   3 - "T1".ROWID[ROWID,10]

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

56 rows selected.

exec :x:=100000000000000
select object_name,count(1) from t1 where object_Id<:x group by object_name;
select * From table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

SQL> select * From table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8g00231cdyxus, child number 1
-------------------------------------
select object_name,count(1) from t1 where object_Id<:x group by
object_name

Plan hash value: 136660032

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |  1052 (100)|          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY     |      | 60608 |  2367K|  3440K|  1052   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   | 72769 |  2842K|       |   387   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

Outline Data

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 100000000000000

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
   2 - filter("OBJECT_ID"<:X)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], COUNT(*)[22]
   2 - (rowset=256) "OBJECT_NAME"[VARCHAR2,128]

52 rows selected.

SQL> 

SQL>  select child_number,buffer_gets,is_bind_sensitive bs,is_shareable sh, is_bind_aware ba from v$sql where sql_id='8g00231cdyxus';

CHILD_NUMBER BUFFER_GETS B S B
------------ ----------- - - -
           0        1786 Y N N
           1        1437 Y Y Y

SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_Id='8g00231cdyxus';

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           1 <X                                                0 0.900000   1.100000

----这次还是发生了硬解析。
var x number
exec :x:=100
select object_name,count(1) from t1 where object_Id<:x group by object_name; 在执行一次
SQL> select child_number,buffer_gets,is_bind_sensitive bs,is_shareable sh, is_bind_aware ba from v$sql where sql_id='8g00231cdyxus';

CHILD_NUMBER BUFFER_GETS B S B
------------ ----------- - - -
           0        1786 Y N N
           1        1437 Y Y Y
           2           4 Y Y Y
SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_Id='8g00231cdyxus';

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           2 <X                                                0 0.001194   0.001460
           1 <X                                                0 0.900000   1.100000

在执行一个

var x number
exec :x:=300

select object_name,count(1) from t1 where object_Id<:x group by object_name;

SQL> select child_number,buffer_gets,is_bind_sensitive bs,is_shareable sh, is_bind_aware ba from v$sql where sql_id='8g00231cdyxus';

CHILD_NUMBER BUFFER_GETS B S B
------------ ----------- - - -
           0        3546 Y N N
           1           4 Y N Y
           2        1437 Y Y Y
           3          21 Y Y Y

SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_Id='8g00231cdyxus';

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           3 <X                                                0 0.001194   0.004438
           2 <X                                                0 0.900000   1.100000
           1 <X                                                0 0.001194   0.001460

SQL> 
————————————————