V$ACTIVE_SESSION_HISTORY檢視的使用
V$ACTIVE_SESSION_HISTORY 顯示資料庫中的取樣會話活動。ASH每秒從v$session中取快照,存在V$ACTIVE_SESSION_HISTORY中,並收集所有活動會話的等待資訊。若ASH資料被重新整理到磁碟,則需要從DBA_HIS_ACTIVE_SESS_HISTORY檢視中查詢相關資訊。
該檢視是ASH的核心,用以記錄活動SESSION的歷史等待資訊,每秒取樣一次,這部分內容記錄在記憶體中,期望值是記錄一個小時的內容。
用法舉例:查詢最近一分鐘內,最消耗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))
---------------------
在資料庫出現效能問題的時候使用awr,ash,addm都是不錯的選擇,實際上直接查詢v$active_session_history也能很快定位解決問題。
實際上如果檢視v$active_session_history檢視,結合一些檢視可以獲取許多資訊。
舉幾個例子來說明:
1.確定那個物件有高的等待:
SELECT a.current_obj#, o.object_name, o.object_type, a.event, SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, dba_objects o
WHERE a.sample_time BETWEEN SYSDATE - 30 / 1440 AND SYSDATE AND a.current_obj# = o.object_id
GROUP BY a.current_obj#, o.object_name, o.object_type, a.event
ORDER BY total_wait_time desc ;
2.看看一段時間主要是那些等待事件:
SELECT a.event, SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a
WHERE a.sample_time BETWEEN SYSDATE - 30 / 1440 AND SYSDATE
GROUP BY a.event
ORDER BY total_wait_time DESC;
3.看看那個回話有問題:
SELECT s.SID, s.username, SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$session s
WHERE a.sample_time BETWEEN SYSDATE - 30 / 1440 AND SYSDATE AND a.session_id = s.SID
GROUP BY s.SID, s.username
ORDER BY total_wait_time DESC;
--當然這個只能查詢最近的會準一點,回話退出就不行了。
4.看看那個sql語句有問題。
SELECT a.user_id, d.username, s.sql_text, SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$sqlarea s, dba_users d
WHERE a.sample_time BETWEEN SYSDATE - 15 / 1440 AND SYSDATE AND a.sql_id = s.sql_id AND a.user_id = d.user_id
GROUP BY a.user_id, s.sql_text, d.username
order by SUM (a.wait_time + a.time_waited) desc
-- 這裡查詢的是v$sqlarea檢視。
同樣你可以使用檢視DBA_HIST_ACTIVE_SESS_HISTORY代替v$active_session_history查詢歷史的資訊。
select * from dba_objects where wner='SYS' and object_name like 'DBA_HIST%' and object_type='VIEW';
利用這些檢視定位許多資訊問題
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14377/viewspace-2286582/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 轉載--V$ACTIVE_SESSION_HISTORY檢視的使用Session
- v$active_session_history檢視Session
- v$active_session_history檢視[轉]Session
- V$ACTIVE_SESSION_HISTORY 檢視中包含的資訊Session
- 11G 中的V$ACTIVE_SESSION_HISTORY 檢視沒有資料Session
- [20211214]檢視檢視V$ACTIVE_SESSION_HISTORY遇到奇怪問題.txtSession
- 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
- [20130125]利用v$active_session_history檢視解決資料庫問題.txtSession資料庫
- 【實驗】【檢視】使用v$bgprocess檢視獲得所有後臺程式資訊
- 11g v$active_session_history的新增列Session
- 使用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
- 自己總結的V$檢視(轉)
- 【會話】V$SESSION檢視會話Session
- oracle v$sysstat效能檢視Oracle
- Oracle 等待事件V$檢視Oracle事件
- v$resource_limit檢視MIT
- 【轉載】使用檢視V$RESERVED_WORDS得到Oracle的保留字Oracle
- 【保留字】使用檢視V$RESERVED_WORDS得到Oracle的保留字Oracle
- v$sesstat,v$mystat,v$statname和v$sysstat檢視簡介
- V$SESSION_LONGOPS 檢視的用法SessionGo
- 【管理】Oracle 常用的V$ 檢視指令碼Oracle指令碼
- 【Oracle九大效能檢視】之6.v$process檢視Oracle
- sql server 檢視tempdb使用的相關檢視SQLServer
- V$SQLAREA 檢視TOP_SQLSQL
- Oracle中效能檢視V$SYSSTATOracle
- v$sql_plan 檢視解析SQL
- oracle V$PGASTAT檢視記錄OracleAST
- V$PGASTAT動態效能檢視AST