Re: slow query on CDB_FREE_SPACE

Sometimes all that is needed is to collect dictionary statistics.

On Tue, Oct 17, 2023 at 01:38 Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Dear all,
>
> Most probably we all hit at some point issues where querying
> DBA_FREE_SPACE is slow.
>
> I have the next issue:
> -> query on CDB_FREE_SPACE is slow , issue being located in a particular
> PDB
> -> querying DBA_FREE_SPACE in problematic PDB I can see issue is caused by
> execution plan full scanning X$KTFBUE
> -> I gathered stats on X$KTFBUE and issue was still there
> -> I observed that setting optimizer_adaptive_plans to false will do the
> trick
> -> went back in container , set optimizer_adaptive_plans to false at
> session level and worked, so I thought great, I`ll create a sql patch with
> hint opt_param('optimizer_adaptive_plans' 'false') but when hint is
> applied in the query from CDB$ROOT is not propagated into PDBs (is working
> only with alter session). The hint is working if I switch to PDB and run
> the query with the hint there.
>
> Any ideas on how to solve this problem?
>
> Thank you.
>
>

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