[20130125]利用v$active_session_history視圖解決資料庫問題.txt

[20130125]利用v$active_session_history視圖解決資料庫問題.txt

在資料庫出現性能問題的時候使用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

--當然這個隻能查詢最近的會準一點，回話退出就不行了。

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';

利用這些視圖定位許多資訊問題。