V$ACTIVE_SESSION_HISTORY 顯示資料庫中的取樣會話活動。ASH每秒從v$session中取快照，存在V$ACTIVE_SESSION_HISTORY中，並收集所有活動會話的等待資訊。若ASH資料被重新整理到磁碟，則需要從DBA_HIS_ACTIVE_SESS_HISTORY檢視中查詢相關資訊。

該檢視是ASH的核心，用以記錄活動SESSION的歷史等待資訊，每秒取樣一次，這部分內容記錄在記憶體中，期望值是記錄一個小時的內容。

列名	資料型別	說明
SAMPLE_ID	NUMBER	樣本的ID
SAMPLE_TIME	TIMESTAMP(3)	取樣本的時間
SESSION_ID	NUMBER	會話識別符號; 對映到 V$SESSION.SID
SESSION_SERIAL#	NUMBER	會話序列號 (用於唯一標識一個會話的物件); 對映到 V$SESSION.SERIAL#
USER_ID	NUMBER	Oracle使用者識別符號; 對映到 V$SESSION.USER#
SQL_ID	VARCHAR2(13)	會話在取樣時執行的 SQL 語句的 SQL 識別符號
SQL_CHILD_NUMBER	NUMBER	Child number of the SQL statement that the session was executing at the time of sampling
SQL_PLAN_HASH_VALUE	NUMBER	sql遊標計劃的數值表示形式。這所有會話樣本的資訊可能不可用。v$session不包含此資訊。
FORCE_MATCHING_SIGNATURE	NUMBER	The signature used when the CURSOR_SHARING parameter is set to FORCE
SQL_OPCODE	NUMBER	Indicates what phase of operation the SQL statement was in; maps to V$SESSION.COMMAND。 “V$SESSION” for information on interpreting this column
SERVICE_HASH	NUMBER	Hash that identifies the Service; maps to V$ACTIVE_SERVICES.NAME_HASH
SESSION_TYPE	VARCHAR2(10)	會話型別: · FOREGROUND · BACKGROUND
SESSION_STATE	VARCHAR2(7)	會話狀態: · WAITING · ON CPU
QC_SESSION_ID	NUMBER	查詢協調器的會話ID。This information is only available if the sampled session is a parallel query slave. For all other sessions, the value is 0.
QC_INSTANCE_ID	NUMBER	查詢協調器例項的ID。 This information is only available if the sampled session is a parallel query slave. For all other sessions, the value is 0.
BLOCKING_SESSION	NUMBER	阻塞會話的會話識別符號。Populated only when the session was waiting for enqueues or a “buffer busy” wait. Maps toV$SESSION.BLOCKING_SESSION.
BLOCKING_SESSION_STATUS	VARCHAR2(11)	阻塞會話的狀態: · VALID · NO HOLDER · GLOBAL · NOT IN WAIT · UNKNOWN
BLOCKING_SESSION_SERIAL#	NUMBER	阻塞會話的序列號
EVENT	VARCHAR2(64)	If SESSION_STATE = WAITING, then the event for which the session was waiting for at the time of sampling.If SESSION_STATE = ON CPU, then this column will be NULL.See Also: “Oracle Wait Events”
EVENT_ID	NUMBER	Identifier of the resource or event for which the session is waiting or for which the session last waited. Interpretation is similar to that of theEVENT column.
EVENT#	NUMBER	Number of the resource or event for which the session is waiting or for which the session last waited. Interpretation is similar to that of theEVENT column.
SEQ#	NUMBER	序列號唯一標識等待（增加每個等待）
P1TEXT	VARCHAR2(64)	第一個附加引數的文字
P1	NUMBER	第一個附加引數
P2TEXT	VARCHAR2(64)	第二個引數的文字
P2	NUMBER	第二個附加引數
P3TEXT	VARCHAR2(64)	第三個附加引數的文字
P3	NUMBER	第三個附加引數
WAIT_CLASS	VARCHAR2(64)	Wait class name of the event for which the session was waiting at the time of sampling. Interpretation is similar to that of the EVENT column. Maps to V$SESSION.WAIT_CLASS.
WAIT_CLASS_ID	NUMBER	等待的會話在等待的時間取樣的事件的類識別符號。Interpretation is similar to that of the EVENTcolumn. Maps to V$SESSION.WAIT_CLASS_ID.
WAIT_TIME	NUMBER	0 if the session was waiting at the time of samplingTotal wait time for the event for which the session last waited if the session was on the CPU when sampledWhether or not WAIT_TIME = 0 is what is useful to find the SESSION_STATE at the time of sampling, rather than the actual value of WAIT_TIMEitself. Maps to V$SESSION.WAIT_TIME.
TIME_WAITED	NUMBER	If SESSION_STATE = WAITING, then the time that the session actually spent waiting for that EVENT. This column is set for waits that were in progress at the time the sample was taken.If a wait event lasted for more than a second and was caught waiting in more than one session sample row, then the actual time spent waiting for that wait event will be populated in the last of those session sample rows. At any given time, this information will not be available for the latest session sample.
XID	RAW(8)	Transaction ID that the session was working on at the time of sampling. V$SESSION does not contain this information.
CURRENT_OBJ#	NUMBER	物件ID的會話被引用的物件。此資訊僅供如果會話在等待申請，叢集，併發和使用者I / O等待事件。對映到 V$SESSION.ROW_WAIT_OBJ#.
CURRENT_FILE#	NUMBER	File number of the file containing the block that the session is referencing. This information is only available if the session was waiting for Cluster, Concurrency, and User I/O wait events. Maps to V$SESSION.ROW_WAIT_FILE#.
CURRENT_BLOCK#	NUMBER	ID of the block that the session is referencing. This information is only available if the session was waiting for Cluster, Concurrency, and User I/O wait events. Maps to V$SESSION.ROW_WAIT_BLOCK#.
PROGRAM	VARCHAR2(48)	作業系統程式的名稱
MODULE	VARCHAR2(48)	Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure
ACTION	VARCHAR2(32)	Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_ACTION procedure
CLIENT_ID	VARCHAR2(64)	Client identifier of the session; maps to V$SESSION.CLIENT_IDENTIFIER

