[20150430]11G SPM與cardinality feedback2
[20150430]11G SPM與cardinality feedback問題2.txt
1.問題說明:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SELECT f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule
FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p
WHERE f.owner = 'SCOTT'
AND f.table_name = 'EMP'
AND f.constraint_type = 'R'
AND SYS.all_cons_columns.constraint_name = f.constraint_name
AND SYS.all_cons_columns.table_name = 'EMP'
AND SYS.all_cons_columns.owner = 'SCOTT'
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION;
--為了測試方便,我建立檔案a.sql,便於多次執行.第1次執行很慢,我的測試機器開始2次需要將近4秒.在第二次執行計劃出現 cardinality feedback.
--再一次硬分析,依舊分析時間很長,執行也很慢.第3次看執行計劃使用快了許多.
--繼續昨天的測試:
http://blog.itpub.net/267265/viewspace-1611161/
--昨天的測試提到SPM與cardinality feedback存在問題,這個問題導致一個奇怪的現象,在spm存在的情況下,每次都是1次硬分析,而由於
--這條語句的特殊性,每次分析時間太長,導致執行時間也延長.
--但是昨天SPM相關檢視dba_sql_plan_baselines記錄如下,是否是另外一個accepted=NO導致的呢?
SCOTT@test> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SQL_7f4b11a38e3e8b09 SQL_PLAN_7yksjnf73x2s93753d4dd YES NO NO AUTO-CAPTURE 9172444460233952009
SQL_7f4b11a38e3e8b09 SQL_PLAN_7yksjnf73x2s9f002201c YES YES NO AUTO-CAPTURE 9172444460233952009
2.啟用看看:
--啟用是這個命令,看了文件,好像使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE不行.至少我這個版本是這樣.
variable v_basenum number;
variable v_text varchar2(1000);
variable v_sql_handle varchar2(30);
variable v_plan_name_1 varchar2(30);
exec :v_sql_handle := 'SQL_7f4b11a38e3e8b09';
exec :v_plan_name_1 := 'SQL_PLAN_7yksjnf73x2s93753d4dd';
exec :v_text := dbms_spm.evolve_sql_plan_baseline(:v_sql_handle,:v_plan_name_1,verify=> 'NO', commit=>'YES');
SCOTT@test> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SQL_7f4b11a38e3e8b09 SQL_PLAN_7yksjnf73x2s93753d4dd YES YES NO AUTO-CAPTURE 9172444460233952009
SQL_7f4b11a38e3e8b09 SQL_PLAN_7yksjnf73x2s9f002201c YES YES NO AUTO-CAPTURE 9172444460233952009
--繼續測試看看.
SCOTT@test> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.44
SCOTT@test> @hide _optimizer_use_feedback
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------ ----------------------- ---------------------- ---------------------- ----------------------
_optimizer_use_feedback optimizer use feedback TRUE TRUE TRUE
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
no rows selected
SCOTT@test> set timing on
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
no rows selected
Elapsed: 00:00:00.03
SCOTT@test> @a
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:03.84
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I EXECUTIONS SQL_PLAN_BASELINE
------------ - ---------- ------------------------------
0 Y 1 SQL_PLAN_7yksjnf73x2s9f002201c
Elapsed: 00:00:00.00
SCOTT@test> @a
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:03.73
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I EXECUTIONS SQL_PLAN_BASELINE
------------ - ---------- ------------------------------
0 N 1 SQL_PLAN_7yksjnf73x2s9f002201c
1 Y 1 SQL_PLAN_7yksjnf73x2s93753d4dd
Elapsed: 00:00:00.00
SCOTT@test> @a
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:00.01
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I EXECUTIONS SQL_PLAN_BASELINE
------------ - ---------- ------------------------------
0 N 1 SQL_PLAN_7yksjnf73x2s9f002201c
1 Y 2 SQL_PLAN_7yksjnf73x2s93753d4dd
Elapsed: 00:00:00.00
--第1次3.84秒,第2次3.73秒,第3次執行0.01秒,可以發現最後執行選擇的計劃的SQL_PLAN_BASELINE='SQL_PLAN_7yksjnf73x2s93753d4dd'.
COTT@test> @share gmzkkrbp9s3zb
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''gmzkkrbp9s3zb''',
SQL_TEXT = SELECT f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p WHERE f.owner = 'SCOTT' AND f.table_name = 'EMP' AND
f.constraint_type = 'R' AND SYS.all_cons_columns.constraint_name = f.constraint_name AND SYS.all_cons_columns.table_name = 'EMP' AND SYS.all_cons_columns.owner = 'SCOTT' AND p.owner = f.r_owner AND p.constraint_name = f.r_constraint_name ORDER BY f.constraint_name,
SYS.all_cons_columns.POSITION
SQL_ID = gmzkkrbp9s3zb
ADDRESS = 00000000BE842A50
CHILD_ADDRESS = 00000000BE7F77F0
CHILD_NUMBER = 0
USE_FEEDBACK_STATS = Y
REASON =
--------------------------------------------------
SQL_TEXT = SELECT f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p WHERE f.owner = 'SCOTT' AND f.table_name = 'EMP' AND
f.constraint_type = 'R' AND SYS.all_cons_columns.constraint_name = f.constraint_name AND SYS.all_cons_columns.table_name = 'EMP' AND SYS.all_cons_columns.owner = 'SCOTT' AND p.owner = f.r_owner AND p.constraint_name = f.r_constraint_name ORDER BY f.constraint_name,
SYS.all_cons_columns.POSITION
SQL_ID = gmzkkrbp9s3zb
ADDRESS = 00000000BE842A50
CHILD_ADDRESS = 00000000B0F3D370
CHILD_NUMBER = 1
REASON =
--------------------------------------------------
PL/SQL procedure successfully completed.
3.關閉SQL_PLAN_BASELINE='SQL_PLAN_7yksjnf73x2s9f002201c'看看:
variable v_plan_name_2 varchar2(30);
exec :v_plan_name_2 := 'SQL_PLAN_7yksjnf73x2s9f002201c';
exec :v_basenum:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_2,attribute_name => 'ENABLED', attribute_value => 'NO');
SCOTT@test> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SQL_7f4b11a38e3e8b09 SQL_PLAN_7yksjnf73x2s93753d4dd YES YES NO AUTO-CAPTURE 9172444460233952009
SQL_7f4b11a38e3e8b09 SQL_PLAN_7yksjnf73x2s9f002201c NO YES NO AUTO-CAPTURE 9172444460233952009
SCOTT@test> alter system flush shared_pool;
System altered.
SCOTT@test> set timing on
SCOTT@test> @a
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:04.89
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
no rows selected
Elapsed: 00:00:00.03
SCOTT@test> @a
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:04.84
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I EXECUTIONS SQL_PLAN_BASELINE
------------ - ---------- ------------------------------
0 Y 1
Elapsed: 00:00:00.00
SCOTT@test> @a
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:03.74
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I EXECUTIONS SQL_PLAN_BASELINE
------------ - ---------- ------------------------------
0 N 1
--問題再次再現,is_shareable='N',而EXECUTIONS=1.
4.刪除SQL_PLAN_BASELINE='SQL_PLAN_7yksjnf73x2s9f002201c'看看:
variable v_plan_name_2 varchar2(30);
exec :v_plan_name_2 := 'SQL_PLAN_7yksjnf73x2s9f002201c';
exec :v_basenum:=DBMS_SPM.drop_sql_plan_baseline (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_2);
SCOTT@test> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SQL_7f4b11a38e3e8b09 SQL_PLAN_7yksjnf73x2s93753d4dd YES YES NO AUTO-CAPTURE 9172444460233952009
--問題依舊(不貼出來了,結果一樣的),看來使用SPM要注意這個問題,估計是bug.
SCOTT@test> alter session set "_optimizer_use_feedback"=false;
Session altered.
Elapsed: 00:00:00.01
SCOTT@test> @a
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:04.83
SCOTT@test> @a
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:00.01
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I EXECUTIONS SQL_PLAN_BASELINE
------------ - ---------- ------------------------------
0 N 1
1 Y 2
Elapsed: 00:00:00.00
--注意這時沒有使用SPM.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1612791/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 11g SQL效能的新特性(二)- Cardinality FeedbackOracleSQL
- Cardinality
- [20150706]11G cardinality feedback問題
- [20120805]11G SPM的學習2.txt
- 【DBA】Oracle 11g 針對SQL效能的新特性(二)- Cardinality FeedbackOracleSQL
- 選擇率(selectivity)與基數(cardinality)
- 【sql調優】cardinality測試與簡析SQL
- ORACLE 11G 使用SPM來調整SQL語句的執行計劃OracleSQL
- [20120806]11G SPM的學習4.txt
- [20120806]11G SPM的學習3.txt
- 7.40 CARDINALITY
- SQL Plan Management(SPM)SQL
- 【SPM】Oracle計劃管理器SPM介紹及用例Oracle
- [20180928]exists與cardinality.txt
- Oracle Database Cardinality FeedbackOracleDatabase
- Cardinality指什麼?
- Cardinality (222)
- Cardinality的計算
- [20130109]SPM與sql profile.txtSQL
- 16.基數(Cardinality)
- CARDINALITY HINT用法小試
- [20120807]11G SPM的學習5.txt--第3方優化優化
- [20120830]11G SPM的學習6.txt--第3方優化.txt優化
- Are we ready for learned cardinality estimation?
- 基數反饋(Cardinality Feedback)
- 那些語句使用cardinality feedback
- Cardinality Feedback基數反饋
- 【SPM】Oracle如何固定執行計劃Oracle
- SPM適用的場景和示例
- 自適應遊標共享(ACS)與sql計劃管理(SPM)的相互影響SQL
- SQL大致流程、SPM、軟軟、軟、硬解析SQL
- (原)汽車行業ERP要求四:SPM行業
- [20130123]spm與sql profile的主要區別在那裡.txtSQL
- zt:Cardinality (SQL statements) 最好的解釋SQL
- ORACLE 通過SPM為SQL語句加HINTOracleSQL
- SPM設定保留空間和保留時效
- 11g oracleexits 與in 之爭Oracle
- 使用SPM和STA進行固定執行計劃