查詢DBA_HIST_ACTIVE_SESS_HISTORY緩慢

[20140822]查詢DBA_HIST_ACTIVE_SESS_HISTORY緩慢.txt

生產系統前一段時間我修改了awr保留時間間隔，主要是比較每個月底要執行的報表。

SCOTT@test> select 40*60*24 from dual ;
  40*60*24
----------
     57600

--exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>57600, interval=>60);
-- 設定保留40天，間隔60分鐘

SQL> SELECT * FROM dba_hist_wr_control;

      DBID SNAP_INTERVAL       RETENTION          TOPNSQL
---------- ------------------- ------------------ ----------
  168324986 +00000 01:00:00.0   +00040 00:00:00.0  DEFAULT

--可以昨天當我查詢檢視DBA_HIST_ACTIVE_SESS_HISTORY時非常慢。也許保留時間太長的原因。
--我的查詢語句如下:

select * from DBA_HIST_ACTIVE_SESS_HISTORY  where event = 'enq: TX - row lock contention' and sample_time>=trunc(sysdate);

-- 5，6分鐘都沒有出來。這個讓我仔細看執行計劃，以及檢視定義。
-- 確定主要訪問的是基表sys.WRH$_ACTIVE_SESSION_HISTORY.

-- 檢視定義。
-- WRH$_ACTIVE_SESSION_HISTORY  (Table)
--
CREATE TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY
(
  SNAP_ID                    NUMBER             NOT NULL,
  DBID                       NUMBER             NOT NULL,
  INSTANCE_NUMBER            NUMBER             NOT NULL,
  SAMPLE_ID                  NUMBER             NOT NULL,
  SAMPLE_TIME                TIMESTAMP(3)       NOT NULL,
  SESSION_ID                 NUMBER             NOT NULL,
  SESSION_SERIAL#            NUMBER,
  USER_ID                    NUMBER,
  SQL_ID                     VARCHAR2(13 BYTE),
  SQL_CHILD_NUMBER           NUMBER,
  SQL_PLAN_HASH_VALUE        NUMBER,
  SERVICE_HASH               NUMBER,
  SESSION_TYPE               NUMBER,
  SQL_OPCODE                 NUMBER,
  QC_SESSION_ID              NUMBER,
  QC_INSTANCE_ID             NUMBER,
  CURRENT_OBJ#               NUMBER,
  CURRENT_FILE#              NUMBER,
  CURRENT_BLOCK#             NUMBER,
  SEQ#                       NUMBER,
  EVENT_ID                   NUMBER,
  P1                         NUMBER,
  P2                         NUMBER,
  P3                         NUMBER,
  WAIT_TIME                  NUMBER,
  TIME_WAITED                NUMBER,
  PROGRAM                    VARCHAR2(64 BYTE),
  MODULE                     VARCHAR2(48 BYTE),
  ACTION                     VARCHAR2(32 BYTE),
  CLIENT_ID                  VARCHAR2(64 BYTE),
  FORCE_MATCHING_SIGNATURE   NUMBER,
  BLOCKING_SESSION           NUMBER,
  BLOCKING_SESSION_SERIAL#   NUMBER,
  XID                        RAW(8),
  PLSQL_ENTRY_OBJECT_ID      NUMBER,
  PLSQL_ENTRY_SUBPROGRAM_ID  NUMBER,
  PLSQL_OBJECT_ID            NUMBER,
  PLSQL_SUBPROGRAM_ID        NUMBER,
  FLAGS                      NUMBER
)
PARTITION BY RANGE (DBID, SNAP_ID)
( 
  PARTITION WRH$_ACTIVE_168324986_18057 VALUES LESS THAN (168324986, 18153), 
  PARTITION WRH$_ACTIVE_168324986_18153 VALUES LESS THAN (168324986, 18273), 
  PARTITION WRH$_ACTIVE_168324986_18273 VALUES LESS THAN (168324986, 18369), 
  PARTITION WRH$_ACTIVE_168324986_18369 VALUES LESS THAN (168324986, 18465), 
  PARTITION WRH$_ACTIVE_168324986_18465 VALUES LESS THAN (168324986, 18561), 
  PARTITION WRH$_ACTIVE_168324986_18561 VALUES LESS THAN (168324986, 18657), 
  PARTITION WRH$_ACTIVE_168324986_18657 VALUES LESS THAN (168324986, 18776), 
  PARTITION WRH$_ACTIVE_168324986_18776 VALUES LESS THAN (168324986, 18872), 
  PARTITION WRH$_ACTIVE_168324986_18872 VALUES LESS THAN (168324986, 18968), 
  PARTITION WRH$_ACTIVE_168324986_18968 VALUES LESS THAN (168324986, 19064), 
  PARTITION WRH$_ACTIVE_168324986_19064 VALUES LESS THAN (168324986, MAXVALUE), 
  PARTITION WRH$_ACTIVE_SES_MXDB_MXSN VALUES LESS THAN (MAXVALUE, MAXVALUE)
)
NOPARALLEL
ENABLE ROW MOVEMENT;

--
-- WRH$_ACTIVE_SESSION_HISTORY_PK  (Index)
--
--  Dependencies:
--   WRH$_ACTIVE_SESSION_HISTORY (Table)
--
CREATE UNIQUE INDEX SYS.WRH$_ACTIVE_SESSION_HISTORY_PK ON SYS.WRH$_ACTIVE_SESSION_HISTORY
(DBID, SNAP_ID, INSTANCE_NUMBER, SAMPLE_ID, SESSION_ID)
  LOCAL ( 
  PARTITION WRH$_ACTIVE_168324986_18057, 
  PARTITION WRH$_ACTIVE_168324986_18153, 
  PARTITION WRH$_ACTIVE_168324986_18273, 
  PARTITION WRH$_ACTIVE_168324986_18369, 
  PARTITION WRH$_ACTIVE_168324986_18465, 
  PARTITION WRH$_ACTIVE_168324986_18561, 
  PARTITION WRH$_ACTIVE_168324986_18657, 
  PARTITION WRH$_ACTIVE_168324986_18776, 
  PARTITION WRH$_ACTIVE_168324986_18872, 
  PARTITION WRH$_ACTIVE_168324986_18968, 
  PARTITION WRH$_ACTIVE_168324986_19064, 
  PARTITION WRH$_ACTIVE_SES_MXDB_MXSN
)
NOPARALLEL;

--
-- Non Foreign Key Constraints for Table WRH$_ACTIVE_SESSION_HISTORY
--
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY ADD (
  CONSTRAINT WRH$_ACTIVE_SESSION_HISTORY_PK
  PRIMARY KEY
  (DBID, SNAP_ID, INSTANCE_NUMBER, SAMPLE_ID, SESSION_ID)
  USING INDEX LOCAL
  ENABLE VALIDATE);

--從定義知道，如果要查詢快1點，必須加入DBID, SNAP_ID兩個欄位,查詢範圍才會縮小。
--首先確定snap_id範圍
select * from SYS.DBA_HIST_SNAPSHOT order by 1 desc ;

--我改了一下
SELECT *
  FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE     event like  'enq%: TX - row lock contention'
       AND snap_id in ( select snap_id from DBA_HIST_SNAPSHOT where END_INTERVAL_TIME between trunc(sysdate)-1 and trunc(sysdate))
       AND DBID = 168324986
       and instance_number=1;

--這樣1分鐘基本結果就出來了。