Monitor parallel sessions
                                                 Last update (2010-08-05 14:05:10)
                                                                                                                   Date added (2008-02-14 12:00:26)

Summary
The parallel query option of Oracle can really speed up things when you perform full table scans or full index scans(in case you have multiple cpus). To monitor the sessions related to parallel operations you can use the following query.
SELECT DECODE(px.qcinst_id,NULL,username, ' - '||LOWER(SUBSTR(pp.SERVER_NAME,
LENGTH(pp.SERVER_NAME)-4,4) ) )"Username", DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
TO_CHAR( px.server_set) "SlaveSet", s.program, TO_CHAR(s.SID) "SID",
TO_CHAR(px.inst_id) "Slave INST", DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) AS STATE,    
CASE  sw.state WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) ELSE NULL END AS wait_event ,
DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) "QC SID",
TO_CHAR(px.qcinst_id) "QC INST", px.req_degree "Req DOP", px.DEGREE "Actual DOP", 
DECODE(px.server_set,'',s.last_call_et,'') "Elapsed seconds"
FROM gv$px_session px, gv$session s, gv$px_process pp, gv$session_wait sw
WHERE px.SID=s.SID (+)
AND px.serial#=s.serial#(+)
AND px.inst_id = s.inst_id(+)
AND px.SID = pp.SID (+)
AND px.serial#=pp.serial#(+)
AND sw.SID = s.SID 
AND sw.inst_id = s.inst_id  
ORDER BY DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), px.QCSID, 
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;

QC/Slave    Username  OSUSER       MODULE                 PROGRAM             Slave Set SID   SERIAL QC SID Req DOP  Act DOP  Elapsed(sec)
----------- --------- ------------ ---------------------- ------------------- --------- ----- ------ ------ -------- -------- ------------
QC          SYSTEM    spyros       SQL*Plus               sqlplusw.exe                  1117  7674   1117                                0
....(Slave)  - p003   spyros       SQL*Plus               oracle@ecdb2 (P003) 1         139   7732   1117   2        2
....(Slave)  - p003   spyros       SQL*Plus               oracle@ecdb1 (P003) 1         1317  2669   1117   2        2
QC          APPS      kaparelis    TOAD 9.1.0.62          toad.exe                      528   7672   528                              1590
....(Slave)  - p001   kaparelis    TOAD 9.1.0.62          oracle@ecdb1 (P001) 1         369   20916  528    2        2
....(Slave)  - p001   kaparelis    TOAD 9.1.0.62          oracle@ecdb2 (P001) 1         1570  10515  528    2        2
QC          SYSTEM    george       TOAD background query  toad.exe                      561   13101  561                              1030
....(Slave)  - p002   george       TOAD background query  oracle@ecdb2 (P002) 1         290   24595  561    2        2
....(Slave)  - p002   george       TOAD background query  oracle@ecdb1 (P002) 1         1017  13497  561    2        2

Bear in mind some details: First, you can only know the elapsed time for a parallel operation, not the remaining
and second, parallel is not performing so well in RAC systems with interconnect bandwidth less than 4Gigabit (Restrict RAC parallel operations to a limited number of instance)

Is it parallel performing well?
There is one way to find out, database stores statistics for parallel executions.
SELECT NAME, VALUE, ROUND((RATIO_TO_REPORT(VALUE) OVER ())*100, 2)|| '%' PERC 
FROM V$SYSSTAT WHERE NAME LIKE 'Parallel%' ORDER BY NAME DESC;
Output
NAME                                                                  VALUE PERC
---------------------------------------------------------------- ---------- -----------------------------------------
Parallel operations not downgraded                                    30041 99.3%
Parallel operations downgraded to serial                                 27 .09%
Parallel operations downgraded 75 to 99 pct                               0 0%
Parallel operations downgraded 50 to 75 pct                              41 .14%
Parallel operations downgraded 25 to 50 pct                             116 .38%
Parallel operations downgraded 1 to 25 pct                               28 .09%

6 rows selected.
Other links
How to setup parallel in an SMP Server
Restrict RAC parallel operations to a limited number of instance
Reviews
Categories
Filters
Search