COUNT(*)
----------
17689
scott@PROD>select count(*) from t where object_id=1;
COUNT(*)
----------
17689
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
OBJECT_ID FREQUENCY
OBJECT_NAME NONE
SQL_ID HASH_VALUE
------------- ----------
366bf1pphk8vp 1795761013
sys@PROD>select sql_id,hash_value from v$sql where sql_text like 'select object_id from t where object_id=99';
SQL_ID HASH_VALUE
------------- ----------
7bracwmz4k9bh 4266206576
Enter value for sqlid: 366bf1pphk8vp
old 1: select * from table(dbms_xplan.display_cursor('&sqlid',null,'TYPICAL LAST'))
new 1: select * from table(dbms_xplan.display_cursor('366bf1pphk8vp',null,'TYPICAL LAST'))
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 366bf1pphk8vp, child number 0
-------------------------------------
select object_id from t where object_id=1
Plan hash value: 521550358
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
|* 1 | INDEX FAST FULL SCAN| I_T_ID | 17688 | 53064 | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
18 rows selected.
Enter value for sqlid: 7bracwmz4k9bh
old 1: select * from table(dbms_xplan.display_cursor('&sqlid',null,'TYPICAL LAST'))
new 1: select * from table(dbms_xplan.display_cursor('7bracwmz4k9bh',null,'TYPICAL LAST'))
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 7bracwmz4k9bh, child number 0
-------------------------------------
select object_id from t where object_id=99
Plan hash value: 1420114084
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| I_T_ID | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=99)
18 rows selected.
HASH_VALUE
----------
956031419
2 from v$sql where hash_value=956031419
3 ;
PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
------------
PL/SQL procedure successfully completed.
scott@PROD>select object_id from t where object_id=:a;
PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
521550358 2 0 Y N Y
OBJECT_ID
----------
99
PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
521550358 2 0 Y N Y
1420114084 1 1 Y Y Y
OBJECT_ID
----------
99
PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
521550358 2 0 Y N Y
1420114084 2 1 Y Y Y
PL/SQL procedure successfully completed.
scott@PROD>select object_id from t where object_id=:a;
PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
521550358 2 0 Y N N
1420114084 2 1 Y Y Y
521550358 1 2 Y Y Y
PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
521550358 2 0 Y N N
1420114084 2 1 Y Y Y
521550358 2 2 Y Y Y
PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
521550358 2 0 Y N N
1420114084 2 1 Y Y Y
521550358 3 2 Y Y Y
PL/SQL procedure successfully completed.
scott@PROD>select object_id from t where object_id=:a;
OBJECT_ID
----------
99
PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
521550358 2 0 Y N N
1420114084 3 1 Y Y Y
521550358 3 2 Y Y Y
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 1
0 1 1
0 2 0
1 0 3
1 1 0
1 2 0
2 0 0
2 2 0
CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------ ---------------------------------------- ---------- ---------- ----------
2 =A 0 0.899949 1.099938
1 =A 0 0.000051 0.000062
ROWS_PROCESSED,BUFFER_GETS,CPU_TIME
3 FROM v$sql_cs_statistics WHERE HASH_VALUE = '956031419';
CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
------------ ------------------- - ---------- -------------- ----------- ----------
2 2342552567 Y 1 17688 1218 0
1 1937997561 Y 1 1 2 0
0 2342552567 Y 1 17688 1264 0
sys@PROD> SELECT CHILD_NUMBER,VALUE_STRING,LAST_CAPTURED FROM v$sql_bind_capture WHERE HASH_VALUE =956031419 order by 1;
CHILD_NUMBER VALUE_STRING LAST_CAPT
------------ --------------- ---------
0 1 29-JAN-15
1 99 29-JAN-15
2 1 29-JAN-15
如何关闭ACS?
alter system set "_optimizer_extended_cursor_sharing_rel"=none;
alter system set "_optimizer_extended_cursor_sharing"=none;
alter system set "_optimizer_adaptive_cursor_sharing"=false;
证明:
SQL> alter session set "_optimizer_extended_cursor_sharing_rel"=none;
SQL> alter session set "_optimizer_extended_cursor_sharing"=none;
SQL> alter session set "_optimizer_adaptive_cursor_sharing"=false;
SQL> alter system flush shared_pool;
SQL> var v number;
SQL> exec :v :=2;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 1 286 N N Y
SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7354 N N Y
SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 3 9710 N N Y
可见,当我在session级别关闭这三个隐藏参数后,IS_BIND_SENSITIVE始终为N,更换绑定变量后也不会产生新的cursor。
所以,当我们再做11GR2升级时,可以先考虑关闭这三个参数谋求SQL PLAN的稳定的同时,使用其他11G的new feature。
毕竟对于高并发的OLTP数据库,稳定重于一切。
<四>使用hint强制BIND_AWARE
在我研究11G所有新hint时,发现了这个hint:BIND_AWARE。
于是,就有了研究ACS的冲动,才有了这篇文章。
这个hint故名思意,会强制SQL产生BIND_AWARE的cursor。
更加强悍的是,即使你如上面第三点所示关闭了这三个ACS的参数,但hint依旧生效!
我先如上在session级别关闭这三个ACS的参数,然后进行了如下实验。
SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
我们先发现,IS_BIND_AWARE=N。
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 7515 N N Y
接着加上BIND_AWARE这个hint:
SQL> select /*+BIND_AWARE*/ * from TESTBYHAO
2 where id = :v;
可以看见区别了吧,IS_BIND_SENSITIVE=Y,IS_BIND_AWARE=Y。
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 7296 Y Y Y
接下来更换绑定变量再run几次,结果就是我们所熟悉的了。
再重申下,这是在我关闭session级别的ACS参数后进行的测试。
可见,BIND_AWARE这个hint很强悍。
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 2 7296 Y Y Y
1 254123311 2 73 Y Y N
2 254123311 1 2 Y Y Y
于是,我想到了这样的假设的情况,当我们升级到11GR2后,由于对ACS不了解,不敢用,于是再系统级别关闭了ACS的三个参数。但是突然某一天,我发现了一个由于data skew并且采用绑定变量的SQL PLAN不好调整时,我可以让开发人员对这个特定的SQL加上这个hint,让其突破关闭ACS的限制,使用ACS。于是,这似乎可以成为新的SQL tunning的好方法。
<五>万能的outline强于一切
本来写完前四点就想结束了,但突然想到我们现有的系统上使用了无数的outline来固定SQL PLAN。那么如果升级到11GR2后,在ACS的强大统治力下,outline会不会失效呢?
带着这个疑问,我做了如下的实验,结论是:outline强于一切!甚至可以突破BIND_AWARE这个强有力的hint的限制!
实验一:不使用BIND_AWARE这个hint
SQL> select /*comment*/ * from TESTBYHAO
2 where id = :v;
先如愿产生一个ACS会生效的cursor:
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 7519 Y N Y
使用outline固定这个SQL。
alter session set current_schema=HAOZHU_USER;
create outline ol_4107335673 for category temp_plan on
select /*comment*/ * from TESTBYHAO
where id = :v ;
alter session set current_schema=HAOZHU_USER;
create outline ol_temp4107335673 for category temp_plan on
select /*+full(TESTBYHAO)*/ /*comment*/ * from TESTBYHAO
where id = :v;
再exchange这两个outline。
接着换id=2再次执行同样的SQL:
SQL> exec :v:=2
SQL> select /*comment*/ * from TESTBYHAO
2 where id = :v ;
结果v$SQL里产生了一个新的cursor(新的HASH_VALUE),并不是先前的cursor了,也不是先前的Child Cursor。
再多次执行上面的id=2的同样的SQL后,我们可以看到:
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 4 461.75 N N Y
可见,使用outline后,即使你开启了ACS,ACS也不生效!
实验二:使用BIND_AWARE hint
接着有人会问,你第四点提到的BIND_AWARE这个hint这么强大,能够突破关闭ACS的限制,那么能否突破outline的限制呢?
带着这个疑问,我做了如下实验:
先使用BIND_AWARE hint,走index range scan:
SQL> select /*+BIND_AWARE*/ /*comment*/ * from TESTBYHAO
2 where id = :v;
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 1 73 Y Y Y
如上,可见三个“Y”看着十分地舒服。
我接着创建outline固定使用全表扫描而不走index。
alter session set current_schema=HAOZHU_USER;
create outline ol_new for category temp_plan on
select /*+BIND_AWARE*/ /*comment*/ * from TESTBYHAO
where id = :v;
alter session set current_schema=HAOZHU_USER;
create outline ol_tempnew for category temp_plan on
select /*+FULL(TESTBYHAO)*/ /*+BIND_AWARE*/ /*comment*/ * from TESTBYHAO
where id = :v;
exchange这两个outline。
然后再run一模一样的这个SQL:
SQL> /
498 rows selected.
Note
-----
- outline "OL_NEW" used for this statement
接着看v$SQL里:
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 584 N N Y
果然,新的执行计划出现了,代替了原来的那个执行计划。
执行次数还是1,意味者前面的cursor被flush出去了,这是一个崭新的cursor。
IS_BIND_AWARE=N,IS_BIND_SENSITIVE=N意味着这个SQL不受ACS控制了!