Script to monitor parallel queries in oracle database

Monitor parallel queries running in Oracle Database

Query to monitor currently running queries with parallel option:

select
 s.inst_id,
 decode(px.qcinst_id,NULL,s.username,
 ' - '||lower(substr(s.program,length(s.program)-4,4) ) )
"Username",
 decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
 to_char( px.server_set) "Slave Set",
 to_char(s.sid) "SID",
 decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
 px.req_degree "Requested DOP",
 px.degree "Actual DOP", p.spid
 from
 gv$px_session px,
 gv$session s, gv$process p
 where
 px.sid=s.sid (+) and
 px.serial#=s.serial# and
 px.inst_id = s.inst_id
 and p.inst_id = s.inst_id
 and p.addr=s.paddr
 order by 5,1 desc

Note: v$px_session, v$session is the dynamic view used to fetch parallel queries in Oracle database.

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle