我们先来看看跟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
统计的各个子游标的执行情况