【性能优化】optimizer statistics统计信息管理技巧

视图 DBA_OPTSTAT_OPERATIONS  记录了详细的DBMS_STATS操作历史，可以看到 包括 gather_database_stats (auto) 、gather_table_stats(到表级别)、copy_table_stats(到表级别)。其数据来源于 WRI$_OPTSTAT_OPR   SQL> select distinct operation from DBA_OPTSTAT_OPERATIONS; OPERATION ---------------------------------------------------------------- copy_table_stats gather_database_stats gather_table_stats lock_table_stats unlock_table_stats purge_stats gather_database_stats (auto) SQL> select dbms_stats.get_stats_history_availability from dual; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 24-APR-13 08.31.36.886874000 AM +00:00 ==》最早可用的历史统计信息   SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31 ==> 统计信息的保留期 exec dbms_stats.alter_stats_history_retention(10);   ==》修改统计信息保留期 select * from dba_tab_stats_history ==》查询某张表的 统计信息历史情况， 但是注意dba_tab_stats_history 并不记录实际的历史统计信息数据   function diff_table_stats_in_history( ownname varchar2, tabname varchar2, time1 timestamp with time zone, time2 timestamp with time zone default null, pctthreshold number default 10) return clob;   diff_table_stats_in_history 用以列出 统计信息历史差异     SQL> select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2013-05-25 09:01:46 SQL> insert into opt_test select rownum from dual connect by level <=10000; 10000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(user,'OPT_TEST'); PL/SQL procedure successfully completed. SQL> set long 999999999 select report, maxdiffpct from table(dbms_stats.diff_table_stats_in_history('SYS','OPT_TEST',to_timestamp('2013-05-25 09:01:46','YYYY-MM-DD hh24:mi:ss'))); /     SQL> select table_name from dba_tables where table_name like '%OPTSTAT%'; TABLE_NAME -------------------------------------------------------------------------------------------------------------------------------- WRI$_OPTSTAT_SYNOPSIS$ WRI$_OPTSTAT_HISTHEAD_HISTORY WRI$_OPTSTAT_HISTGRM_HISTORY WRI$_OPTSTAT_SYNOPSIS_HEAD$ WRI$_OPTSTAT_SYNOPSIS_PARTGRP OPTSTAT_USER_PREFS$ OPTSTAT_HIST_CONTROL$ WRI$_OPTSTAT_OPR_TASKS WRI$_OPTSTAT_OPR WRI$_OPTSTAT_AUX_HISTORY WRI$_OPTSTAT_IND_HISTORY WRI$_OPTSTAT_TAB_HISTORY   SQL> desc WRI$_OPTSTAT_TAB_HISTORY            ==》 实际存放了表的历史统计信息 Name Null? Type ----------------------------------------- -------- ---------------------------- OBJ# NOT NULL NUMBER SAVTIME TIMESTAMP(6) WITH TIME ZONE FLAGS NUMBER ROWCNT NUMBER BLKCNT NUMBER AVGRLN NUMBER SAMPLESIZE NUMBER ANALYZETIME DATE CACHEDBLK NUMBER CACHEHIT NUMBER LOGICALREAD NUMBER SPARE1 NUMBER SPARE2 NUMBER SPARE3 NUMBER SPARE4 VARCHAR2(1000) SPARE5 VARCHAR2(1000) SPARE6 TIMESTAMP(6) WITH TIME ZONE