dbms_stats與執行計劃的合理性plan__optimizer_invalidation_period

wisdomone1發表於2013-03-26

老外關於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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章