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 D08CD100315769 for ; Thu, 11 Nov 2021 17:49:55 +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 CB254498C5; Thu, 11 Nov 2021 16:49:54 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id B5DD540085; Thu, 11 Nov 2021 16:49:54 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1636649394; bh=9bMMpanv3bIEAA3u/2tnokhmWNQ+/OUKBO1Scl+PwWU=; h=From:Sender:Sender:From; b=B51EfVYojJwNhXb1yQdi7y21xP9nMMVN9ppRxmy6mY+FyYILzvnaj3JY0UZmshj36 phP+QYJOK71aaMwi6OGnAOFVy5b4TWFMvtG2+0D4hAYX+9go72u77CT1RnsXLKOu2h kqYTV9jr2oRmy65ZuKTf7SqIKktwKfnnZi/l9DiU= 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 X_nArvpAxPW5; Thu, 11 Nov 2021 16:49:54 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 81A424006A; Thu, 11 Nov 2021 16:49:51 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1636649392; bh=9bMMpanv3bIEAA3u/2tnokhmWNQ+/OUKBO1Scl+PwWU=; h=From:Sender:Sender:From; b=M38XavZxEDcYFyLtfrzdxqKqSXePZLJx/izDPr0iijjz3Y1L7xj5G8ihe3r035wLT qI/5zv7a5cnxtOZuVJA1zcYsjgwtSQlzkaovfoZ+LJdK/uDVd9rJkKLp+ofH+B10tm 7ee3T1ms0dAZYanlJG3fh1yLS0JfKbVyedl3/Np4= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 11 Nov 2021 16:49:50 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id D5EB53FFFC for ; Thu, 11 Nov 2021 16:49:49 +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=WYdxHkqy; 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 NBUwgIWWN2wo for ; Thu, 11 Nov 2021 16:49:49 +0000 (UTC) Received: from mail-yb1-f179.google.com (mail-yb1-f179.google.com [209.85.219.179]) (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 A89223FFF0 for ; Thu, 11 Nov 2021 16:49:49 +0000 (UTC) Received: by mail-yb1-f179.google.com with SMTP id q74so16541182ybq.11 for ; Thu, 11 Nov 2021 08:49:49 -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=Bb9v50rTixLAJqWyfDJfQhMdi8QZgyeZNxLhsZRWb64=; b=Xa0tw5RIBsKU/hDYnj5wi4Cr5ouryVGifwpxcmlwbuU+peotl+TBsBAfxxG1kc2+Hn JxevFrwm5wLr3YM6v4N6GFk9adLvQ0pK5jMseFGB3X/vTbejCtDBIdDST8WnEpgloh9J 2u5m/w+2f9Rddy5/rmPZKs1KcWJLVWRfLurvMnCbcHEwdmeORj2xWOHzOpAyNEx0/iMn H6KSUga3mJHWHDCwQ1jmhZeR7T0jQhYB6xN4cVI6CHe2qMmG2gxnnfoFTJ1qBM77a/N/ WqDWmrkmJLCxwgutdZ9tegHcYfIlYeRoumQdbI/75Do6fVdskrP8wj+zEsuntEZVSXZZ reSw== X-Gm-Message-State: AOAM5323sBGuw4WZ3tNsCVk45LIIWNomf3NVXYggz8ddG8y2uUEH0olP tVsr+XAGiAdhUtlHb26q6Q0ptVwGdglDGxFym56iCOPP X-Google-Smtp-Source: ABdhPJxlkZO4bLgzdnB7qtKr4n979lEL70stchkAUsF8ZedQGapZBVfqYfKWCJn6sdQJEDbqDBMiefHOdjKcSUV+grc= X-Received: by 2002:a25:2a89:: with SMTP id q131mr9278096ybq.436.1636649389076; Thu, 11 Nov 2021 08:49:49 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Lok P Date: Thu, 11 Nov 2021 22:19:37 +0530 Message-ID: Subject: Re: Odd behavior with queries having DB link in 19C To: Pap Cc: Oracle L Content-Type: multipart/alternative; boundary="00000000000075367405d0862134" X-archive-position: 81353 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: loknath.73@gmail.com Precedence: normal Reply-To: loknath.73@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 --00000000000075367405d0862134 Content-Type: text/plain; charset="UTF-8" Thank You. Actually that was my first thought only in this situation to see the behaviour by forcing the path-1 outline, but because of the presence of the DB link I am unable to run it because that db link credential is only accessible to the specific application user/schema from which it gets called. And yes the bad part is , this type of estimation and plan deviation is observed in many queries. must be because this table PE is a very commonly used one. On Thu, Nov 11, 2021 at 10:13 PM Pap wrote: > 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' ) >> >> --00000000000075367405d0862134 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank You. Actually that was my first thought only in this= situation to see the behaviour by forcing the path-1 outline, but because = of the presence=C2=A0of the DB link I am unable to run it because that db l= ink credential is only accessible to the specific application user/schema f= rom which it=C2=A0gets called. And yes the bad part is , this type of estim= ation and plan deviation=C2=A0is observed in many queries. must be because = this table PE is a very commonly used one.

On Thu, Nov 11, 2021 at 10:13 PM = Pap <oracle.developer35@= gmail.com> wrote:
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 p= aths 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 datab= ase with 19C DB version but with OFE as 11.2.0.4. But strangely we are stil= l seeing some of the queries(mainly having DB link) to this database from a= nother 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 datab= ase 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 c= ases 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), th= is query was running with default plan path- 1 as below but post this targe= t database changed to 19c with OFE-11.2,=C2=A0its 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 ".=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 profi= le in source database, we saw in the remote queries its submitting in the t= arget 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 (eve= n we have suitable index for them i.e. column PE in table PE is a primary k= ey and a unique index on column CNA of table CNA exists in the target datab= ase). So here basically table PE having a column name as PE and table CNA a= lso having column name as CNA and both are unique.

