[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分鐘基本結果就出來了。

總結

以上是生活随笔為你收集整理的查询DBA_HIST_ACTIVE_SESS_HISTORY缓慢的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。