【性能优化】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