Hi Jonathan and Sayan,
The UNNEST hint didn't work but the query is completed within 30 secs
post-re-wrote to outer join.
Also, could you help me understand, what is the difference between the
10053 traces manually enabled on the query and retrieving from the
DBMS_SQLDIAG .DUMP_TRACE API?
Best Regards,
Amit S
On Thu, May 27, 2021 at 5:05 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
wrote:
>
> Before trying a rewrite - or going though the painful process of trying to
> interpret a CBO trace file, you could pursue three ideas:
> a) Does Oracle consider subquery unnesting to be illegal ... try adding an
> /*+ unnest */ hint to the subquery itself to see if it is possible (even it
> produces a very bad plan as a result)
> b) Does Oracle think the subquery will happen only a very small number of
> times - if so address address the cause of of the bad cardinality estimate
> before chasing the subquery error
> c) Has Oracle run the subquery at the earliest possible moment or the
> latest possible moment - if there are multiple subqueries have they been
> run in the wrong order: would it help to change the timing of when the
> subquery runs.
>
> Regards
> Jonathan Lewis
>
>
>
> On Wed, 26 May 2021 at 15:29, Amit Saroha <dmarc-noreply_at_freelists.org>
> wrote:
>
>> Hi,
>>
>> In one of the queries TKProof shows, most time is spent in accessing a
>> table thousands of times inside NOT EXIST condition.
>>
>> I am looking for your inputs if it's a promising idea to replace NOT
>> EXIST with the LEFT OUTER JOIN and IS NULL condition?
>>
>> Any inputs in this regard are appreciated.
>>
>>
>> Regards,
>> Amit S
>>
