Re: slow query on CDB_FREE_SPACE

I don't know if there's a better way to do it, but when I enabled sql tracing in the CDB and checked the trace files from the PX servers that queried the PDBs I got the following SQL from the CDB:

SQL ID: 9vdwxrs9uxg1y Plan Hash: 0

SELECT /*CROSSCON*/ /*SYS."CDB_FREE_SPACE"*/ /*"K"*/ /* ID */ * FROM
 "SYS"."DBA_FREE_SPACE" and this in the PDBs

SQL ID: ck97dnj86zn7r Plan Hash: 2733218305

SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */   "TABLESPACE_NAME","FILE_ID","BLOCK_ID","BYTES","BLOCKS","RELATIVE_FNO" FROM
 "SYS"."DBA_FREE_SPACE" "K" WHERE 1=1 So, as a hack, I'd try creating an SQL Patch with the hint you want for the PDB statement. I don't know if that should be done in the CDB, or in PDB that is giving you the problem.

Regards
Jonathan Lewis

On Tue, 17 Oct 2023 at 09: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