RE: Issue with Parallel query execution
- From: <Govind.Arumugam@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Feb 2007 14:34:01 -0600
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
Other related posts:
