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@SEL$1
10 - SEL$1 / D@SEL$1
11 - SEL$1 / D@SEL$1
13 - SEL$1 / PV@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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Balwanth B <balwanthdba@xxxxxxxxx>
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 |
------------------------------------------------------------
---------------------------------------------------------------
