Query to Identify Parallel Slaves

Home Forums DBA – Wiki Query to Identify Parallel Slaves

Tagged: , ,

This topic contains 0 replies, has 1 voice, and was last updated by  Amit Bansal 4 years, 4 months ago.

  • Author
    Posts
  • #2527

    Amit Bansal
    Keymaster

    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

You must be logged in to reply to this topic.