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

利用这些视图定位许多信息问题。