Thanks Gary! Capacity is not a problem for us. We had a plenty of parallel threads available. Our intention was to reduce the elapsed time of the query. We are also looking into dynamically changing the parallel degree depending on the number of rows. My research suggests that it runs with a parallel degree of 4 when the number of rows is in excess of 5000. But I would set it to 4 if we have more than 1000 rows. Less than this, I am comfortable with running in serial mode. This approach will suit us fine as we have 15 of the concurrent threads running simultaneously working on 15 market groups. See you in Dallas during the Symposium. Thanks, Govind -----Original Message----- From: Cary Millsap [mailto:cary.millsap@xxxxxxxxxx] Sent: Wednesday, February 21, 2007 4:08 PM To: Arumugam, Govind; oracle-l@xxxxxxxxxxxxx Subject: RE: Issue with Parallel query execution Am I right to sum this up as follows? "Increasing the amount of capacity consumed by this query by 4x increases its throughput by 2x." Or is the capacity increase really 8x, once you account for both producer and consumer PX processes? Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Hotsos Symposium 2007 / March 4-8 / Dallas Visit www.hotsos.com for curriculum and schedule details... -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Govind.Arumugam@xxxxxxxxxx Sent: Wednesday, February 21, 2007 2:34 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Issue with Parallel query execution Just to wrap up this discussion thread, this is what I found. Oracle determines the parallel degree depending on the number of rows in the table debtmanagernewaccountc1. I have set the parallel degree of this table from 1 to 4. It now runs in parallel mode all the time. The query uses the materialized subquery using WITH clause of 9i. I have reduced the average runtime by 50%. WITH dmnewaccountnorslrc1 AS ( SELECT dm1.marketcd, dm1.acctnodenbr FROM debtmanagernewaccountc1 dm1 WHERE NOT EXISTS ( SELECT 1 FROM customer cu WHERE dm1.acctnodenbr = cu.nodenbr AND cu.custtypecd = 'RS')) SELECT dm.marketcd, dm.acctnodenbr, ol1.nodenbr, ol1.ordernbr, ol1.orderlinenbr, ol1.orderlinetypecd, ol1.startts, oh1.closets, NVL (msc.mobilechgresncd, ' '), NVL (msc.switchxactstatuscd, ' ') FROM dmnewaccountnorslrc1 dm, orderline ol1, orderheader oh1, mobilestatuschange msc WHERE ( (dm.acctnodenbr > ' ' AND dm.marketcd = ' ') OR dm.acctnodenbr >= ' ' ) AND dm.acctnodenbr = oh1.acctnodenbr AND oh1.ordernbr = ol1.ordernbr AND oh1.marketcd = ol1.marketcd AND ol1.startts <= TO_DATE ('20070109200224', 'YYYYMMDDHH24MISS') AND oh1.closets <= TO_DATE ('20070109200224', 'YYYYMMDDHH24MISS') AND oh1.closets > TO_DATE ('20050109200224', 'YYYYMMDDHH24MISS') AND oh1.orderstatuscd = 'C' AND ol1.ordernbr = msc.ordernbr(+) AND ol1.orderlinenbr = msc.orderlinenbr(+) UNION SELECT dm.marketcd, dm.acctnodenbr, ol1.nodenbr, ol1.ordernbr, ol1.orderlinenbr, ol1.orderlinetypecd, ol1.startts, oh1.closets, ' ' AS mobilechgresncd, ' ' AS switchxactstatuscd FROM dmnewaccountnorslrc1 dm, accounthistory ah, orderline ol1, orderheader oh1 WHERE ( (dm.acctnodenbr > ' ' AND dm.marketcd = ' ') OR dm.acctnodenbr >= ' ' ) AND dm.marketcd = ah.marketcd AND dm.acctnodenbr = ah.parentnodenbr AND ah.effectivets <= '20070109200224' AND ah.effectivets > '20050109200224' AND ah.nodenbr = ol1.nodenbr AND oh1.ordernbr = ol1.ordernbr AND oh1.marketcd = ol1.marketcd AND ol1.startts <= TO_DATE ('20070109200224', 'YYYYMMDDHH24MISS') AND oh1.closets <= TO_DATE ('20070109200224', 'YYYYMMDDHH24MISS') AND oh1.closets > TO_DATE ('20050109200224', 'YYYYMMDDHH24MISS') AND oh1.orderstatuscd = 'C' ORDER BY 1, 2; Thanks, Govind -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Arumugam, Govind Sent: Tuesday, December 12, 2006 3:13 PM To: oracle-l@xxxxxxxxxxxxx Subject: Issue with Parallel query execution Dear Gurus, I am troubleshooting one of the daily batch programs that is not requesting the right amount of parallel degree. I see this job requesting a parallel degree of 4 and getting 4. But in yesterday's run, it only asked for 2 and got 2. The degree of parallelism is set at the table level. We are on "Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production" The job started at 21:16 and completed at 22:47, a runtime of 91 minutes running with a degree of 2. It typically runs in less than 40 minutes if it runs with a degree of 4. I went through the metalink note Checklist for Performance Problems with Parallel Execution Note:280939.1. The data from v$px_session is saved off to a file every 5 minutes using the query "select * from v$px_session" Date Time v$sysstat Parameter Name Value 12112006 21:10 Parallel operations not downgraded 15727655 12112006 21:10 Parallel operations downgraded to serial 17048 12112006 21:10 Parallel operations downgraded 25 to 50 pct 1162 12112006 21:10 Parallel operations downgraded 1 to 25 pct 583 12112006 21:15 Parallel operations not downgraded 15729888 12112006 21:15 Parallel operations downgraded to serial 17048 12112006 21:15 Parallel operations downgraded 25 to 50 pct 1162 12112006 21:15 Parallel operations downgraded 1 to 25 pct 583 The job started around here. We had a plenty of parallel server threads available for Oracle to use as seen by "select * from V$PQ_SYSSTAT". We used up just 72 out of a maximum of 281 parallel threads. STATISTIC VALUE ------------------------------ ---------- Servers Busy 72 Servers Idle 20 Servers Highwater 281 12112006 21:20 Parallel operations not downgraded 15732051 12112006 21:20 Parallel operations downgraded to serial 17052 12112006 21:20 Parallel operations downgraded 25 to 50 pct 1162 12112006 21:20 Parallel operations downgraded 1 to 25 pct 583 12112006 21:25 Parallel operations not downgraded 15733831 12112006 21:25 Parallel operations downgraded to serial 17052 12112006 21:25 Parallel operations downgraded 25 to 50 pct 1162 12112006 21:25 Parallel operations downgraded 1 to 25 pct 583 SID SERIAL# PSID USERNAME PROCESS Command STAT PROGRAM Physical Rds ------ ------- ------ --------- ------------ ------------------ ---- ------------------------------ ------------ 528 53832 1332 PVXDBE3A 5856 SELECT ACTI oracle@acilf510 (P034) 0 1332 34009 1332 PVXDBE3A 25735 ACTI ? @acilh508 (TNS V1-V3) 4,204 1890 49106 1332 PVXDBE3A 5866 SELECT ACTI oracle@acilf510 (P037) 13,947 1529 16639 1332 PVXDBE3A 5862 SELECT ACTI oracle@acilf510 (P035) 0 2360 24030 1332 PVXDBE3A 5864 SELECT ACTI oracle@acilf510 (P036) 14,019 SADDR SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE ---------------- ------ ------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- 0000040240A4D0E8 528 53832 1332 34009 1 1 1 1 2 2 0000040241AC5F10 1332 34009 1332 000004023FAC6F98 1529 16639 1332 34009 1 1 1 2 2 2 0000040243B3DAF8 2360 24030 1332 34009 1 1 2 1 2 2 0000040243AFFA18 1890 49106 1332 34009 1 1 2 2 2 2 Is there a way for us to find out why the DOP was chosen at 2 for this query? I would greatly appreciate any insight. I am thinking about "alter SESSION FORCE PARALLEL QUERY PARALLEL 4" through a logon trigger to force the DOP at 4 as a workaround. This will be tested in a production like environment first. Any other solutions or pointers? Sorry about the long e-mail. Thanks, Govind ************************************************************************ ****************** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, Alltel requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. -- //www.freelists.org/webpage/oracle-l ************************************************************************ ****************** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, Alltel requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. -- //www.freelists.org/webpage/oracle-l ****************************************************************************************** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, Alltel requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. -- //www.freelists.org/webpage/oracle-l