Re: slow query on CDB_FREE_SPACE
From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 17 Oct 2023 10:55:35 -0400
Message-ID: <f424c0fd-eba2-49e2-a7d8-48a1e0c88ff6_at_gmail.com>
Date: Tue, 17 Oct 2023 10:55:35 -0400
Message-ID: <f424c0fd-eba2-49e2-a7d8-48a1e0c88ff6_at_gmail.com>
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-1217 https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 17 2023 - 16:55:35 CEST