dbms_stats與執行計劃的合理性plan__optimizer_invalidation_period
老外關於dbms_stats統計收集與執行計劃變動問題
http://gavinsoorma.com/2012/09/why-do-my-execution-plans-not-change-after-gathering-statistics-a-look-at-rolling-cursor-invalidations/
SQL> select dbms_stats.get_prefs('NO_INVALIDATE','SYSTEM','MYOBJECTS') from dual;
DBMS_STATS.GET_PREFS('NO_INVAL
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE
/***構建測試表並插入重複值很高的記錄*******/
SQL> create table t_object(object_id int,object_type varchar2(100));
Table created
SQL> insert into t_object select level,'table' from dual connect by level<=100000;
100000 rows inserted
SQL> insert into t_object select level,'index' from dual connect by level<=3;
3 rows inserted
SQL> commit;
Commit complete
SQL> create index idx_t_object on t_object(object_type);
Index created
SQL> select object_type,count(object_id) from t_object group by object_type;
OBJECT_TYPE COUNT(OBJECT_ID)
-------------------------------------------------------------------------------- ----------------
table 100000
index 3
SQL> select /*test2*/ distinct object_type from t_object where object_type='index';
OBJECT_TYPE
--------------------------------------------------------------------------------
index
SQL> select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sqlarea where sql_text like '%/*test2*/%';
SQL_TEXT PLAN_HASH_VALUE SQL_ID PARSE_CALLS EXECUTIONS ADDRESS
-------------------------------------------------------------------------------- --------------- ------------- ----------- ---------- ----------------
select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sq 232555890 28mq12bavybz2 5 5 000007FF06134BD8
select /*test2*/ distinct object_type from t_object where object_type='index' 3554248643 fy7xurbuv7qa2 1 1 000007FF06E96270
SQL> select * from table(dbms_xplan.display_cursor('fy7xurbuv7qa2',null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fy7xurbuv7qa2, child number 0
-------------------------------------
select /*test2*/ distinct object_type from t_object where
object_type='index'
Plan hash value: 3554248643
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT UNIQUE NOSORT| | 3 | 156 | 2 (50)| 00:00:0
|* 2 | INDEX RANGE SCAN | IDX_T_OBJECT | 3 | 156 | 1 (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='index')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
24 rows selected
SQL>
SQL>
SQL>
SQL>
SQL> update t_object set object_type='index' where object_type='table' and rownum<=90000;
90000 rows updated
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'t_object',cascade=>true);
PL/SQL procedure successfully completed
SQL> select /*test2*/ distinct object_type from t_object where object_type='index';
OBJECT_TYPE
--------------------------------------------------------------------------------
index
SQL> select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sqlarea where sql_text like '%/*test2*/%';
SQL_TEXT PLAN_HASH_VALUE SQL_ID PARSE_CALLS EXECUTIONS ADDRESS
-------------------------------------------------------------------------------- --------------- ------------- ----------- ---------- ----------------
select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sq 232555890 28mq12bavybz2 6 6 000007FF06134BD8
select /*test2*/ distinct object_type from t_object where object_type='index' 3554248643 fy7xurbuv7qa2 2 2 000007FF06E96270
SQL> select * from table(dbms_xplan.display_cursor('fy7xurbuv7qa2',null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fy7xurbuv7qa2, child number 0
-------------------------------------
select /*test2*/ distinct object_type from t_object where
object_type='index'
Plan hash value: 3554248643
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT UNIQUE NOSORT| | 3 | 156 | 2 (50)| 00:00:0
|* 2 | INDEX RANGE SCAN | IDX_T_OBJECT | 3 | 156 | 1 (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='index')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
24 rows selected
SQL> alter system set "_optimizer_invalidation_period" = 60 scope=memory;
System altered
SQL> exec dbms_stats.gather_table_stats(user,'t_object',cascade=>true);
PL/SQL procedure successfully completed
SQL> select /*test2*/ distinct object_type from t_object where object_type='index';
OBJECT_TYPE
--------------------------------------------------------------------------------
index
SQL> select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sqlarea where sql_text like '%/*test2*/%';
SQL_TEXT PLAN_HASH_VALUE SQL_ID PARSE_CALLS EXECUTIONS ADDRESS
-------------------------------------------------------------------------------- --------------- ------------- ----------- ---------- ----------------
select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sq 232555890 28mq12bavybz2 7 7 000007FF06134BD8
select /*test2*/ distinct object_type from t_object where object_type='index' 2142696267 fy7xurbuv7qa2 3 3 000007FF06E96270
SQL> select * from table(dbms_xplan.display_cursor('fy7xurbuv7qa2',null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fy7xurbuv7qa2, child number 0
-------------------------------------
select /*test2*/ distinct object_type from t_object where
object_type='index'
Plan hash value: 3554248643
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT UNIQUE NOSORT| | 3 | 156 | 2 (50)| 00:00:0
|* 2 | INDEX RANGE SCAN | IDX_T_OBJECT | 3 | 156 | 1 (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='index')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL_ID fy7xurbuv7qa2, child number 1
-------------------------------------
select /*test2*/ distinct object_type from t_object where
object_type='index'
Plan hash value: 2142696267
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 77 (100)| |
| 1 | SORT UNIQUE NOSORT| | 1 | 6 | 77 (12)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T_OBJECT | 89844 | 526K| 70 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter("OBJECT_TYPE"='index')
44 rows selected
SQL> select * from v$sql_shared_cursor v where v.ADDRESS='000007FF06E96270';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER UNBOUND_CURSOR SQL_TYPE_MISMATCH OPTIMIZER_MISMATCH OUTLINE_MISMATCH STATS_ROW_MISMATCH LITERAL_MISMATCH FORCE_HARD_PARSE EXPLAIN_PLAN_CURSOR BUFFERED_DML_MISMATCH PDML_ENV_MISMATCH INST_DRTLD_MISMATCH SLAVE_QC_MISMATCH TYPECHECK_MISMATCH AUTH_CHECK_MISMATCH BIND_MISMATCH DESCRIBE_MISMATCH LANGUAGE_MISMATCH TRANSLATION_MISMATCH BIND_EQUIV_FAILURE INSUFF_PRIVS INSUFF_PRIVS_REM REMOTE_TRANS_MISMATCH LOGMINER_SESSION_MISMATCH INCOMP_LTRL_MISMATCH OVERLAP_TIME_MISMATCH EDITION_MISMATCH MV_QUERY_GEN_MISMATCH USER_BIND_PEEK_MISMATCH TYPCHK_DEP_MISMATCH NO_TRIGGER_MISMATCH FLASHBACK_CURSOR ANYDATA_TRANSFORMATION INCOMPLETE_CURSOR TOP_LEVEL_RPI_CURSOR DIFFERENT_LONG_LENGTH LOGICAL_STANDBY_APPLY DIFF_CALL_DURN BIND_UACS_DIFF PLSQL_CMP_SWITCHS_DIFF CURSOR_PARTS_MISMATCH STB_OBJECT_MISMATCH CROSSEDITION_TRIGGER_MISMATCH PQ_SLAVE_MISMATCH TOP_LEVEL_DDL_MISMATCH MULTI_PX_MISMATCH BIND_PEEKED_PQ_MISMATCH MV_REWRITE_MISMATCH ROLL_INVALID_MISMATCH OPTIMIZER_MODE_MISMATCH PX_MISMATCH MV_STALEOBJ_MISMATCH FLASHBACK_TABLE_MISMATCH LITREP_COMP_MISMATCH PLSQL_DEBUG LOAD_OPTIMIZER_STATS ACL_MISMATCH FLASHBACK_ARCHIVE_MISMATCH LOCK_USER_SCHEMA_FAILED REMOTE_MAPPING_MISMATCH LOAD_RUNTIME_HEAP_FAILED HASH_MATCH_FAILED PURGED_CURSOR BIND_LENGTH_UPGRADEABLE
------------- ---------------- ---------------- ------------ -------------- ----------------- ------------------ ---------------- ------------------ ---------------- ---------------- ------------------- --------------------- ----------------- ------------------- ----------------- ------------------ ------------------- ------------- ----------------- ----------------- -------------------- ------------------ ------------ ---------------- --------------------- ------------------------- -------------------- --------------------- ---------------- --------------------- ----------------------- ------------------- ------------------- ---------------- ---------------------- ----------------- -------------------- --------------------- --------------------- -------------- -------------- ---------------------- --------------------- ------------------- ----------------------------- ----------------- ---------------------- ----------------- ----------------------- ------------------- --------------------- ----------------------- ----------- -------------------- ------------------------ -------------------- ----------- -------------------- ------------ -------------------------- ----------------------- ----------------------- ------------------------ ----------------- ------------- -----------------------
fy7xurbuv7qa2 000007FF06E96270 000007FF076257D8 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
fy7xurbuv7qa2 000007FF06E96270 000007FF07F14448 1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N
小結：1,oracle預設在取樣會讓cached cursor無效,導致大量的hard parse,容易出現shared pool latch的library cache latch
2,查閱http://space.itpub.net/?uid-9240380-action-viewspace-itemid-757204,
可獲取_optimizer_invalidation_period引數控制：即定義分析物件的遊標多久會無效，這樣因為重新收集統計才會產生更為合理的執行計劃
time window for invalidation of cursors of analyzed objects
3,dbms_stats包的有些引數非常重要,在使用要多加測試
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-757205/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- toad與執行計劃
- 執行計劃-1：獲取執行計劃
- 柱狀圖與執行計劃
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 分析執行計劃最佳化SQLORACLE的執行計劃(轉)SQLOracle
- 執行計劃
- SQL的執行計劃SQL
- 執行計劃的理解.
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- SYBASE執行計劃
- MySQL 執行計劃MySql
- MySQL執行計劃MySql
- sql 執行計劃SQL
- ORACLE執行計劃Oracle
- ORACLE柱狀圖與執行計劃(轉)Oracle
- 分割槽表與堆表執行計劃的不同
- 生成執行計劃的方法
- Oracle中檢視已執行sql的執行計劃OracleSQL
- 【效能優化】執行計劃與直方圖優化直方圖
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- oracle執行計劃與統計資訊的一些總結Oracle
- sqm執行計劃的繫結
- SqlServer的執行計劃如何分析？SQLServer
- 看懂Oracle中的執行計劃Oracle
- 獲取執行計劃的方法
- ORACLE執行計劃的介紹Oracle
- 執行計劃的閱讀方法
- sql的執行計劃 詳解SQL
- ORACLE執行計劃的檢視Oracle
- 執行計劃的代價估算
- oracle執行計劃的使用(EXPLAIN)OracleAI
- ORALCE的執行計劃穩定性
- 檢視執行計劃的方法
- 修改Process Chain的執行計劃AI
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