Script To Monitor Parallel Queries/SQL (Doc ID 457857.1)

admin

Purpose
The script has 4 SQL commands. The first 1 gives an overview of all running parallel queries with all slaves. It shows whether a slave is waiting and for what event it waits. The second command shows for the PX Deq events the processes that are exchange data. The third 1 shows for long running processes what are the slaves do.  The fourth 1 shows what sqls are currently executing, and what is queued
Requirements

This is designed to work in SQL Plus. It also works with other tools, but than the formating is lost.
Configuring

The script needs privileges to do a select on  gv$px_session, gv$px_process gv$session_longops , gv$session_wait, gv$session and gv$sql_monitor
Instructions

You can run the the complete script or use only some of the SQL commands to monitor your database.
Caution
This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

1. col username for a12
   
2. col "QC SID" for A6
   
3. col "SID" for A6
   
4. col "QC/Slave" for A8
   
5. col "Req. DOP" for 9999
   
6. col "Actual DOP" for 9999
   
7. col "Slaveset" for A8
   
8. col "Slave INST" for A9
   
9. col "QC INST" for A6
   
10. set lines 250 pages 5000
    
11. col wait_event format a30
    
12. select
    
13. decode(px.qcinst_id,NULL,username,
    
14. ' - '||lower(substr(pp.SERVER_NAME,
    
15. length(pp.SERVER_NAME)-4,4) ) )"Username",
    
16. decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
    
17. to_char( px.server_set) "SlaveSet",
    
18. to_char(s.sid) "SID",
    
19. to_char(px.inst_id) "Slave INST",
    
20. decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,
    
21. case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
    
22. decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
    
23. to_char(px.qcinst_id) "QC INST",
    
24. px.req_degree "Req. DOP",
    
25. px.degree "Actual DOP"
    
26. from gv$px_session px,
    
27. gv$session s ,
    
28. gv$px_process pp,
    
29. gv$session_wait sw
    
30. where px.sid=s.sid (+)
    
31. and px.serial#=s.serial#(+)
    
32. and px.inst_id = s.inst_id(+)
    
33. and px.sid = pp.sid (+)
    
34. and px.serial#=pp.serial#(+)
    
35. and sw.sid = s.sid
    
36. and sw.inst_id = s.inst_id
    
37. order by
    
38. decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
    
39. px.QCSID,
    
40. decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
    
41. px.SERVER_SET,
    
42. px.INST_ID
    
43. /
    
44. 
    
45. 
    
46. set lines 250 pages 5000
    
47. col wait_event format a30
    
48. select
    
49. sw.SID as RCVSID,
    
50. decode(pp.server_name,
    
51. NULL, 'A QC',
    
52. pp.server_name) as RCVR,
    
53. sw.inst_id as RCVRINST,
    
54. case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
    
55. decode(bitand(p1, 65535),
    
56. 65535, 'QC',
    
57. 'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR,
    
58. mod(bitand(p1,16711680) , 65535) as SNDRINST,
    
59. decode(bitand(p1, 65535),
    
60. 65535, ps.qcsid,
    
61. (select
    
62. sid
    
63. from
    
64. gv$px_process
    
65. where
    
66. server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and
    
67. inst_id = bitand(sw.p1, 16711680) - 65535)
    
68. ) as SNDRSID,
    
69. decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE
    
70. from
    
71. gv$session_wait sw,
    
72. gv$px_process pp,
    
73. gv$px_session ps
    
74. where
    
75. sw.sid = pp.sid (+) and
    
76. sw.inst_id = pp.inst_id (+) and
    
77. sw.sid = ps.sid (+) and
    
78. sw.inst_id = ps.inst_id (+) and
    
79. p1text = 'sleeptime/senderid' and
    
80. bitand(p1, 268435456) = 268435456
    
81. order by
    
82. decode(ps.QCINST_ID, NULL, ps.INST_ID, ps.QCINST_ID),
    
83. ps.QCSID,
    
84. decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP),
    
85. ps.SERVER_SET,
    
86. ps.INST_ID
    
87. /
    
88. 
    
89. 
    
90. set lines 250 pages 5000
    
91. col "Username" for a12
    
92. col "QC/Slave" for A8
    
93. col "Slaveset" for A8
    
94. col "Slave INST" for A9
    
95. col "QC SID" for A6
    
96. col "QC INST" for A6
    
97. col "operation_name" for A30
    
98. col "target" for A30
    
99. select
    
100. decode(px.qcinst_id,NULL,username,
     
101. ' - '||lower(substr(pp.SERVER_NAME,
     
102. length(pp.SERVER_NAME)-4,4) ) )"Username",
     
103. decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
     
104. to_char( px.server_set) "SlaveSet",
     
105. to_char(px.inst_id) "Slave INST",
     
106. substr(opname,1,30) operation_name,
     
107. substr(target,1,30) target,
     
108. sofar,
     
109. totalwork,
     
110. units,
     
111. start_time,
     
112. timestamp,
     
113. decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
     
114. to_char(px.qcinst_id) "QC INST"
     
115. from gv$px_session px,
     
116. gv$px_process pp,
     
117. gv$session_longops s
     
118. where px.sid=s.sid
     
119. and px.serial#=s.serial#
     
120. and px.inst_id = s.inst_id
     
121. and px.sid = pp.sid (+)
     
122. and px.serial#=pp.serial#(+)
     
123. order by
     
124. decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
     
125. px.QCSID,
     
126. decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
     
127. px.SERVER_SET,
     
128. px.INST_ID
     
129. /
     
130. 
     
131. 
     
132. set lines 250 pages 5000
     
133. col sql_text for a50
     
134. col sid for 99999
     
135. col status for a11
     
136. col slv_req for 99999
     
137. col slv_alloc for 99999
     
138. col sql_id format a16
     
139. col secs_in_q for 99999
     
140. SELECT sql_id, sid,
     
141. session_serial# sess#,
     
142. status ,
     
143. px_servers_requested slv_req,
     
144. px_servers_allocated slv_alloc,
     
145. substr(sql_text,1,50)||'...' sql_text,
     
146. queuing_time/1000000 secs_in_q
     
147. FROM gv$sql_monitor
     
148. WHERE status in ('QUEUED','EXECUTING') and sql_text is not null
     
149. ORDER BY status desc, secs_in_q desc, sql_id
     
150. /

复制代码

1. set linesize 120
   
2. col sql_text for a50
   
3. col sid for 9999
   
4. col status for a10
   
5. col slv_req for 999
   
6. col slv_alloc for 999
   
7. col secs_in_q for 99999
   
8. SELECT sql_id,
   
9.        sid,
   
10.        session_serial# sess#,
    
11.        status ,
    
12.        px_servers_requested slv_req,
    
13.        px_servers_allocated slv_alloc,
    
14.        substr(sql_text,1,50)||'...' sql_text,
    
15.        queuing_time/1000000 secs_in_q
    
16. FROM gv$sql_monitor
    
17. WHERE  status in ('QUEUED','EXECUTING') and sql_text is not null
    
18. ORDER BY status desc, secs_in_q desc, sql_id;

复制代码