[20151105]檢視DBA_HIST_SEG_STAT_OBJ.txt

[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);