Adaptive Cursor Sharing(ACS)的研究

最新推荐文章于 2022-08-09 13:16:10 发布
bigclouder
最新推荐文章于 2022-08-09 13:16:10 发布
阅读量707
点赞数
分类专栏： oracle 调优
本文链接：https://blog.csdn.net/kiwi_kid/article/details/44786793
版权
oracle 调优专栏收录该内容
20 篇文章 1 订阅
订阅专栏
我们先来看看跟ACS相关的三个隐藏参数，是用来控制是否启用ACS
col ksppinm for a30
col ksppstvl for a20
col ksppdesc for a35
SELECT   ksppinm, ksppstvl, ksppdesc
FROM   x$ksppi x, x$ksppcv y
WHERE   x.indx = y.indx AND  ksppinm = '_optimizer_adaptive_cursor_sharing';
KSPPINM                        KSPPSTVL             KSPPDESC
------------------------------ -------------------- -----------------------------------
_optimizer_adaptive_cursor_sha TRUE                 optimizer adaptive cursor sharing
ring

SELECT   ksppinm, ksppstvl, ksppdesc
FROM   x$ksppi x, x$ksppcv y
WHERE   x.indx = y.indx AND  ksppinm = '_optimizer_extended_cursor_sharing';
KSPPINM                        KSPPSTVL             KSPPDESC
------------------------------ -------------------- -----------------------------------
_optimizer_extended_cursor_sha UDO                  optimizer extended cursor sharing
ring

SELECT   ksppinm, ksppstvl, ksppdesc
FROM   x$ksppi x, x$ksppcv y
WHERE   x.indx = y.indx AND  ksppinm = '_optimizer_extended_cursor_sharing_rel';
KSPPINM                        KSPPSTVL             KSPPDESC
------------------------------ -------------------- -----------------------------------
_optimizer_extended_cursor_sha SIMPLE               optimizer extended cursor sharing f
ring_rel
所以如果我们要关闭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;

下面我们就来进行实验
准备一张测试表，数据分布极不均匀
20:10:16 scott@prod> select count(*) from test where object_id=1;

     COUNT(*)
-------------
      2400000

Elapsed: 00:00:00.60
20:11:41 scott@prod> select count(*) from test;

     COUNT(*)
-------------
      2406496

20:07:06 scott@prod> create index idx_test on test(object_id);

Index created.

收集柱状图
exec dbms_stats.gather_table_stats('SCOTT','TEST',method_opt=>'for all columns size skewonly');

20:14:41 scott@prod> select COLUMN_NAME,HISTOGRAM from user_tab_columns where TABLE_NAME='TEST' and column_name='OBJECT_ID';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      HEIGHT BALANCED

关于histogram总共有两种，一种是HEIGHT BALANCED，另外一种是FREQUENCY，这个是oracle是根据NDV来进行选择
如果有254个以下的非空的不同值，就是FREQUENCY的柱状图
如果有254个以上的非空的不同值，就是HEIGHT BALANCED的柱状图
关于柱状图的其他的特性，我会在另外的文章中再进行总结
由于set autotrace on 不是显示的实际的执行计划，是根据统计信息进行估算的，并不一定能够反应真是的执行计划，我们使用
select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced'));来显示正确的执行计划

20:26:37 scott@prod> var x number
20:26:51 scott@prod> exec :x:=48056

PL/SQL procedure successfully completed.

20:27:03 scott@prod> select * from test where object_id=:x;

20:35:50 sys@prod> select sql_id,sql_text,EXECUTIONS  from v$sql where sql_text ='select * from test where object_id=:x';

SQL_ID        SQL_TEXT                                    EXECUTIONS
------------- ---------------------------------------- -------------
fk1y97mvmdu8f select * from test where object_id=:x                1

