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 723011960301 for ; Fri, 7 Jul 2017 17:32:22 +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 17:32:22 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4506F9933; Fri, 7 Jul 2017 11:32:21 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1499441541; bh=duznBjsEy0ktSlaydv7WxN3yjOrC4NcakvV0DapWTIo=; h=From:To:Subject:Date:References:In-Reply-To:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=Jsug1AlButyjYycJ9K5EWUal6Zn864iWKiF43TI6OwXqF6dQ+VQHxFQW9JozZg0+s MwjqgBXq5yF/cQfsH+DZQYg/T01j4jQ2JFzrEiLboSHVLPmP0Ibg67BanfOSiiUIVH FWMdxasWVAOdch6Kymqy4pyPk4wR4RIYmD0pJbu0= 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 wt8o3pH4DYZS; Fri, 7 Jul 2017 11:32:21 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BFA319900; Fri, 7 Jul 2017 11:32:08 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1499441540; bh=duznBjsEy0ktSlaydv7WxN3yjOrC4NcakvV0DapWTIo=; h=From:To:Subject:Date:References:In-Reply-To:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=HN/1qD3gcTUuzpx/8ZLSrQD6+eYmHB+wnCpX+6EVmTp93D1GSly7UiMVLIy/irNyt OgbrHhkbSozum6hDNo/1LLI3445CguIVTLMjLfU2U8LhTvgRJNhT3vvifJWZMfk95i zYVsmP2Y7OQqgiLgfg0mVLlVgZIm+6iT0KoiAl7o= Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 07 Jul 2017 11:30:47 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0819998F2 for ; Fri, 7 Jul 2017 11:30:46 -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 6CsjPhyBXFsO for ; Fri, 7 Jul 2017 11:30:45 -0400 (EDT) Received: from g4t3425.houston.hpe.com (g4t3425.houston.hpe.com [15.241.140.78]) (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id A85D298F0 for ; Fri, 7 Jul 2017 11:30:44 -0400 (EDT) Received: from G1W8106.americas.hpqcorp.net (g1w8106.austin.hp.com [16.193.72.61]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-SHA384 (256/256 bits)) (No client certificate requested) by g4t3425.houston.hpe.com (Postfix) with ESMTPS id EAA286A; Fri, 7 Jul 2017 15:30:43 +0000 (UTC) Received: from G1W8106.americas.hpqcorp.net (2002:10c1:483d::10c1:483d) by G1W8106.americas.hpqcorp.net (2002:10c1:483d::10c1:483d) with Microsoft SMTP Server (TLS) id 15.0.1178.4; Fri, 7 Jul 2017 15:30:43 +0000 Received: from NAM03-BY2-obe.outbound.protection.outlook.com (15.241.52.13) by G1W8106.americas.hpqcorp.net (16.193.72.61) with Microsoft SMTP Server (TLS) id 15.0.1178.4 via Frontend Transport; Fri, 7 Jul 2017 15:30:43 +0000 Received: from DF4PR84MB0124.NAMPRD84.PROD.OUTLOOK.COM (10.162.192.22) by DF4PR84MB0122.NAMPRD84.PROD.OUTLOOK.COM (10.162.192.20) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256) id 15.1.1220.11; Fri, 7 Jul 2017 15:30:41 +0000 Received: from DF4PR84MB0124.NAMPRD84.PROD.OUTLOOK.COM ([10.162.192.22]) by DF4PR84MB0124.NAMPRD84.PROD.OUTLOOK.COM ([10.162.192.22]) with mapi id 15.01.1220.018; Fri, 7 Jul 2017 15:30:41 +0000 From: "Powell, Mark" To: ORACLE-L , "balwanthdba@gmail.com" Subject: Re: Parallel not being used by Oracle Thread-Topic: Parallel not being used by Oracle Date: Fri, 7 Jul 2017 15:30:41 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: freelists.org; dkim=none (message not signed) header.d=none;freelists.org; dmarc=none action=none header.from=hpe.com; x-originating-ip: [15.203.233.78] x-ms-publictraffictype: Email x-microsoft-exchange-diagnostics: 1;DF4PR84MB0122;7:pHsPRkYaYMbahvgvNEx6D9E79IKOa4+mQtlPgVt6oZG4vsMJgQ/TqyNjWe9AfqYlbht84hLnyZaiUbNPkWFf+cljYHTBoHyYp3d0ns3B+jg6LmIooB0XBt0n6MsR/fpBsJY3y0HwpEGyQMG+HXfKie/IhOJo+Kd6hlEpFnpu8HoE7UZC7IJmlyMfyIjZr8J96rIt2cWkcREOZmalRaiaMf0Ky0/TUQks8Wd7e8mjNXYqTD2a5++JFr/btZg9zrME8yN2FWXprOVaxLqwhppbmcK040783aAx4iQLGaZqiJUStAuAkyYMR15J5R6uwWj4RrH3oKuR4H1tCN8/5ouXsP3gmDK1yIITpdds7nmuIWTUHQpiNZMCOuwhDl2iR8AvXml2tgnkvTMhiWfrw2B2ZMDgKU/g790Yb/y6t9KYzAJmUHLz/3DavqnBITih/E0kXgd/iA2GxCyTzjiCx/h+znZ3nIMicvOtSfTg27fYWj0nBJZKPQBeib78LsV2iYwGmO+/gfvkLItJadRtPfkspN4996fFSi08WRlk97y4nhg3UC3cwnOcSmzZgbcrr1v31amWFMM4wYQSzyGiUDZIQ6+oaAALIFwvsVBjUKvwsYaQDF28dgz/Wx7XsFWzXYedquvoAAlQ3wsu37f3u+63JmN6nEReRUS0ciJDPOtNrU5uBNV2aKNa8EHqwV+VxpRNXltNa1DuLh4M5pSiqFGvwixMQV2R4APgYjEy7WaEkepiCg0vNCFsxhx/2Ez+s0MzEpVsyYbdys2dZP25qsw6gACCTJQstvZ6n+cudTM/a8Q= x-ms-office365-filtering-correlation-id: 9bbc2618-c25d-4396-0c60-08d4c54d20a3 x-ms-office365-filtering-ht: Tenant x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:(300000500095)(300135000095)(300000501095)(300135300095)(22001)(300000502095)(300135100095)(2017030254075)(48565401081)(300000503095)(300135400095)(2017052603031)(201703131423075)(201703031133081)(300000504095)(300135200095)(300000505095)(300135600095)(300000506095)(300135500095);SRVR:DF4PR84MB0122; x-ms-traffictypediagnostic: DF4PR84MB0122: x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:(236129657087228); x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(100000700101)(100105000095)(100000701101)(100105300095)(100000702101)(100105100095)(6040450)(601004)(2401047)(5005006)(8121501046)(3002001)(10201501046)(93006095)(93001095)(100000703101)(100105400095)(6055026)(6041248)(201703131423075)(201702281528075)(201703061421075)(201703061406153)(20161123562025)(20161123558100)(20161123564025)(20161123555025)(20161123560025)(6072148)(100000704101)(100105200095)(100000705101)(100105500095);SRVR:DF4PR84MB0122;BCL:0;PCL:0;RULEID:(100000800101)(100110000095)(100000801101)(100110300095)(100000802101)(100110100095)(100000803101)(100110400095)(100000804101)(100110200095)(100000805101)(100110500095);SRVR:DF4PR84MB0122; x-forefront-prvs: 0361212EA8 x-forefront-antispam-report: SFV:NSPM;SFS:(10019020)(6009001)(39400400002)(39860400002)(39840400002)(39450400003)(39850400002)(39410400002)(377454003)(2906002)(81166006)(229853002)(7696004)(2501003)(54896002)(55016002)(2950100002)(74316002)(66066001)(8936002)(77096006)(5660300001)(86362001)(2900100001)(9686003)(189998001)(33656002)(6506006)(478600001)(7736002)(6246003)(6436002)(19627405001)(3846002)(6116002)(3660700001)(76176999)(8676002)(38730400002)(25786009)(102836003)(14454004)(53936002)(54356999)(39060400002)(3280700002)(50986999);DIR:OUT;SFP:1102;SCL:1;SRVR:DF4PR84MB0122;H:DF4PR84MB0124.NAMPRD84.PROD.OUTLOOK.COM;FPR:;SPF:None;MLV:ovrnspm;PTR:InfoNoRecords;LANG:en; spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_DF4PR84MB01247757DBFD635A79EAA1A0CCAA0DF4PR84MB0124NAMP_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-originalarrivaltime: 07 Jul 2017 15:30:41.6167 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 105b2061-b669-4b31-92ac-24d304d195dc X-MS-Exchange-Transport-CrossTenantHeadersStamped: DF4PR84MB0122 X-OriginatorOrg: hpe.com X-archive-position: 68730 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mark.powell2@dxc.com Precedence: normal Reply-To: mark.powell2@dxc.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 --_000_DF4PR84MB01247757DBFD635A79EAA1A0CCAA0DF4PR84MB0124NAMP_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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@freelists.org on beha= lf of Balwanth B 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 an= d 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 | 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 | ---------------------------------------------------------------------------= ------------------------------------------------ --_000_DF4PR84MB01247757DBFD635A79EAA1A0CCAA0DF4PR84MB0124NAMP_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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


