[Amit Bansal]

Useful query to identify parallel slaves and QC.

col username for a12
col module for a10 trunc
col state for a20
col "QC SID" for A6
col SID for a10
col "QC/Slave" for A10
col "ReqDOP" for 999
col "ActDOP" for 999
col "slave set" for  A10
col event for a25 trunc
col action for a20 trunc
col p1text for a20 trunc
col secwait for 99999
col state for a10 trunc
col object for a25 trunc
col command for a15 trunc
set pages 300  lines 300
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",s.module,s.sql_id,s.event,s.status
  from
    gv$px_session px,
    gv$session s
  where
    px.sid=s.sid (+) and
    px.serial#=s.serial# and
    px.inst_id = s.inst_id
 order by 2 desc;

E.g I am querying a table with 4 DOP. I force this using alter session

alter session force parallel query parallel 4;
select count(*) from big_table;

If you run above query in separate session, you will find output like below

  INST_ID Username	QC/Slave   Slave Set  SID	 QC SID Requested DOP Actual DOP MODULE     SQL_ID	  EVENT 		    STATUS
---------- ------------ ---------- ---------- ---------- ------ ------------- ---------- ---------- ------------- ------------------------- --------
	 1 AMITBANS	QC		      374	 374				 SQL*Plus   6fxg0qj7j7ftd PX Deq: Execute Reply     ACTIVE
	 1  - p003	(Slave)    1	      143	 374		    4	       4 SQL*Plus   6fxg0qj7j7ftd direct path read	    ACTIVE
	 1  - p002	(Slave)    1	      139	 374		    4	       4 SQL*Plus   6fxg0qj7j7ftd direct path read	    ACTIVE
	 1  - p001	(Slave)    1	      26	 374		    4	       4 SQL*Plus   6fxg0qj7j7ftd direct path read	    ACTIVE
	 1  - p000	(Slave)    1	      389	 374		    4	       4 SQL*Plus   6fxg0qj7j7ftd direct path read	    ACTIVE

QC tells you that it’s query co-ordinator. This also lists down requested parallelism and actual DOP which this session got. Pretty useful if you want to clear sessions. Just kill QC and it will clear up all child slaves

[Author]

Posts