[20201210]11G ACS相關問題.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2740941/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20191202]關於hugepages相關問題.txt
- [20180819]關於父子游標問題(11g).txt
- [20190108]rlwrap sqlplus tee相關問題.txtSQL
- [20180413]熱備模式相關問題.txt模式
- Oracle 11g RAC之HAIP相關問題總結OracleAI
- [20201126]11g VPD的問題.txt
- [20190110]rlwrap sqlplus tee相關問題3.txtSQL
- [20180413]熱備模式相關問題2.txt模式
- [20191220]關於共享記憶體段相關問題.txt記憶體
- [20230306]學習UNIFIED audit--dg相關問題.txtNifi
- [20190929]bash使用bc計算的相關問題.txt
- [20201210]sql語句優化.txtSQL優化
- [20191223]關於共享記憶體段相關問題3.txt記憶體
- 11G Adaptive Cursor Sharing(ACS)的研究APT
- electron相關問題
- django相關問題Django
- Docker 相關問題Docker
- octomap相關問題
- [20240313]toad gather_plan_statistics執行計劃相關問題.txt
- 大模型相關問題大模型
- python pip相關問題Python
- SpringBoot-相關問題Spring Boot
- Java相關問題整理Java
- [20201116]11g連線謂詞推入push_pred問題.txt
- [20191129]關於hugepages的問題.txt
- [20181123]關於降序索引問題.txt索引
- [20180403]關於時區問題.txt
- RUST所有權相關問題Rust
- 瀏覽器相關問題瀏覽器
- mysql相關問題總結MySql
- Spring相關問題記錄Spring
- Silverlight安裝相關問題
- 關於 go-micro 相關問題Go
- 關於盒模型相關的問題模型
- [20230317]關於TIME_WAIT問題.txtAI
- [20210520]關於主鍵索引問題.txt索引
- [20190918]關於函式索引問題.txt函式索引
- [20181229]關於字串的分配問題.txt字串