[20151105]视图DBA_HIST_SEG_STAT_OBJ.txt
--前一阵子看别人的awr报表.链接http://www.itpub.net/thread-1940496-1-1.html
Segments by Physical Reads
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
** MISSING ** KH_TBS ** MISSING: 19105652/19105652 ** MISSING ** UNDEFINED 139,026,245 15.04
--可以发现上面的内容.从提示看这个对象已经不存在或者删除了.
--实际上我想如果知道当时的对象名称对于分析也许有一些好处.
--我仔细看了dba_hist_开头的视图,发现DBA_HIST_SEG_STAT,DBA_HIST_SEG_STAT_OBJ记录了这些信息.
--从理论讲awr报表生成应该读取的信息一定来源这里.
--按照道理这些信息已经无法找到,不知道通过logminer是否可以发现.
--自己做一个测试验证为什么出现MISSING的情况.
1.测试环境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t123 as select * from dual ;
Table created.
SCOTT@test> create table t456 as select * from dual ;
Table created.
SCOTT@test> drop table t123 purge ;
Table dropped.
SCOTT@test> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
2.检查:
SCOTT@test> select * from DBA_HIST_SEG_STAT_OBJ where object_name in ('T123' ,'T456') and owner=user;
DBID TS# OBJ# DATAOBJ# OWNER OBJECT_NAME SUBOBJECT_ OBJECT_TYPE TABLESPACE_NAME PARTITIO
---------- ---------- ---------- ---------- ------ -------------------- ---------- ------------------ --------------- --------
2071943378 4 319342 319342 SCOTT T456 TABLE USERS NONE
--可以发现表T456在视图DBA_HIST_SEG_STAT_OBJ存在.但是表T123看不见.
--可以推测T123是 OBJ#=319341.
SCOTT@test> column SUBOBJECT_NAME format a30
SCOTT@test> select * from DBA_HIST_SEG_STAT_OBJ where obj# in (319341,319342);
DBID TS# OBJ# DATAOBJ# OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE TABLESPACE_NAME PARTITIO
---------- --- ---------- ---------- ------------- ------------------------------ --------------- ----------- --------------- --------
2071943378 4 319341 319341 ** MISSING ** ** MISSING: 319341/319341 ** MISSING ** UNDEFINED USERS NONE
2071943378 4 319342 319342 SCOTT T456 TABLE USERS NONE
--可以发现建立awr create snapshot时要把这个之时间点建立对象名称记录到DBA_HIST_SEG_STAT_OBJ(基表SYS.WRH$_SEG_STAT_OBJ).
--但是在这个时间点内建立又删除的对象无法记录名称.通过这个视图可以记录查询一些历史对象的名称.大致确定某个索引的建立时间.
--从某种意思讲适当延长awr报表的保存时间还是有积极意义.缺省的设置太小了.我个人建议最好保持45天,这点磁盘空间消耗不会太大.
SCOTT@test> SELECT * FROM dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------ ----------------- ----------
2071943378 +00000 01:00:00.0 +00008 00:00:00.0 50000
--要设置保留40天，间隔60分钟、应该执行：
SCOTT@test> select 40*60*24 from dual ;
40*60*24
----------
57600
--exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>57600, interval=>60);