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 6B94F1961371 for ; Fri, 7 Jul 2017 20:59:40 +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:59:40 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C1C489B8D; Fri, 7 Jul 2017 14:59:38 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1499453978; bh=9IqZFKGdom9gOP3tDfh+8+NTSosVNyKmM0EvYpgb3d4=; h=From:To:CC:Subject:Date:References:In-Reply-To:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=ujuA8m3aXgP5Lhk/ih849nLwt27coER9igavzEy8oMIQELhXPUmTfXvU58TvS4K+1 mcDD+3Oid7Van6Z4BDq8E7z+13gXsN8xCTp94s2z/oYGib77J5VvEPQJL4G1+fTp+q 1YEmPo+xS3z1f+NhsE5QYIJuBLPEek7kuEs39SKw= 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 x4pNOCGEq43u; Fri, 7 Jul 2017 14:59:38 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EA9099B74; Fri, 7 Jul 2017 14:59:25 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1499453977; bh=9IqZFKGdom9gOP3tDfh+8+NTSosVNyKmM0EvYpgb3d4=; h=From:To:CC:Subject:Date:References:In-Reply-To:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=tPPGzffJenqRTyyeeGmouaKUzA1BdqSOvlweDvcMnIv81I6IbE4CT1Xo7G8FCKYNq QIayzL26uZhJY3I/6PlP0t4yPxdhSnuzPrpZdvFQw0t+qvVo+b2qP8U8oWZxDZ6Ero drb+XbosOGIS6paDYQIxCqSYYP1OZsDCCVrztpLQ= Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 07 Jul 2017 14:58:04 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 73F959B72 for ; Fri, 7 Jul 2017 14:58:02 -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 XACRE-Jp8jc2 for ; Fri, 7 Jul 2017 14:58:02 -0400 (EDT) Received: from g2t2352.austin.hpe.com (g2t2352.austin.hpe.com [15.233.44.25]) (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 2252A9B70 for ; Fri, 7 Jul 2017 14:58:00 -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 g2t2352.austin.hpe.com (Postfix) with ESMTPS id 32BBE78; Fri, 7 Jul 2017 18:57:59 +0000 (UTC) Received: from G9W8672.americas.hpqcorp.net (16.220.49.31) by G1W8106.americas.hpqcorp.net (16.193.72.61) with Microsoft SMTP Server (TLS) id 15.0.1178.4; Fri, 7 Jul 2017 18:57:45 +0000 Received: from G1W8106.americas.hpqcorp.net (16.193.72.61) by G9W8672.americas.hpqcorp.net (16.220.49.31) with Microsoft SMTP Server (TLS) id 15.0.1178.4; Fri, 7 Jul 2017 18:57:44 +0000 Received: from NAM03-DM3-obe.outbound.protection.outlook.com (15.241.52.11) 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 18:57:44 +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 18:57:42 +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 18:57:42 +0000 From: "Powell, Mark" To: Balwanth B , Jonathan Lewis CC: ORACLE-L Subject: Re: Parallel not being used by Oracle Thread-Topic: Parallel not being used by Oracle Date: Fri, 7 Jul 2017 18:57:42 +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: gmail.com; dkim=none (message not signed) header.d=none;gmail.com; dmarc=none action=none header.from=hpe.com; x-originating-ip: [12.47.84.135] x-ms-publictraffictype: Email x-microsoft-exchange-diagnostics: 1;DF4PR84MB0122;7:hD91/vQejt3Xm88OQfV1UW3Ek20wz1InOnNv+K+Ayx358oWyVCxltRrdyu9d5kY4FTN/WcyQdzwek8UBfeftIZMgjufgIQsPOsCnghEtQBjKcvSDNi5xi9YwTkJDfSMR7hJEIuQ/gtCvC73GLeveV0WB856SuGo54EGsNBr4wboP25PRa/7BzVkzxUUAUhHD6n/3cLDuRevdruWyTlzy9UQo3UT38A0KCBUnbPsdrit2gJbgsIlCsNlX/SBPfLB8XgYB7gljRcQ6lpbBL3261x7O6+dwqa60HJK9TpmVqRHWHVlDjTHXxmPwXneu34nwy/7jSBUHQSQy3LWnV+veCROU6juXtYaikN/4gluwwXuwc1uDYYrimk6z9Kuc6oeLmxaureRZEVSQ8JW6FQu/eqIlAhxUYts45PsboHZwXeIOJXQl0tCTRBT2CaLRCZojToS5rHcQNIrshKg4JAYSnrK4e1ET6KiCghTjcWW6mil9yD0QPGKuX4XFj/URCi1hzhu/2bsI0hG1RRQzH6oxcgCaOl6/ThvzZC/MMd6bbZdD9GR7bDNlBO+nCtjLUe0PxxBZTS8mF7gxR+az7eF5G4/v82XYKTHpI+7j69kTsGjLR4aeqdqx9UvGAwxO3dqoS+I6bZH3UqzT+Kek7cQ9eJ4LPyFBhxaIlqpbva0l1sP8UQKE2L7SL57wPhHlIiZv+TiW1HxktJmU1Jcgja6zPrXokdKtvP2d7ZIsZUmfeKcP7GU6uBr6cED4xbs2mQxjHe6GiFs2/OR+KLxysuBBiXxhWOaeGL3xJPxTivO+nNQ= x-ms-office365-filtering-correlation-id: e8fac6d7-5265-42c8-4028-08d4c56a0c33 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)(201702281549075)(300000504095)(300135200095)(300000505095)(300135600095)(300000506095)(300135500095);SRVR:DF4PR84MB0122; x-ms-traffictypediagnostic: DF4PR84MB0122: x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:(72170088055959)(236129657087228)(47647156867600)(48057245064654)(148574349560750)(247924648384137); x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(100000700101)(100105000095)(100000701101)(100105300095)(100000702101)(100105100095)(6040450)(601004)(2401047)(8121501046)(5005006)(93006095)(93001095)(100000703101)(100105400095)(10201501046)(3002001)(6055026)(6041248)(20161123564025)(201703131423075)(201702281528075)(201703061421075)(201703061406153)(20161123555025)(20161123558100)(20161123560025)(20161123562025)(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)(39400400002)(39840400002)(39860400002)(39450400003)(39850400002)(39410400002)(24454002)(377454003)(2906002)(7696004)(229853002)(236005)(54896002)(6306002)(81166006)(74316002)(55016002)(2950100002)(66066001)(8936002)(77096006)(86362001)(9686003)(189998001)(2900100001)(5660300001)(6506006)(53546010)(93886004)(33656002)(4326008)(7736002)(478600001)(966005)(6246003)(606006)(6436002)(19627405001)(6116002)(3846002)(3660700001)(76176999)(38730400002)(8676002)(53936002)(14454004)(102836003)(54356999)(3280700002)(25786009)(39060400002)(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_DF4PR84MB0124785FB77B3242E31FE28CCCAA0DF4PR84MB0124NAMP_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-originalarrivaltime: 07 Jul 2017 18:57:42.6410 (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: 68737 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_DF4PR84MB0124785FB77B3242E31FE28CCCAA0DF4PR84MB0124NAMP_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable My view is if the previous plan took 6 hours and the new plan takes 1 hour = then obviously the new plan is better. I just need to try to determine wha= t the plan is telling me so that I can understand why. Mark Powell Database Administration (313) 592-5148 ________________________________ From: Balwanth B Sent: Friday, July 7, 2017 2:21:51 PM To: Jonathan Lewis; Powell, Mark Cc: ORACLE-L Subject: Re: Parallel not being used by Oracle I am still not sure how long with this take, because in staging parameterv= alue table has 10% records of what we have in production. Num of records in= production parametervalue table has 622 million records.. If you see my pr= evious sql monitoring plan in staging environment that did full table on de= vice,parametervalue,and TMP_HDM_CLEANUP_INSTANCE(driving table),where I saw= time benefit before it ran for 6 hrs runs but now its running for 1hr .. B= ut here it still uses index range scans but with parallel this time.. I am = totally confused... does the above plan which I just sent looks good? On Fri, Jul 7, 2017 at 2:20 PM, Balwanth B > wrote: I am still not sure how long with this take, because in staging parameterva= lue table has 10% records of what we have in staging. Num of records in pro= duction parametervalue table has 622 million records.. If you see my previo= us sql monitoring plan in staging environment that did full table on device= ,parametervalue,and TMP_HDM_CLEANUP_INSTANCE(driving table).. But here it s= till uses index range scans but with parallel this time.. I am totally conf= used... does the above plan which I just sent looks good? On Fri, Jul 7, 2017 at 2:16 PM, Balwanth B > wrote: 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 =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 Plan hash value: 3730697107 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------ | Id | Operation | Name | Row= s | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Dis= trib | ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------ | 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 (RA= ND) | | 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 | 41= 25 | 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 | 18= 22K| | 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>=3D:Z AND :Z<=3D:Z) 11 - access("D"."DEVICETYPE_ID"=3D"TMP"."DEVICETYPE_ID") 13 - access("D"."CACHED_DATA_RECORD_ID"=3D"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"[NUMBE= R,22] 2 - (#keys=3D0) "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "D"."DEVICETY= PE_ID"[NUMBER,22] 3 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "D"."DEVICETYPE_ID"[NUMBE= R,22] 4 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "D"."DEVICETYPE_ID"[NUMBE= R,22] 5 - (#keys=3D2) "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "D"."DEVICETY= PE_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"[NUMB= ER,22], "D"."CACHED_DATA_RECORD_ID"[NUMBER,22] 8 - "TMP"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "TMP"."DEVICETYPE_ID"[NU= MBER,22] 9 - "TMP"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "TMP"."DEVICETYPE_ID"[NU= MBER,22] 10 - "D"."DEVICETYPE_ID"[NUMBER,22], "D"."CACHED_DATA_RECORD_ID"[NUMBER,2= 2] 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 jonatha= n 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 > 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.w= ordpress.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: oracle-l-bounce@freelists.org <= oracle-l-bounce@freelists.org> on beh= alf 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 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_DF4PR84MB0124785FB77B3242E31FE28CCCAA0DF4PR84MB0124NAMP_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable


My view is if the previous plan took 6 hours and the new plan takes 1 ho= ur then obviously the new plan is better.  I just need to try to deter= mine what the plan is telling me so that I can understand why.



Mark Powell
Database Administration
(313) 592-5148


From: Balwanth B <balwan= thdba@gmail.com>
Sent: Friday, July 7, 2017 2:21:51 PM
To: Jonathan Lewis; Powell, Mark
Cc: ORACLE-L
Subject: Re: Parallel not being used by Oracle
 
I am still not sure how l= ong with this take, because in staging  parametervalue table has 10% r= ecords of what we have in production. Num of records in production paramete= rvalue table has 622 million records.. If you see my previous sql monitoring plan in staging environment that did fu= ll table on device,parametervalue,and TMP_HDM_CLEANUP_INSTANCE(driving table),where I saw time benef= it before it ran for 6 hrs runs but now its running for 1hr .. But here it still uses index range scans but with p= arallel this time.. I am totally confused... does the above plan which I ju= st sent looks good?

On Fri, Jul 7, 2017 at 2:20 PM, Balwanth B <balwanthdba@= gmail.com> wrote:
I am still not sure how long with this take, because in st= aging parametervalue table has 10% records of what we have in staging. Num = of records in production parametervalue table has 622 million records.. If = you see my previous sql monitoring plan in staging environment that did full table on device,parametervalue,a= nd TMP_HDM_CLEANUP_INSTANCE(driv= ing table).. But here it still uses index range scans but with parallel thi= s time.. I am totally confused... does the above plan which I just sent looks good?


On Fri, Jul 7, 2017 at 2:16 PM, Balwanth B <balwanthdba@= gmail.com> wrote:
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 =3D PV.DATA_RECORD_ID AND D.DEVICETYPE_ID =3D&= nbsp;
TMP.DEVICETYPE_ID AND PV.PARAMETER_VALUE_NAME LIKE 
TMP.PARAMETER_VALUE_NAME ESCAPE :B1
 
Plan hash value: 3730697107
 
------------------------------------------------------------= -----------------------------------------------------------------= -------------------------------
| Id  | Operation              = ;            | Name       &nbs= p;             | Rows  | Bytes | Cost (%= CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| P= Q Distrib |
------------------------------------------------------------= -----------------------------------------------------------------= -------------------------------
|   0 | SELECT STATEMENT            = ;       |               &= nbsp;          |       |   &nb= sp;   |  3444 (100)|          |   &= nbsp;   |       |        |   &= nbsp;  |            |
|   1 |  PX COORDINATOR           &= nbsp;        |            = ;              |       | =       |            |   &n= bsp;      |       |       | &n= bsp;      |      |        = ;    |
|   2 |   PX SEND QC (RANDOM)         &n= bsp;    | :TQ10001             &nbs= p;   |     1 |   100 |  3444   (1)| 00:00:42 = |       |       |  Q1,01 | P->S | QC = (RAND)  |
|   3 |    HASH UNIQUE         &nbs= p;           |           =                |     1 | =   100 |  3444   (1)| 00:00:42 |       |  = ;     |  Q1,01 | PCWP |           &= nbsp;|
|   4 |     PX RECEIVE         &nbs= p;           |           =                |     1 | =   100 |  3443   (1)| 00:00:42 |       |  = ;     |  Q1,01 | PCWP |           &= nbsp;|
|   5 |      PX SEND HASH       &nb= sp;          | :TQ10000        = ;         |     1 |   100 |  3443 &= nbsp; (1)| 00:00:42 |       |       |  Q= 1,00 | P->P | HASH       |
|   6 |       NESTED LOOPS       &n= bsp;         |            = ;              |     1 |  = ; 100 |  3443   (1)| 00:00:42 |       |   &nb= sp;   |  Q1,00 | PCWP |            = |
|   7 |        NESTED LOOPS     &nb= sp;          |           =                |     1 | =    57 |  3442   (1)| 00:00:42 |       | =       |  Q1,00 | PCWP |         &nb= sp;  |
|   8 |         PX BLOCK ITERATOR   &nbs= p;      |               &= nbsp;          |       |   &nb= sp;   |            |      = ;    |       |       |  Q1,00 = | PCWC |            |
|*  9 |          TABLE ACCESS FULL  = ;       | TMP_HDM_CLEANUP_INSTANCE |  4125 |   185= K|     3   (0)| 00:00:01 |       |   &nb= sp;   |  Q1,00 | PCWP |            = |
|  10 |         TABLE ACCESS BY INDEX ROWID| = DEVICE                   | &nb= sp;   1 |    11 |     1   (0)| 00:00:01 | &nb= sp;     |       |  Q1,00 | PCWP |   &nbs= p;        |
|* 11 |          INDEX RANGE SCAN   &nbs= p;      | SYS_C0016783           &n= bsp; |  1822K|       |     1   (0)| 00:0= 0:01 |       |       |  Q1,00 | PCWP | &= nbsp;          |
|  12 |        PARTITION HASH ITERATOR  =   |                   &n= bsp;      |     1 |    43 |    = ; 1   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP | &= nbsp;          |
|* 13 |         INDEX RANGE SCAN     &nb= sp;     | 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>=3D:Z AND :Z<=3D:Z)
  11 - access("D"."DEVICETYPE_ID"=3D"TM<= wbr>P"."DEVICETYPE_ID")
  13 - access("D"."CACHED_DATA_RECORD_ID"= ;=3D"PV"."DATA_RECORD_ID" AND "PV"."PARA= METER_VALUE_NAME" LIKE "TMP"."PARAMETER_VALUE_NAME"= ; ESCAPE :B1)
       filter("PV"."PARAMETER_VALUE= _NAME" LIKE "TMP"."PARAMETER_VALUE_NAME" ESCA= PE :B1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "D"."DEVICETYPE_ID"[NUMBER,22]
   2 - (#keys=3D0) "PV"."PARAMETER_VALUE_NAME= "[VARCHAR2,255], "D"."DEVICETYPE_ID"[NUMBER,2= 2]
   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=3D2) "PV"."PARAMETER_VALUE_NAME= "[VARCHAR2,255], "D"."DEVICETYPE_ID"[NUMBER,2= 2]
   6 - "D"."DEVICETYPE_ID"[NUMBER,22], "PV"."PARAMETER_VALUE_NAME"[VARCHAR2,255]
   7 - "TMP"."PARAMETER_VALUE_NAME"[VARCHAR2,255], "D"."DEVICETYPE_ID"[NUMBER,22], &q= uot;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], &= quot;D"."CACHED_DATA_RECORD_ID"[NUMBER,22]
  11 - "D".ROWID[ROWID,10], "D"."DEVICET= YPE_ID"[NUMBER,22]
  12 - "PV"."PARAMETER_VALUE_NAME"[VARCH= AR2,255]
  13 - "PV"."PARAMETER_VALUE_NAME"[VARCH= AR2,255]
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 4

On Fri, Jul 7, 2017 at 1:44 PM, Balwanth B <balwanthdba@= gmail.com> wrote:
previos email sql monitor report was from staging environm= ent... As jonathan said, I tried forcing parallel its still taking serial a= ccess.. Following his suggestions, will let you know how this goes.

On Fri, Jul 7, 2017 at 11:29 AM, Jonathan Lewis = <jonath= an@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 indexe= d 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
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? Ca= n 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              &nbs= p;       | Name            &nb= sp;        | Rows  | Bytes | Cost (%CPU)| Time&nbs= p;    | Pstart| Pstop |
-----------------------------------------------------------------= ----------------------------------------------------------
|   0 | SELECT STATEMENT           =    |                &nbs= p;         |       |    &= nbsp;  | 12395 (100)|          |   =    |       |
|   1 |  HASH UNIQUE           = ;        |            &nb= sp;             |     1 | = ;  100 | 12395   (1)| 00:02:29 |       |=        |
|   2 |   NESTED LOOPS         = ;        |            &nb= sp;             |     1 | = ;  100 | 12393   (1)| 00:02:29 |       |=        |
|   3 |    NESTED LOOPS        &nbs= p;       |              &= nbsp;           |     1 |  &nb= sp; 57 | 12390   (1)| 00:02:29 |       | = ;      |
|   4 |     TABLE ACCESS FULL      =     | TMP_HDM_CLEANUP_INSTANCE |  4125 |   185K|&n= bsp;   11   (0)| 00:00:01 |       | = ;      |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEVICE&nb= sp;                  |  &= nbsp;  1 |    11 |     3   (0)| 00:= 00:01 |       |       |
|*  6 |      INDEX RANGE SCAN       = ;   | SYS_C0016783             |&nb= sp; 1822K|       |     3   (0)= | 00:00:01 |       |       |
|   7 |    PARTITION HASH ITERATOR     |=                      = ;     |     1 |    43 |    &nb= sp;3   (0)| 00:00:01 |   KEY |   KEY |
|*  8 |     INDEX RANGE SCAN       =    | UQ_PARAM_NEW             = ;|     1 |    43 |     3  &nbs= p;(0)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------= ----------------------------------------------------------




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