Issue with Parallel query execution

From: <Govind.Arumugam_at_alltel.com>
Date: Tue, 12 Dec 2006 14:13:15 -0600
Message-ID: <C8C159860377BD4CB874629A9C1DEACB07D226@scarlitnt640.alltel.com>

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 Received on Tue Dec 12 2006 - 14:13:15 CST