q_topevent

admin

1. --等待事件组
   
2. select WAIT_CLASS,
   
3.        TOTAL_WAITS,
   
4.        round(100 * (TOTAL_WAITS / SUM_WAITS), 2) PCT_WAITS,
   
5.        ROUND((TIME_WAITED / 100), 2) TIME_WAITED_SECS,
   
6.        round(100 * (TIME_WAITED / SUM_TIME), 2) PCT_TIME
   
7.   from (select WAIT_CLASS, TOTAL_WAITS, TIME_WAITED
   
8.           from V$SYSTEM_WAIT_CLASS
   
9.          where WAIT_CLASS != 'Idle'),
   
10.        (select sum(TOTAL_WAITS) SUM_WAITS, sum(TIME_WAITED) SUM_TIME
    
11.           from V$SYSTEM_WAIT_CLASS
    
12.          where WAIT_CLASS != 'Idle')
    
13. order by 5 desc;
    
14. select WAIT_CLASS,count(*) from v$session_wait where wait_time is not null group by WAIT_CLASS order by 2 desc;
    
15. --等待事件top
    
16. set linesize 120
    
17. col event for a60
    
18. select event,
    
19.        sum(decode(wait_time, 0, 0, 1)) prev,
    
20.        sum(decode(wait_time, 0, 1, 0)) cur,
    
21.        count(*) tot
    
22.   from v$session_wait
    
23. group by event
    
24. order by 4 desc;
    
25. 
    
26. --等待耗时top
    
27. select *
    
28.   from (select event, total_waits, average_wait
    
29.           from v$system_event
    
30.          where event not like 'SQL%'
    
31.          order by 3 desc)
    
32. where rownum <= 10;
    
33. 
    
34. --查询当前的top_sql,根据cpu_time,elapsed_time,disk_read,executions等条件替换
    
35. select sql_id,command_type,cpu_time
    
36. from
    
37. (select sql_id,
    
38.         command_type,
    
39.         cpu_time,
    
40.                rank() over(order by disk_read desc) exec_rank
    
41.           from v$sql)
    
42. where  exec_rank <= 10
    
43. --根据等待事件查询相关的sql语句
    
44. select b.*, sq.sql_text
    
45.   from v$session se,
    
46.        v$sql sq,
    
47.        (select a.*
    
48.           from v$sql s,
    
49.                (select sid, p1
    
50.                   from v$session_wait
    
51.                  where event = 'library cache: mutex X') a
    
52.          where s.hash_value = a.p1) b
    
53. where se.sid = b.sid
    
54.    and se.sql_hash_value = sq.hash_value
    
55. select sql_id
    
56.   from v$sql
    
57. where hash_value in （select distinct p1 from v$session_wait
    
58. where event = 'log file sync')
    
59. 
    
60. select sid,serial#,username,blocking_session,ROW_WAIT_OBJ#,ROW_WAIT_BLOCK# from v$session where event='select b.*, sq.sql_text
    
61.   from v$session se,
    
62.        v$sql sq,
    
63.        (select a.*
    
64.           from v$sql s,
    
65.                (select sid, p1
    
66.                   from v$session_wait
    
67.                  where event = 'library cache: mutex X') a
    
68.          where s.hash_value = a.p1) b
    
69. where se.sid = b.sid
    
70.    and se.sql_hash_value = sq.hash_value
    
71. select sql_id
    
72.   from v$sql
    
73. where hash_value in （select distinct p1 from v$session_wait
    
74. where event = 'log file sync')';
    
75. --根据等待事件查询cursor pin s wait on x 的语句
    
76. select a.*, s.sql_text
    
77.   from v$sql s,
    
78.        (select sid,
    
79.                event,
    
80.                wait_class,
    
81.                p1 cursor_hash_value,
    
82.                p2raw Mutex_value,
    
83.                to_number(substr(p2raw, 1, 8), 'xxxxxxxx') hold_nutex_x_sid
    
84.           from v$session_wait
    
85.          where event like 'cursor%') a
    
86. where s.hash_value = a.cursor_hash_value
    

复制代码