Re: Parallel not being used by Oracle

From: Powell, Mark <mark.powell2_at_dxc.com>
Date: Fri, 7 Jul 2017 15:30:41 +0000
Message-ID: <DF4PR84MB01247757DBFD635A79EAA1A0CCAA0_at_DF4PR84MB0124.NAMPRD84.PROD.OUTLOOK.COM>

Balwanth, Since the database resource manager can be used to limit the DOP has this been ruled out?

Mark Powell
Database Administration
(313) 592-5148

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Balwanth B <balwanthdba_at_gmail.com> Sent: Friday, July 7, 2017 10:44:56 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 |
---------------------------------------------------------------------------------------------------------------------------

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 07 2017 - 17:30:41 CEST