[20150402]11G ACS使用问题.txt

2021-12-06 12:41:52

--11G ACS(adaptive cursor sharing) 是11G的新特性,主要用来解决绑定变量窥视的问题,当时使用绑定变量时如果带入的参数插线倾斜
--,导致执行计划出现问题.

两个新列在V$SQL视图里，IS_BIND_SENSITIVE 和 IS_BIND_AWARE，IS_BIND_SENSITIVE为Y的CURSOR，代表这个CURSOR被监控了，而
IS_BIND_AWARE为Y的，代表这个CURSOR每次被执行都要计算它谓词的选择率然后根据选择率查看是否当前共享池中是否有满足要求的执行
计划，如果有重用，如果没有，重新生成一个。

V$SQL_CS_HISTOGRAM视图，主要记录SQL处理的行数的直方图，处理的行数驱动着ACS发挥作用。直方图bucket_id 0-2在每次游标执行的
时候字段count发生变化，每一个bucket代表着操作的数据量，在11.2.0.3版本，当返回的行数在0-1000时候，将会在bucket_id 为0的桶
，1000-1000000，将会在bucket_id为1的桶，多余1000000，将会在bucket_id为2的桶，请读者不要死记数字，各个版本可能不同。

V$SQL_CS_SELECTIVITY，记录游标谓词的选择率范围，最大值，最小值。
V$SQL_CS_STATISTICS 记录游标处理的行数、buffer gets等信息
SQL命令的CURSOR首先会被标注为IS_BIND_SENSITIVE='Y'，在后面的执行过程中会决定是否这个CURSOR是bind aware的。

--实际上在使用中存在一些限制或者称为问题,自己做一个总结,我喜欢通过例子来说明:

1.建立测试环境:

create table t (id number ,name varchar2(20),status varchar2(1));
insert into t select rownum ,rownum||'test' ,decode(rownum,1e3,'N','Y') from dual connect by levelcommit ;
create index i_t_status on t (status);

SCOTT@test> exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns status size 254',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.

--在status上建立直方图.

SCOTT@test> set verify off
SCOTT@test> @tab_lh scott t
COLUMN_NAME          DATA_TYPE DATA_LENGTH N NUM_DISTINCT    DENSITY SAMPLE_SIZE TRANS_LOW            TRANS_HIGH            NUM_NULLS NUM_BUCKETS LAST_ANALYZED       HISTOGRAM       DATA_DEFAULT
-------------------- ---------- ----------- - ------------ ---------- ----------- -------------------- -------------------- ---------- ----------- ------------------- --------------- --------------------
ID                   NUMBER              22 Y         1000       .001        1000 1                    1000                          0           1 2015-04-02 11:16:50 NONE
NAME                 VARCHAR2            20 Y         1000       .001        1000 1000test             9test                         0           1 2015-04-02 11:16:50 NONE
STATUS               VARCHAR2             1 Y            2      .0005        1000 N                    Y                             0           2 2015-04-02 11:16:50 FREQUENCY

2.带入参数执行:

SCOTT@test> variable b varchar2(1);
SCOTT@test> exec :b :='Y'
PL/SQL procedure successfully completed.

select * from t where status = :b;

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f05ajm6at0c2c, child number 0
-------------------------------------
select * from t where status = :b
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |       |     3 (100)|          |
|* 1 | TABLE ACCESS FULL| T    |    999 | 13986 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
--执行计划选择全部扫描,因为status='Y'记录很多.

SCOTT@test> exec :b :='N'
PL/SQL procedure successfully completed.

SCOTT@test> select * from t where status = :b;
ID NAME S
---------- -------------------- -
1000 1000test N

SCOTT@test> SELECT child_number, executions, buffer_gets, is_bind_sensitive,is_bind_aware FROM v$sql WHERE sql_id='f05ajm6at0c2c';
CHILD_NUMBER EXECUTIONS BUFFER_GETS I I
------------ ---------- ----------- - -
0 6 51 Y N

--is_bind_sensitive ='Y',标识绑敏感.

SCOTT@test> select * from V$SQL_CS_HISTOGRAM where sql_id='f05ajm6at0c2c';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AFFEDB98 2509254732 f05ajm6at0c2c            0          0          6
00000000AFFEDB98 2509254732 f05ajm6at0c2c            0          1          0
00000000AFFEDB98 2509254732 f05ajm6at0c2c            0          2          0

--BUCKET_ID = 0 , count=6(我已经执行多次),因为无论status带入'Y','N',因为没有越过1000的限制,count无法进入BUCKET_ID=1.