Mark Powell
Database Administration
(313) 592-5148


From: oracle-l-bounce@freel= ists.org <oracle-l-bounce@freelists.org> on behalf of Balwanth B <= balwanthdba@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 f= or 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),&nb= sp;
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 <= /div>
PV.PARAMETER_VALUE_NAME LIKE TMP.PARAMETER_VALUE_NAME ESCAPE :B1


----------------------------------------------------------------------= -----------------------------------------------------
| Id  | Operation              = ;        | Name           &nbs= p;         | Rows  | Bytes | Cost (%CPU)| Time &nb= sp;   | Pstart| Pstop |
----------------------------------------------------------------------= -----------------------------------------------------
|   0 | SELECT STATEMENT            = ;   |                   &= nbsp;      |       |       | 1= 2395 (100)|          |       | &nbs= p;     |
|   1 |  HASH UNIQUE           &nbs= p;       |               =            |     1 |   100 | 1= 2395   (1)| 00:02:29 |       |       |
|   2 |   NESTED LOOPS           &n= bsp;     |                = ;          |     1 |   100 | 12393 =   (1)| 00:02:29 |       |       |
|   3 |    NESTED LOOPS         &nb= sp;      |               =            |     1 |    5= 7 | 12390   (1)| 00:02:29 |       |      = ; |
|   4 |     TABLE ACCESS FULL       &nbs= p;  | TMP_HDM_CLEANUP_INSTANCE |  4125 |   185K|   &nbs= p;11   (0)| 00:00:01 |       |       |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEVICE   =                 |     1 |=    11 |     3   (0)| 00:00:01 |     &nb= sp; |       |
|*  6 |      INDEX RANGE SCAN      =    | SYS_C0016783             | &n= bsp;1822K|       |     3   (0)| 00:00:01 | &n= bsp;     |       |
|   7 |    PARTITION HASH ITERATOR     | &nbs= p;                     &n= bsp;  |     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 |
----------------------------------------------------------------------= -----------------------------------------------------
--_000_DF4PR84MB01247757DBFD635A79EAA1A0CCAA0DF4PR84MB0124NAMP_-- -- http://www.freelists.org/webpage/oracle-l