[20120420]11GR2新特性ACS的使用问题.txt
11GR2中采用Adaptive Cursor Sharing一定程度解决了bind peeking的问题。在我的测试中遇到一些问题，自己写一个例子测试如下：
1.建立测试环境:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> CREATE TABLE t1 AS
SELECT ROWNUM id1,FLOOR(SQRT(ROWNUM)) id2 ,'test' NAME FROM DUAL CONNECT BY LEVEL
SQL> insert into t1 select rownum+9999,100 ,'test' NAME FROM DUAL CONNECT BY LEVEL
SQL> create index i_t1_id2 on t1(id2);
Index created.
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => user
,TabName => 'T1'
,Estimate_Percent => NULL
,Method_Opt => 'FOR COLUMNS ID2 SIZE 254'
,Degree => 4
,Cascade => TRUE
,No_Invalidate => TRUE);
END;
/
SQL> column low_value format a10
SQL> column high_value format a10
SQL> column data_type format a10
SQL> select column_name,data_type,num_distinct,low_value,high_value,num_buckets,histogram from dba_tab_cols where wner=user and table_name='T1';
COLUMN_NAME DATA_TYPE NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ------------ ---------- ---------- ----------- ---------------
ID1 NUMBER 19998 C102 C3026463 1 NONE
ID2 NUMBER 100 C102 C202 100 FREQUENCY
NAME CHAR 1 74657374 74657374 1 NONE
SQL>
--可以发现ID2建立的直方图是FREQUENCY直方图。
SQL> column ENDPOINT_ACTUAL_VALUE format a10
SQL> column COLUMN_NAME format a10
SQL> column owner format a10
SQL> column table_name format a10
SQL> select * from dba_tab_histograms where wner=user and table_name='T1' and column_name='ID2';
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SCOTT T1 ID2 3 1
SCOTT T1 ID2 8 2
SCOTT T1 ID2 15 3
SCOTT T1 ID2 24 4
SCOTT T1 ID2 35 5
SCOTT T1 ID2 48 6
SCOTT T1 ID2 63 7
SCOTT T1 ID2 80 8
SCOTT T1 ID2 99 9
SCOTT T1 ID2 120 10
....
SCOTT T1 ID2 7920 88
SCOTT T1 ID2 8099 89
SCOTT T1 ID2 8280 90
SCOTT T1 ID2 8463 91
SCOTT T1 ID2 8648 92
SCOTT T1 ID2 8835 93
SCOTT T1 ID2 9024 94
SCOTT T1 ID2 9215 95
SCOTT T1 ID2 9408 96
SCOTT T1 ID2 9603 97
SCOTT T1 ID2 9800 98
SCOTT T1 ID2 9999 99
SCOTT T1 ID2 19998 100
100 rows selected.
2.检查与建立测试脚本,适当编辑一下：
set head off trimout on trimspool on
column a format a100
spool acs.sql
select 'set termout off' a from dual
union all
select 'variable a number;' a from dual
union all
select 'exec :a :='||rownum||';'||chr(10)||'select * from t1 where id2= :a;' a from dual connect by level
union all
select 'exec :a :='||'100;' a from dual
union all
select 'select * from t1 where id2= :a;' a from dual connect by level
union all
select 'set termout on' a from dual ;
--产生的sql脚本中:a=100的执行了108+1次，其他情况的执行了109次，两种正好相等。
--并且在脚本执行顺序上是最后执行:a := 100的语句执行108次。
3.调用acs.sql并执行它，很明显在:a 100时选择使用索引是最好的执行计划，而等于:a :=100 选择全表扫描最佳。
SQL> host cat dpc.sql
select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS cost'));
SQL> alter system flush shared_pool;
System altered.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------------------------------------
cursor_sharing string EXACT
SQL
SQL> @acs.sql
SQL> @dpc.sql
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 01yvuvyfm4fhb, child number 0
-------------------------------------
select * from t1 where id2= :a
Plan hash value: 3984699272
----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T1_ID2 | 3 | 1 (0)|
----------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID2"=:A)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
30 rows selected.
--可以发现执行计划还是使用索引.并且知道sql_id='01yvuvyfm4fhb'.
SQL> @ share 01yvuvyfm4fhb
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''01yvuvyfm4fhb''',
SQL_TEXT = select * from t1 where id2= :a
SQL_ID = 01yvuvyfm4fhb
ADDRESS = 00000000997A7898
CHILD_ADDRESS = 000000009363BCF0
CHILD_NUMBER = 0
LOAD_OPTIMIZER_STATS = Y
--------------------------------------------------
PL/SQL procedure successfully completed.
--可以发现仅仅存在一个子光标。并没有产生2个光标！
4.查看与ACS有关的试图：
SQL> select * from V$SQL_CS_SELECTIVITY where sql_id='01yvuvyfm4fhb';
no rows selected
SQL> select * from V$SQL_CS_HISTOGRAM where sql_id='01yvuvyfm4fhb';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000997A7898 2637314571 01yvuvyfm4fhb 0 0 109
00000000997A7898 2637314571 01yvuvyfm4fhb 0 1 109
00000000997A7898 2637314571 01yvuvyfm4fhb 0 2 0
SQL> select * from V$SQL_CS_STATISTICS where sql_id='01yvuvyfm4fhb';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
00000000997A7898 2637314571 01yvuvyfm4fhb 0 336594526 N 1 19998 146 0
00000000997A7898 2637314571 01yvuvyfm4fhb 0 2342552567 Y 1 6 71 0
00000000997A7898 2637314571 01yvuvyfm4fhb 0 1237562873 N 1 42 5 0
-- 从V$SQL_CS_HISTOGRAM看sql_id='01yvuvyfm4fhb',蓝色部分都是109次。
--换一句话讲如果在具体应用中如果执行:a :=100的次数不超过其他数据的执行次数，ACS将不会生效。
5.我们再执行一次:a:=100的情况看看。
SQL> exec :a :=100;
PL/SQL procedure successfully completed.
SQL> select * from t1 where id2= :a;
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 01yvuvyfm4fhb, child number 1
-------------------------------------
select * from t1 where id2= :a
Plan hash value: 3617692013
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 16 (100)|
|* 1 | TABLE ACCESS FULL| T1 | 9999 | 16 (0)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
--------------------------------------------------
1 - filter("ID2"=:A)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
29 rows selected.
--可以发现现在才出现全表扫描的执行计划。
SQL> column sql_text format a30
SQL> column is_obsolete format a10
SQL> column is_bind_aware format a10
SQL> column is_shareable format a10
SQL> column is_bind_aware format a10
SQL> SELECT sql_text, sql_id, child_number,executions, plsql_exec_time, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_id='01yvuvyfm4fhb';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PLSQL_EXEC_TIME IS_OBSOLET I IS_BIND_AW IS_SHAREAB
------------------------------ ------------- ------------ ---------- --------------- ---------- - ---------- ----------
select * from t1 where id2= :a 01yvuvyfm4fhb 0 218 0 N Y N Y
select * from t1 where id2= :a 01yvuvyfm4fhb 1 1 0 N Y Y Y
SQL> exec :a :=99;
PL/SQL procedure successfully completed.
SQL> select * from t1 where id2= :a;
SQL> @dpc
SQL> @dpc
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 01yvuvyfm4fhb, child number 2
-------------------------------------
select * from t1 where id2= :a
Plan hash value: 3984699272
----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 199 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T1_ID2 | 199 | 1 (0)|
----------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 99
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID2"=:A)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
30 rows selected.
SQL> SELECT sql_text, sql_id, child_number,executions, plsql_exec_time, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_id='01yvuvyfm4fhb';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PLSQL_EXEC_TIME IS_OBSOLET I IS_BIND_AW IS_SHAREAB
------------------------------ ------------- ------------ ---------- --------------- ---------- - ---------- ----------
select * from t1 where id2= :a 01yvuvyfm4fhb 0 218 0 N Y N N
select * from t1 where id2= :a 01yvuvyfm4fhb 1 1 0 N Y Y Y
select * from t1 where id2= :a 01yvuvyfm4fhb 2 1 0 N Y Y Y
--可以发现child_number=0的is_shareable='N',不再共享。最后将被淘汰出共享池。
6.查看与ACS有关的试图：
SQL> select * from V$SQL_CS_SELECTIVITY where sql_id='01yvuvyfm4fhb';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000997A7898 2637314571 01yvuvyfm4fhb 2 =A 0 0.008956 0.010946
00000000997A7898 2637314571 01yvuvyfm4fhb 1 =A 0 0.449977 0.549972
SQL> select * from V$SQL_CS_HISTOGRAM where sql_id='01yvuvyfm4fhb';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000997A7898 2637314571 01yvuvyfm4fhb 2 0 1
00000000997A7898 2637314571 01yvuvyfm4fhb 2 1 0
00000000997A7898 2637314571 01yvuvyfm4fhb 2 2 0
00000000997A7898 2637314571 01yvuvyfm4fhb 1 0 0
00000000997A7898 2637314571 01yvuvyfm4fhb 1 1 1
00000000997A7898 2637314571 01yvuvyfm4fhb 1 2 0
00000000997A7898 2637314571 01yvuvyfm4fhb 0 0 109
00000000997A7898 2637314571 01yvuvyfm4fhb 0 1 109
00000000997A7898 2637314571 01yvuvyfm4fhb 0 2 0
9 rows selected.
SQL> select * from V$SQL_CS_STATISTICS where sql_id='01yvuvyfm4fhb';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
00000000997A7898 2637314571 01yvuvyfm4fhb 2 1937997561 Y 1 398 5 0
00000000997A7898 2637314571 01yvuvyfm4fhb 1 336594526 Y 1 9999 103 0
00000000997A7898 2637314571 01yvuvyfm4fhb 0 336594526 N 1 19998 146 0
00000000997A7898 2637314571 01yvuvyfm4fhb 0 2342552567 Y 1 6 71 0
00000000997A7898 2637314571 01yvuvyfm4fhb 0 1237562873 N 1 42 5 0
7.总结：
可以发现ACS在解决bind peeking上依旧存在一定的局限行，要解决它当然有许多方法。也许最好的方法还是加入提示/*+ BIND_AWARE */ .
时间： 2024-10-11 06:04:34