问题:
您想找出数据库中最近的重要等待以及负责大多数等待的用户,SQL语句和对象。
解
Oracle为我们提供了V $ACTIVE_SESSION_HISTORY,以获取有关最常见的等待事件以及SQL语句,数据库对象和负责这些等待的用户的信息。
SQL查找等待事件
示例SELECT event,
SUM(wait_time + time_waited) total_wait_time
FROM v$active_session_history
GROUP BY event
ORDER BY total_wait_time DESC;
要找出等待时间最多的用户,我们可以在SQL下面使用。
示例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.session_id=s.sid
GROUP BY s.sid,
s.username
ORDER BY total_wait_time DESC;
为了找出等待时间最多的对象,我们可以在SQL下面使用。
示例SELECT a.current_obj#,
d.object_name,
d.object_type,
a.event,
SUM(a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a,
dba_objects d
WHERE a.current_obj# = d.object_id
GROUP BY a.current_obj#,
d.object_name,
d.object_type,
a.event
ORDER BY total_wait_time;
最后,我们可以使用以下查询来确定等待最长时间的SQL语句。
示例SELECT a.user_id,
u.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 u
WHERE a.sql_id = s.sql_id
AND a.user_id = u.user_id
GROUP BY a.user_id,
s.sql_text,
u.username;