There are many reasons why Oracle might not run parallel when hinted to do so.
If you dump the part of the display plan or sql monitor where it tells you WHY
it used the parallelism it did, that would be helpful.
I notice you’ve got an index. IF you dump the part of the plan and it says
something about running serial due to hint despite your hint, then you’ve hit
the nasty bug where it reports the existence of an index it thinks cannot be
used in batched rowids and therefore parallel is useless into a false message
about a hint.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Balwanth B
Sent: Friday, July 07, 2017 10:45 AM
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 |
---------------------------------------------------------------------------------------------------------------------------
