Re: Parallel not being used by Oracle
Date: Fri, 7 Jul 2017 18:57:42 +0000
Message-ID: <DF4PR84MB0124785FB77B3242E31FE28CCCAA0_at_DF4PR84MB0124.NAMPRD84.PROD.OUTLOOK.COM>
My view is if the previous plan took 6 hours and the new plan takes 1 hour then obviously the new plan is better. I just need to try to determine what the plan is telling me so that I can understand why.
Mark Powell
Database Administration
(313) 592-5148
From: Balwanth B <balwanthdba_at_gmail.com> Sent: Friday, July 7, 2017 2:21:51 PM
To: Jonathan Lewis; Powell, Mark
Cc: ORACLE-L
Subject: Re: Parallel not being used by Oracle
I am still not sure how long with this take, because in staging parametervalue table has 10% records of what we have in production. Num of records in production parametervalue table has 622 million records.. If you see my previous sql monitoring plan in staging environment that did full table on device,parametervalue,and TMP_HDM_CLEANUP_INSTANCE(driving table),where I saw time benefit before it ran for 6 hrs runs but now its running for 1hr .. But here it still uses index range scans but with parallel this time.. I am totally confused... does the above plan which I just sent looks good?
On Fri, Jul 7, 2017 at 2:20 PM, Balwanth B <balwanthdba_at_gmail.com<mailto:balwanthdba_at_gmail.com>> wrote: I am still not sure how long with this take, because in staging parametervalue table has 10% records of what we have in staging. Num of records in production parametervalue table has 622 million records.. If you see my previous sql monitoring plan in staging environment that did full table on device,parametervalue,and TMP_HDM_CLEANUP_INSTANCE(driving table).. But here it still uses index range scans but with parallel this time.. I am totally confused... does the above plan which I just sent looks good?
On Fri, Jul 7, 2017 at 2:16 PM, Balwanth B <balwanthdba_at_gmail.com<mailto:balwanthdba_at_gmail.com>> wrote: 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| PQ Distrib |
| 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<mailto: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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Balwanth B <balwanthdba_at_gmail.com<mailto: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:57:42 CEST