查詢最近一分鐘內，最消耗CPU的sql語句

select sql_id,count(*),
round(count(*)/sum(count(*)) over (),2) pctload
from v$active_session_history
where sample_time > sysdate -1/(24*60)
and session_type <> 'BACKGROUND'
and session_state= 'ON CPU'
group by sql_id
order by count(*) desc;

查詢最近一分鐘內，最消耗I/O的sql語句
select ash.sql_id,count(*)
from v$active_session_history ash,v$event_name evt
where ash.sample_time > sysdate -1/(24*60)
and ash.session_state = 'WAITING'
and ash.event_id = evt.event_id
and evt.wait_class = 'USER I/O'
group by ash.sql_id
order by count(*) desc;

查詢最近一分鐘內，最消耗CPU的session
select session_id,count(*)
from v$active_session_history
where session_state = 'ON CPU'
and sample_time > sysdate -1/(24*60)
group by session_id
order by count(*) desc;

查詢最近一分鐘內，最消耗資源的sql語句
select ash.sql_id,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAIT",
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0))
"IO",
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)
group by ash.sql_id
order by sum(decode(ash.session_state,'ON CPU',1,1)) desc;

查詢最近一分鐘內，最消耗資源的session
select ash.session_id,ash.session_serial#,ash.user_id,ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAITING",
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0))
"IO",
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where en.event# = ash.event# and ash.sample_time > sysdate -1/(24*60)
group by ash.session_id,ash.user_id,ash.session_serial#,ash.program
order by sum(decode(ash.session_state,'ON CPU',1,1))

今天工作中用到了，當時比較急也不知道怎麼查，下來總結了一下！

檢視兩個時間點之間的等待事件

SQL>

select session_id,SAMPLE_TIME,SQL_ID,SQL_PLAN_HASH_VALUE,EVENT,BLOCKING_SESSION from V$ACTIVE_SESSION_HISTORY WHERE SAMPLE_TIME BETWEEN TO_DATE('20150205 03:00:00','YYYYMMDD HH24:MI:SS') and TO_DATE('20150205 03:03:00','YYYYMMDD HH24:MI:SS');

SESSION_ID SAMPLE_TIME SQL_ID SQL_PLAN_HASH_VALUE EVENT BLOCKING_SESSION

---------- ------------------------------ ------------- ------------------- -------------------- ----------------

288 05-2月 -15 03.01.07.768 上午 0 null event

268 05-2月 -15 03.00.41.232 上午 0

286 05-2月 -15 03.00.41.232 上午 0 control file paralle

l write

295 05-2月 -15 03.00.41.232 上午 0 control file paralle

l write

268 05-2月 -15 03.00.40.232 上午 0

286 05-2月 -15 03.00.40.232 上午 0

268 05-2月 -15 03.00.39.232 上午 0 db file sequential r

ead

檢視等待事件並分組排序

SQL> select count(*),EVENT from V$ACTIVE_SESSION_HISTORY

WHERE SAMPLE_TIME BETWEEN TO_DATE('20150205 03:00:00','YYYYMMDD HH24:MI:SS')

and TO_DATE('20150205 03:03:00','YYYYMMDD HH24:MI:SS') group by event order by count(*) desc;

COUNT(*) EVENT

---------- --------------------

4

2 db file sequential r

ead

2 control file paralle

l write

2 log file parallel wr

ite

1 null event

1 log file sync

6 rows selected

v$action_session_history

相關文章