本文主要是介绍【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
概述
我们知道,由于绑定变量窥视(Bind Peeking)功能,SQL文在进行硬解析(Hard Parse)时,会代入绑定变量的值来估算选择基数(cardinality )并做成执行计划,而相同的SQL文以后在执行过程中,都会共享初次执行时做成的执行计划。
如果表的数据分布不均或者数据倾斜时,用于估算的变量值和以后执行中的用到的变量值估算选择基数(cardinality )差异很大时,通过绑定变量窥视(Bind Peeking)功能做成的执行计划,针对某些变量值的执行可能都是最优的,甚至可能引起很严重的性能问题。
因此,优化器在11.1以后的版本上,为了解决绑定变量窥视(Bind Peeking)的问题,推出了自适应游标共享(Adaptive Cursor Sharing 以后简称ACS)功能,使包含绑定变量的同一条SQL语句在多次执行时,不会盲目的共享执行计划,而会根据绑定变量值和执行过程中收集信息的反馈,可以使用多个不同执行计划,避免性能问题。
下面我们将通过几个例子来进一步了解ACS功能。
例子1(ACS无效)
首先,我们看一下在绑定变量窥视(Bind Peeking)功能有效,但是自适应游标共享功能无效的情况下,执行以下SQL文的动作。(基于10.2.0.5版本测试)
0.准备测试用表和数据
SQL> conn scott/tiger
Connected.
SQL> create table TEST_ACS(c1 number, c2 varchar2(100));Table created.SQL> BEGIN FOR i IN 1 .. 1000 LOOP FOR j IN 1 .. 10 LOOP INSERT INTO TEST_ACS VALUES (i,'a');COMMIT; END LOOP; END LOOP;
END;
/ 2 3 4 5 6 7 8 9 10 PL/SQL procedure successfully completed.SQL> create index ind1 on TEST_ACS(c1);Index created.SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'TEST_ACS',CASCADE => TRUE); PL/SQL procedure successfully completed.---我们准备了一个表包含10000条数据,c1列有1~1000的值,并且每个值都有10条数据。
---并且在c1列有个索引ind1SQL> SELECT count(c1), count(DISTINCT c1), min(c1), max(c1) FROM TEST_ACS;COUNT(C1) COUNT(DISTINCTC1) MIN(C1) MAX(C1)
---------- ----------------- ---------- ----------10000 1000 1 1000
1.首先我们指定变量值为3,执行下面的SQL文
SQL> var x number;
SQL> exec :x := 3 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------1 a
...3 a30 rows selected.SQL>
2.因为绑定变量窥视(Bind Peeking)功能的影响,所以硬解析选择执行计划时,
会把绑定变量值3代入到SQL文中计算基数,SQL文在10000条数据中选择了30条数据,
所以,选择了索引IND1进行INDEX RANGE SCAN。
SQL> set line 200
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 0
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :xPlan hash value: 3882350253---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_ACS | 30 |
|* 2 | INDEX RANGE SCAN | IND1 | 30 |
---------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("C1"<=:X)
...25 rows selected.
3.我们再指定变量值为9,再次执行下面的SQL文。
虽然这时候SQL文在10000条数据中选择了9000条数据,选择率高达90%,但是因为SQL文已经解析过了,所以会继续用之前的执行计划,E-Rows还是30,使用用INDEX RANGE SCAN.
SQL> exec :x := 9 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x; C1 C2
------ --------------------
1 a
...900 a9000 rows selected.SQL>
SQL> set line 200
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 0
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :xPlan hash value: 3882350253---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_ACS | 30 |
|* 2 | INDEX RANGE SCAN | IND1 | 30 |
---------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("C1"<=:X)
....25 rows selected.SQL>
SQL> select child_number, executions, buffer_gets,plan_hash_value
from v$sql
where sql_id = 'cuf4u4th4w0nz'; 2 3 CHILD_NUMBER EXECUTIONS BUFFER_GETS PLAN_HASH_VALUE
------------ ---------- ----------- ---------------0 2 1239 3882350253
4.我们再次指定变量值为9,执行下面的SQL文。
这时候SQL文还是在10000条数据中选择了9000条数据,但是因为SQL文已经解析过了,所以会继续用之前的执行计划,使用用INDEX RANGE SCAN
SQL> exec :x := 900 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------1 a...900 a9000 rows selected.SQL>
5.无论以后指定变量值是什么,执行计划都会用最初做成的执行计划。
这样通过绑定变量窥视(Bind Peeking)功能做成的执行计划,针对某些变量值的执行可能不是最优的,甚至可能引起很严重的性能问题。
SQL> exec :x := 800 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x; C1 C2
------ --------------------
1 a
...
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 0
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :xPlan hash value: 3882350253---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_ACS | 30 |
|* 2 | INDEX RANGE SCAN | IND1 | 30 |
---------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("C1"<=:X)...25 rows selected.
通过上面的例子,我们看到由于绑定变量窥视(Bind Peeking)的影响,对于变量值为9时,虽让本应该选择Table Full Scan才是最优的执行计划,还是使用了初次执行时根据变量值为3时做成的执行计划。
因此,如果最初用于估算的变量值不能代表大多数变量值时的特点时,对于生产系统,有时候可能是灾难性的。
例子2(ACS有效)
下面让我们看看,在11g后的版本上自适应游标共享(Adaptive Cursor Sharing 以后简称ACS)功能有效时,是如何改善这种这个问题。
ACS相关的动态视图
在以后执行过程中,我们可以通过观察相关的动态视图,来看看在这个过程中都发生了什么。
首先,我们介绍下在以后过程中用到的主要4个视图,下面是相关内容的简单介绍。
V$SQL视图在这个视图中主要有3个和ACS相关的列: is_bind_sensitive:用于监视SQL游标是否是绑定敏感(bind sensitive),是否可用ACS。is_bind_aware:用于监视SQL游标是否意识到需要根据绑定变量值来选择执行计划(bind aware)。is_shareable:用于监视SQL游标是否可以被共享。V$SQL_SHARED_CURSOR用于查看SQL游标没有共享的原因。V$SQL_CS_SELECTIVITY视图主要用来监视SQL游标的各个变量的选择率范围(SELECTIVITY CUBE)。它包含着各个条件谓词、绑定变量值以及它的最大值和最小值等信息。V$SQL_CS_HISTOGRAM根据所操作的行数,记录每个子游标执行的次数直方图。V$SQL_CS_STATISTICS 每个子游标执行的执行状况。(采样信息)
关于以上视图的定义和内容详细可以参考在线文档。
Oracle Database Online Documentation 11g Release 2 (11.2) / Database Reference
V$SQL
V$SQL_SHARED_CURSOR
V$SQL_CS_SELECTIVITY
V$SQL_CS_HISTOGRAM
V$SQL_CS_STATISTICS
下面开始我们的测试 (基于11.2.0.4版本测试):
准备测试用表和数据
0.准备测试用表和数据
SQL> SQL> conn scott/tigerConnected.SQL> SQL> drop table TEST_ACS; Table dropped.SQL> create table TEST_ACS(c1 number, c2 varchar2(100)); Table created. SQL> SQL> BEGIN2 FOR i IN 1 .. 1000 LOOP3 FOR j IN 1 .. 10 LOOP4 INSERT INTO TEST_ACS5 VALUES (i,'a');6 COMMIT;7 END LOOP;8 END LOOP;9 END;10 / PL/SQL procedure successfully completed. SQL> SQL> create index ind1 on TEST_ACS(c1); Index created.SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'TEST_ACS',CASCADE => TRUE); PL/SQL procedure successfully completed.---我们准备了一个表包含10000条数据,c1列有1~1000的值,并且每个值都有10条数据。---并且在c1列有个索引ind1SQL> SELECT count(c1), count(DISTINCT c1), min(c1), max(c1) FROM TEST_ACS;COUNT(C1) COUNT(DISTINCTC1)MIN(C1)MAX(C1)---------- ----------------- ---------- ----------10000 1000 1 1000
Test1:指定变量值为3,首次执行
首先我们指定变量值为3,首次执行下面的SQL文
SQL> var x number;
SQL> exec :x := 3 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------1 a
...3 a 30 rows selected.SQL>
1.因为绑定变量窥视(Bind Peeking)功能的影响,所以硬解析选择执行计划时,会把绑定变量值3代入到SQL文中计算基数,SQL文在10000条数据中选择了30条数据,所以,选择了索引IND1进行INDEX RANGE SCAN。
SQL> set line 200
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 0
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :x Plan hash value: 3882350253 ---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT| ||
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_ACS | 30 |
|* 2 | INDEX RANGE SCAN | IND1 | 30 |
---------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("C1"<=:X)
当SQL文第一次被执行的时候,因为绑定变量窥视(Bind Peeking)功能的影响,所以硬解析选择执行计划时,会把绑定变量值3代入到SQL文中计算选择率。
因为SQL文在10000条数据中选择了30条数据,选择率为0.03,所以优化器选择了通过索引IND1进行访问数据(INDEX RANGE SCAN)。
SQL> col PREDICATE format a20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id,child_number, executions, buffer_gets,2 is_bind_sensitive BS, is_bind_aware BA,IS_SHAREABLE SH,plan_hash_value3 from v$sql4 where sql_id = 'cuf4u4th4w0nz';SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B S PLAN_HASH_VALUE
------------- ------------ ---------- ----------- - - - ---------------
cuf4u4th4w0nz0 1 7 Y N Y 3882350253 ---1.V$SQL.is_bind_sensitive为Y,游标被标示成bind-sensitive,可以使用ACS。
---V$SQL.IS_SHAREABLE为Y,游标可以被共享
---V$SQL.is_bind_aware为N,没有意识到绑定变量不同会影响执行计划SQL>
SQL> select sql_id, child_number, BIND_EQUIV_FAILURE, LOAD_OPTIMIZER_STATS2 from V$SQL_SHARED_CURSOR3 where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER B L
------------- ------------ - -
cuf4u4th4w0nz0 N N---1.游标共享信息V$SQL_SHARED_CURSOR.BIND_EQUIV_FAILURE为N
---V$SQL_SHARED_CURSOR.LOAD_OPTIMIZER_STATS为N (供以后做对比)SQL>
SQL> select *2 from v$sql_cs_statistics3 where sql_id='cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz0 2706503459 Y 1 60 7 0---1.v$sql_cs_statistics采样信息中,记录了游标执行的执行状况,包括处理的行数,消耗的Buffer等信息SQL>
SQL> ---no data in V$SQL_CS_SELECTIVITY for the first time.
SQL> select *2 from v$sql_cs_selectivity3 where sql_id = 'cuf4u4th4w0nz';no rows selected---1.V$SQL_CS_SELECTIVITY 中还没有任何信息。SQL>
SQL> select *2 from V$SQL_CS_HISTOGRAM3 where sql_id='cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz0 0 1 ★
000000008A976370 1615725215 cuf4u4th4w0nz0 1 0
000000008A976370 1615725215 cuf4u4th4w0nz0 2 0 ---1.V$SQL_CS_HISTOGRAM里根据变量值做成的执行次数直方图中,本次执行计算到BUCKET_ID:0中,1次。
根据上面的相关信息,我们可以知道,当包含绑定变量信息的SQL文第一次执行的时候,会发生下面的一些动作:
・SQL文进行硬解析(HARD Parse)
・根据最初绑定变量值为3,进行估算选择率(Bind Peeking)
・游标被标示成bind-sensitive
・选择出最优执行计划保存:
・记录了游标执行的执行状况,包括处理的行数,消耗的Buffer等信息
・记录了游标执行次数直方图
Test2:指定变量值为9,执行SQL文
下面我们传递一个变量9,再次执行SQL文。
SQL> --var x number;
SQL> exec :x := 900 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------1 a 1 a
...900 a 900 a 9000 rows selected.---还是使用最初的执行计划索引扫描。SQL> set line 2000
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 0
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :x Plan hash value: 3882350253---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT| ||
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_ACS | 30 |
|* 2 | INDEX RANGE SCAN | IND1 | 30 |
---------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("C1"<=:X)
虽然SQL文在10000条数据中选择了9000条数据,选择率应该为0.9,
但是因为该SQL文已经硬解析过了,所以这次是软解析,使用以前的执行计划,通过索引IND1进行访问数据(INDEX RANGE SCAN)。
再次观察相关动态视图的变化:
SQL>
SQL> col PREDICATE format a20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id,child_number, executions, buffer_gets,2 is_bind_sensitive BS, is_bind_aware BA,IS_SHAREABLE SH,plan_hash_value3 from v$sql4 where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER EXECUTIONS BUFFER_GETS B B S PLAN_HASH_VALUE
------------- ------------ ---------- ----------- - - - ---------------
cuf4u4th4w0nz0 21239 Y N Y 3882350253---2.V$SQL中的内容没有变化,和指定变量值为3时一样。SQL>
SQL> select sql_id, child_number, BIND_EQUIV_FAILURE, LOAD_OPTIMIZER_STATS2 from V$SQL_SHARED_CURSOR3 where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER B L
------------- ------------ - -
cuf4u4th4w0nz0 N Y ----2.但是因为估算的选择率(0.03)和实际执行的选择率(0.9)差距很大,V$SQL_SHARED_CURSOR游标共享信息中LOAD_OPTIMIZER_STATS变为Y SQL> select *2 from v$sql_cs_statistics3 where sql_id='cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz0 2706503459 Y 1 60 7 0 ---2.v$sql_cs_statistics采样信息并没有变化。(因为是采样,所以不一定随时变化)SQL>
SQL> select *2 from v$sql_cs_selectivity3 where sql_id = 'cuf4u4th4w0nz';no rows selected---2.V$SQL_CS_SELECTIVITY 中还是没有任何信息。SQL> select *2 from V$SQL_CS_HISTOGRAM3 where sql_id='cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz0 0 1
000000008A976370 1615725215 cuf4u4th4w0nz0 1 1
000000008A976370 1615725215 cuf4u4th4w0nz0 2 0 ---2.V$SQL_CS_HISTOGRAM里根据变量值做成的执行次数直方图中,本次执行计算到BUCKET_ID:1中,1次。
根据上面的相关信息,我们可以知道,当SQL文第二次执行,
并且绑定变量值和之前的绑定变量值的选择率很大时,会发生下面的一些动作:
・SQL文进行软解析(Soft Parse)
・使用以前的执行计划执行
・比较统计信息 (估算的选择率:0.03和实际执行的选择率:0.9)
・如果差距很大时,V$SQL_SHARED_CURSOR游标共享信息中LOAD_OPTIMIZER_STATS变为Y。(激活ECS)保存:
・记录了游标执行的执行状况,包括处理的行数,消耗的Buffer等信息
・记录了游标执行次数直方图(根据行数选择BUCKET)
・SQL变为BIND Aware
Test3:指定变量值为9,再次执行SQL文
下面我们传递一个变量9,再次执行SQL文:
SQL> --var x number;
SQL> exec :x := 900 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------
67 a
...900 a9000 rows selected.SQL> ---通过全表扫描执行。SQL> set line 2000
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 1
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :x Plan hash value: 3781643149-----------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------
| 0 | SELECT STATEMENT | ||
|* 1 | TABLE ACCESS FULL| TEST_ACS | 9009 |
-----------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("C1"<=:X)
由于上次执行时发现,由于变量值的影响,SQL文的选择率预估值和实际执行时的差别很大(Bind Aware),所以这次执行SQL文重新编译,执行计划发生了变化,通过全表扫描执行。
再次观察相关动态视图的变化:
SQL> col PREDICATE format a20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id,child_number, executions, buffer_gets,2 is_bind_sensitive BS, is_bind_aware BA,IS_SHAREABLE SH,plan_hash_value3 from v$sql4 where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER EXECUTIONS BUFFER_GETS B B S PLAN_HASH_VALUE
------------- ------------ ---------- ----------- - - - ---------------
cuf4u4th4w0nz0 21239 Y N N 3882350253
cuf4u4th4w0nz1 1 622 Y Y Y 3781643149---3.V$SQL中生成了一个新的游标,
并且新生成的游标1IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y.
原来那个游标0,IS_SHAREABLE变为N.SQL> select sql_id, child_number, BIND_EQUIV_FAILURE, LOAD_OPTIMIZER_STATS2 from V$SQL_SHARED_CURSOR3 where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER B L
------------- ------------ - -
cuf4u4th4w0nz0 N Y
cuf4u4th4w0nz1 Y N ---3. V$SQL_SHARED_CURSOR中生成一条BIND_EQUIV_FAILURE为Y的游标。
(绑定变量值的选择率和存在的游标不符)SQL> select *2 from v$sql_cs_statistics3 where sql_id='cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz1 3116944019 Y 1 9000 622 0
000000008A976370 1615725215 cuf4u4th4w0nz0 2706503459 Y 1 60 7 0 ★---3. v$sql_cs_statistics采样信息中追加了一条最新的采样SQL> select *2 from v$sql_cs_selectivity3 where sql_id = 'cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER PREDICATE RANGE_ID LOWHIGH
---------------- ---------- ------------- ------------ -------------------- ---------- ---------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz1 <=X 0 0.810810 0.990990 ---3. V$SQL_CS_SELECTIVITY 中记录了新追加的游标的选择率范围(依赖于绑定变量值)SQL>
SQL> select *2 from V$SQL_CS_HISTOGRAM3 where sql_id='cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz1 0 0
000000008A976370 1615725215 cuf4u4th4w0nz1 1 1 ★
000000008A976370 1615725215 cuf4u4th4w0nz1 2 0
000000008A976370 1615725215 cuf4u4th4w0nz0 0 1
000000008A976370 1615725215 cuf4u4th4w0nz0 1 1
000000008A976370 1615725215 cuf4u4th4w0nz0 2 0 6 rows selected.---3. V$SQL_CS_HISTOGRAM里记录了新追加的游标的直方图,并记录执行次数到BUCKET_ID:1中,1次。
根据上面的相关信息,我们可以知道,当SQL文第三次执行时(和第二次相同的变量),会发生下面的一些动作:
・SQL文进行硬解析(Hard Parse)
・生成新的执行计划
・V$SQL_SHARED_CURSOR游标共享信息中BIND_EQUIV_FAILURE变为Y。
・V$SQL中生成IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y的新游标1.原来那个游标0,IS_SHAREABLE变为N保存:
・记录游标执行的执行状况,包括处理的行数,消耗的Buffer等信息
・记录游标执行次数直方图(根据行数选择BUCKET)
・SQL变为BIND Aware
・记录游标的选择率范围(selectivity cube)
Test4:指定变量值为3,再次执行SQL文 (SELECTIVITY CUBE)
指定变量值为3 (和最初执行时一样),再次执行SQL文
SQL> --var x number;
SQL> exec :x := 3 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------1 a
...3 a30 rows selected.---SQL文重新解析,通过索引扫描执行SQL> set line 200
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 2
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :x Plan hash value: 3882350253 ---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT| ||
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_ACS | 30 |
|* 2 | INDEX RANGE SCAN | IND1 | 30 |
---------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("C1"<=:X)
这次执行计划发生了变化,SQL文重新解析,通过索引扫描执行。
再次观察相关动态视图的变化:
SQL> ---sql_id:cuf4u4th4w0nz
SQL> col PREDICATE format a20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id,child_number, executions, buffer_gets,2 is_bind_sensitive BS, is_bind_aware BA,IS_SHAREABLE SH,plan_hash_value3 from v$sql4 where sql_id = 'cuf4u4th4w0nz';SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B S PLAN_HASH_VALUE
------------- ------------ ---------- ----------- - - - ---------------
cuf4u4th4w0nz0 21239 Y N N 3882350253
cuf4u4th4w0nz1 1 622 Y Y Y 3781643149
cuf4u4th4w0nz2 1 7 Y Y Y 3882350253 ★---4.V$SQL中生成了一个新游标CHILD# 2,
并且新生成的游标IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y.SQL> select *2 from v$sql_cs_statistics3 where sql_id='cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz2 2706503459 Y 1 60 7 0★
000000008A976370 1615725215 cuf4u4th4w0nz1 3116944019 Y 1 9000 622 0
000000008A976370 1615725215 cuf4u4th4w0nz0 2706503459 Y 1 60 7 0---4. v$sql_cs_statistics采样信息中追加了一条最新的采样SQL>
SQL> ---select CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH
SQL> select *2 from v$sql_cs_selectivity3 where sql_id = 'cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOWHIGH
---------------- ---------- ------------- ------------ -------------------- ---------- ---------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz2 <=X 0 0.002702 0.003302 ★
000000008A976370 1615725215 cuf4u4th4w0nz1 <=X 0 0.810810 0.990990 ---4. V$SQL_CS_SELECTIVITY 中记录了新追加游标CHILD# 2的选择率范围(依赖于绑定变量值)SQL> select *2 from V$SQL_CS_HISTOGRAM3 where sql_id='cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz2 0 1 ★
000000008A976370 1615725215 cuf4u4th4w0nz2 1 0
000000008A976370 1615725215 cuf4u4th4w0nz2 2 0
000000008A976370 1615725215 cuf4u4th4w0nz1 0 0
000000008A976370 1615725215 cuf4u4th4w0nz1 1 1
000000008A976370 1615725215 cuf4u4th4w0nz1 2 0
000000008A976370 1615725215 cuf4u4th4w0nz0 0 1
000000008A976370 1615725215 cuf4u4th4w0nz0 1 1
000000008A976370 1615725215 cuf4u4th4w0nz0 2 0 9 rows selected.---4. V$SQL_CS_HISTOGRAM里记录了CHILD# 2的直方图,并记录执行次数到BUCKET_ID:0中,1次。
根据上面的相关信息,我们可以知道,当SQL文第四次执行时(和第一次相同的变量),
由于第一次生成的游标变成IS_SHAREABLE为N,会发生下面的一些动作:
・SQL文进行硬解析(Hard Parse)
・生成新的执行计划
・V$SQL中生成IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y的新游标.保存:
・记录游标执行的执行状况,包括处理的行数,消耗的Buffer等信息
・记录游标执行次数直方图(根据行数选择BUCKET)
・记录游标的选择率范围(selectivity cube)
Test5:指定变量值为5,再次执行SQL文 (Merge)
指定变量值为5,再次执行SQL文.
我们知道对于本条SQL文变量值为5时符合条件的选择率是50/10000=0.005,并不在以前执行过游标的选择率范围内(变量值为3的选择率范围为0.002702~0.003302;变量值为9的选择率范围为0.810810~0.990990),
所以SQL文 依然是进行了硬解析。
SQL> --var x number;
SQL> exec :x := 5 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ -------------------- 1 a
...5 a 5 a 50 rows selected.
---SQL文选择了最优的执行计划,通过索引扫描。
SQL> set line 200
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 3
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :x Plan hash value: 3882350253 ---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT| ||
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_ACS | 50 |
|* 2 | INDEX RANGE SCAN | IND1 | 50 |
---------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("C1"<=:X)
SQL文重新解析,通过索引扫描执行。再次观察相关动态视图的变化:
SQL> col PREDICATE format a20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id,child_number, executions, buffer_gets,2 is_bind_sensitive BS, is_bind_aware BA,IS_SHAREABLE SH,plan_hash_value3 from v$sql4 where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER EXECUTIONS BUFFER_GETS B B S PLAN_HASH_VALUE
------------- ------------ ---------- ----------- - - - ---------------
cuf4u4th4w0nz0 21239 Y N N 3882350253
cuf4u4th4w0nz1 1 622 Y Y Y 3781643149
cuf4u4th4w0nz2 1 7 Y Y N 3882350253
cuf4u4th4w0nz3 1 11 Y Y Y 3882350253 ★---5.V$SQL中生成了一个新游标CHILD#3,CHILD#3游标的IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y;而且我们注意到和新生成CHILD#3拥有相同执行计划的CHILD#2的IS_SHAREABLE变成了N,也就所说CHILD#2不再被共享进入了age out队列。SQL> select *2 from v$sql_cs_statistics3 where sql_id='cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz3 365452098 Y 1100 11 0★
000000008A976370 1615725215 cuf4u4th4w0nz2 2706503459 Y 1 60 7 0
000000008A976370 1615725215 cuf4u4th4w0nz1 3116944019 Y 1 9000 622 0
000000008A976370 1615725215 cuf4u4th4w0nz0 2706503459 Y 1 60 7 0---5. v$sql_cs_statistics采样信息中追加了一条最新的采样SQL> select *2 from v$sql_cs_selectivity3 where sql_id = 'cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ -------------------- ---------- ---------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz3 <=X 0 0.002702 0.005504★
000000008A976370 1615725215 cuf4u4th4w0nz2 <=X 0 0.002702 0.003302
000000008A976370 1615725215 cuf4u4th4w0nz1 <=X 0 0.810810 0.990990---5. V$SQL_CS_SELECTIVITY 中记录了新追加游标CHILD# 3的选择率范围(依赖于绑定变量值5)而且CHILD#3的选择率范围(0.002702~0.005504)把CHILD#2的选择率范围也包含了在内(0.002702~0.003302)。SQL> select *2 from V$SQL_CS_HISTOGRAM3 where sql_id='cuf4u4th4w0nz';ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz 3 0 1 ★
000000008A976370 1615725215 cuf4u4th4w0nz 3 1 0 ★
000000008A976370 1615725215 cuf4u4th4w0nz 3 2 0 ★
000000008A976370 1615725215 cuf4u4th4w0nz 2 0 1
000000008A976370 1615725215 cuf4u4th4w0nz 2 1 0
000000008A976370 1615725215 cuf4u4th4w0nz 2 2 0
000000008A976370 1615725215 cuf4u4th4w0nz 1 0 0
000000008A976370 1615725215 cuf4u4th4w0nz 1 1 1
000000008A976370 1615725215 cuf4u4th4w0nz 1 2 0
000000008A976370 1615725215 cuf4u4th4w0nz 0 0 1
000000008A976370 1615725215 cuf4u4th4w0nz 0 1 1
000000008A976370 1615725215 cuf4u4th4w0nz 0 2 0 12 rows selected.---5. V$SQL_CS_HISTOGRAM里记录了CHILD# 3的直方图,并记录执行次数到BUCKET_ID:0中,1次。
根据上面的相关信息,我们可以知道,当SQL文第五次执行时(不在之前的变量值的选择率范围内),会发生下面的一些动作:
・SQL文进行硬解析(Hard Parse)
・生成新的执行计划
・V$SQL中生成IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y的新游标.
・把之前有相同执行计划的游标IS_SHAREABLE标记为N,成为age out的对象。
・合并具有相同计划的游标(Merge)保存:
・记录游标执行的执行状况,包括处理的行数,消耗的Buffer等信息
・记录游标执行次数直方图(根据行数选择BUCKET)
・记录合并后游标的选择率范围(selectivity cube)
ACS通过这种游标合并的操作,可以尽量减少子游标的数量,避免生成过多的子游标,减少内存等资源的浪费。
补充
我们也看到,在V$SQL_CS_HISTOGRAM视图中,每个子游标都有3行直方图数据,分别为Bucket 0、Bucket 1和Bucket 2,针对SQL文的每次执行,Oracle都会在相应的Bucket 行中记录子游标的执行次数。
子游标的执行次数到底记录在哪个Bucket 呢?
事实上,Oracle是根据实际执行所操作的总行数(Row Source Processing)来生成直方图的。
一般条数较多时,记录的方式如下:
Bucket 0: <1024(1K)行 ,
Bucket 1:大于或等于1024行小于1024*1024 (1M)行,
Bucket 2:大于或等于1024*1024 (1M)行。
而这个直方图最大的作用在于,比较SQL文的Child#0的Bucket 的高度(COUNT),如果其中Bucket的高度满足一定条件时,就会启用ACS功能(Bind Aware)。
※注意:
这仅仅是判断逻辑中的最简单的一种方式,还有其他更为复杂的判断逻辑,在这里暂时不做介绍。
ACS 的处理流程
下面引用一下Oracle ACE Mohamed Houri做的一个ACS流程图,来总体的回顾一下ACS的处理过程。
总结
通过上面的例子,我们基本了解了ACS的基本处理流程和ACS的好处:
解决由于绑定变量窥视功能导致的性能问题。
参考
Oracle Database Online Documentation 12c Release 1 (12.1) / Database SQL Tuning Guide
>Adaptive Cursor Sharing
版权声明:本文为博主原创文章,转载请注明出处,谢谢。http://blog.csdn.net/lukeunique
这篇关于【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!