Re: How to fix cache buffer chain issue

" Do you mean to say even the current plan can be tweaked(through some hints) to make it perform more efficiently?"

I mean that you already have multiple plans - one is already <100 times better than the other (as you only do the access of all rows in TAD that have DT_CR=MAX(DT_CR) once per union part rather than once per row from the previous joins). This is evidenced by your own observations that sometimes it is much faster (although not the most efficient it can be).

Having a close look at the two plans - the reason for the difference is due to the cardinality estimates for TNI. If, when the query is hard parsed, Oracle believes there will be 0-1 rows coming from the TMMC join: it will use a nested loop join and filter on MCD when it accesses the TAD table (via the DT_CR index). If, when the query is hard parsed, Oracle estimates that you will join from multiple rows from TMMC (eg 12 in your shared plans): it will want to do a hash join to guarantee doing the DT_CR=MAX(DT_CR) read once but can't filter on the TMMC join condition until later. The nested loop plan is not good when multiple rows come from TMMC. Does this mean the TNI.NE = 'XX' filter is also a variable (or a literal that has been placed there by dynamic SQL), or is there some updated statistics in play here?

Your "optional" filter against MCD is going to harm the optimizer, but it shouldn't make a difference to the early part of the plan. I would still suggest you get it under control. As Jonathan mentioned in one of the earlier replies, the solution would be to give Oracle multiple queries. This is from a PL/SQL block (the bind variable names gives it away), so you should have access to the overall code, it would be easy enough to have an if statement in the code: if <some variable> is not null (and is not 'ZZZZ') then run the query with a filter on TAD.MCD = <some variable> , otherwise leave the filter out.

But, as the others are suggesting, we can make this even better by simplifying (removing) the unions. Your filter against ADT will simplify to `ADT is not null`, so it's *probably* not worth including it in any index.

An index on TAD (DT_CR, MCD) is probably what you will end up benefiting from *as well as* the rewrite.

Thanks,
Andrew

On Tue, 22 Jun 2021 at 22:31, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> JL previously suggested:
>
>
>
> “If you rewrite the first bit of this to:
>
> ADT >= trunc(:b2) - 1,
>
> then add ADT to the index on dt_cr, this should allow Oracle to resolve
> the predicate in the index and may therefore reduce the visits to the
> table.”
>
>
>
> IF you do that (and the corresponding “between” resolutions) AND add ALL
> to your unions, you should resolve which rows you need from the index
> without a table visit and therefore experience goodness.
>
>
>
> NOT having to perform unneeded de-duplication in theory should prevent
> unneeded insertion into a hash (or sorting) along the way to get the final
> result set.
>
>
>
> Now it appeared there were some missing ranges of ADT in the filter sets
> (days 15 through 59, unless JL just got tired of the tedious typing), so
> whether it is cheaper to read the whole day from the table filtering or
> accessing just the rows you need filtered by an improved index requires
> actuals testing.
>
>
>
> My perception was that it was going to be a chore for y’all to convert to
> a case statement and test that. I could be wrong about that. I have no
> tangible support for my notion that adding ADT to your index on DT_CR will
> prove of benefit in the rest of your code base for relatively little cost.
>
>
>
> Good luck.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Pap
> *Sent:* Tuesday, June 22, 2021 2:23 PM
> *To:* Mark W. Farnham
> *Cc:* Jonathan Lewis; Andy Sayer; Dominic Brooks; Mladen Gogala; Oracle L
> *Subject:* Re: How to fix cache buffer chain issue
>
>
>
> Thank You Mark.
>
>
>
> Agree that the date ranges(filter on ADT) seem to be non overlapping and
> in that case we can replace the UNION with UNION ALL. But even in that case
> won't the table/index access for table TAD, (which is mostly the concern
> here) is going to happen the same 17 times i.e. as many times the number
> of UNION clauses are there in the query? I think what Jonathan is
> suggesting is to get rid of all the 17 UNION clauses and make the same
> logic achieve in one query, I am still trying to understand that one.
>
>
>
> Regards
>
> Pap
>
>
>
> On Mon, Jun 21, 2021 at 8:00 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> AND (not but), this analysis (non-overlapping and therefore
> non-duplicative ranges) indicates UNION ALL can in fact be used in place of
> UNION. JL’s “get it all” and produce the pieces by case probably does even
> better than that, but considering code change cost versus making problems
> go away, changing UNION to UNION ALL might be good enough.
>
>
>
> (Quite apart from that I wonder whether the rest of the query code on this
> data set screams to be partitioned by DT_CT and not use an index instead of
> a partition scan.)
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
> *Sent:* Monday, June 21, 2021 4:06 AM
> *To:* Andy Sayer
> *Cc:* Dominic Brooks; gogala.mladen_at_gmail.com; oracle-l_at_freelists.org
> *Subject:* Re: How to fix cache buffer chain issue
>
>
>
>
>
> Thanks, Mladen and Andy, for prompting me to look at the original SQL.
>
>
>
> Without spending more than a couple of minutes on the code I get the
> impression that it's trying to
>
> 1) get all the data for a given date DT_CT, then
>
> 2) summarise it into non-overlapping ranges of adt.
>
>
>
> The code seems very repetitive with the main variation being:
>
> AND (TRUNC ( :B2) - ADT) <= 1
>
>
>
> AND (TRUNC ( :B2) - ADT) > 1
> AND (TRUNC ( :B2) - ADT) <= 2
>
>
>
> AND (TRUNC ( :B2) - ADT) > 2
> AND (TRUNC ( :B2) - ADT) <= 3
>
>
>
> AND (TRUNC ( :B2) - ADT) > 3
> AND (TRUNC ( :B2) - ADT) <= 4
>
>
>
> AND (TRUNC ( :B2) - ADT) > 4
> AND (TRUNC ( :B2) - ADT) <= 9
>
>
>
> AND (TRUNC ( :B2) - ADT) > 9
> AND (TRUNC ( :B2) - ADT) <= 14
>
>
>
> AND (TRUNC ( :B2) - ADT) > 60
> AND (TRUNC ( :B2) - ADT) <= 90
>
>
>
> AND (TRUNC ( :B2) - ADT) > 90
>
>
>
> A "global" improvement might be to extract all the data for the date and
> then use something like a case expression to sum the data by the required
> data ranges.
>
>
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>

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