[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很容易派生出许多子光标，或者讲问题多多。在真正的生产系统使用中一定要注意。
--//原作者的测试就篇幅很大，我的测试无法完全还原他的测试。不过基本道理是不变的。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2740941/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
