slow query on CDB_FREE_SPACE
Date: Tue, 17 Oct 2023 11:37:08 +0300
Message-ID: <CA+riqSVK=ns3XAb_R0hGjBiBUk3DYXw+FCjK7-qsueK60HvVHQ_at_mail.gmail.com>
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-lReceived on Tue Oct 17 2023 - 10:37:08 CEST