Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Issue with Parallel query execution

RE: Issue with Parallel query execution

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 21 Feb 2007 15:08:23 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF97F8B0@WIN02.hotsos.com> 





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_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of

Govind.Arumugam_at_alltel.com


Sent: Wednesday, February 21, 2007 2:34 PM
To: oracle-l_at_freelists.org


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_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Arumugam, Govind

Sent: Tuesday, December 12, 2006 3:13 PM
To: oracle-l_at_freelists.org


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_at_acilf510 (P034)                    0
  1332   34009   1332 PVXDBE3A  25735                           ACTI
?  @acilh508 (TNS V1-V3)           4,204
  1890   49106   1332 PVXDBE3A  5866         SELECT             ACTI
oracle_at_acilf510 (P037)               13,947
  1529   16639   1332 PVXDBE3A  5862         SELECT             ACTI
oracle_at_acilf510 (P035)                    0
  2360   24030   1332 PVXDBE3A  5864         SELECT             ACTI
oracle_at_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. 



--
http://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. 


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Received on Wed Feb 21 2007 - 15:08:23 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US