Re: Plan for recursive query

On 11/6/23 04:53, Krishnaprasad Yadav wrote:
> Dear Gurus,
>
> We have isue during mv refresh , we see spike of Cpu waits  , we see
> recursive querys are top waiting for cpu
>
> We see two plan for recursive querys , one is using FTS and other is
> Index range Scan ,Need to understand , is attaching good plan for
> recursive query is fine , or it is not good practise to follow for
> recursive query and  does it can  creates any issue
>
> Regards,
> Krish

Recursive queries query system tables. So, creating indexes or using baselines is out of the question. There is probably something wrong with the dictionary statistics. As Jonathan has recently pointed out, gathering dictionary stats may have some unforeseen consequences.  He suggested using DBMS_STATS.GATHER_TABLE_STATS on the objects referenced by those recursive queries. Alternatively, you could wait for the period of inactivity and gather dictionary stats, but that is likely to cause a parsing storm.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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