轉載--V$ACTIVE_SESSION_HISTORY檢視的使用
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) |
會話型別:
|
SESSION_STATE |
VARCHAR2(7) |
會話狀態:
|
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) |
阻塞會話的狀態:
|
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 EVENT column. 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_TIME itself. 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 |
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;
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;
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;
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;
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))
相關文章
- v$active_session_history檢視[轉]Session
- V$ACTIVE_SESSION_HISTORY檢視的使用Session
- v$active_session_history檢視Session
- V$ACTIVE_SESSION_HISTORY 檢視中包含的資訊Session
- 【轉載】使用檢視V$RESERVED_WORDS得到Oracle的保留字Oracle
- 11G 中的V$ACTIVE_SESSION_HISTORY 檢視沒有資料Session
- [20211214]檢視檢視V$ACTIVE_SESSION_HISTORY遇到奇怪問題.txtSession
- 自己總結的V$檢視(轉)
- v$sql檢視和v$sqlarea檢視的構建SQL
- 使用V$SQL_PLAN檢視SQL
- 授權某使用者,檢視動態效能檢視的許可權(如v$latch,v$lock,v$sqlarea,v$sql,v$sysstat)SQL
- 結合載入檢視使用
- Oracle常用的V$檢視Oracle
- v檢視的授權
- v$session 檢視Session
- [轉載]SQLServer之檢視簡介SQLServer
- 【轉載】Oracle資料字典檢視Oracle
- [20130125]利用v$active_session_history檢視解決資料庫問題.txtSession資料庫
- 【實驗】【檢視】使用v$bgprocess檢視獲得所有後臺程式資訊
- 11g v$active_session_history的新增列Session
- [HP-UX] 如何檢視linux/unix檢視wwn號[轉載]Linux
- 使用V$檢視觀察Oracle執行狀況Oracle
- Oracle的V$檢視和DBA_檢視的參考提示Oracle
- 幾個檢視 v$mystat v$systata v$sessionSession
- v$sqlarea,v$sql,v$sqltext三個檢視的區別SQL
- Oracle的v$動態檢視 收藏Oracle
- V$session 檢視的小運用Session
- (轉)Oracle動態效能檢視學習之v$processOracle
- Oracle動態效能檢視學習之 V$ROLLSTAT -- 轉Oracle
- 【會話】V$SESSION檢視會話Session
- oracle v$sysstat效能檢視Oracle
- Oracle 等待事件V$檢視Oracle事件
- v$resource_limit檢視MIT
- 【保留字】使用檢視V$RESERVED_WORDS得到Oracle的保留字Oracle
- (轉載)iOS中獲取某個檢視的截圖iOS
- 檢視主機的記憶體使用情 (轉)記憶體
- v$sesstat,v$mystat,v$statname和v$sysstat檢視簡介
- V$SESSION_LONGOPS 檢視的用法SessionGo