Re: Priority of profile baseline patch
- From: Pap <oracle.developer35@xxxxxxxxx>
- To: Jonathan Lewis <jlewisoracle@xxxxxxxxx>, Lok P <loknath.73@xxxxxxxxx>
- Date: Wed, 22 Dec 2021 17:27:53 +0530
Thank You So much Jonathan.
Attached is the sql hints/outline of the profile which is pushed through
dbms_sqltune.import_sql_profile procedure.
And yes this query is having two of the tables MTD and MDD both are
partitioned. And there are ~97 index partitions for table MDD for one of
the indexes which is in UNUSABLE state. However that query is going for a
full table scan on that table MDD in the plan.So can that still cause such
a plan issue because of those unusable index partitions? But yes that
column on which index is created is part of the join condition in this
query, is it because that optimizer is going for that table expansion Or
dividing the query evaluation into multiple pieces, even though showing
full scan in the plan in each of the expanded sections?
expand_table hint is used for MDD table in the outline section even for the
sql profile. So wondering , as Lok pointed, how this OR_EXPAND of two
predicates now becomes non reproducible and it's going for three
predicates/sets plan?
On Wed, Dec 22, 2021 at 3:42 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
You've got Table expansion, OR-expansion, and Join factorization going on
in the old plan, but the join factorization does not occur in the new plan.
The significant factor is probably the Table expansion - this suggests you
have some partial indexing in place, or some partitions of local indexes
that are currently unusable. If there was something about the pattern of
"missing" index data that FORCED oracle to use a 3-way OR-expansion then
everything else follows from there.
Have you posted the content of the profile yet ? If so I missed it.
Regards
Jonathan Lewis
On Mon, 20 Dec 2021 at 20:19, Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank You So much Jonathan.
I think you are spot on wrt the OR expansion point. The plan which I am
seeing now is not getting reproduced and thus causing a new
baseline generation is having OR expansion. And thus the total number of
lines in this new plan is ~113 as compared to ~87 in the earlier plan. I
have attached the sample query with the old plan and the new one with their
respective outlines.
But yes the question would be why optimizer is now not able to produce
the old plan(i.e mostly without OR expansion). No such object
definition change or parameter setup changes have been done. Statistics are
getting gathered on a daily basis on the underlying objects but that should
not cause such issues. And this plan suddenly appeared after a
particular date and is continuing now. And the note section in the new plan
is showing the section below (i.e. it failed to reproduce the baseline).
And used the sql profile, but the sql profile plan/old plan is not exactly
the same as this one.
We have not had any sql patch created here but yes the sql profile was
not created using the traditional method(which would have a bunch of
opt_estimate hints) rather its created by forcing the exact outline hints
of an awr plan i.e the old plan in the attached doc. So do you think that
forced outline hints through sql profile can cause such an issue?
[[BEGIN_OUTLINE_DATA]]
[[IGNORE_OPTIM_EMBEDDED_HINTS]]
[[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]
[[DB_VERSION('19.1.0')]]
[[OPT_PARAM('_fix_control' '27268249:0')]]
[[ALL_ROWS]]
[[OUTLINE_LEAF(@"SEL$1")]]
[[OUTLINE_LEAF(@"SEL$D67CB2D9")]]
[[OUTLINE_LEAF(@"SEL$2")]]
[[OUTLINE_LEAF(@"SEL$4")]]
[[OUTLINE_LEAF(@"SEL$EC770CBC")]]
[[OUTLINE_LEAF(@"SEL$D67CB2D4")]]
[[OUTLINE_LEAF(@"SEL$0F3929D0")]]
[[OUTLINE_LEAF(@"SEL$EC770CBD")]]
[[OUTLINE_LEAF(@"SEL$D67CB2D5")]]
[[OUTLINE_LEAF(@"SEL$583AEB9A")]]
[[OUTLINE_LEAF(@"SET$CE5EDD29")]]
[[OUTLINE_LEAF(@"SEL$F61B142F")]]
[[MERGE(@"SEL$4119B633" >"SEL$FBFF865E")]]
[[OUTLINE_LEAF(@"SEL$EC770CBA")]]
[[MATERIALIZE(@"SEL$2")]]
[[OUTLINE_LEAF(@"SEL$D67CB2D2")]]
[[MATERIALIZE(@"SEL$1")]]
[[OUTLINE_LEAF(@"SEL$FED9171C")]]
[[OUTLINE_LEAF(@"SEL$EC770CBB")]]
[[OUTLINE_LEAF(@"SEL$D67CB2D3")]]
[[OUTLINE_LEAF(@"SEL$9FA593F3")]]
[[OUTLINE_LEAF(@"SET$CB26004B")]]
[[OUTLINE_LEAF(@"SEL$CE6FF33E")]]
[[MERGE(@"SEL$BBC9940B" >"SEL$9C1508C7")]]
[[OUTLINE_LEAF(@"SET$CD5DAC4C")]]
[[EXPAND_TABLE(@"SEL$3" "MDD"@"SEL$3")]]
[[OUTLINE_LEAF(@"SEL$ED6E2898")]]
[[OUTLINE(@"SEL$BDF4FBD0")]]
[[OUTLINE(@"SEL$D53A378B")]]
[[OUTLINE(@"SET$B37F4FFA")]]
[[FACTORIZE_JOIN(@"SET$9A7C2438"("edcp"@"SET$9A7C2438_2"
"edcp"@"SET$9A7C2438_1"))]]
[[OUTLINE(@"SEL$FBFF865E")]]
[[OR_EXPAND(@"SET$CD5DAC4C_2" (1) (2))]]
[[OUTLINE(@"SEL$4119B633")]]
[[MERGE(@"SEL$0FA1E029" >"SEL$3B8FD131")]]
[[OUTLINE(@"SEL$E5EA116A")]]
[[OUTLINE(@"SEL$D6577BAE")]]
[[OUTLINE(@"SET$991F55A2")]]
[[FACTORIZE_JOIN(@"SET$136C5676"("edcp"@"SET$136C5676_2"
"edcp"@"SET$136C5676_1"))]]
[[OUTLINE(@"SEL$9C1508C7")]]
[[OR_EXPAND(@"SET$CD5DAC4C_1" (1) (2))]]
[[OUTLINE(@"SEL$BBC9940B")]]
[[MERGE(@"SEL$AE92F862" >"SEL$AAA06791")]][[OUTLINE(@"SEL$3")]]
[[OUTLINE(@"SET$9A7C2438_1")]]
[[OUTLINE(@"SET$9A7C2438_2")]]
[[OUTLINE(@"SET$9A7C2438")]]
[[OUTLINE(@"SET$CD5DAC4C_2")]]
[[OUTLINE(@"SEL$3B8FD131")]]
[[OUTLINE(@"SEL$0FA1E029")]]
[[OUTLINE(@"SET$136C5676_1")]]
[[OUTLINE(@"SET$136C5676_2")]]
[[OUTLINE(@"SET$136C5676")]]
[[OUTLINE(@"SET$CD5DAC4C_1")]]
[[OUTLINE(@"SEL$AAA06791")]]
[[OUTLINE(@"SEL$AE92F862")]]
[[OUTLINE(@"SET$CD5DAC4C")]]
[[EXPAND_TABLE(@"SEL$3" "MDD"@"SEL$3")]]
[[NO_ACCESS(@"SEL$ED6E2898" "VW_TE_11"@"SEL$ED6E2898")]]
[[PQ_FILTER(@"SEL$ED6E2898" SERIAL)]]
[[NO_ACCESS(@"SEL$CE6FF33E" "VW_JF_SET$CB26004B"@"SEL$D6577BAE")]]
[[FULL(@"SEL$CE6FF33E" "edcp"@"SET$136C5676_2")]]
[[LEADING(@"SEL$CE6FF33E" "VW_JF_SET$CB26004B"@"SEL$D6577BAE"
"edcp"@"SET$136C5676_2")]]
[[USE_HASH(@"SEL$CE6FF33E" "edcp"@"SET$136C5676_2")]]
[[NO_ACCESS(@"SEL$F61B142F" "VW_JF_SET$CE5EDD29"@"SEL$D53A378B")]]
[[FULL(@"SEL$F61B142F" "edcp"@"SET$9A7C2438_2")]]
[[LEADING(@"SEL$F61B142F" "VW_JF_SET$CE5EDD29"@"SEL$D53A378B"
"edcp"@"SET$9A7C2438_2")]]
[[USE_HASH(@"SEL$F61B142F" "edcp"@"SET$9A7C2438_2")]]
[[NO_ACCESS(@"SEL$583AEB9A" "ref1"@"SET$9A7C2438_2")]]
[[FULL(@"SEL$583AEB9A" "MDD"@"SET$9A7C2438_2")]]
[[NO_ACCESS(@"SEL$583AEB9A" "ref2"@"SET$9A7C2438_2")]]
[[FULL(@"SEL$583AEB9A" "mtd"@"SET$9A7C2438_2")]]
[[LEADING(@"SEL$583AEB9A" "ref1"@"SET$9A7C2438_2" "MDD"@"SET$9A7C2438_2"
"ref2"@"SET$9A7C2438_2" "mtd"@"SET$9A7C2438_2")]]
[[USE_NL(@"SEL$583AEB9A" "MDD"@"SET$9A7C2438_2")]]
[[USE_HASH(@"SEL$583AEB9A" "ref2"@"SET$9A7C2438_2")]]
[[USE_HASH(@"SEL$583AEB9A" "mtd"@"SET$9A7C2438_2")]]
[[PX_JOIN_FILTER(@"SEL$583AEB9A" "mtd"@"SET$9A7C2438_2")]]
[[SWAP_JOIN_INPUTS(@"SEL$583AEB9A" "ref2"@"SET$9A7C2438_2")]]
[[NO_ACCESS(@"SEL$0F3929D0" "ref1"@"SET$9A7C2438_1")]]
[[FULL(@"SEL$0F3929D0" "MDD"@"SET$9A7C2438_1")]]
[[NO_ACCESS(@"SEL$0F3929D0" "ref2"@"SET$9A7C2438_1")]]
[[FULL(@"SEL$0F3929D0" "mtd"@"SET$9A7C2438_1")]]
[[LEADING(@"SEL$0F3929D0" "ref1"@"SET$9A7C2438_1" "MDD"@"SET$9A7C2438_1"
"ref2"@"SET$9A7C2438_1" "mtd"@"SET$9A7C2438_1")]]
[[USE_NL(@"SEL$0F3929D0" "MDD"@"SET$9A7C2438_1")]]
[[USE_HASH(@"SEL$0F3929D0" "ref2"@"SET$9A7C2438_1")]]
[[USE_HASH(@"SEL$0F3929D0" "mtd"@"SET$9A7C2438_1")]]
[[PX_JOIN_FILTER(@"SEL$0F3929D0" "mtd"@"SET$9A7C2438_1")]]
[[SWAP_JOIN_INPUTS(@"SEL$0F3929D0" "ref2"@"SET$9A7C2438_1")]]
[[FULL(@"SEL$EC770CBC" "T1"@"SEL$EC770CBC")]]
[[FULL(@"SEL$D67CB2D4" "T1"@"SEL$D67CB2D4")]]
[[FULL(@"SEL$EC770CBD" "T1"@"SEL$EC770CBD")]]
[[FULL(@"SEL$D67CB2D5" "T1"@"SEL$D67CB2D5")]]
[[NO_ACCESS(@"SEL$9FA593F3" "ref1"@"SET$136C5676_2")]]
[[FULL(@"SEL$9FA593F3" "MDD"@"SET$136C5676_2")]]
[[NO_ACCESS(@"SEL$9FA593F3" "ref2"@"SET$136C5676_2")]]
[[FULL(@"SEL$9FA593F3" "mtd"@"SET$136C5676_2")]]
[[LEADING(@"SEL$9FA593F3" "ref1"@"SET$136C5676_2" "MDD"@"SET$136C5676_2"
"ref2"@"SET$136C5676_2" "mtd"@"SET$136C5676_2")]]
[[USE_NL(@"SEL$9FA593F3" "MDD"@"SET$136C5676_2")]]
[[USE_HASH(@"SEL$9FA593F3" "ref2"@"SET$136C5676_2")]]
[[USE_HASH(@"SEL$9FA593F3" "mtd"@"SET$136C5676_2")]]
[[PX_JOIN_FILTER(@"SEL$9FA593F3" "mtd"@"SET$136C5676_2")]]
[[SWAP_JOIN_INPUTS(@"SEL$9FA593F3" "ref2"@"SET$136C5676_2")]]
[[NO_ACCESS(@"SEL$FED9171C" "ref1"@"SET$136C5676_1")]]
[[FULL(@"SEL$FED9171C" "MDD"@"SET$136C5676_1")]]
[[NO_ACCESS(@"SEL$FED9171C" "ref2"@"SET$136C5676_1")]]
[[FULL(@"SEL$FED9171C" "mtd"@"SET$136C5676_1")]]
[[LEADING(@"SEL$FED9171C" "ref1"@"SET$136C5676_1" "MDD"@"SET$136C5676_1"
"ref2"@"SET$136C5676_1" "mtd"@"SET$136C5676_1")]]
[[USE_NL(@"SEL$FED9171C" "MDD"@"SET$136C5676_1")]]
[[USE_HASH(@"SEL$FED9171C" "ref2"@"SET$136C5676_1")]]
[[USE_HASH(@"SEL$FED9171C" "mtd"@"SET$136C5676_1")]]
[[PX_JOIN_FILTER(@"SEL$FED9171C" "mtd"@"SET$136C5676_1")]]
[[SWAP_JOIN_INPUTS(@"SEL$FED9171C" "ref2"@"SET$136C5676_1")]]
[[FULL(@"SEL$EC770CBA" "T1"@"SEL$EC770CBA")]]
[[FULL(@"SEL$D67CB2D2" "T1"@"SEL$D67CB2D2")]]
[[FULL(@"SEL$EC770CBB" "T1"@"SEL$EC770CBB")]]
[[FULL(@"SEL$D67CB2D3" "T1"@"SEL$D67CB2D3")]]
[[FULL(@"SEL$4" "SPPEC"@"SEL$4")]]
[[NO_ACCESS(@"SEL$2" "ref1"@"SEL$2")]]
[[FULL(@"SEL$2" "bfid_stg"@"SEL$2")]]
[[FULL(@"SEL$2" "vcd_stg"@"SEL$2")]]
[[LEADING(@"SEL$2" "ref1"@"SEL$2" "bfid_stg"@"SEL$2" "vcd_stg"@"SEL$2")]]
[[USE_NL(@"SEL$2" "bfid_stg"@"SEL$2")]]
[[USE_HASH(@"SEL$2" "vcd_stg"@"SEL$2")]]
[[PX_JOIN_FILTER(@"SEL$2" "vcd_stg"@"SEL$2")]]
[[INDEX_RS_ASC(@"SEL$1" "essc"@"SEL$1" ("essc"."btid"))]]
[[FULL(@"SEL$D67CB2D9" "T1"@"SEL$D67CB2D9")]]
[[END_OUTLINE_DATA]]></hint></outline_data>
Other related posts:
