Re: slow query on CDB_FREE_SPACE

It can be a very bad idea to call gather_fixed_object_stats() because of the time it takes and the impact it can have on current plans. For special cases it's worth nothing that 'gather_table_stats()' will work even on the fixed tables. So after looking at the query plan it might be a lot better to gather stats only on the tables (and indexes) used in that branch of the plan.

I mention that point in an example at
https://jonathanlewis.wordpress.com/2016/10/17/fixed-stats-2/ - with a reference to the fact that Timur was the one who let me know about it ;)

I haven't checked the details, but one possible issue with that PDB is that (for example) there's one tablespace with a very large number of free extents and you need a histogram to make the optimizer pick a better plan in that PDB - I'd query x$ktfbue from that PDB with some aggregate queries to see if I could spot why it was a special case.

Regards
Jonathan Lewis

On Tue, 17 Oct 2023 at 15:56, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 10/17/23 10:14, Timur Akhmadeev wrote:
>
> I've checked a few 19.18 databases and can't reproduce the adaptive stuff
> in that part of the plan.
> Things I'd try:
> 1) simple: purge dba_recyclebin in PDBs & gather dictionary stats - the
> plan shape may change after this
> 2) set X$KTFBUE blocks/empty_blocks statistics manually to something
> meaningful (use get_table_stats followed by set_table_stats calls) - those
> stats are nulls since it's not a real object. I suspect the full table scan
> cost will go up and it may naturally help to avoid FTS.
>
> Timur, what do you think about RUNNING GATHER_FIXED_OBJECT_STATS?
> Personally, I would try that before settings stats for the fixed tables.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

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