Operations supported by Oracle parallel processing

posted May 19, 2012, 8:02 AM by Sachchida Ojha   [ updated May 19, 2012, 1:46 PM ]
1. Queries that involve table or index range scans
2. Bulk insert,update, or delete operations
3. Table and index creations
4. The collection of STATS using DBMS_STATS
5. Backup and recovery using RMAN

SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
  DEGREE "Degree", REQ_DEGREE "Req Degree"
FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;
SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';
SELECT NAME, VALUE, ROUND((RATIO_TO_REPORT(VALUE) OVER ())*100, 2)|| '%' PERC 
FROM V$SYSSTAT WHERE NAME LIKE 'Parallel%' ORDER BY NAME DESC;

SELECT dfo_number, tq_id, server_type, process, num_rows FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type, process;

SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set", NAME "Stat Name", VALUE FROM GV$PX_SESSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC# AND NAME LIKE 'PHYSICAL READS' AND VALUE > 0 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

SELECT px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst", px.SERVER_GROUP "Group", px.SERVER_SET "Set", px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event" FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND s.paddr = p.addr (+) AND s.inst_id = p.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;
Comments