I can see from th= e outline of the good plan, PATH-1 that its adding full hints to table PE a= nd 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,=C2=A0 so i am assumi= ng it must not be submitting those remote queries in target database with t= hose additional hints, so why it's doing that now?
=C2=A0
Re= mote queries even after adding profile to the query in source database spaw= ning 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") FUL= L ("P") */ "PE","CNA" FROM "PE" &qu= ot;P" WHERE "CNA" IS NOT NULL AND :1=3D"PE"
SEL= ECT /*+ ALL_ROWS USE_NL ("C") FULL ("C") */ "CNA&q= uot;,"L1","L2","L3","L4","L5&q= uot; FROM "CNA" "C" WHERE :1=3D"CNA"


Query:-

SELECT .......
=C2=A0 FROM TBRBP TBRBP, PE@dblnk1 PE, CNA@dblnk1 CN= A
=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--------------------------------------------------= --------------------------------------------------------------------
| I= d =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| Bytes | Cost (%CPU)| Time =C2=A0 =C2=A0 | Inst =C2=A0 |I= N-OUT|
-----------------------------------------------------------------= -----------------------------------------------------
| =C2=A0 0 | SELEC= T 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=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 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 NEST= ED 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=A09307 | =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| TBRBP =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 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 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
Outline Data
-------------
=C2=A0 =C2=A0/*+
=C2=A0= =C2=A0 =C2=A0 BEGIN_OUTLINE_DATA
=C2=A0 =C2=A0 =C2=A0 IGNORE_OPTIM_EMBE= DDED_HINTS
=C2=A0 =C2=A0 =C2=A0 OPTIMIZER_FEATURES_ENABLE('11.2.0.4&= #39;)
=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 INDEX_RS_ASC(@"SEL$1" "TBRBP"= @"SEL$1" ("TBRBP"."PDAY"))
=C2=A0 =C2=A0 = =C2=A0 FULL(@"SEL$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"@"SE= L$1" "PE"@"SEL$1" "CNA"@"SEL$1"= ;)
=C2=A0 =C2=A0 =C2=A0 USE_NL(@"SEL$1" "PE"@"S= EL$1")
=C2=A0 =C2=A0 =C2=A0 USE_NL(@"SEL$1" "CNA&quo= t;@"SEL$1")
=C2=A0 =C2=A0 =C2=A0 END_OUTLINE_DATA
=C2=A0 */=
=C2=A0
Peeked Binds (identified by position):
-------------------= -------------------
=C2=A0 =C2=A0 1 - :B1 (DATE): 09/13/2019 00:00:00=C2=A0
Remote SQL Information (identified by operation id):
--------= --------------------------------------------
=C2=A0 =C2=A05 - SELECT &qu= ot;PE","CCNA" FROM "PE" "PE" WHERE :1=3D= "PE" (accessing 'dblnk1.CMPNY1.COM' )
=C2=A0
=C2=A0 =C2=A06 - SELE= CT "CNA","L1","L2","L3","L4&qu= ot;,"L5" FROM "CNA" "CNA" WHERE =C2=A0:1=3D&q= uot;CNA" (accessing 'dblnk1.CMPNY1.COM' )


<= b>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| Bytes | Cost (%CPU)| Time =C2=A0 =C2=A0 | Inst =C2=A0 |IN-OUT= |
----------------------------------------------------------------------= ------------------------------------------------
| =C2=A0 0 | SELECT STA= TEMENT =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =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| TB= RBP =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 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 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->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 OP= TIMIZER_FEATURES_ENABLE('11.2.0.4')
=C2=A0 =C2=A0 =C2=A0 DB_VERS= ION('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" "PE"@"SEL$1")
=C2=A0 =C2=A0 =C2=A0 INDE= X_RS_ASC(@"SEL$1" "TBRBP"@"SEL$1" ("TBRB= P"."PDAY"))
=C2=A0 =C2=A0 =C2=A0 FULL(@"SEL$1" = "CNA"@"SEL$1")
=C2=A0 =C2=A0 =C2=A0 LEADING(@"S= EL$1" "PE"@"SEL$1" "TBRBP"@"SEL$1&q= uot; "CNA"@"SEL$1")
=C2=A0 =C2=A0 =C2=A0 USE_HASH(@&= quot;SEL$1" "TBRBP"@"SEL$1")
=C2=A0 =C2=A0 =C2= =A0 USE_NL(@"SEL$1" "CNA"@"SEL$1")
=C2=A0 = =C2=A0 =C2=A0 SWAP_JOIN_INPUTS(@"SEL$1" "TBRBP"@"S= EL$1")
=C2=A0 =C2=A0 =C2=A0 END_OUTLINE_DATA
=C2=A0 */
=C2=A0=
Peeked Binds (identified by position):
-----------------------------= ---------
=C2=A0 =C2=A0 1 - :B1 (DATE): 10/24/2021 00:00:00
=C2=A0Remote SQL Information (identified by operation id):
------------------= ----------------------------------
=C2=A0 =C2=A05 - SELECT "PE"= ;,"CCNA" FROM "PE" "PE" WHERE "CCNA"= ; IS NOT NULL (accessing 'dblnk1.CMPNY1.COM' )
=C2=A0
=C2=A0 =C2=A06 - SELECT "CNA","L1",&q= uot;L2","L3","L4","L5" FROM "CNA&qu= ot; "CNA" WHERE =C2=A0:1=3D"CNA" (accessing 'dblnk1.CMPNY1.COM'= )

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