查询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分钟基本结果就出来了。