V$ACTIVE_SESSION_HISTORY視圖的使用

V$ACTIVE_SESSION_HISTORY 顯示數(shù)據(jù)庫(kù)中的采樣會(huì)話活動(dòng)。ASH每秒從v$session中取快照，存在V$ACTIVE_SESSION_HISTORY中，并收集所有活動(dòng)會(huì)話的等待信息。若ASH數(shù)據(jù)被刷新到磁盤(pán)，則需要從DBA_HIS_ACTIVE_SESS_HISTORY視圖中查詢相關(guān)信息。

該視圖是ASH的核心，用以記錄活動(dòng)SESSION的歷史等待信息，每秒采樣一次，這部分內(nèi)容記錄在內(nèi)存中，期望值是記錄一個(gè)小時(shí)的內(nèi)容。

用法舉例：查找最近一分鐘內(nèi)，最消耗CPU的sql語(yǔ)句

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;

用法舉例：查找最近一分鐘內(nèi)，最消耗I/O的sql語(yǔ)句

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;

用法舉例：查找最近一分鐘內(nèi)，最消耗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;

用法舉例：查找最近一分鐘內(nèi)，最消耗資源的sql語(yǔ)句

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;

用法舉例：查找最近一分鐘內(nèi)，最消耗資源的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))

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

在數(shù)據(jù)庫(kù)出現(xiàn)性能問(wèn)題的時(shí)候使用awr,ash,addm都是不錯(cuò)的選擇，實(shí)際上直接查詢v$active_session_history也能很快定位解決問(wèn)題。
實(shí)際上如果查看v$active_session_history視圖，結(jié)合一些視圖可以獲取許多信息。
舉幾個(gè)例子來(lái)說(shuō)明:

1.確定那個(gè)對(duì)象有高的等待:
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.看看一段時(shí)間主要是那些等待事件：
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.看看那個(gè)回話有問(wèn)題:
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;
--當(dāng)然這個(gè)只能查詢最近的會(huì)準(zhǔn)一點(diǎn)，回話退出就不行了。

4.看看那個(gè)sql語(yǔ)句有問(wèn)題。
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';

利用這些視圖定位許多信息問(wèn)題