oracle等待事件相关查询

转载

yoyo918 2019-07-08 16:52:15 博主文章分类：oracle 优化

--------------------------查询数据库等待时间和实际执行时间的相对百分比--------------------- select * from v$sysmetric a where a.METRIC_NAME in ('Database CPU Time Ratio', 'Database Wait Time Ratio') and a.INTSIZE_CSEC = (select max(intsize_csec) from v$sysmetric);

-------------------------------------查询数据库中过去30分钟引起最多等待的sql语句---------------- select ash.USER_ID, u.username, sum(ash.WAIT_TIME) ttl_wait_time, s.SQL_TEXT from v$active_session_history ash, v$sqlarea s, dba_users u where ash.SAMPLE_TIME between sysdate - 60 / 2880 and sysdate and ash.SQL_ID = s.SQL_ID and ash.USER_ID = u.user_id group by ash.USER_ID, s.SQL_TEXT, u.username order by ttl_wait_time desc

-----------------------------------------查询数据库中的等待事件---------------------- select event, count() from v$session_wait group by event order by count() desc

---------------------------------------查询数据库过去15分钟最重要的等待事件--------------- select ash.EVENT, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time from v$active_session_history ash where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate group by event order by total_wait_time desc

----------------------------------------在过去15分钟哪些用户经历了等待--------------------- select s.SID, s.USERNAME, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time from v$active_session_history ash, v$session s where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and ash.SESSION_ID = s.SID group by s.SID, s.USERNAME order by total_wait_time desc;

-------------------------------------查询等待时间最长的对象--------------------------------------- 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.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and a.CURRENT_OBJ# = d.object_id group by a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT order by total_wait_time desc;

--------------------------------------------查询过去15分钟等待时间最长的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.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and a.SQL_ID = s.SQL_ID and a.USER_ID = u.user_id group by a.USER_ID, s.SQL_TEXT, u.username order by total_wait_time desc;

------------------------------------------那些SQL消耗更多的IO-------------------------------------- select * from (select s.PARSING_SCHEMA_NAME, s.DIRECT_WRITES, substr(s.SQL_TEXT, 1, 500), s.DISK_READS from v$sql s order by s.DISK_READS desc) where rownum < 20

---------------------------------------查看哪些会话正在等待IO资源------------------------------------- SELECT username, program, machine, sql_id FROM V$SESSION WHERE EVENT LIKE 'db file%read';

----------------------------------查看正在等待IO资源的对象----------------------------------- SELECT d.object_name, d.object_type, d.owner FROM V$SESSION s, dba_objects d WHERE EVENT LIKE 'db file%read' and s.ROW_WAIT_OBJ# = d.object_id

---------------------------查看redo日志切换频率--------------------------------------------- Select round(FIRST_TIME, 'DD'), THREAD#, Count(SEQUENCE#) From v$log_history Group By round(FIRST_TIME, 'DD'), THREAD# Order By 1, 2

SELECT trunc(first_time) "Date", to_char(first_time, 'Dy') "Day", count(1) "Total", SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0", SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1", SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2", SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3", SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4", SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5", SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6", SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7", SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8", SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9", SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10", SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11", SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12", SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13", SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14", SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15", SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16", SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17", SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18", SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19", SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20", SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21", SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22", SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23" FROM V$log_history group by trunc(first_time), to_char(first_time, 'Dy') Order by 1