[20201210]11G ACS相关问题.txt

来源：这里教程网时间：2026-03-03 16:18:05 作者：

[20201210]11G ACS相关问题.txt --//昨天看崔华<基于oracle的sql优化>，里面有ACS(自适应游标共享)的测试，我仅仅重复测试，加强理解: 1.环境： SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试建立： SCOTT@book> create table t1 as select * from dba_objects; Table created. SCOTT@book> create index idx_t1 on t1(object_type); Index created. SCOTT@book> update t1 set object_type='TABLE' where rownum<60001; 60000 rows updated. SCOTT@book> update t1 set object_type='CLUSTER' where rownum<2; 1 row updated. SCOTT@book> commit ; Commit complete. SCOTT@book> commit ; Commit complete. SCOTT@book> select count(*) from t1; COUNT(*) ---------- 87033 SCOTT@book> select count(*) from t1 where object_type='CLUSTER'; COUNT(*) ---------- 2 SCOTT@book> select count(*) from t1 where object_type='TABLE'; COUNT(*) ---------- 61720 SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't1',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE auto ',Cascade => True ,No_Invalidate => false); PL/SQL procedure successfully completed. SCOTT@book> select column_name,num_buckets,histogram from dba_tab_col_statistics where table_name='T1' and column_name='OBJECT_TYPE'; COLUMN_NAME NUM_BUCKETS HISTOGRAM -------------------- ----------- --------------- OBJECT_TYPE 34 FREQUENCY --//OBJECT_type建立了FREQUENCY直方图。 SCOTT@book> select object_type,count(*) from t1 group by object_type order by 2 desc; OBJECT_TYPE COUNT(*) ------------------- ---------- TABLE 61720 SYNONYM 7638 JAVA CLASS 6065 INDEX 3520 TYPE 1592 VIEW 1291 JAVA RESOURCE 940 LOB 856 PACKAGE 715 PACKAGE BODY 678 TRIGGER 625 JAVA DATA 323 INDEX PARTITION 258 FUNCTION 226 TYPE BODY 131 TABLE PARTITION 91 SEQUENCE 86 PROCEDURE 65 XML SCHEMA 54 LIBRARY 49 OPERATOR 40 QUEUE 18 INDEXTYPE 9 RULE SET 8 DIRECTORY 7 DIMENSION 5 JOB 5 EVALUATION CONTEXT 4 DATABASE LINK 3 CONTEXT 3 MATERIALIZED VIEW 3 JAVA SOURCE 2 CLUSTER 2 JOB CLASS 1 34 rows selected. 3.测试： SCOTT@book> alter system flush shared_pool; System altered. SCOTT@book> var x varchar2(30); SCOTT@book> exec :x := 'CLUSTER'; PL/SQL procedure successfully completed. SCOTT@book> select count(*) from t1 where object_type = :x; COUNT(*) ---------- 2 SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 1846089035 7mgjr79r0k5ab 0 6e09154b SCOTT@book> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_id='7mgjr79r0k5ab'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ ------------- ------------- ---------- select count(*) from t1 where object_type = :x 7mgjr79r0k5ab 1 1 SCOTT@book> column bs format a2 SCOTT@book> column ba format a2 SCOTT@book> column sh format a2 SCOTT@book> select child_number,executions,buffer_gets,is_bind_sensitive bs,is_bind_aware ba,is_shareable sh,plan_hash_value from v$sql where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE ------------ ---------- ----------- -- -- -- --------------- 0 1 53 Y N Y 1970818898 SCOTT@book> @ dpc 7mgjr79r0k5ab '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 7mgjr79r0k5ab, child number 0 ------------------------------------- select count(*) from t1 where object_type = :x Plan hash value: 1970818898 ----------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | INDEX RANGE SCAN| IDX_T1 | 2 | 14 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=852): 'CLUSTER' Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_TYPE"=:X) SCOTT@book> exec :x := 'TABLE'; PL/SQL procedure successfully completed. SCOTT@book> select count(*) from t1 where object_type = :x; COUNT(*) ---------- 61720 SCOTT@book> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_id='7mgjr79r0k5ab'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ ------------- ------------- ---------- select count(*) from t1 where object_type = :x 7mgjr79r0k5ab 1 2 SCOTT@book> @ dpc 7mgjr79r0k5ab '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 7mgjr79r0k5ab, child number 0 ------------------------------------- select count(*) from t1 where object_type = :x Plan hash value: 1970818898 ----------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | INDEX RANGE SCAN| IDX_T1 | 2 | 14 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=852): 'CLUSTER' Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_TYPE"=:X) SCOTT@book> select child_number,executions,buffer_gets,is_bind_sensitive bs,is_bind_aware ba,is_shareable sh,plan_hash_value from v$sql where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE ------------ ---------- ----------- -- -- -- --------------- 0 2 307 Y N Y 1970818898 SCOTT@book> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7mgjr79r0k5ab'; no rows selected --//再次执行： :x= 'TABLE'. SCOTT@book> select count(*) from t1 where object_type = :x; COUNT(*) ---------- 61720 SCOTT@book> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_id='7mgjr79r0k5ab'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ ------------- ------------- ---------- select count(*) from t1 where object_type = :x 7mgjr79r0k5ab 2 3 --//version_count=2. SCOTT@book> select child_number,executions,buffer_gets,is_bind_sensitive bs,is_bind_aware ba,is_shareable sh,plan_hash_value from v$sql where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE ------------ ---------- ----------- -- -- -- --------------- 0 2 307 Y N N 1970818898 1 1 502 Y Y Y 2101382132 --//产生新的子光标。并且is_bind_sensitive,is_bind_aware,is_shareable的值均为Y。说明该语句标记为bind_aware.并且原来的子光 --//标is_shareable标识为N,不再共享,这里跟原作者的测试不一致,他的测试还是is_shareable=Y。 SCOTT@book> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ --------- ---------- ---------- ---------- 1 =X 0 0.638241 0.780072 --//上述的范围在可选择率S，上下浮动10%。总记录数87033,OBJECT_TYPE='TABLE',占61720。 61720/87033*0.9 = .63824066733308055564 61720/87033*1.1 = .78007192674043179023 --//基本一致。 SCOTT@book> select round(61720/87033*0.9,6) low ,round(61720/87033*1.1,6) high from dual; LOW HIGH ---------- ---------- .638241 .780072 4.继续测试： SCOTT@book> exec :x := 'INDEX'; PL/SQL procedure successfully completed. SCOTT@book> select count(*) from t1 where object_type = :x; COUNT(*) ---------- 3520 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 7mgjr79r0k5ab, child number 2 ------------------------------------- select count(*) from t1 where object_type = :x Plan hash value: 1970818898 ----------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 16 (100)| | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | INDEX RANGE SCAN| IDX_T1 | 3520 | 24640 | 16 (0)| 00:00:01 | ----------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=852): 'INDEX' Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_TYPE"=:X) SCOTT@book> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_id='7mgjr79r0k5ab'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ ------------- ------------- ---------- select count(*) from t1 where object_type = :x 7mgjr79r0k5ab 3 4 --//有生成新的子光标。 SCOTT@book> select child_number,executions,buffer_gets,is_bind_sensitive bs,is_bind_aware ba,is_shareable sh,plan_hash_value from v$sql where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE ------------ ---------- ----------- -- -- -- --------------- 0 2 307 Y N N 1970818898 1 1 502 Y Y Y 2101382132 2 1 15 Y Y Y 1970818898 --//有生成新的子光标。 SCOTT@book> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------------------------------------- ---------- ---------- ---------- 2 =X 0 0.036400 0.044489 1 =X 0 0.638241 0.780072 SCOTT@book> select round(3520/87033*0.9,6) low ,round(3520/87033*1.1,6) high from dual; LOW HIGH ---------- ---------- .0364 .044489 --//low，high不再CHILD_NUMBER=1的范围里面，不会合并。 SCOTT@book> exec :x := 'SYNONYM'; PL/SQL procedure successfully completed. SCOTT@book> select count(*) from t1 where object_type = :x; COUNT(*) ---------- 7638 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 7mgjr79r0k5ab, child number 3 ------------------------------------- select count(*) from t1 where object_type = :x Plan hash value: 1970818898 ----------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 32 (100)| | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | INDEX RANGE SCAN| IDX_T1 | 7638 | 53466 | 32 (0)| 00:00:01 | ----------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=852): 'SYNONYM' Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_TYPE"=:X) SCOTT@book> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_id='7mgjr79r0k5ab'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ ------------- ------------- ---------- select count(*) from t1 where object_type = :x 7mgjr79r0k5ab 4 5 --//有生成了新的子光标。 SCOTT@book> select child_number,executions,buffer_gets,is_bind_sensitive bs,is_bind_aware ba,is_shareable sh,plan_hash_value from v$sql where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE ------------ ---------- ----------- -- -- -- --------------- 0 2 307 Y N N 1970818898 1 1 502 Y Y Y 2101382132 2 1 15 Y Y N 1970818898 3 1 94 Y Y Y 1970818898 --//注意子光标CHILD_NUMBER=2,is_shareable='N',不再共享。 SCOTT@book> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------------------------------------- ---------- ---------- ---------- 3 =X 0 0.036400 0.096536 2 =X 0 0.036400 0.044489 1 =X 0 0.638241 0.780072 SCOTT@book> select round(7638/87033*0.9,6) low ,round(7638/87033*1.1,6) high from dual; LOW HIGH ---------- ---------- .078984 .096536 --//执行计划与CHILD_NUMBER＝2一致，low，high合并，变成范围在0 0.036400 ~~ 0.096536 之间。 SCOTT@book> exec :x := 'JAVA CLASS'; PL/SQL procedure successfully completed. SCOTT@book> select count(*) from t1 where object_type = :x; COUNT(*) ---------- 6065 SCOTT@book> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_id='7mgjr79r0k5ab'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ ------------- ------------- ---------- select count(*) from t1 where object_type = :x 7mgjr79r0k5ab 4 6 --//并没有生成新的子光标。与作者不同，主要一些信息不一样。 SCOTT@book> select child_number,executions,buffer_gets,is_bind_sensitive bs,is_bind_aware ba,is_shareable sh,plan_hash_value from v$sql where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE ------------ ---------- ----------- -- -- -- --------------- 0 2 307 Y N N 1970818898 1 1 502 Y Y Y 2101382132 2 1 15 Y Y N 1970818898 3 2 186 Y Y Y 1970818898 SCOTT@book> select round(6065/87033*0.9,6) low ,round(6065/87033*1.1,6) high from dual; LOW HIGH ---------- ---------- .062718 .076655 --//low,high在0 0.036400 ~~ 0.096536之间。不会产生新的子光标。 SCOTT@book> exec :x := 'CLUSTER'; PL/SQL procedure successfully completed. SCOTT@book> select count(*) from t1 where object_type = :x; COUNT(*) ---------- 2 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 7mgjr79r0k5ab, child number 4 ------------------------------------- select count(*) from t1 where object_type = :x Plan hash value: 1970818898 ----------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | INDEX RANGE SCAN| IDX_T1 | 2 | 14 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=852): 'CLUSTER' Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_TYPE"=:X) --//有生成新的子光标。 SCOTT@book> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_id='7mgjr79r0k5ab'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ ------------- ------------- ---------- select count(*) from t1 where object_type = :x 7mgjr79r0k5ab 5 7 SCOTT@book> select child_number,executions,buffer_gets,is_bind_sensitive bs,is_bind_aware ba,is_shareable sh,plan_hash_value from v$sql where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE ------------ ---------- ----------- -- -- -- --------------- 0 2 307 Y N N 1970818898 1 1 502 Y Y Y 2101382132 2 1 15 Y Y N 1970818898 3 2 186 Y Y N 1970818898 4 1 3 Y Y Y 1970818898 --//子光标 child_number=3,is_shareable=N,不再共享。 SCOTT@book> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------------------------------------- ---------- ---------- ---------- 3 =X 0 0.036400 0.096536 2 =X 0 0.036400 0.044489 1 =X 0 0.638241 0.780072 --//嗯，什么没有CHILD_NUMBER＝4的情况呢。 SCOTT@book> select round(2/87033*0.9,6) low ,round(2/87033*1.1,6) high from dual; LOW HIGH ---------- ---------- .000021 .000025 --//要再执行1次原来的语句，不知道为什么？ SCOTT@book> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------------------------------------- ---------- ---------- ---------- 4 =X 0 0.000021 0.000025 3 =X 0 0.036400 0.096536 2 =X 0 0.036400 0.044489 1 =X 0 0.638241 0.780072 --//low的范围发生变化,时间上我数值估计差异太大，单独生成新的子光标。很奇怪没有合并。 SCOTT@book> exec :x := 'JAVA CLASS'; PL/SQL procedure successfully completed. SCOTT@book> select count(*) from t1 where object_type = :x; COUNT(*) ---------- 6065 SCOTT@book> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_id='7mgjr79r0k5ab'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ ------------- ------------- ---------- select count(*) from t1 where object_type = :x 7mgjr79r0k5ab 6 8 --//又产生新的子光标。 SCOTT@book> select child_number,executions,buffer_gets,is_bind_sensitive bs,is_bind_aware ba,is_shareable sh,plan_hash_value from v$sql where sql_id='7mgjr79r0k5ab'; CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE ------------ ---------- ----------- -- -- -- --------------- 0 2 307 Y N N 1970818898 1 1 502 Y Y Y 2101382132 2 1 15 Y Y N 1970818898 3 2 186 Y Y N 1970818898 4 1 23 Y Y N 1970818898 5 1 92 Y Y Y 1970818898 6 rows selected. --//child_number = 1,5 的 is_shareable =Y.其它不再共享。 SCOTT@book> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity ; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------------------------------------- ---------- ---------- ---------- 5 =X 0 0.000021 0.076655 4 =X 0 0.000021 0.000025 3 =X 0 0.036400 0.096536 2 =X 0 0.036400 0.044489 1 =X 0 0.638241 0.780072 SCOTT@book> select round(6065/87033*0.9,6) low ,round(6065/87033*1.1,6) high from dual; LOW HIGH ---------- ---------- .062718 .076655 --//low，high范围再次出现变化。总之ACS很容易派生出许多子光标，或者讲问题多多。在真正的生产系统使用中一定要注意。 --//原作者的测试就篇幅很大，我的测试无法完全还原他的测试。不过基本道理是不变的。