Re: Parallel not being used by Oracle

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 7 Jul 2017 15:29:55 +0000
Message-ID: <MMXP123MB1037B860622EC21C9AF97212A5AA0_at_MMXP123MB1037.GBRP123.PROD.OUTLOOK.COM>

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-l

Received on Fri Jul 07 2017 - 17:29:55 CEST