- This topic has 0 replies, 1 voice, and was last updated 11 years, 2 months ago by Amit Bansal.
Viewing 0 reply threads
-
AuthorPosts
-
-
14 July, 2013 at 7:00 pm #2527Amit BansalKeymaster
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
-
-
AuthorPosts
Viewing 0 reply threads
- You must be logged in to reply to this topic.
Recent Comments