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
