Re: How to fix cache buffer chain issue

There isn't really much point in asking us to optimise your query bit by bit since there are lots of details that we don't know about your data and the work done by the query until you give us the next little bit of information.
However, as a highly localised enhancement there are two obvious details visible in the predicate information / predicates that could allow you to reduce the CPU spent visit (and presumably discarding rows from ) the table.

  14 - filter(((NVL(:B1,'ZZZZ')='ZZZZ' OR ("TAD"."MCD"=:B1 AND NVL(:B1,'ZZZZ')<>'ZZZZ')) AND

              TRUNC(:B2)-"ADT"<=1))

which seems to come from

            AND (TRUNC ( :B2) - ADT) <= 1
            AND (   (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
                 OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')

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.

As for the other bit with the two strange nvl()s - is that trying to obfuscate the actual values used and confusing the issue, or is it intended as a complicated way of saying: "tad.mcd = :b1 or :b1 is null" A predicate of the latter type should not appear in the SQL - the client code should code of the form:

if :b1 is null then execute SQL1 else execute SQL2

Note - however, whether or not this tweak for the indexing with matching preidcates gives you some benefit, the large-scale structure of the query is suspect - 17 query blocks in a union is an indication of a need to review the requirement and how best to specify it.

Regards
Jonathan Lewis

On Sat, 19 Jun 2021 at 18:21, Pap <oracle.developer35_at_gmail.com> wrote:

> Thank You Jonathan.
>
> I see it's mostly line no -14 i.e. *TABLE ACCESS BY INDEX ROWID *part
> which is where most of the CPU has been spent as evident from ASH*. *Will
> it be a good idea to evaluate the " SELECT MAX (DT_CR) FROM TAD"
> beforehand and store it in a variable and use that bind value as filter
> variable in this query? It will minimize the 'INDEX FULL SCAN(MIN/MAX) ' to
> zero in the query path but the 'index range scan" TAD_IX2 i.e. predicate
> -15 will still be there in the path, but I'm wondering if it will be a good
> idea in this situation to make the query use lesser resources?
>
> There exists another index on column PMENT of table TAD. But was
> thinking if any new index on table TAD can help in making the situation
> better here?
>
> Regards
> Pap
>
>

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