Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 093CE1961323 for ; Fri, 7 Jul 2017 20:17:52 +0200 (CEST) Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Fri, 7 Jul 2017 20:17:51 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 97DED9A79; Fri, 7 Jul 2017 14:17:50 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1499451470; bh=oGeDMfFTbOd/NP4SJskp11TcVLeK29M1mD9jMu6QIlM=; h=In-Reply-To:References:From:Date:Subject:To:Cc:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=icghRXSVgR7izk7B8gsS90gCovtkFjIcB1iR0NcQg6njScgOomKj28z2uVn3xdPsT CeFC7omXPLWFtf3s29ZiB4fUzf+kF0+FEaDIRHwZdivqiPbn3PddWhhWtbch3ich4t eg4LZejN1wUF89mgVOJBNAKbyjLXrFMX2RWQlLc0= X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id DiQX5-I0r_2Z; Fri, 7 Jul 2017 14:17:50 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3204C9A6B; Fri, 7 Jul 2017 14:17:38 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1499451470; bh=oGeDMfFTbOd/NP4SJskp11TcVLeK29M1mD9jMu6QIlM=; h=In-Reply-To:References:From:Date:Subject:To:Cc:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=icghRXSVgR7izk7B8gsS90gCovtkFjIcB1iR0NcQg6njScgOomKj28z2uVn3xdPsT CeFC7omXPLWFtf3s29ZiB4fUzf+kF0+FEaDIRHwZdivqiPbn3PddWhhWtbch3ich4t eg4LZejN1wUF89mgVOJBNAKbyjLXrFMX2RWQlLc0= Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 07 Jul 2017 14:16:16 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 641409968 for ; Fri, 7 Jul 2017 14:16:16 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 4N83J_EyGI38 for ; Fri, 7 Jul 2017 14:16:16 -0400 (EDT) Received: from mail-it0-f46.google.com (mail-it0-f46.google.com [209.85.214.46]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 374C39934 for ; Fri, 7 Jul 2017 14:16:16 -0400 (EDT) Received: by mail-it0-f46.google.com with SMTP id 188so4697278itx.0 for ; Fri, 07 Jul 2017 11:16:16 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=LCgcXwbxbOj01543eWOK+0R2iseq+npyQlmX7ykfdsg=; b=JGssn62m2mOxvXtwNzvMcSEzpoMQMQzHVPjPddVHZ4WEk3tx1qwYs1P7RMgzCEHh/p VhT9TlNhqBpJDvsDU2eh8o0UBaV1MEcgitRFmNunz3UgHA0VHIRa3NdCmAvgNMfJQav1 Hu8CxSohHTBjjaRLpF5dzTYYRYsEBFvAON8wuESasKlOILK08bP6erj4eb3eM/cuijS2 4rWhjPxG99jxcof+T+vS9GshNrfX3D2LwksgceawuTnerqaPBEMBSj8bh/YkoNkOkba1 A3UX3YuOj1RdbIHcjB0HV8fgVhN46Ptg8Hj4uEjhE6cv7GYBTcdLk9mmwcufyN6yuVTy W1sQ== X-Gm-Message-State: AIVw113tkGMMfkCDgvope6QlTpkRFD1yS55g5NmIFmQ+bUszQ3Ic0Ziz fuTNfa9xFNsHkkutvjropt9Yxt+7vA== X-Received: by 10.36.112.9 with SMTP id f9mr216139itc.39.1499451374896; Fri, 07 Jul 2017 11:16:14 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.48.19 with HTTP; Fri, 7 Jul 2017 11:16:13 -0700 (PDT) In-Reply-To: References: From: Balwanth B Date: Fri, 7 Jul 2017 14:16:13 -0400 Message-ID: Subject: Re: Parallel not being used by Oracle To: Jonathan Lewis , "Powell, Mark" Cc: ORACLE-L Content-Type: multipart/alternative; boundary="001a11441b0c8f286d0553be3a6e" X-archive-position: 68734 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: balwanthdba@gmail.com Precedence: normal Reply-To: balwanthdba@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --001a11441b0c8f286d0553be3a6e Content-Type: text/plain; charset="UTF-8" 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 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@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@freelists.org on >> behalf of Balwanth B >> 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 | >> ------------------------------------------------------------ >> --------------------------------------------------------------- >> > > --001a11441b0c8f286d0553be3a6e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
HI Jonathan,

After making the change I = am able to see below plan

SQL_ID =C2=A06a1dch= jszsarm, child number 0
-------------------------------------
SELECT /*+ parallel(4) full(parametervalue) */=C2=A0
DISTI= NCT(PV.PARAMETER_VALUE_NAME), D.DEVICETYPE_ID FROM DEVICE D,=C2=A0
PARAMETERVALUE PV, TMP_HDM_CLEANUP_INSTANCE TMP WHERE=C2=A0
D.C= ACHED_DATA_RECORD_ID =3D PV.DATA_RECORD_ID AND D.DEVICETYPE_ID =3D=C2=A0
TMP.DEVICETYPE_ID AND PV.PARAMETER_VALUE_NAME LIKE=C2=A0
= TMP.PARAMETER_VALUE_NAME ESCAPE :B1
=C2=A0
Plan hash va= lue: 3730697107
=C2=A0
--------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------
| Id =C2=A0| Op= eration =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0| Name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Rows =C2=A0| Bytes | Cost (%CPU)| Time =C2=A0= =C2=A0 | Pstart| Pstop | =C2=A0 =C2=A0TQ =C2=A0|IN-OUT| PQ Distrib |
=
----------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------
| =C2=A0 0 | SELECT STATEMENT =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 =C2=A0 =C2=A0 | =C2=A03444 (100)| =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2= =A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= |
| =C2=A0 1 | =C2=A0PX COORDINATOR =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|
| =C2=A0 2 | =C2=A0 PX SEND QC (RAND= OM) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| :TQ10001 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 1 | =C2=A0 10= 0 | =C2=A03444 =C2=A0 (1)| 00:00:42 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 | =C2=A0Q1,01 | P->S | QC (RAND) =C2=A0|
| =C2=A0 3 | = =C2=A0 =C2=A0HASH UNIQUE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 1 | =C2=A0 100 | =C2= =A03444 =C2=A0 (1)| 00:00:42 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 = | =C2=A0Q1,01 | PCWP | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
| =C2=A0 4 | =C2=A0 =C2=A0 PX RECEIVE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 1 | =C2= =A0 100 | =C2=A03443 =C2=A0 (1)| 00:00:42 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 = =C2=A0 =C2=A0 | =C2=A0Q1,01 | PCWP | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0|
| =C2=A0 5 | =C2=A0 =C2=A0 =C2=A0PX SEND HASH =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| :TQ10000 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 1 | =C2=A0 100 | = =C2=A03443 =C2=A0 (1)| 00:00:42 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2= =A0 | =C2=A0Q1,00 | P->P | HASH =C2=A0 =C2=A0 =C2=A0 |
| =C2= =A0 6 | =C2=A0 =C2=A0 =C2=A0 NESTED LOOPS =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 1 | =C2=A0 100 | = =C2=A03443 =C2=A0 (1)| 00:00:42 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2= =A0 | =C2=A0Q1,00 | PCWP | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
=
| =C2=A0 7 | =C2=A0 =C2=A0 =C2=A0 =C2=A0NESTED LOOPS =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 1 | = =C2=A0 =C2=A057 | =C2=A03442 =C2=A0 (1)| 00:00:42 | =C2=A0 =C2=A0 =C2=A0 | = =C2=A0 =C2=A0 =C2=A0 | =C2=A0Q1,00 | PCWP | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0|
| =C2=A0 8 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 PX BLOCK ITE= RATOR =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 | =C2=A0Q1,00 | PCWC | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
|* =C2=A09 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0TABLE ACCESS FULL = =C2=A0 =C2=A0 =C2=A0 =C2=A0 | TMP_HDM_CLEANUP_INSTANCE | =C2=A04125 | =C2= =A0 185K| =C2=A0 =C2=A0 3 =C2=A0 (0)| 00:00:01 | =C2=A0 =C2=A0 =C2=A0 | =C2= =A0 =C2=A0 =C2=A0 | =C2=A0Q1,00 | PCWP | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0|
| =C2=A010 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 TABLE ACCESS BY= INDEX ROWID| DEVICE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | =C2=A0 =C2=A0 1 | =C2=A0 =C2=A011 | =C2=A0 =C2=A0 1 =C2=A0 (0)= | 00:00:01 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0Q1,00 | PC= WP | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
|* 11 | =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0INDEX RANGE SCAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| SYS_C0016783 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A01822K| = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 1 =C2=A0 (0)| 00:00:01 | =C2=A0 =C2=A0= =C2=A0 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0Q1,00 | PCWP | =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0|
| =C2=A012 | =C2=A0 =C2=A0 =C2=A0 =C2=A0PAR= TITION HASH ITERATOR =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 1 | =C2= =A0 =C2=A043 | =C2=A0 =C2=A0 1 =C2=A0 (0)| 00:00:01 | =C2=A0 KEY | =C2=A0 K= EY | =C2=A0Q1,00 | PCWP | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
<= div>|* 13 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 INDEX RANGE SCAN =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | UQ_PARAM_NEW =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = | =C2=A0 =C2=A0 1 | =C2=A0 =C2=A043 | =C2=A0 =C2=A0 1 =C2=A0 (0)| 00:00:01 = | =C2=A0 KEY | =C2=A0 KEY | =C2=A0Q1,00 | PCWP | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0|
----------------------------------------------= ---------------------------------------------------------------------------= -----------------------------------

=C2= =A0
Query Block Name / Object Alias (identified by operation id):=
-------------------------------------------------------------
=C2=A0
=C2=A0 =C2=A01 - SEL$1
=C2=A0 =C2=A09 - = SEL$1 / TMP@SEL$1
=C2=A0 10 - SEL$1 / D@SEL$1
=C2=A0 11= - SEL$1 / D@SEL$1
=C2=A0 13 - SEL$1 / PV@SEL$1
=C2=A0<= /div>
Predicate Information (identified by operation id):
---= ------------------------------------------------
=C2=A0
=C2=A0 =C2=A09 - access(:Z>=3D:Z AND :Z<=3D:Z)
=C2=A0 11 -= access("D"."DEVICETYPE_ID"=3D"TMP"."DEV= ICETYPE_ID")
=C2=A0 13 - access("D"."CACHED_D= ATA_RECORD_ID"=3D"PV"."DATA_RECORD_ID" AND "P= V"."PARAMETER_VALUE_NAME" LIKE "TMP"."PARAMET= ER_VALUE_NAME" ESCAPE :B1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0filter= ("PV"."PARAMETER_VALUE_NAME" LIKE "TMP"."= ;PARAMETER_VALUE_NAME" ESCAPE :B1)
=C2=A0
Column P= rojection Information (identified by operation id):
-------------= ----------------------------------------------
=C2=A0
= =C2=A0 =C2=A01 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,2= 55], "D"."DEVICETYPE_ID"[NUMBER,22]
=C2=A0 = =C2=A02 - (#keys=3D0) "PV"."PARAMETER_VALUE_NAME"[VARCH= AR2,255], "D"."DEVICETYPE_ID"[NUMBER,22]
=C2= =A0 =C2=A03 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255]= , "D"."DEVICETYPE_ID"[NUMBER,22]
=C2=A0 =C2= =A04 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "= ;D"."DEVICETYPE_ID"[NUMBER,22]
=C2=A0 =C2=A05 - (#= keys=3D2) "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255], &q= uot;D"."DEVICETYPE_ID"[NUMBER,22]
=C2=A0 =C2=A06 -= "D"."DEVICETYPE_ID"[NUMBER,22], "PV"."P= ARAMETER_VALUE_NAME"[VARCHAR2,255]
=C2=A0 =C2=A07 - "TM= P"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "D"."= ;DEVICETYPE_ID"[NUMBER,22], "D"."CACHED_DATA_RECORD_ID&= quot;[NUMBER,22]
=C2=A0 =C2=A08 - "TMP"."PARAMETER= _VALUE_NAME"[VARCHAR2,255], "TMP"."DEVICETYPE_ID"[= NUMBER,22]
=C2=A0 =C2=A09 - "TMP"."PARAMETER_VALUE= _NAME"[VARCHAR2,255], "TMP"."DEVICETYPE_ID"[NUMBER= ,22]
=C2=A0 10 - "D"."DEVICETYPE_ID"[NUMBER,2= 2], "D"."CACHED_DATA_RECORD_ID"[NUMBER,22]
= =C2=A0 11 - "D".ROWID[ROWID,10], "D"."DEVICETYPE_I= D"[NUMBER,22]
=C2=A0 12 - "PV"."PARAMETER_VAL= UE_NAME"[VARCHAR2,255]
=C2=A0 13 - "PV"."PARA= METER_VALUE_NAME"[VARCHAR2,255]
=C2=A0
Note
<= div>-----
=C2=A0 =C2=A0- automatic DOP: Computed Degree of Parall= elism is 4

On Fri, Jul 7, 2017 at 1:44 PM, Balwanth B <= ;balwanthdba@gma= il.com> wrote:
previos email sql monitor report was from staging environment... As jo= nathan said, I tried forcing parallel its still taking serial access.. Foll= owing his suggestions, will let you know how this goes.

On Fri, Jul 7, 2017 at 11:29 AM, Jonathan Lewis <jo= nathan@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 pl= an 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 hin= t with both parallel() and full().

Regards
Jonathan Lewis


________________________________________
From: or= acle-l-bounce@freelists.org <oracle-l-bounce@freelists.org> on b= ehalf of Balwanth B <balwanthdba@gmail.com>
Sent: 07 July 2017 15:44:56
To: ORA= CLE-L
Subject: Parallel not being used by Oracle

This particular query which is=C2=A0 part of PL/SQL=C2=A0 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 =3D
PV.DATA_RECORD_ID AND D.DEVICETYPE_ID =3D TMP.DEVICETYPE_ID AND
PV.PARAMETER_VALUE_NAME LIKE TMP.PARAMETER_VALUE_NAME ESCAPE :B1


-----------------------------------------------------------------= ----------------------------------------------------------
| Id=C2=A0 | Operation=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | Name=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Rows=C2=A0 | Bytes | Cost (%CPU)| Time= =C2=A0 =C2=A0 =C2=A0| Pstart| Pstop |
-----------------------------------------------------------------= ----------------------------------------------------------
|=C2=A0 =C2=A00 | SELECT STATEMENT=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 = =C2=A0 =C2=A0| 12395 (100)|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2= =A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0|
|=C2=A0 =C2=A01 |=C2=A0 HASH UNIQUE=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A01 |= =C2=A0 =C2=A0100 | 12395=C2=A0 =C2=A0(1)| 00:02:29 |=C2=A0 =C2=A0 =C2=A0 = =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0|
|=C2=A0 =C2=A02 |=C2=A0 =C2=A0NESTED LOOPS=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A01 |= =C2=A0 =C2=A0100 | 12393=C2=A0 =C2=A0(1)| 00:02:29 |=C2=A0 =C2=A0 =C2=A0 = =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0|
|=C2=A0 =C2=A03 |=C2=A0 =C2=A0 NESTED LOOPS=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A01 |=C2=A0 = =C2=A0 57 | 12390=C2=A0 =C2=A0(1)| 00:02:29 |=C2=A0 =C2=A0 =C2=A0 =C2=A0|= =C2=A0 =C2=A0 =C2=A0 =C2=A0|
|=C2=A0 =C2=A04 |=C2=A0 =C2=A0 =C2=A0TABLE ACCESS FULL=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | TMP_HDM_CLEANUP_INSTANCE |=C2=A0 4125 |=C2=A0 =C2=A0185K|= =C2=A0 =C2=A0 11=C2=A0 =C2=A0(0)| 00:00:01 |=C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2= =A0 =C2=A0 =C2=A0 =C2=A0|
|=C2=A0 =C2=A05 |=C2=A0 =C2=A0 =C2=A0TABLE ACCESS BY INDEX ROWID| DEVICE=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 = =C2=A0 =C2=A01 |=C2=A0 =C2=A0 11 |=C2=A0 =C2=A0 =C2=A03=C2=A0 =C2=A0(0)| 00= :00:01 |=C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0|
|*=C2=A0 6 |=C2=A0 =C2=A0 =C2=A0 INDEX RANGE SCAN=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | SYS_C0016783=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|= =C2=A0 1822K|=C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A03=C2=A0 =C2=A0= (0)| 00:00:01 |=C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0|
|=C2=A0 =C2=A07 |=C2=A0 =C2=A0 PARTITION HASH ITERATOR=C2=A0 =C2=A0 =C2=A0|= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A01 |=C2=A0 =C2=A0 43 |=C2=A0 =C2=A0 = =C2=A03=C2=A0 =C2=A0(0)| 00:00:01 |=C2=A0 =C2=A0KEY |=C2=A0 =C2=A0KEY |
|*=C2=A0 8 |=C2=A0 =C2=A0 =C2=A0INDEX RANGE SCAN=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0| UQ_PARAM_NEW=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0|=C2=A0 =C2=A0 =C2=A01 |=C2=A0 =C2=A0 43 |=C2=A0 =C2=A0 =C2=A03=C2=A0 = =C2=A0(0)| 00:00:01 |=C2=A0 =C2=A0KEY |=C2=A0 =C2=A0KEY |
-----------------------------------------------------------------= ----------------------------------------------------------


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