20:33:22 sys@prod> select * from table(dbms_xplan.display_cursor('fk1y97mvmdu8f',null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fk1y97mvmdu8f, child number 0
-------------------------------------
select * from test where object_id=:x

Plan hash value: 2473784974

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     3 |   285 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST |     3 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - SEL$1 / TEST@SEL$1
   2 - SEL$1 / TEST@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

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

   1 - :X (NUMBER): 48056

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

   2 - access("OBJECT_ID"=:X)

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

   1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
       "TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
       "TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
       "TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
       "TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
       "TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
       "TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
       "TEST"."EDITION_NAME"[VARCHAR2,30]
   2 - "TEST".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

可以看到此时走的是索引的范围扫描

我们换object_id=1的，可以看到，总共240多万的表，object_id=1的就有240万，按道理这种肯定是应该走全表扫描的
exec :x:=1
select * from test where object_id=:x;

20:36:23 sys@prod>  select sql_id,sql_text,EXECUTIONS  from v$sql where sql_text ='select * from test where object_id=:x';

SQL_ID        SQL_TEXT                                    EXECUTIONS
------------- ---------------------------------------- -------------
fk1y97mvmdu8f select * from test where object_id=:x                2

可以看到这个子游标的执行次数已经变成了2次
在来看此时的执行计划
20:38:39 sys@prod> select * from table(dbms_xplan.display_cursor('fk1y97mvmdu8f',null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fk1y97mvmdu8f, child number 0
-------------------------------------
select * from test where object_id=:x

Plan hash value: 2473784974

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     3 |   285 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST |     3 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - SEL$1 / TEST@SEL$1
   2 - SEL$1 / TEST@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

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

   1 - :X (NUMBER): 48056

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

   2 - access("OBJECT_ID"=:X)

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

   1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
       "TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
       "TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
       "TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
       "TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
       "TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
       "TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
       "TEST"."EDITION_NAME"[VARCHAR2,30]
   2 - "TEST".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
还是索引的范围的扫描，现在现在走这个执行计划是不合适的，由于绑定变量窥视，oracle走错了执行计划
select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,
     BUFFER_GETS/EXECUTIONS BG_PER_EX,
     IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
     from v$sql where sql_id='fk1y97mvmdu8f'; 


select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,
BUFFER_GETS/EXECUTIONS BG_PER_EX,
IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
from v$sql where sql_id='fk1y97mvmdu8f'; 

 CHILD_NUMBER PLAN_HASH_VALUE    EXECUTIONS     BG_PER_EX B B S
------------- --------------- ------------- ------------- - - -
            0      2473784974             2          3913 Y N Y
            
从v$SQL中，可以看到这个cursor的数据，其中
IS_BIND_SENSITIVE=Y，表明使用绑定变量窥视来生成这次执行计划，这次执行计划是取决于这个绑定变量的，如果Oracle发现有其他的绑定变量出现，是可能生成其他的执行计划的。
IS_BIND_AWARE=N，表明Oracle还没有使用extended cursor sharing。
IS_SHAREABLE=Y，表明这个cursor可以被再次使用，即能够共享；反之，设为N代表着这个cursor已经过时了，不会被再用了，这个cursor将会等待被age out出shared pool。

此时我们在执行这条sql语句
select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_text ='select * from test where object_id=:x';

SQL_ID        SQL_TEXT                                  CHILD_NUMBER    EXECUTIONS I I I PLAN_HASH_VALUE
------------- ---------------------------------------- ------------- ------------- - - - ---------------
fk1y97mvmdu8f select * from test where object_id=:x                0             2 Y N N      2473784974
fk1y97mvmdu8f select * from test where object_id=:x                1             1 Y Y Y      1357081020
可以看到重新生成了新的子游标
查看新游标的执行计划
Elapsed: 00:00:00.13
06:14:18 sys@orcl> select * from table(dbms_xplan.display_cursor('fk1y97mvmdu8f',1,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fk1y97mvmdu8f, child number 1
-------------------------------------
select * from test where object_id=:x

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  5528 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |  1369K|   125M|  5528   (1)| 00:01:07 |
--------------------------------------------------------------------------

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

   1 - SEL$1 / TEST@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TEST"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - :X (NUMBER): 100

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

   1 - filter("OBJECT_ID"=:X)

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

   1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
       "TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
       "TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
       "TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
       "TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
       "TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
       "TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
       "TEST"."EDITION_NAME"[VARCHAR2,30]
       
如果此时我们赋予一个另外的值，我们再重新执行以下这个sql语句
06:18:00 kiwi@orcl> exec :x:=57603

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
06:19:14 kiwi@orcl> select * from test where object_id=:x;


SQL_ID        SQL_TEXT                                  CHILD_NUMBER    EXECUTIONS I I I PLAN_HASH_VALUE
------------- ---------------------------------------- ------------- ------------- - - - ---------------
fk1y97mvmdu8f select * from test where object_id=:x                0             2 Y N N      2473784974
fk1y97mvmdu8f select * from test where object_id=:x                1             4 Y N N      1357081020
fk1y97mvmdu8f select * from test where object_id=:x                2             1 Y Y Y      2473784974

我们此时如果在反复的执行
06:26:39 kiwi@orcl> exec :x:=100

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
06:27:05 kiwi@orcl> select * from test where object_id=:x;

SQL_ID        SQL_TEXT                                  CHILD_NUMBER    EXECUTIONS I I I PLAN_HASH_VALUE
------------- ---------------------------------------- ------------- ------------- - - - ---------------
fk1y97mvmdu8f select * from test where object_id=:x                0             2 Y N N      2473784974
fk1y97mvmdu8f select * from test where object_id=:x                1             4 Y N N      1357081020
fk1y97mvmdu8f select * from test where object_id=:x                2             4 Y Y Y      2473784974
fk1y97mvmdu8f select * from test where object_id=:x                3             1 Y Y Y      1357081020
这样重复的执行下来，oracle就会找到可以被重复使用是几种执行计划，并把他们共享出去

ACS相关的视图
select child_number,predicate,range_id,low,high from v$sql_cs_selectivity         
where sql_id='fk1y97mvmdu8f' order by 1;  
 CHILD_NUMBER PREDICATE                                     RANGE_ID LOW        HIGH
------------- ---------------------------------------- ------------- ---------- ----------
            2 =X                                                   0 0.000018   0.000022
            3 =X                                                   0 0.892941   1.091373
统计的各个子游标的选择性范围            

select child_number,bind_set_hash_value,peeked,executions,rows_processed,buffer_gets                        
from v$sql_cs_statistics where sql_id='fk1y97mvmdu8f'                                                       
order by 1;

 CHILD_NUMBER BIND_SET_HASH_VALUE P    EXECUTIONS ROWS_PROCESSED   BUFFER_GETS
------------- ------------------- - ------------- -------------- -------------
            0          2251692860 Y             1              2             5
            1           336594526 Y             1        1380000        110576
            2          1059637539 Y             1              2             5
            3           336594526 Y             1           1411           111
统计的各个子游标的执行情况
bigclouder
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Adaptive Cursor Sharing(ACS)的研究

我们先来看看跟ACS相关的三个隐藏参数，是用来控制是否启用ACScol ksppinm for a30col ksppstvl for a20col ksppdesc for a35SELECT ksppinm, ksppstvl, ksppdescFROM x$ksppi x, x$ksppcv yWHERE x.indx = y.indx AND kspp
复制链接

扫一扫
专栏目录