Re: slow query on CDB_FREE_SPACE

From: Timur Akhmadeev <timur.akhmadeev_at_gmail.com>
Date: Tue, 17 Oct 2023 17:14:12 +0300
Message-ID: <CACGsLCLH=rjoY-MNs__pL4F6ZR=xFtXm12Bgu4XGrqRAOVm6YA_at_mail.gmail.com>

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.

On Tue, Oct 17, 2023 at 4:04 PM Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Basically the most import part for me is to figure out a way to make
> dba_free_space work, and to figure out why with adaptive plans off is smart
> enough to pick FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) . Forcing
> this index via a hint seems in particular a bit tricky. Second sql monitor
> is just the starting part, is usually taking few hours
>
>
> SQL Text
>
> ------------------------------
>
> SELECT /*+ OPT_PARAM('optimizer_adaptive_plans' 'false') monitor*/
> f.file_id file_id, SUM(f.bytes) fbytes FROM CDB_free_space f GROUP BY
> f.file_id
>
>
>
> Global Stats
>
> ================================================================
>
> | Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
>
> | Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
>
> ================================================================
>
> | 0.06 | 0.02 | 0.04 | 2 | 2171 | 82 | 656KB |
>
> ================================================================
>
>
>
> SQL Plan Monitoring Details (Plan Hash Value=389633790)
>
>
> ===================================================================================================================================================================================
>
> | Id | Operation | Name |
> Rows | Cost | Time | Start | Execs | Rows | Read | Read |
> Mem | Activity | Activity Detail |
>
> | | | |
> (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
> (Max) | (%) | (# samples) |
>
>
> ===================================================================================================================================================================================
>
> | 0 | SELECT STATEMENT |
> | | | 1 | +0 | 1 | 15 | |
> | . | | |
>
> | 1 | HASH GROUP BY |
> | 15 | 56 | 1 | +0 | 1 | 15 | | |
> 1MB | | |
>
> | 2 | VIEW | DBA_FREE_SPACE |
> 21453 | 54 | 1 | +0 | 1 | 2122 | | | .
> | | |
>
> | 3 | UNION-ALL |
> | | | 1 | +0 | 1 | 2122 | |
> | . | | |
>
> | 4 | NESTED LOOPS |
> | 1 | 2 | | | 1 | | |
> | . | | |
>
> | 5 | NESTED LOOPS |
> | 1 | 2 | 1 | +0 | 1 | 0 | |
> | . | | |
>
> | 6 | TABLE ACCESS FULL | FILE$
> | 15 | 2 | 1 | +0 | 1 | 15 | |
> | . | | |
>
> | 7 | TABLE ACCESS CLUSTER | FET$
> | 1 | | 1 | +0 | 15 | 0 | |
> | . | | |
>
> | 8 | INDEX UNIQUE SCAN | I_TS#
> | 1 | | 1 | +0 | 15 | 15 | |
> | . | | |
>
> | 9 | TABLE ACCESS CLUSTER | TS$
> | 1 | | | | | | |
> | . | | |
>
> | 10 | INDEX UNIQUE SCAN | I_TS#
> | 1 | | | | | | |
> | . | | |
>
> | 11 | HASH JOIN | |
> 614 | 9 | 1 | +0 | 1 | 543 | | | 1MB
> | | |
>
> | 12 | TABLE ACCESS FULL | FILE$
> | 15 | 2 | 1 | +0 | 1 | 15 | |
> | . | | |
>
> | 13 | NESTED LOOPS | |
> 614 | 7 | 1 | +0 | 1 | 543 | | | .
> | | |
>
> | 14 | TABLE ACCESS FULL | TS$
> | 15 | 7 | 1 | +0 | 1 | 15 | |
> | . | | |
>
> | 15 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1)
> | 42 | | 1 | +0 | 15 | 543 | |
> | . | | |
>
> | 16 | NESTED LOOPS | |
> 20836 | 27 | 1 | +0 | 1 | 1579 | | | .
> | | |
>
> | 17 | HASH JOIN |
> | 71 | 27 | 1 | +0 | 1 | 82 | | |
> 719KB | | |
>
> | 18 | NESTED LOOPS |
> | 71 | 25 | 1 | +0 | 1 | 82 | |
> | . | | |
>
> | 19 | NESTED LOOPS | |
> 1095 | 25 | 1 | +0 | 1 | 82 | | | .
> | | |
>
> | 20 | TABLE ACCESS FULL | TS$
> | 15 | 7 | 1 | +0 | 1 | 15 | | |
> . | | |
>
> | 21 | INDEX RANGE SCAN | RECYCLEBIN$_TS
> | 73 | 1 | 1 | +0 | 15 | 82 | |
> | . | | |
>
> | 22 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$
> | 5 | 4 | 1 | +0 | 82 | 82 | |
> | . | | |
>
> | 23 | TABLE ACCESS FULL | FILE$
> | 15 | 2 | 1 | +0 | 1 | 15 | |
> | . | | |
>
> | 24 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) |
> 295 | | 1 | +0 | 82 | 1579 | 82 | 656KB | .
> | | |
>
> | 25 | NESTED LOOPS |
> | 1 | 13 | | | 1 | | |
> | . | | |
>
> | 26 | NESTED LOOPS |
> | 73 | 13 | | | 1 | | |
> | . | | |
>
> | 27 | NESTED LOOPS |
> | 1 | 9 | | | 1 | | |
> | . | | |
>
> | 28 | NESTED LOOPS |
> | 1 | 9 | | | 1 | | |
> | . | | |
>
> | 29 | TABLE ACCESS FULL | TS$
> | 1 | 7 | | | 1 | |
> | | . | | |
>
> | 30 | TABLE ACCESS CLUSTER | UET$
> | 1 | 2 | | | | | |
> | . | | |
>
> | 31 | INDEX RANGE SCAN | I_FILE#_BLOCK#
> | 1 | 2 | | | | | |
> | . | | |
>
> | 32 | TABLE ACCESS BY INDEX ROWID | FILE$
> | 1 | | | | | | |
> | . | | |
>
> | 33 | INDEX UNIQUE SCAN | I_FILE2
> | 1 | | | | | | |
> | . | | |
>
> | 34 | INDEX RANGE SCAN | RECYCLEBIN$_TS
> | 73 | 1 | | | | | |
> | . | | |
>
> | 35 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$
> | 1 | 4 | | | | | |
> | . | | |
>
> | 36 | NESTED LOOPS |
> | 1 | 3 | | | 1 | | |
> | . | | |
>
> | 37 | NESTED LOOPS |
> | 1 | 3 | | | 1 | | |
> | . | | |
>
> | 38 | NESTED LOOPS |
> | 1 | 2 | | | 1 | | |
> | . | | |
>
> | 39 | TABLE ACCESS FULL | NEW_LOST_WRITE_EXTENTS$
> | 1 | 2 | | | 1 | | |
> | . | | |
>
> | 40 | TABLE ACCESS CLUSTER | TS$
> | 1 | | | | | | |
> | . | | |
>
> | 41 | INDEX UNIQUE SCAN | I_TS#
> | 1 | | | | | | |
> | . | | |
>
> | 42 | INDEX RANGE SCAN | I_FILE2
> | 1 | 1 | | | | | |
> | . | | |
>
> | 43 | TABLE ACCESS BY INDEX ROWID | FILE$
> | 1 | 1 | | | | | |
> | . | | |
>
>
>
>
>
> SQL Text
>
> ------------------------------
>
> SELECT /* OPT_PARAM('optimizer_adaptive_plans' 'false') monitor*/
> f.file_id file_id, SUM(f.bytes) fbytes FROM CDB_free_space f GROUP BY
> f.file_id
>
>
>
> Global Stats
>
> =========================================================
>
> | Elapsed | Cpu | IO | Buffer | Read | Read |
>
> | Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes |
>
> =========================================================
>
> | 21 | 2.83 | 18 | 302K | 58326 | 456MB |
>
> =========================================================
>
>
>
> SQL Plan Monitoring Details (Plan Hash Value=2701564472)
>
>
> ======================================================================================================================================================================================================
>
> | Id | Operation | Name
> | Rows | Cost | Time | Start | Execs | Rows | Read | Read |
> Mem | Activity | Activity Detail |
>
> | | |
> | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes
> | | (%) | (# samples) |
>
>
> ======================================================================================================================================================================================================
>
> | 0 | SELECT STATEMENT |
> | | | 1 | +4 | 1 |
> 0 | | | . | | |
>
> | 1 | HASH GROUP BY |
> | 15 | 56 | 1 | +4 | 1 | 0 | |
> | 1MB | | |
>
> | 2 | VIEW | DBA_FREE_SPACE
> | 21453 | 54 | 1 | +4 | 1 | 544 | |
> | . | | |
>
> | 3 | UNION-ALL |
> | | | 1 | +4 | 1 | 544 | |
> | . | | |
>
> | 4 | NESTED LOOPS |
> | 1 | 2 | | | 1 | | |
> | . | | |
>
> | 5 | NESTED LOOPS |
> | 1 | 2 | 1 | +4 | 1 | 0 | |
> | . | | |
>
> | 6 | TABLE ACCESS FULL | FILE$
> | 15 | 2 | 1 | +4 | 1 | 15 | |
> | . | | |
>
> | 7 | TABLE ACCESS CLUSTER | FET$
> | 1 | | 1 | +4 | 15 | 0 | |
> | . | | |
>
> | 8 | INDEX UNIQUE SCAN | I_TS#
> | 1 | | 1 | +4 | 15 | 15 | |
> | . | | |
>
> | 9 | TABLE ACCESS CLUSTER | TS$
> | 1 | | | | |
> | | | . | | |
>
> | 10 | INDEX UNIQUE SCAN | I_TS#
> | 1 | | | | | | |
> | . | | |
>
> | 11 | HASH JOIN |
> | 614 | 9 | 1 | +4 | 1 | 543 | |
> | . | | |
>
> | 12 | TABLE ACCESS FULL | FILE$
> | 15 | 2 | 1 | +4 | 1 | 15 | |
> | . | | |
>
> | 13 | NESTED LOOPS |
> | 614 | 7 | 1 | +4 | 1 | 543 | |
> | . | | |
>
> | 14 | TABLE ACCESS FULL | TS$
> | 15 | 7 | 1 | +4 | 1 | 15 | |
> | . | | |
>
> | 15 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1)
> | 42 | | 1 | +4 | 15 | 543 | |
> | . | | |
>
> | 16 | NESTED LOOPS |
> | 20836 | 27 | 1 | +4 | 1 | 1 | |
> | . | | |
>
> | 17 | HASH JOIN |
> | 71 | 27 | 1 | +4 | 1 | 1 | | |
> 468KB | | |
>
> | 18 | HASH JOIN |
> | 71 | 25 | 1 | +4 | 1 | 1
> | | | . | | |
>
> | 19 | NESTED LOOPS |
> | 71 | 25 | 1 | +4 | 1 | 82 | |
> | . | | |
>
> | 20 | NESTED LOOPS |
> | 1095 | 25 | 1 | +4 | 1 | 82
> | | | . | | |
>
> | 21 | STATISTICS COLLECTOR |
> | | | 1 | +4 | 1 | 15 | |
> | . | | |
>
> | 22 | TABLE ACCESS FULL | TS$
> | 15 | 7 | 1 | +4 | 1 | 15 | |
> | . | | |
>
> | 23 | INDEX RANGE SCAN | RECYCLEBIN$_TS
> | 73 | 1 | 1 | +4 | 15 | 82 | |
> | . | | |
>
> | 24 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$
> | 5 | 4 | 1 | +4 | 82 | 82 | |
> | . | | |
>
> | 25 | TABLE ACCESS FULL | RECYCLEBIN$
> | 5 | 4 | | | | | |
> | . | | |
>
> | -> 26 | TABLE ACCESS FULL | FILE$
> | 15 | 2 | 17 | +4 | 1 | 6 | |
> | . | | |
>
> | -> 27 | FIXED TABLE FULL | X$KTFBUE
> | 295 | | 21 | +1 | 1 | 1 | 58236 | 455MB
> | . | 100.00 | Cpu (2) |
>
> | | |
> | | | | | | | |
> | | | db file sequential read (19) |
>
> | 28 | HASH JOIN |
> | 1 | 13 | | | |
> | | | . | | |
>
> | 29 | NESTED LOOPS |
> | 1 | 13 | | | | | |
> | . | | |
>
> | 30 | NESTED LOOPS |
> | 73 | 13 | | | | | |
> | . | | |
>
> | 31 | STATISTICS COLLECTOR |
> | | | | | | | |
> | . | | |
>
> | 32 | NESTED LOOPS |
> | 1 | 9 | | | | | |
> | . | | |
>
> | 33 | HASH JOIN |
> | 1 | 9 | | | | | |
> | . | | |
>
> | 34 | NESTED LOOPS |
> | 1 | 9 | | | | | |
> | . | | |
>
> | 35 | STATISTICS COLLECTOR |
> | | | | | | | |
> | . | | |
>
> | 36 | TABLE ACCESS FULL | TS$
> | 1 | 7 | | | | | |
> | . | | |
>
> | 37 | TABLE ACCESS CLUSTER | UET$
> | 1 | 2 | | | |
> | | | . | | |
>
> | 38 | INDEX RANGE SCAN | I_FILE#_BLOCK#
> | 1 | 2 | | | | | |
> | . | | |
>
> | 39 | TABLE ACCESS FULL | UET$
> | 1 | 2 | | | | | |
> | . | | |
>
> | 40 | TABLE ACCESS BY INDEX ROWID | FILE$
> | 1 | | | | | | |
> | . | | |
>
> | 41 | INDEX UNIQUE SCAN | I_FILE2
> | 1 | | | | | | |
> | . | | |
>
> | 42 | INDEX RANGE SCAN | RECYCLEBIN$_TS
> | 73 | 1 | | | | | |
> | . | | |
>
> | 43 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$
> | 1 | 4 | | | | | |
> | . | | |
>
> | 44 | TABLE ACCESS FULL | RECYCLEBIN$
> | 1 | 4 | | | | | |
> | . | | |
>
> | 45 | HASH JOIN |
> | 1 | 3 | | | | | |
> | . | | |
>
> | 46 | NESTED LOOPS |
> | 1 | 3 | | | |
> | | | . | | |
>
> | 47 | NESTED LOOPS |
> | 1 | 3 | | | | | |
> | . | | |
>
> | 48 | STATISTICS COLLECTOR |
> | | | | | |
> | | | . | | |
>
> | 49 | NESTED LOOPS |
> | 1 | 2 | | | | | |
> | . | | |
>
> | 50 | TABLE ACCESS FULL | NEW_LOST_WRITE_EXTENTS$
> | 1 | 2 | | | | | |
> | . | | |
>
> | 51 | TABLE ACCESS CLUSTER | TS$
> | 1 | | | | | | |
> | . | | |
>
> | 52 | INDEX UNIQUE SCAN | I_TS#
> | 1 | | | | | | |
> | . | | |
>
> | 53 | INDEX RANGE SCAN | I_FILE2
> | 1 | 1 | | | | | |
> | . | | |
>
> | 54 | TABLE ACCESS BY INDEX ROWID | FILE$
> | 1 | 1 | | | | | |
> | . | | |
>
> | 55 | TABLE ACCESS FULL | FILE$
> | 1 | 1 | | | | | |
> | . | | |
>
>
> ======================================================================================================================================================================================================
>
> În mar., 17 oct. 2023 la 12:51, Timur Akhmadeev <timur.akhmadeev_at_gmail.com>
> a scris:
>
>> Does it work with "_px_cdb_view_enabled"=false?
>>
>> On Tue, Oct 17, 2023 at 11:38 AM 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.
>>>
>>>
>>
>> --
>> Regards
>> Timur Akhmadeev
>>
>

-- 
Regards
Timur Akhmadeev

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

Received on Tue Oct 17 2023 - 16:14:12 CEST

This message: [ Message body ]
Next message: Jared Still: "Re: slow query on CDB_FREE_SPACE"
Previous message: Jonathan Lewis: "Re: slow query on CDB_FREE_SPACE"
In reply to Laurentiu Oprea: "Re: slow query on CDB_FREE_SPACE"
Next in thread: Mladen Gogala: "Re: slow query on CDB_FREE_SPACE"
Reply: Mladen Gogala: "Re: slow query on CDB_FREE_SPACE"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message