Re: USE_CONCAT plan operation after April 2021 Bundle Patch (12.1.0.2) Questions
From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Fri, 27 Aug 2021 11:58:30 -0400
Message-ID: <CAP79kiRe=3UtoiTt0LK7AjCc-v=oa1PrzMJ5iRbows5TytLjgA_at_mail.gmail.com>
"RAW_COB_CLAIMS"_at_"SEL$18" "CLI"_at_"SEL$14" "I"@"SEL$12" "VAD"@"SEL$15"
"BL"_at_"SEL$13")
"REPLENISHMENT"."REPLENISHED_ON"))
*/
Date: Fri, 27 Aug 2021 11:58:30 -0400
Message-ID: <CAP79kiRe=3UtoiTt0LK7AjCc-v=oa1PrzMJ5iRbows5TytLjgA_at_mail.gmail.com>
Ok, so resetting (in my session) all the optimizer adaptive stuff generates
the best performance so far at 3 minutes for the query with the following
outline and an adaptive plan.
(Some additional details follow)
Elapsed Time: 00:03:42.45
/*+
BEGIN_OUTLINE_DATA
USE_NL(_at_"SEL$6F4644B4" "CL"_at_"SEL$11")
FULL(_at_"SEL$63682743" "SRCL"_at_"SEL$10")
USE_HASH(_at_"SEL$63682743" "SRCL"_at_"SEL$10")
FULL(_at_"SEL$2F5D08D6" "D"_at_"SEL$3")
USE_HASH(_at_"SEL$2F5D08D6" "D"_at_"SEL$3")
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_fix_control' '7452863:0')
ALL_ROWS
OUTLINE_LEAF(_at_"SEL$19")
OUTLINE_LEAF(_at_"SEL$551CC24A")
OUTLINE_LEAF(_at_"SEL$16")
OUTLINE_LEAF(_at_"SEL$6F4644B4")
UNNEST(_at_"SEL$18")
OUTLINE_LEAF(_at_"SEL$63682743")
MERGE(_at_"SEL$9A1971F9")
OUTLINE_LEAF(_at_"SEL$085F0B7A")
MERGE(_at_"SEL$15BE40C3")
OUTLINE_LEAF(_at_"SEL$551CC249")
MATERIALIZE(_at_"SEL$19")
OUTLINE_LEAF(_at_"SEL$632AF070")
MERGE(_at_"SEL$D24588F2")
OUTLINE_LEAF(_at_"SEL$3661D78D")
MERGE(_at_"SEL$21")
MERGE(_at_"SEL$A87E79E7")
OUTLINE_LEAF(_at_"SEL$A0D20652")
MERGE(_at_"SEL$8")
OUTLINE_LEAF(_at_"SEL$E6D49DC0")
MERGE(_at_"SEL$AF9A6E20")
OUTLINE_LEAF(_at_"SET$1")
OUTLINE_LEAF(_at_"SEL$28")
OUTLINE_LEAF(_at_"SEL$3A62EF88")
PUSH_PRED(_at_"SEL$2F5D08D6" "from$_subquery$_003"_at_"SEL$34" 4)
OUTLINE_LEAF(_at_"SEL$2F5D08D6")
MERGE(_at_"SEL$34")
OUTLINE_LEAF(_at_"SEL$A0D20652_1")
(57 54) (58 55) (59 56) (60 57) (61 58) (62 59) (63 60) (64
61) (65 62) (66 63) (67 64) (68 65) (69 66) (70 67) (71 68) (72 69) (36 70)
(37 71) (38 72)))
OUTLINE_LEAF(_at_"SEL$A0D20652_2")
OUTLINE(_at_"SEL$19")
OUTLINE(_at_"SEL$8FC7C762")
MERGE(_at_"SEL$33B58238")
OUTLINE(_at_"SEL$18")
OUTLINE(_at_"SEL$29")
OUTLINE(_at_"SEL$9A1971F9")
MERGE(_at_"SEL$65A156B2")
OUTLINE(_at_"SEL$E7AF8512")
ANSI_REARCH(_at_"SEL$24")
OUTLINE(_at_"SEL$15BE40C3")
ANSI_REARCH(_at_"SEL$23")
OUTLINE(_at_"SEL$145B7DA0")
OUTLINE(_at_"SEL$D24588F2")
MERGE(_at_"SEL$6A52835A")
OUTLINE(_at_"SEL$30")
OUTLINE(_at_"SEL$21")
OUTLINE(_at_"SEL$A87E79E7")
OUTLINE(_at_"SEL$9")
OUTLINE(_at_"SEL$8")
OUTLINE(_at_"SEL$31")
OUTLINE(_at_"SEL$AF9A6E20")
MERGE(_at_"SEL$8372BB86")
OUTLINE(_at_"SEL$6220C9A1")
MERGE(_at_"SEL$33BBCCBB")
OUTLINE(_at_"SEL$2F5D08D6")
MERGE(_at_"SEL$34")
OUTLINE(_at_"SEL$34F84EA7")
MERGE(_at_"SEL$55666DC4")
OUTLINE(_at_"SEL$34")
OUTLINE(_at_"SEL$A0D20652")
MERGE(_at_"SEL$8")
OUTLINE(_at_"SEL$10E2387E")
ANSI_REARCH(_at_"SEL$17")
OUTLINE(_at_"SEL$33B58238")
MERGE(_at_"SEL$93108F8D")
OUTLINE(_at_"SEL$A4319FDC")
ANSI_REARCH(_at_"SEL$20")
OUTLINE(_at_"SEL$65A156B2")
ANSI_REARCH(_at_"SEL$10")
OUTLINE(_at_"SEL$24")
OUTLINE(_at_"SEL$23")
OUTLINE(_at_"SEL$99FC2C6E")
ANSI_REARCH(_at_"SEL$26")
OUTLINE(_at_"SEL$6A52835A")
MERGE(_at_"SEL$22")
OUTLINE(_at_"SEL$DBA62079")
ANSI_REARCH(_at_"SEL$27")
OUTLINE(_at_"SEL$8372BB86")
MERGE(_at_"SEL$025E71EF")
OUTLINE(_at_"SEL$37")
OUTLINE(_at_"SEL$33BBCCBB")
MERGE(_at_"SEL$36")
OUTLINE(_at_"SEL$1")
OUTLINE(_at_"SEL$55666DC4")
MERGE(_at_"SEL$9C6E9675")
OUTLINE(_at_"SEL$17")
OUTLINE(_at_"SEL$3CE6BBDD")
ANSI_REARCH(_at_"SEL$C6B06173")
OUTLINE(_at_"SEL$93108F8D")
MERGE(_at_"SEL$E87ED40E")
OUTLINE(_at_"SEL$20")
OUTLINE(_at_"SEL$10")
OUTLINE(_at_"SEL$26")
OUTLINE(_at_"SEL$9F79289E")
ANSI_REARCH(_at_"SEL$25")
OUTLINE(_at_"SEL$22")
OUTLINE(_at_"SEL$27")
OUTLINE(_at_"SEL$9D1717F4")
ANSI_REARCH(_at_"SEL$7")
OUTLINE(_at_"SEL$025E71EF")
MERGE(_at_"SEL$9834E3F4")
OUTLINE(_at_"SEL$35")
OUTLINE(_at_"SEL$36")
OUTLINE(_at_"SEL$2")
OUTLINE(_at_"SEL$9C6E9675")
MERGE(_at_"SEL$470D85D0")
OUTLINE(_at_"SEL$C6B06173")
ANSI_REARCH(_at_"SEL$FC0768F8")
OUTLINE(_at_"SEL$E87ED40E")
MERGE(_at_"SEL$EF9E4006")
OUTLINE(_at_"SEL$25")
OUTLINE(_at_"SEL$7")
OUTLINE(_at_"SEL$6")
OUTLINE(_at_"SEL$9834E3F4")
MERGE(_at_"SEL$4")
OUTLINE(_at_"SEL$38")
OUTLINE(_at_"SEL$470D85D0")
MERGE(_at_"SEL$3")
MERGE(_at_"SEL$32")
OUTLINE(_at_"SEL$15")
OUTLINE(_at_"SEL$FC0768F8")
ANSI_REARCH(_at_"SEL$14")
OUTLINE(_at_"SEL$6F4A64AD")
ANSI_REARCH(_at_"SEL$8ABA6BCC")
OUTLINE(_at_"SEL$EF9E4006")
MERGE(_at_"SEL$11")
OUTLINE(_at_"SEL$5")
OUTLINE(_at_"SEL$4")
OUTLINE(_at_"SEL$33")
OUTLINE(_at_"SEL$3")
OUTLINE(_at_"SEL$32")
OUTLINE(_at_"SEL$14")
OUTLINE(_at_"SEL$8ABA6BCC")
ANSI_REARCH(_at_"SEL$13")
OUTLINE(_at_"SEL$8BB7F254")
ANSI_REARCH(_at_"SEL$12")
OUTLINE(_at_"SEL$11")
OUTLINE(_at_"SEL$13")
OUTLINE(_at_"SEL$12")
NO_ACCESS(_at_"SEL$2F5D08D6" "S"_at_"SEL$3")
NO_ACCESS(_at_"SEL$2F5D08D6" "from$_subquery$_003"_at_"SEL$34")
LEADING(_at_"SEL$2F5D08D6" "S"_at_"SEL$3" "D"@"SEL$3"
"from$_subquery$_003"_at_"SEL$34")
USE_NL(_at_"SEL$2F5D08D6" "from$_subquery$_003"_at_"SEL$34")
NO_ACCESS(_at_"SEL$28" "T"_at_"SEL$28")
USE_HASH_AGGREGATION(_at_"SEL$28")
BATCH_TABLE_ACCESS_BY_ROWID(_at_"SEL$3A62EF88" "CWA"_at_"SEL$35")
INDEX(_at_"SEL$3A62EF88" "PRICE_LIST1"_at_"SEL$36" ("PRICE_LIST1"."NDC"
"PRICE_LIST1"."ACCOUNT_ID" "PRICE_LIST1"."PRICE"))
LEADING(_at_"SEL$3A62EF88" "CWA"_at_"SEL$35" "PRICE_LIST1"@"SEL$36")
USE_NL(_at_"SEL$3A62EF88" "PRICE_LIST1"_at_"SEL$36")
INDEX_RS_ASC(_at_"SEL$E6D49DC0" "C"_at_"SEL$5" ("CONTRACT"."CONTRACT_ID"))
INDEX(_at_"SEL$E6D49DC0" "PO"_at_"SEL$4" ("PURCHASE_ORDER"."CONTRACT_ID"
"PURCHASE_ORDER"."CREATED_ON" "PURCHASE_ORDER"."PURCHASE_ORDER_ID"))
INDEX(_at_"SEL$E6D49DC0" "POI"_at_"SEL$4"
("PURCHASE_ORDER_ITEM"."PURCHASE_ORDER_ID"))
NO_ACCESS(_at_"SEL$E6D49DC0" "RE"_at_"SEL$7")
INDEX_FFS(_at_"SEL$E6D49DC0" "D"_at_"SEL$6" ("DRUG"."NDC" "DRUG"."PACK_QTY"
"DRUG"."PACK_SIZE"))
LEADING(_at_"SEL$E6D49DC0" "C"_at_"SEL$5" "PO"@"SEL$4" "POI"@"SEL$4"
"RE"_at_"SEL$7" "D"_at_"SEL$6")
USE_NL(_at_"SEL$E6D49DC0" "PO"_at_"SEL$4")
USE_NL(_at_"SEL$E6D49DC0" "POI"_at_"SEL$4")
NLJ_BATCHING(_at_"SEL$E6D49DC0" "POI"_at_"SEL$4")
USE_HASH(_at_"SEL$E6D49DC0" "RE"_at_"SEL$7")
USE_HASH(_at_"SEL$E6D49DC0" "D"_at_"SEL$6")
PX_JOIN_FILTER(_at_"SEL$E6D49DC0" "RE"_at_"SEL$7")
USE_HASH_AGGREGATION(_at_"SEL$E6D49DC0")
NO_ACCESS(_at_"SEL$3661D78D" "VM_NWVW_0"_at_"SEL$A87E79E7")
USE_HASH_AGGREGATION(_at_"SEL$3661D78D")
NO_ACCESS(_at_"SEL$63682743" "CL"_at_"SEL$10")
LEADING(_at_"SEL$63682743" "CL"_at_"SEL$10" "SRCL"@"SEL$10")
USE_HASH_AGGREGATION(_at_"SEL$63682743")
INDEX_RS_ASC(_at_"SEL$6F4644B4" "CT"_at_"SEL$11" ("CONTRACT"."CONTRACT_ID"))
INDEX(_at_"SEL$6F4644B4" "RAW_COB_CLAIMS"_at_"SEL$18"
("RAW_COB_CLAIMS"."SECONDARY_RAW_CLAIM_ID"))
FULL(_at_"SEL$6F4644B4" "CLI"_at_"SEL$14")
INDEX_RS_ASC(_at_"SEL$6F4644B4" "I"_at_"SEL$12" ("INVOICE"."INVOICE_ID"))
NO_ACCESS(_at_"SEL$6F4644B4" "VAD"_at_"SEL$15")
NO_ACCESS(_at_"SEL$6F4644B4" "BL"_at_"SEL$13")
LEADING(_at_"SEL$6F4644B4" "CT"_at_"SEL$11" "CL"@"SEL$11"
"RAW_COB_CLAIMS"_at_"SEL$18" "CLI"_at_"SEL$14" "I"@"SEL$12" "VAD"@"SEL$15"
"BL"_at_"SEL$13")
SUBQUERY_PRUNING(_at_"SEL$6F4644B4" "CL"_at_"SEL$11" PARTITION)
USE_NL(_at_"SEL$6F4644B4" "RAW_COB_CLAIMS"_at_"SEL$18")
USE_HASH(_at_"SEL$6F4644B4" "CLI"_at_"SEL$14")
USE_NL(_at_"SEL$6F4644B4" "I"_at_"SEL$12")
USE_HASH(_at_"SEL$6F4644B4" "VAD"_at_"SEL$15")
USE_HASH(_at_"SEL$6F4644B4" "BL"_at_"SEL$13")
PX_JOIN_FILTER(_at_"SEL$6F4644B4" "VAD"_at_"SEL$15")
USE_HASH_AGGREGATION(_at_"SEL$6F4644B4")
FULL(_at_"SEL$551CC24A" "T1"_at_"SEL$551CC24A")
INDEX(_at_"SEL$16" "ACKNOWLEDGED_DISPENSATION"_at_"SEL$16"
"ACKNOWLEDGED_DISP_IDX01")
USE_HASH_AGGREGATION(_at_"SEL$16")
NO_ACCESS(_at_"SEL$632AF070" "T1"_at_"SEL$22")
INDEX(_at_"SEL$632AF070" "R"_at_"SEL$22" ("REPLENISHMENT"."CONTRACT_ID"
"REPLENISHMENT"."NDC" "REPLENISHMENT"."VOIDED_ON"
"REPLENISHMENT"."REPLENISHED_ON" "REPLENISHMENT"."QUANTITY"
"REPLENISHMENT"."REPLENISHMENT_ID"))
NO_ACCESS(_at_"SEL$632AF070" "BL"_at_"SEL$25")
LEADING(_at_"SEL$632AF070" "T1"_at_"SEL$22" "R"@"SEL$22" "BL"@"SEL$25")
USE_HASH(_at_"SEL$632AF070" "R"_at_"SEL$22")
USE_HASH(_at_"SEL$632AF070" "BL"_at_"SEL$25")
USE_HASH_AGGREGATION(_at_"SEL$632AF070")
FULL(_at_"SEL$551CC249" "T1"_at_"SEL$551CC249")
INDEX(_at_"SEL$085F0B7A" "R"_at_"SEL$23" ("REPLENISHMENT"."CONTRACT_ID"
"REPLENISHMENT"."NDC" "REPLENISHMENT"."VOIDED_ON"
"REPLENISHMENT"."REPLENISHED_ON" "REPLENISHMENT"."QUANTITY"
"REPLENISHMENT"."REPLENISHMENT_ID"))
FULL(_at_"SEL$085F0B7A" "RCL"_at_"SEL$23")
LEADING(_at_"SEL$085F0B7A" "R"_at_"SEL$23" "RCL"@"SEL$23")
USE_HASH(_at_"SEL$085F0B7A" "RCL"_at_"SEL$23")
SWAP_JOIN_INPUTS(_at_"SEL$085F0B7A" "RCL"_at_"SEL$23")
USE_HASH_AGGREGATION(_at_"SEL$085F0B7A")
INDEX_RS_ASC(_at_"SEL$A0D20652_1" "R"_at_"SEL$8"
("REPLENISHMENT"."VOIDED_ON" "REPLENISHMENT"."REPLENISHMENT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(_at_"SEL$A0D20652_1" "R"_at_"SEL$8")
INDEX(_at_"SEL$A0D20652_1" "C"_at_"SEL$8" ("CONTRACT"."CONTRACT_ID"))
INDEX_RS_ASC(_at_"SEL$A0D20652_2" "C"_at_"SEL$A0D20652_2"
("CONTRACT"."CONTRACT_ID"))
INDEX(_at_"SEL$A0D20652_2" "R"_at_"SEL$A0D20652_2"
("REPLENISHMENT"."CONTRACT_ID" "REPLENISHMENT"."VOIDED_ON"
"REPLENISHMENT"."REPLENISHED_ON"))
LEADING(_at_"SEL$A0D20652_1" "R"_at_"SEL$8" "C"@"SEL$8")
LEADING(_at_"SEL$A0D20652_2" "C"_at_"SEL$A0D20652_2" "R"@"SEL$A0D20652_2")
USE_NL(_at_"SEL$A0D20652_1" "C"_at_"SEL$8")
NLJ_BATCHING(_at_"SEL$A0D20652_1" "C"_at_"SEL$8")
USE_NL(_at_"SEL$A0D20652_2" "R"_at_"SEL$A0D20652_2")
NLJ_BATCHING(_at_"SEL$A0D20652_2" "R"_at_"SEL$A0D20652_2")
USE_HASH_AGGREGATION(_at_"SEL$A0D20652")
FULL(_at_"SEL$19" "ORDER_BLACKLIST"_at_"SEL$19")
END_OUTLINE_DATA
*/
The USE_CONCAT line is this:
75 - SEL$A0D20652 / RE_at_SEL$7
Which is:
| Id | Operation | Name
| Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time
| Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes |
| 75 | VIEW |
| 1 | 2346 | 68034 | 119 (3)| 00:00:01
| | | 126 |00:00:00.01 | 199 | 0 | 0 |
I'm not exactly certain which view that is as "RE" table is referenced a couple of times in the SQL in a couple of WITH clauses (I could figure it out but haven't dug that far yet)
Chris
On Fri, Aug 27, 2021 at 10:12 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
> Is the plan showing the operation CONCATENATION, or OR_EXPANSION as its
> implementation of USE_CONCAT() ?
>
> The thing I notice as significant (apart from the use_concat, of course)
> is the
> OPT_PARAM('_optimizer_null_accepting_semijoin' 'false')
> which may be why the end up with a lateral view
> "VW_LAT_EA451CA1"_at_"SEL$EA451CA1"
> The other opt_params look as if they relate to the splitting of the 12.1
> adaptive optimization parameter into two parts (and that could be part of
> the problem, of course).
>
> Have you tried cutting and pasting the Outline from the good plan into the
> SQL to see if the hints are sufficient to reproduce the plan?
> Can you run the query on a patched as well as unpatched version of the
> software? If so running with the CBO trace (10053) enabled would allow you
> to compare all the optimizer parameters and fix_controls that vary between
> the two instances. Search for the heading PARAMETERS USED BY THE OPTIMIZER
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
>
>
>
> On Fri, 27 Aug 2021 at 14:50, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> For this it was "_no_or_expansion" to test getting rid of the
>> USE_CONCAT. (H/T to Noveljic for the help finding the param)
>>
>> It didn't make any difference however.
>>
>> Thanks,
>> Chris
>>
>>
>> On Fri, Aug 27, 2021 at 9:11 AM Noveljic Nenad <
>> nenad.noveljic_at_vontobel.com> wrote:
>>
>>> Hi,
>>>
>>>
>>>
>>> Could you try
>>>
>>>
>>>
>>> ALTER SESSION SET "_optimizer_cbqt_or_expansion"=off;
>>>
>>>
>>>
>>> Best regards,
>>>
>>>
>>>
>>> Nenad
>>>
>>>
>>>
>>> ____________________________________________________
>>>
>>> Please consider the environment before printing this e-mail.
>>>
>>> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>>>
>>>
>>> Important Notice
>>>
>>> This message is intended only for the individual named. It may contain
>>> confidential or privileged information. If you are not the named addressee
>>> you should in particular not disseminate, distribute, modify or copy this
>>> e-mail. Please notify the sender immediately by e-mail, if you have
>>> received this message by mistake and delete it from your system.
>>> Without prejudice to any contractual agreements between you and us which
>>> shall prevail in any case, we take it as your authorization to correspond
>>> with you by e-mail if you send us messages by e-mail. However, we reserve
>>> the right not to execute orders and instructions transmitted by e-mail at
>>> any time and without further explanation.
>>> E-mail transmission may not be secure or error-free as information could
>>> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
>>> processing of incoming e-mails cannot be guaranteed. All liability of
>>> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
>>> referred to as "Vontobel Group") for any damages resulting from e-mail use
>>> is excluded. You are advised that urgent and time sensitive messages should
>>> not be sent by e-mail and if verification is required please request a
>>> printed version.
>>> Please note that all e-mail communications to and from the Vontobel
>>> Group are subject to electronic storage and review by Vontobel Group.
>>> Unless stated to the contrary and without prejudice to any contractual
>>> agreements between you and Vontobel Group which shall prevail in any case,
>>> e-mail-communication is for informational purposes only and is not intended
>>> as an offer or solicitation for the purchase or sale of any financial
>>> instrument or as an official confirmation of any transaction.
>>> The legal basis for the processing of your personal data is the
>>> legitimate interest to develop a commercial relationship with you, as well
>>> as your consent to forward you commercial communications. You can exercise,
>>> at any time and under the terms established under current regulation, your
>>> rights. If you prefer not to receive any further communications, please
>>> contact your client relationship manager if you are a client of Vontobel
>>> Group or notify the sender. Please note for an exact reference to the
>>> affected group entity the corporate e-mail signature. For further
>>> information about data privacy at Vontobel Group please consult
>>> www.vontobel.com.
>>>
>>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 27 2021 - 17:58:30 CEST