Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 66A5A1003033EA for ; Thu, 11 Nov 2021 17:43:42 +0100 (CET) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id A9D8849997; Thu, 11 Nov 2021 16:43:40 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 9DFAD3FDCF; Thu, 11 Nov 2021 16:43:40 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1636649020; bh=YKENlvNnpFC3oTuESkH47kzdUdxLa9TsZKapaqN5KoU=; h=From:Sender:Sender:From; b=xmgC6ZRy9VMGWMMALXgKGEhujRdevCgzhdgBfrPRTRrhYSTA9800uHwCh+6p/Stkn j6fsnGmFbjX+sSNpcD4pE94/EpdVJ97qyM07SpOSmDgSi/ocShd3+waZWCrsZmxoiO vHLr1B9R/IHG2sTarcVd55nWFSf5TxST0H1ZMiqI= X-Virus-Scanned: by FreeLists 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 EMGNlK84Ho3R; Thu, 11 Nov 2021 16:43:40 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 9253A3FDBF; Thu, 11 Nov 2021 16:43:37 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1636649018; bh=YKENlvNnpFC3oTuESkH47kzdUdxLa9TsZKapaqN5KoU=; h=From:Sender:Sender:From; b=PyDF0ROrDiLFykJ3Xf/yXKYh1K2YbdU5b79KbG9CtORzAKAXduryICiPpEjmVriEg AfH+ySumh+igY2aMQzw0ZId2i7hhQwq7cCyxUR4C0WFQ5UCIlZpFAlQ74rMbHKUvMB k2dSlgWj7EmyNiWqw8XDgVR6vM6Q00x4EKYqr6os= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 11 Nov 2021 16:43:36 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id E57063FD9D for ; Thu, 11 Nov 2021 16:43:35 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20210112 header.b=H0ws2AyU; dkim-atps=neutral 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 EPT8qCCCIJ_b for ; Thu, 11 Nov 2021 16:43:35 +0000 (UTC) Received: from mail-ua1-f41.google.com (mail-ua1-f41.google.com [209.85.222.41]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id D414D3FCEF for ; Thu, 11 Nov 2021 16:43:35 +0000 (UTC) Received: by mail-ua1-f41.google.com with SMTP id e10so13175634uab.3 for ; Thu, 11 Nov 2021 08:43:35 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=qv2GCi0RLGKW0FollDujCGZbONRZ2oXGRQI+ra429DM=; b=N7DSVW3PGPp2aulK2MYuXgwFSbWOmeO7B8E7qBeO+X4SyFBhnY7ZsIDezPuWii84Rq AwiCDPST/xXP8uadDfmXkmBRHucpnO2yKvdbQiyW11fhMt6dOK6cyfw0dalJZwNfmd/H gz5YpmMSX66gcCi6DDXWYUGJEi7cAT+JvSsBl4odpepRBagwj4WCHXQNU7xr6ubpsa7x BT6Fip9W2KLzBzQZBUs5dN9ZlCn1NnbXY2O4GruTbdemo+e2+ObbRCu8T5UfcxNPdEc3 wOvHbkIQlmqueRlCGkRLtdn1yalyI8ldGTZy2ueBGpyPc6YfLpsjehC4qciftgwnIxlA TBgQ== X-Gm-Message-State: AOAM533qfd8XafaKIEhcnmQIAgp+sXHUCt/gn1Te/ItNyp/gxnTvyvNP I5odb8lzi6oyLuscMYCr6GIA/DABaLYI+7UnBGE= X-Google-Smtp-Source: ABdhPJwa+Jz2P6llK9sIzgsPoIKm5xeEoSEOF+A0qLGpFUIWDzv0kecnBhIExtlBbrHO47yI2S62h/0laLQLc8UcSPk= X-Received: by 2002:a67:a20d:: with SMTP id l13mr37419vse.59.1636649014985; Thu, 11 Nov 2021 08:43:34 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Pap Date: Thu, 11 Nov 2021 22:13:24 +0530 Message-ID: Subject: Re: Odd behavior with queries having DB link in 19C To: Lok P Cc: Oracle L Content-Type: multipart/alternative; boundary="00000000000029073205d0860b77" X-archive-position: 81352 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: oracle.developer35@gmail.com Precedence: normal Reply-To: oracle.developer35@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 --00000000000029073205d0860b77 Content-Type: text/plain; charset="UTF-8" Can you try forcing the outline of the cheaper execution path(which is spawning the indexed remote query without hints on the target database) to the query and see what the cost is now and if that gives you some clue? On Thu, Nov 11, 2021 at 9:40 PM Lok P wrote: > Hello, After migrating from 11.2.0.4 to 19C(19.9.0.0.0), we saw many > queries were opting for suboptimal paths and thus we decided to set the > optimizer_feature_enable parameter back to 11.2.0.4 in production to avoid > these issues. So now we have this database with 19C DB version but with OFE > as 11.2.0.4. But strangely we are still seeing some of the queries(mainly > having DB link) to this database from another database are performing > poorly because of a bad execution path. The Source database is on version > 11.2.0.4 + OFE 11.2.0.4 and this/target database is on version 19C with > OFE-11.2.0.4. So I wanted to understand if this combination can cause some > bad estimation or change in costing, mainly in cases of involvement of DB > link? > > Below is a sample query ,which runs in source database(which is having > both DB version and OFE as 11.2.0.4), this query was running with default > plan path- 1 as below but post this target database changed to 19c with > OFE-11.2, its opting for path -2. I do see there is a big deviation in > estimation of rows for table PE because of the predicate its now evaluating > i.e. "PE.CCNA is not null ". But wondering why it's not going for the > cheaper indexed path. > > When we tried to set the good path through sql profile in source database, > we saw in the remote queries its submitting in the target database(i.e. > with DB version 19C and OFE-11.2.0.4) having additional hints added to them > as below and are causing them to go for full scan (even we have suitable > index for them i.e. column PE in table PE is a primary key and a unique > index on column CNA of table CNA exists in the target database). So here > basically table PE having a column name as PE and table CNA also having > column name as CNA and both are unique. > > I can see from the outline of the good plan, PATH-1 that its adding full > hints to table PE and CNA, but it was somehow working fine while our target > database was on 11.2.0.4(wrt both DB version and OFE) with same outline, > so i am assuming it must not be submitting those remote queries in target > database with those additional hints, so why it's doing that now? > > Remote queries even after adding profile to the query in source database > spawning with below hints which were mostly not happening this way when > target database was on 11.2.0.4: > SELECT /*+ ALL_ROWS USE_NL ("P") FULL ("P") */ "PE","CNA" FROM "PE" "P" > WHERE "CNA" IS NOT NULL AND :1="PE" > SELECT /*+ ALL_ROWS USE_NL ("C") FULL ("C") */ > "CNA","L1","L2","L3","L4","L5" FROM "CNA" "C" WHERE :1="CNA" > > > *Query:- * > > SELECT ....... > FROM TBRBP TBRBP, PE@dblnk1 PE, CNA@dblnk1 CNA > WHERE TBRBP.PDAY = :B1 > AND TBRBP.PNUM = PE.PE > AND PE.CCNA = CNA.CNA > > *PATH:-1 * > > ---------------------------------------------------------------------------------------------------------------------- > | Id | Operation | Name | Rows | > Bytes | Cost (%CPU)| Time | Inst |IN-OUT| > > ---------------------------------------------------------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | | > | 1390 (100)| | | | > | 1 | NESTED LOOPS | | 227 | > 22019 | 1390 (1)| 00:00:17 | | | > | 2 | NESTED LOOPS | | 227 | > 9307 | 709 (1)| 00:00:09 | | | > | 3 | TABLE ACCESS BY INDEX ROWID| TBRBP | 227 | > 3405 | 27 (0)| 00:00:01 | | | > | 4 | INDEX RANGE SCAN | TBRBP_IX1 | 227 | > | 3 (0)| 00:00:01 | | | > | 5 | REMOTE | PE | 1 | > 26 | 3 (0)| 00:00:01 | dblnk1 | R->S | > | 6 | REMOTE | CNA | 1 | > 56 | 3 (0)| 00:00:01 | dblnk1 | R->S | > > ---------------------------------------------------------------------------------------------------------------------- > > Outline Data > ------------- > /*+ > BEGIN_OUTLINE_DATA > IGNORE_OPTIM_EMBEDDED_HINTS > OPTIMIZER_FEATURES_ENABLE('11.2.0.4') > DB_VERSION('11.2.0.4') > ALL_ROWS > OUTLINE_LEAF(@"SEL$1") > INDEX_RS_ASC(@"SEL$1" "TBRBP"@"SEL$1" ("TBRBP"."PDAY")) > FULL(@"SEL$1" "PE"@"SEL$1") > FULL(@"SEL$1" "CNA"@"SEL$1") > LEADING(@"SEL$1" "TBRBP"@"SEL$1" "PE"@"SEL$1" "CNA"@"SEL$1") > USE_NL(@"SEL$1" "PE"@"SEL$1") > USE_NL(@"SEL$1" "CNA"@"SEL$1") > END_OUTLINE_DATA > */ > > Peeked Binds (identified by position): > -------------------------------------- > 1 - :B1 (DATE): 09/13/2019 00:00:00 > > Remote SQL Information (identified by operation id): > ---------------------------------------------------- > 5 - SELECT "PE","CCNA" FROM "PE" "PE" WHERE :1="PE" (accessing ' > dblnk1.CMPNY1.COM' ) > > 6 - SELECT "CNA","L1","L2","L3","L4","L5" FROM "CNA" "CNA" WHERE > :1="CNA" (accessing 'dblnk1.CMPNY1.COM' ) > > > *PATH : 2* > > ---------------------------------------------------------------------------------------------------------------------- > | Id | Operation | Name | Rows | > Bytes | Cost (%CPU)| Time | Inst |IN-OUT| > > ---------------------------------------------------------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | | > | 70439 (100)| | | | > | 1 | NESTED LOOPS | | 125 | > 12250 | 70439 (2)| 00:14:06 | | | > | 2 | HASH JOIN | | 125 | > 5125 | 70063 (2)| 00:14:01 | | | > | 3 | TABLE ACCESS BY INDEX ROWID| TBRBP | 125 | > 1875 | 17 (0)| 00:00:01 | | | > | 4 | INDEX RANGE SCAN | TBRBP_IX1 | 125 | > | 3 (0)| 00:00:01 | | | > | 5 | REMOTE | PE | 40M| > 1009M| 69927 (2)| 00:14:00 | dblnk1 | R->S | > | 6 | REMOTE | CNA | 1 | > 57 | 3 (0)| 00:00:01 | dblnk1 | R->S | > > ---------------------------------------------------------------------------------------------------------------------- > > Outline Data > ------------- > /*+ > BEGIN_OUTLINE_DATA > IGNORE_OPTIM_EMBEDDED_HINTS > OPTIMIZER_FEATURES_ENABLE('11.2.0.4') > DB_VERSION('11.2.0.4') > ALL_ROWS > OUTLINE_LEAF(@"SEL$1") > FULL(@"SEL$1" "PE"@"SEL$1") > INDEX_RS_ASC(@"SEL$1" "TBRBP"@"SEL$1" ("TBRBP"."PDAY")) > FULL(@"SEL$1" "CNA"@"SEL$1") > LEADING(@"SEL$1" "PE"@"SEL$1" "TBRBP"@"SEL$1" "CNA"@"SEL$1") > USE_HASH(@"SEL$1" "TBRBP"@"SEL$1") > USE_NL(@"SEL$1" "CNA"@"SEL$1") > SWAP_JOIN_INPUTS(@"SEL$1" "TBRBP"@"SEL$1") > END_OUTLINE_DATA > */ > > Peeked Binds (identified by position): > -------------------------------------- > 1 - :B1 (DATE): 10/24/2021 00:00:00 > > Remote SQL Information (identified by operation id): > ---------------------------------------------------- > 5 - SELECT "PE","CCNA" FROM "PE" "PE" WHERE "CCNA" IS NOT NULL > (accessing 'dblnk1.CMPNY1.COM' ) > > 6 - SELECT "CNA","L1","L2","L3","L4","L5" FROM "CNA" "CNA" WHERE > :1="CNA" (accessing 'dblnk1.CMPNY1.COM' ) > > --00000000000029073205d0860b77 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Can you try forcing the=20 outline of the cheaper execution path(which is spawning the indexed remote = query without hints on the target database)=C2=A0to the query and see what = the cost is now and=C2=A0if that gives=C2=A0you some clue?

On Thu, Nov 11, 2= 021 at 9:40 PM Lok P <loknath.73= @gmail.com> wrote:
Hello, After migrating from 11.2.0.4 to 19C(19.= 9.0.0.0), we saw many queries were opting for suboptimal paths and thus we = decided to set the optimizer_feature_enable parameter back to 11.2.0.4 in p= roduction to avoid these issues. So now we have this database with 19C DB v= ersion but with OFE as 11.2.0.4. But strangely we are still seeing some of = the queries(mainly having DB link) to this database from another database a= re performing poorly because of a bad execution path. The Source database i= s on version 11.2.0.4 + OFE 11.2.0.4 and this/target database is on version= 19C with OFE-11.2.0.4. So I wanted to understand if this combination can c= ause some bad estimation or change in costing, mainly in cases of involveme= nt of DB link?

Below is a sample query ,which runs in source databas= e(which is having both DB version and OFE as 11.2.0.4), this query was runn= ing with default plan path- 1 as below but post this target database change= d to 19c with OFE-11.2,=C2=A0its opting for path -2. I do see there is a bi= g deviation in estimation of rows for table PE because of the predicate its= now evaluating i.e. "PE.CCNA is not null ".=C2=A0 But wondering = why it's not going for the cheaper indexed path.=C2=A0

When we tried to set the good path through sql profile in source databas= e, we saw in the remote queries its submitting in the target database(i.e. = with DB version 19C and OFE-11.2.0.4) having additional hints added to them= as below and are causing them to go for full scan (even we have suitable i= ndex for them i.e. column PE in table PE is a primary key and a unique inde= x on column CNA of table CNA exists in the target database). So here basica= lly table PE having a column name as PE and table CNA also having column na= me as CNA and both are unique.

I can see from the outline of the goo= d plan, PATH-1 that its adding full hints to table PE and CNA, but it was s= omehow working fine while our target database was on 11.2.0.4(wrt both DB v= ersion and OFE) with same outline,=C2=A0 so i am assuming it must not be su= bmitting those remote queries in target database with those additional hint= s, so why it's doing that now?
=C2=A0
Remote queries even af= ter adding profile to the query in source database spawning with below hint= s which were mostly not happening this way when target database was on 11.2.0.4:
SELECT /*+ ALL_ROWS USE_NL ("P") FULL ("P") */= "PE","CNA" FROM "PE" "P" WHERE &qu= ot;CNA" IS NOT NULL AND :1=3D"PE"
SELECT /*+ ALL_ROWS USE= _NL ("C") FULL ("C") */ "CNA","L1",= "L2","L3","L4","L5" FROM "CNA&= quot; "C" WHERE :1=3D"CNA"


Query:-

SELECT .......<= br>=C2=A0 FROM TBRBP TBRBP, PE@dblnk1 PE, CNA@dblnk1 CNA
=C2=A0WHERE =C2= =A0 =C2=A0 TBRBP.PDAY =3D :B1
=C2=A0 =C2=A0 =C2=A0 =C2=A0AND TBRBP.PNUM = =3D PE.PE
=C2=A0 =C2=A0 = =C2=A0 =C2=A0AND PE.CCNA =3D CNA.CNA

PATH:-1
= =C2=A0---------------------------------------------------------------------= -------------------------------------------------
| Id =C2=A0| Operation= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Na= me =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Rows =C2=A0| B= ytes | Cost (%CPU)| Time =C2=A0 =C2=A0 | Inst =C2=A0 |IN-OUT|
----------= ---------------------------------------------------------------------------= ---------------------------------
| =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=A01390 (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 1 | =C2=A0NE= STED 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 227 | 22019 | =C2=A01390 =C2=A0 (1)| 00:00:17 | =C2=A0 =C2=A0 =C2=A0 = =C2=A0| =C2=A0 =C2=A0 =C2=A0|
| =C2=A0 2 | =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 227 | =C2=A093= 07 | =C2=A0 709 =C2=A0 (1)| 00:00:09 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 = =C2=A0 =C2=A0|
| =C2=A0 3 | =C2=A0 =C2=A0TABLE ACCESS BY INDEX ROWID| TB= RBP =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 227 | = =C2=A03405 | =C2=A0 =C2=A027 =C2=A0 (0)| 00:00:01 | =C2=A0 =C2=A0 =C2=A0 = =C2=A0| =C2=A0 =C2=A0 =C2=A0|
| =C2=A0 4 | =C2=A0 =C2=A0 INDEX RANGE SCA= N =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| TBRBP_IX1 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0| =C2=A0 227 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 3 =C2=A0 = (0)| 00:00:01 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0|
| =C2= =A0 5 | =C2=A0 =C2=A0REMOTE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | PE =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=A026 | =C2=A0 =C2=A0 3 =C2= =A0 (0)| 00:00:01 | dblnk1 | R->S |
| =C2=A0 6 | =C2=A0 REMOTE =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| CNA= =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=A056 | =C2=A0 =C2=A0 3 =C2=A0 (0)| 00:00:01 | dblnk1 | R= ->S |
---------------------------------------------------------------= -------------------------------------------------------
=C2=A0
Outlin= e Data
-------------
=C2=A0 =C2=A0/*+
=C2=A0 =C2=A0 =C2=A0 BEGIN_O= UTLINE_DATA
=C2=A0 =C2=A0 =C2=A0 IGNORE_OPTIM_EMBEDDED_HINTS
=C2=A0 = =C2=A0 =C2=A0 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
=C2=A0 =C2= =A0 =C2=A0 DB_VERSION('11.2.0.4')
=C2=A0 =C2=A0 =C2=A0 ALL_ROWS<= br>=C2=A0 =C2=A0 =C2=A0 OUTLINE_LEAF(@"SEL$1")
=C2=A0 =C2=A0 = =C2=A0 INDEX_RS_ASC(@"SEL$1" "TBRBP"@"SEL$1" = ("TBRBP"."PDAY"))
=C2=A0 =C2=A0 =C2=A0 FULL(@"S= EL$1" "PE"@"SEL$1")
=C2=A0 =C2=A0 =C2=A0 FULL(@= "SEL$1" "CNA"@"SEL$1")
=C2=A0 =C2=A0 =C2= =A0 LEADING(@"SEL$1" "TBRBP"@"SEL$1" "PE= "@"SEL$1" "CNA"@"SEL$1")
=C2=A0 =C2= =A0 =C2=A0 USE_NL(@"SEL$1" "PE"@"SEL$1")
= =C2=A0 =C2=A0 =C2=A0 USE_NL(@"SEL$1" "CNA"@"SEL$1&= quot;)
=C2=A0 =C2=A0 =C2=A0 END_OUTLINE_DATA
=C2=A0 */
=C2=A0
P= eeked Binds (identified by position):
----------------------------------= ----
=C2=A0 =C2=A0 1 - :B1 (DATE): 09/13/2019 00:00:00
=C2=A0
Remo= te SQL Information (identified by operation id):
-----------------------= -----------------------------
=C2=A0 =C2=A05 - SELECT "PE",&qu= ot;CCNA" FROM "PE" "PE" WHERE :1=3D"PE" = (accessing 'dbln= k1.CMPNY1.COM' )
=C2=A0
=C2=A0 =C2=A06 - SELECT "CNA&quo= t;,"L1","L2","L3","L4","L5&quo= t; FROM "CNA" "CNA" WHERE =C2=A0:1=3D"CNA" (a= ccessing 'dblnk1= .CMPNY1.COM' )


PATH : 2
= ---------------------------------------------------------------------------= -------------------------------------------
| Id =C2=A0| Operation =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 | Rows =C2=A0| Byte= s | Cost (%CPU)| Time =C2=A0 =C2=A0 | Inst =C2=A0 |IN-OUT|
-------------= ---------------------------------------------------------------------------= ------------------------------
| =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 | 70439 (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 1 | =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 125 = | 12250 | 70439 =C2=A0 (2)| 00:14:06 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 = =C2=A0 =C2=A0|
| =C2=A0 2 | =C2=A0 HASH JOIN =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 125 | =C2=A05125 | 70063 = =C2=A0 (2)| 00:14:01 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0|| =C2=A0 3 | =C2=A0 =C2=A0TABLE ACCESS BY INDEX ROWID| TBRBP =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 125 | =C2=A01875 | = =C2=A0 =C2=A017 =C2=A0 (0)| 00:00:01 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 = =C2=A0 =C2=A0|
| =C2=A0 4 | =C2=A0 =C2=A0 INDEX RANGE SCAN =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0| TBRBP_IX1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|= =C2=A0 125 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 3 =C2=A0 (0)| 00:00:01 |= =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0|
| =C2=A0 5 | =C2=A0 = =C2=A0REMOTE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 | PE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= | =C2=A0 =C2=A040M| =C2=A01009M| 69927 =C2=A0 (2)| 00:14:00 | dblnk1 | R-&= gt;S |
| =C2=A0 6 | =C2=A0 REMOTE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| CNA =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=A0= =C2=A0 3 =C2=A0 (0)| 00:00:01 | dblnk1 | R->S |
--------------------= ---------------------------------------------------------------------------= -----------------------
=C2=A0
Outline Data
-------------
=C2= =A0 =C2=A0/*+
=C2=A0 =C2=A0 =C2=A0 BEGIN_OUTLINE_DATA
=C2=A0 =C2=A0 = =C2=A0 IGNORE_OPTIM_EMBEDDED_HINTS
=C2=A0 =C2=A0 =C2=A0 OPTIMIZER_FEATUR= ES_ENABLE('11.2.0.4')
=C2=A0 =C2=A0 =C2=A0 DB_VERSION('11.2.= 0.4')
=C2=A0 =C2=A0 =C2=A0 ALL_ROWS
=C2=A0 =C2=A0 =C2=A0 OUTLINE_= LEAF(@"SEL$1")
=C2=A0 =C2=A0 =C2=A0 FULL(@"SEL$1" &q= uot;PE"@"SEL$1")
=C2=A0 =C2=A0 =C2=A0 INDEX_RS_ASC(@"= ;SEL$1" "TBRBP"@"SEL$1" ("TBRBP"."P= DAY"))
=C2=A0 =C2=A0 =C2=A0 FULL(@"SEL$1" "CNA"= @"SEL$1")
=C2=A0 =C2=A0 =C2=A0 LEADING(@"SEL$1" &quo= t;PE"@"SEL$1" "TBRBP"@"SEL$1" "CNA&= quot;@"SEL$1")
=C2=A0 =C2=A0 =C2=A0 USE_HASH(@"SEL$1"= ; "TBRBP"@"SEL$1")
=C2=A0 =C2=A0 =C2=A0 USE_NL(@&quo= t;SEL$1" "CNA"@"SEL$1")
=C2=A0 =C2=A0 =C2=A0 SW= AP_JOIN_INPUTS(@"SEL$1" "TBRBP"@"SEL$1")
= =C2=A0 =C2=A0 =C2=A0 END_OUTLINE_DATA
=C2=A0 */
=C2=A0
Peeked Bind= s (identified by position):
--------------------------------------
= =C2=A0 =C2=A0 1 - :B1 (DATE): 10/24/2021 00:00:00
=C2=A0
Remote SQL I= nformation (identified by operation id):
-------------------------------= ---------------------
=C2=A0 =C2=A05 - SELECT "PE","CCNA&= quot; FROM "PE" "PE" WHERE "CCNA" IS NOT NULL= (accessing 'dbl= nk1.CMPNY1.COM' )
=C2=A0
=C2=A0 =C2=A06 - SELECT "CNA","L1","L2",&q= uot;L3","L4","L5" FROM "CNA" "CNA&q= uot; WHERE =C2=A0:1=3D"CNA" (accessing 'dblnk1.CMPNY1.COM' )

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