--至少目前11.2.0.3的规定如下:
V$SQL_CS_HISTOGRAM视图，主要记录SQL处理的行数的直方图，处理的行数驱动着ACS发挥作用。直方图bucket_id 0-2在每次游标执行的
时候字段count发生变化，每一个bucket代表着操作的数据量，在11.2.0.3版本，当返回的行数在0-1000时候，将会在bucket_id 为0的桶
，1000-1000000，将会在bucket_id为1的桶，多余1000000，将会在bucket_id为2的桶.

--也就是如果你的数据分布没有达到这个限制,执行计划不会改变.

3.解决方式,就是加入提示bind_aware:

SCOTT@test> exec :b :='Y'
PL/SQL procedure successfully completed.

SCOTT@test> select /*+ bind_aware */ * from t where status = :b;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2a7xbpaxxck4h, child number 0
-------------------------------------
select /*+ bind_aware */ * from t where status = :b
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |       |     3 (100)|          |
|* 1 | TABLE ACCESS FULL| T    |    999 | 13986 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SCOTT@test> exec :b :='N'
PL/SQL procedure successfully completed.

SCOTT@test> select /*+ bind_aware */ * from t where status = :b;
ID NAME S
---------- -------------------- -
1000 1000test N

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2a7xbpaxxck4h, child number 1
-------------------------------------
select /*+ bind_aware */ * from t where status = :b
Plan hash value: 1307943387
-------------------------------------------------------------------------------------------
| Id | Operation                   | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |        |       |     2 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| T          |      1 |    14 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T_STATUS |      1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

SCOTT@test> select * from V$SQL_CS_HISTOGRAM where sql_id='2a7xbpaxxck4h';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AF69D140 3151382672 2a7xbpaxxck4h            1          0          1
00000000AF69D140 3151382672 2a7xbpaxxck4h            1          1          0
00000000AF69D140 3151382672 2a7xbpaxxck4h            1          2          0
00000000AF69D140 3151382672 2a7xbpaxxck4h            0          0          1
00000000AF69D140 3151382672 2a7xbpaxxck4h            0          1          0
00000000AF69D140 3151382672 2a7xbpaxxck4h            0          2          0
6 rows selected.

--每个变量执行1次.执行计划不一样. 很奇怪提示bind_aware是如何判断的呢?

4.改动一些数据看看:
SCOTT@test> update t set status='N' where id>=971;
30 rows updated.

SCOTT@test> commit ;
Commit complete.

--重复上面的测试,执行如下:
select /*+ bind_aware */ * from t where status = :b

--执行计划还是会变化.

--视乎bind_aware不受这个的限制,我估计这个通过统计计算cost,选择小的cost执行计划.

5.重新增加修改数据:

SCOTT@test> update t set status='Y' where id>=971;
30 rows updated.

SCOTT@test> insert into t values (1001,'1001test','Y');
1 row created.

SCOTT@test> insert into t values (1002,'1002test','N');
1 row created.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> select count(*),status from t group by status ;
COUNT(*) S
---------- -
1001 Y
1 N

exec :b :='Y'
select * from t where status = :b;

SCOTT@test> select * from V$SQL_CS_HISTOGRAM where sql_id='f05ajm6at0c2c';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE7C4BB8 2509254732 f05ajm6at0c2c            0          0          0
00000000BE7C4BB8 2509254732 f05ajm6at0c2c            0          1          1
00000000BE7C4BB8 2509254732 f05ajm6at0c2c            0          2          0

exec :b :='N'
select * from t where status = :b;

SCOTT@test> select * from V$SQL_CS_HISTOGRAM where sql_id='f05ajm6at0c2c';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE7C4BB8 2509254732 f05ajm6at0c2c            0          0          1
00000000BE7C4BB8 2509254732 f05ajm6at0c2c            0          1          1
00000000BE7C4BB8 2509254732 f05ajm6at0c2c            0          2          0

--再执行1次可以发现执行计划改变.

SCOTT@test> select * from t where status = :b;
ID NAME S
---------- -------------------- -
1002 1002test N

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f05ajm6at0c2c, child number 1
-------------------------------------
select * from t where status = :b
Plan hash value: 1307943387
-------------------------------------------------------------------------------------------
| Id | Operation                   | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |        |       |     2 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| T          |      1 |    14 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T_STATUS |      1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
--已经生成了新的执行计划.

6.可以发现上面的执行计划变化,实际上生产系统的情况会非常复杂,比如平时业务模式是访问status='N'的执行次数很高,而'Y'的执行会
很少,而这样导致V$SQL_CS_HISTOGRAM的bucker_id的count差异很大,这样导致一个结果就是执行计划一直不会变化.

