Re: slow query on CDB_FREE_SPACE
Date: Tue, 17 Oct 2023 16:03:50 +0300
Message-ID: <CA+riqSWss_xynD=syiQ15vx43gATRUTTuVTtmKLyABh7U+hKLw_at_mail.gmail.com>
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 |
| | | |
| 0 | SELECT STATEMENT |
| 1 | HASH GROUP BY | |
Rows | Cost | Time | Start | Execs | Rows | Read | Read |
Mem | Activity | Activity Detail |
(Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
(Max) | (%) | (# samples) |
| | | 1 | +0 | 1 | 15 | |
| . | | |
15 | 56 | 1 | +0 | 1 | 15 | | | 1MB
| 4 | NESTED LOOPS |
| | |
| 2 | VIEW | DBA_FREE_SPACE |
21453 | 54 | 1 | +0 | 1 | 2122 | | | .
| | |
| 3 | UNION-ALL |
| | | 1 | +0 | 1 | 2122 | |
| . | | |
| 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 >
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 17 2023 - 15:03:50 CEST