老外关于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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-757205/
扫一扫
1040
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
