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@xxxxxxxxx>
a scris:
Does it work with "_px_cdb_view_enabled"=false?
On Tue, Oct 17, 2023 at 11:38 AM Laurentiu Oprea <
laurentiu.oprea06@xxxxxxxxx> 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