SCOTT@test> alter system flush shared_pool;
System altered.

SCOTT@test> select * from V$SQL_CS_HISTOGRAM where sql_id='f05ajm6at0c2c';
no rows selected

SCOTT@test> exec :b :='N'
SCOTT@test> select * from t where status = :b;
--select * from t where status = :b; 执行10次.

SCOTT@test> select * from V$SQL_CS_HISTOGRAM where sql_id='f05ajm6at0c2c';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE600D08 2509254732 f05ajm6at0c2c            0          0         10
00000000BE600D08 2509254732 f05ajm6at0c2c            0          1          0
00000000BE600D08 2509254732 f05ajm6at0c2c            0          2          0

--可以发现这时BUCKET_ID=0,count=10.

exec :b :='N'
select * from t where status = :b;
--select * from t where status = :b; 执行9次.

SCOTT@test> select * from V$SQL_CS_HISTOGRAM where sql_id='f05ajm6at0c2c';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE600D08 2509254732 f05ajm6at0c2c            0          0         10
00000000BE600D08 2509254732 f05ajm6at0c2c            0          1          9
00000000BE600D08 2509254732 f05ajm6at0c2c            0          2          0

--这个时候你看执行计划(status='Y'),可以发现依旧是不会走全表扫描.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f05ajm6at0c2c, child number 0
-------------------------------------
select * from t where status = :b
Plan hash value: 1307943387
-------------------------------------------------------------------------------------------
| Id | Operation                   | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |        |       |     2 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| T          |      1 |    14 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T_STATUS |      1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

--在真实的环境执行的status='N'的次数会很多,这样不要做梦status='Y'的执行计划会改变.
--可以参考我以前写的blog:
http://blog.itpub.net/267265/viewspace-1336242/
http://blog.itpub.net/267265/viewspace-1336353/
http://blog.itpub.net/267265/viewspace-1368531/
http://blog.itpub.net/267265/viewspace-1369051/

--实际上如果这种情况出现,我个人认为最佳的方式就是使用bind_aware提示,或者修改sql代码,直接带入文字值.

7.实际上上面的列有直方图,如果没有直方图,实际上不做等值查询，也有可能在没有直方图的列上使用到ACS。

SCOTT@test> create index i_t_id on t(id);
Index created.

SCOTT@test> variable a number ;
SCOTT@test> exec :a := 1;
PL/SQL procedure successfully completed.

SCOTT@test> select * from t where id > :a;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8w4vqqabx4qxw, child number 0
-------------------------------------
select * from t where id > :a
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |       |     3 (100)|          |
|* 1 | TABLE ACCESS FULL| T    |   1001 | 14014 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SCOTT@test> exec :a := 1002;

PL/SQL procedure successfully completed.

SCOTT@test> select * from t where id > :a;
no rows selected

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8w4vqqabx4qxw, child number 0
-------------------------------------
select * from t where id > :a
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |       |     3 (100)|          |
|* 1 | TABLE ACCESS FULL| T    |   1001 | 14014 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SCOTT@test> select * from t where id > :a;
no rows selected

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8w4vqqabx4qxw, child number 1
-------------------------------------
select * from t where id > :a
Plan hash value: 4153437776
---------------------------------------------------------------------------------------
| Id | Operation                   | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |       |     3 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| T      |      1 |    14 |     3   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T_ID |      1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

-- 生成了新的执行计划.

SCOTT@test> SELECT child_number, executions, buffer_gets, is_bind_sensitive,is_bind_aware FROM v$sql WHERE sql_id='8w4vqqabx4qxw';
CHILD_NUMBER EXECUTIONS BUFFER_GETS I I
------------ ---------- ----------- - -
0 2 17 Y N
1 1 2 Y Y

SCOTT@test> select * from V$SQL_CS_HISTOGRAM where sql_id='8w4vqqabx4qxw';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AFB51FD8 2547145660 8w4vqqabx4qxw            1          0          1
00000000AFB51FD8 2547145660 8w4vqqabx4qxw            1          1          0
00000000AFB51FD8 2547145660 8w4vqqabx4qxw            1          2          0
00000000AFB51FD8 2547145660 8w4vqqabx4qxw            0          0          1
00000000AFB51FD8 2547145660 8w4vqqabx4qxw            0          1          1
00000000AFB51FD8 2547145660 8w4vqqabx4qxw            0          2          0

6 rows selected.

码农公寓

相关文章