Re: Parallel not being used by Oracle
From: Balwanth B <balwanthdba_at_gmail.com>
Date: Fri, 7 Jul 2017 14:16:13 -0400
Message-ID: <CAL72EnAR0ywwGztRNzgC=LSJ2oU4T32Xm_+i9ZXo-CQyrSjmmw_at_mail.gmail.com>
SELECT /*+ parallel(4) full(parametervalue) */ DISTINCT(PV.PARAMETER_VALUE_NAME), D.DEVICETYPE_ID FROM DEVICE D, PARAMETERVALUE PV, TMP_HDM_CLEANUP_INSTANCE TMP WHERE D.CACHED_DATA_RECORD_ID = PV.DATA_RECORD_ID AND D.DEVICETYPE_ID = TMP.DEVICETYPE_ID AND PV.PARAMETER_VALUE_NAME LIKE TMP.PARAMETER_VALUE_NAME ESCAPE :B1 Plan hash value: 3730697107
9 - "TMP"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "TMP"."DEVICETYPE_ID"[NUMBER,22]
10 - "D"."DEVICETYPE_ID"[NUMBER,22],
"D"."CACHED_DATA_RECORD_ID"[NUMBER,22]
Date: Fri, 7 Jul 2017 14:16:13 -0400
Message-ID: <CAL72EnAR0ywwGztRNzgC=LSJ2oU4T32Xm_+i9ZXo-CQyrSjmmw_at_mail.gmail.com>
HI Jonathan,
After making the change I am able to see below plan
SQL_ID 6a1dchjszsarm, child number 0
SELECT /*+ parallel(4) full(parametervalue) */ DISTINCT(PV.PARAMETER_VALUE_NAME), D.DEVICETYPE_ID FROM DEVICE D, PARAMETERVALUE PV, TMP_HDM_CLEANUP_INSTANCE TMP WHERE D.CACHED_DATA_RECORD_ID = PV.DATA_RECORD_ID AND D.DEVICETYPE_ID = TMP.DEVICETYPE_ID AND PV.PARAMETER_VALUE_NAME LIKE TMP.PARAMETER_VALUE_NAME ESCAPE :B1 Plan hash value: 3730697107
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQDistrib |
| 0 | SELECT STATEMENT | | | | 3444 (100)| | | | | |
|
| 1 | PX COORDINATOR | | | | | | | | | |
|
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 100 | 3444 (1)| 00:00:42 | | | Q1,01 | P->S | QC (RAND) | | 3 | HASH UNIQUE | | 1 | 100 | 3444 (1)| 00:00:42 | | | Q1,01 | PCWP |
|
| 4 | PX RECEIVE | | 1 | 100 | 3443 (1)| 00:00:42 | | | Q1,01 | PCWP |
|
| 5 | PX SEND HASH | :TQ10000 | 1 | 100 | 3443 (1)| 00:00:42 | | | Q1,00 | P->P | HASH | | 6 | NESTED LOOPS | | 1 | 100 | 3443 (1)| 00:00:42 | | | Q1,00 | PCWP |
|
| 7 | NESTED LOOPS | | 1 | 57 | 3442 (1)| 00:00:42 | | | Q1,00 | PCWP |
|
| 8 | PX BLOCK ITERATOR | | | | | | | | Q1,00 | PCWC |
|
|* 9 | TABLE ACCESS FULL | TMP_HDM_CLEANUP_INSTANCE | 4125 | 185K| 3 (0)| 00:00:01 | | | Q1,00 | PCWP | | | 10 | TABLE ACCESS BY INDEX ROWID| DEVICE | 1 | 11 | 1 (0)| 00:00:01 | | | Q1,00 | PCWP |
|
|* 11 | INDEX RANGE SCAN | SYS_C0016783 | 1822K| | 1 (0)| 00:00:01 | | | Q1,00 | PCWP | | | 12 | PARTITION HASH ITERATOR | | 1 | 43 | 1 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP |
|
|* 13 | INDEX RANGE SCAN | UQ_PARAM_NEW | 1 | 43 | 1 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP |
|
Query Block Name / Object Alias (identified by operation id):
1 - SEL$1 9 - SEL$1 / TMP_at_SEL$1 10 - SEL$1 / D_at_SEL$1 11 - SEL$1 / D_at_SEL$1 13 - SEL$1 / PV_at_SEL$1
Predicate Information (identified by operation id):
9 - access(:Z>=:Z AND :Z<=:Z)
11 - access("D"."DEVICETYPE_ID"="TMP"."DEVICETYPE_ID")
13 - access("D"."CACHED_DATA_RECORD_ID"="PV"."DATA_RECORD_ID" AND
"PV"."PARAMETER_VALUE_NAME" LIKE "TMP"."PARAMETER_VALUE_NAME" ESCAPE :B1)
filter("PV"."PARAMETER_VALUE_NAME" LIKE "TMP"."PARAMETER_VALUE_NAME"
ESCAPE :B1)
Column Projection Information (identified by operation id):
1 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255],
"D"."DEVICETYPE_ID"[NUMBER,22]
2 - (#keys=0) "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255],
"D"."DEVICETYPE_ID"[NUMBER,22]
3 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255],
"D"."DEVICETYPE_ID"[NUMBER,22]
4 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255],
"D"."DEVICETYPE_ID"[NUMBER,22]
5 - (#keys=2) "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255],
"D"."DEVICETYPE_ID"[NUMBER,22]
6 - "D"."DEVICETYPE_ID"[NUMBER,22],
"PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255]
7 - "TMP"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "D"."DEVICETYPE_ID"[NUMBER,22], "D"."CACHED_DATA_RECORD_ID"[NUMBER,22] 8 - "TMP"."PARAMETER_VALUE_NAME"[VARCHAR2,255],"TMP"."DEVICETYPE_ID"[NUMBER,22]
9 - "TMP"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "TMP"."DEVICETYPE_ID"[NUMBER,22]
10 - "D"."DEVICETYPE_ID"[NUMBER,22],
"D"."CACHED_DATA_RECORD_ID"[NUMBER,22]
11 - "D".ROWID[ROWID,10], "D"."DEVICETYPE_ID"[NUMBER,22] 12 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255] 13 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255]
Note
- automatic DOP: Computed Degree of Parallelism is 4
On Fri, Jul 7, 2017 at 1:44 PM, Balwanth B <balwanthdba_at_gmail.com> wrote:
> previos email sql monitor report was from staging environment... As > jonathan said, I tried forcing parallel its still taking serial access.. > Following his suggestions, will let you know how this goes. > > On Fri, Jul 7, 2017 at 11:29 AM, Jonathan Lewis < > jonathan_at_jlcomp.demon.co.uk> wrote: > >> >> The parallel() hints do not tell the optimizer to produce a plan that >> runs parallel, they tell the optimizer to consider the cost of using >> parallelism. The optimizer will still take a serial plan if there is a >> valid serial plan with a lower cost than the valid parallel plans. >> https://jonathanlewis.wordpress.com/2017/05/25/parallelism/ >> >> Your plan shows that Oracle has a problem with the statistics on the >> DEVICE table (or the function-based(?) sys_C0016783; possibly this has an >> effect on the choice of plan; certainly the optimizer thinks that it only >> needs to find one row from parametervalue to satisfy the query (one row >> produced by operation 3 - to drive an indexed access into a parametervalue). >> >> If you really want to get a parallel scan of parametervalue you need to >> hint with both parallel() and full(). >> >> Regards >> Jonathan Lewis >> >> >> ________________________________________ >> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on >> behalf of Balwanth B <balwanthdba_at_gmail.com> >> Sent: 07 July 2017 15:44:56 >> To: ORACLE-L >> Subject: Parallel not being used by Oracle >> >> This particular query which is part of PL/SQL is running for long time >> and doesn't use parallel even when parallel hint is given? Can someone >> please throw some light here. >> >> >> SELECT /*+ parallel(pv,4) */ DISTINCT(PV.PARAMETER_VALUE_NAME), >> D.DEVICETYPE_ID FROM DEVICE D, PARAMETERVALUE PV, >> TMP_HDM_CLEANUP_INSTANCE TMP WHERE D.CACHED_DATA_RECORD_ID = >> PV.DATA_RECORD_ID AND D.DEVICETYPE_ID = TMP.DEVICETYPE_ID AND >> PV.PARAMETER_VALUE_NAME LIKE TMP.PARAMETER_VALUE_NAME ESCAPE :B1 >> >> >> ------------------------------------------------------------ >> --------------------------------------------------------------- >> | Id | Operation | Name | Rows >> | Bytes | Cost (%CPU)| Time | Pstart| Pstop | >> ------------------------------------------------------------ >> --------------------------------------------------------------- >> | 0 | SELECT STATEMENT | | >> | | 12395 (100)| | | | >> | 1 | HASH UNIQUE | | 1 >> | 100 | 12395 (1)| 00:02:29 | | | >> | 2 | NESTED LOOPS | | 1 >> | 100 | 12393 (1)| 00:02:29 | | | >> | 3 | NESTED LOOPS | | 1 >> | 57 | 12390 (1)| 00:02:29 | | | >> | 4 | TABLE ACCESS FULL | TMP_HDM_CLEANUP_INSTANCE | 4125 >> | 185K| 11 (0)| 00:00:01 | | | >> | 5 | TABLE ACCESS BY INDEX ROWID| DEVICE | 1 >> | 11 | 3 (0)| 00:00:01 | | | >> |* 6 | INDEX RANGE SCAN | SYS_C0016783 | >> 1822K| | 3 (0)| 00:00:01 | | | >> | 7 | PARTITION HASH ITERATOR | | 1 >> | 43 | 3 (0)| 00:00:01 | KEY | KEY | >> |* 8 | INDEX RANGE SCAN | UQ_PARAM_NEW | 1 >> | 43 | 3 (0)| 00:00:01 | KEY | KEY | >> ------------------------------------------------------------ >> --------------------------------------------------------------- >> > >
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 07 2017 - 20:16:13 CEST