Re: Why the SQL query about oracle datafile used rate is slow?

Here's the attachment for the SQL real execution plan.

Quanwen Zhao <quanwenzhao_at_gmail.com> 于2023年1月9日周一 22:08写道：

> Hello my oracle friends :-),
>
> Happy New Year!
>
> My client told me their oracle rac 19.13 (non-cdb)'s a SQL query about
> datafile used rate is so very slow. The SQL is like this:
>
> SELECT * FROM
>> (SELECT rownum AS rnum, r.*
>> FROM (
>> SELECT f.file_id,
>> f.file_name,
>> f.tablespace_name,
>> round(f.bytes/1024/1024, 2) filesize_mb,
>> f.blocks,
>> round((f.bytes-nvl(s.bytes, 0))/1024/1024, 2) used_mb,
>> CASE f.autoextensible WHEN 'NO' THEN
>> round((f.bytes-nvl(s.bytes, 0))/f.bytes, 4)*100
>> ELSE round((f.bytes-nvl(s.bytes, 0))/f.maxbytes, 4)*100
>> END AS used_ratio,
>> CASE f.autoextensible WHEN 'NO' THEN
>> round(f.bytes/1024/1024, 2)
>> ELSE round(f.maxbytes/1024/1024, 2)
>> END AS maxsize_mb,
>> CASE f.autoextensible WHEN 'NO' THEN f.blocks
>> ELSE f.maxblocks
>> END AS maxblocks,
>> f.autoextensible,
>> f.online_status,
>> 'NORMAL' AS types
>> FROM dba_data_files f, dba_tablespaces t, (SELECT file_id,
>> SUM(bytes) bytes FROM dba_free_space GROUP BY file_id) s
>> WHERE f.tablespace_name = t.tablespace_name
>> AND f.file_id = s.file_id
>> AND t.contents = 'PERMANENT'
>> ORDER BY f.file_id
>> ) r
>> WHERE rownum <= 30
>> ) WHERE rnum >= 1;
>>
>
> It took *17 minutes and 20 second* returning 30 rows.
>
> I've pushed the row-source execution plan from memory, here's the some
> steps:
>
> SET LINESIZE 400
> SET PAGESIZE 400
>
> SET SERVEROUTPUT OFF
>
> SET FEEDBACK ON SQL_ID
>
> ALTER SESSION SET statistics_level = all;
>
> running the previous SQL. (at the end of SQL, it'll show
> *SQL_ID: 2c4s3xc4fuujy*).
>
> SELECT * FROM table(DBMS_XPLAN.display_cursor('2c4s3xc4fuujy', NULL,
> 'ALLSTATS LAST'));
>
> Plan hash value: 3170555723
>>
>>
>> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> | Id | Operation | Name
>> | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem
>> | 1Mem | Used-Mem |
>>
>> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> | 0 | SELECT STATEMENT |
>> | 1 | | 30 |00:17:20.77 | 41M| 505 | |
>> | |
>> |* 1 | VIEW |
>> | 1 | 4 | 30 |00:17:20.77 | 41M| 505 | |
>> | |
>> |* 2 | COUNT STOPKEY |
>> | 1 | | 30 |00:17:20.77 | 41M| 505 | |
>> | |
>> | 3 | VIEW |
>> | 1 | 4 | 30 |00:17:20.77 | 41M| 505 | |
>> | |
>> |* 4 | SORT ORDER BY STOPKEY |
>> | 1 | 4 | 30 |00:17:20.77 | 41M| 505 | 6144 |
>> 6144 | 6144 (0)|
>> | 5 | NESTED LOOPS |
>> | 1 | 4 | 292 |00:17:20.77 | 41M| 505 | |
>> | |
>> |* 6 | HASH JOIN |
>> | 1 | 1 | 4072 |00:00:04.71 |8324 | 0 | 1856K|
>> 1856K| 1333K (0)|
>> | 7 | NESTED LOOPS OUTER |
>> | 1 | 1 | 32 |00:00:00.01 | 94 | 0 | |
>> | |
>> | 8 | NESTED LOOPS |
>> | 1 | 1 | 32 |00:00:00.01 | 93 | 0 | |
>> | |
>> | 9 | FIXED TABLE FULL | X$KCFISTSA
>> | 1 | 6 | 35 |00:00:00.01 | 54 | 0 | |
>> | |
>> |* 10 | TABLE ACCESS CLUSTER | TS$
>> | 35 | 1 | 32 |00:00:00.01 | 39 | 0 | |
>> | |
>> |* 11 | INDEX UNIQUE SCAN | I_TS#
>> | 35 | 1 | 35 |00:00:00.01 | 4 | 0 | |
>> | |
>> |* 12 | INDEX RANGE SCAN | I_IMSVCTS1
>> | 32 | 1 | 0 |00:00:00.01 | 1 | 0 | |
>> | |
>> | 13 | VIEW | DBA_DATA_FILES
>> | 1 | 11 | 4082 |00:00:04.70 |8230 | 0 | |
>> | |
>> | 14 | UNION-ALL |
>> | 1 | | 4082 |00:00:04.69 |8230 | 0 | |
>> | |
>> | 15 | NESTED LOOPS |
>> | 1 | 1 | 0 |00:00:00.01 | 31 | 0 | |
>> | |
>> | 16 | NESTED LOOPS |
>> | 1 | 1 | 0 |00:00:00.01 | 31 | 0 | |
>> | |
>> |* 17 | HASH JOIN |
>> | 1 | 1 | 0 |00:00:00.01 | 31 | 0 | 1162K|
>> 1162K| 514K (0)|
>> |* 18 | TABLE ACCESS FULL | FILE$
>> | 1 | 1 | 0 |00:00:00.01 | 31 | 0 | |
>> | |
>> |* 19 | FIXED TABLE FULL | X$KCCFN
>> | 0 | 14 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> |* 20 | FIXED TABLE FIXED INDEX | X$KCCFE
>> (ind:1) | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
>> | | |
>> | 21 | TABLE ACCESS CLUSTER | TS$
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> |* 22 | INDEX UNIQUE SCAN | I_TS#
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> | 23 | NESTED LOOPS |
>> | 1 | 10 | 4082 |00:00:04.66 |8199 | 0 | |
>> | |
>> | 24 | NESTED LOOPS |
>> | 1 | 10 | 4082 |00:00:04.61 |4113 | 0 | |
>> | |
>> | 25 | NESTED LOOPS |
>> | 1 | 10 | 4082 |00:00:04.36 | 31 | 0 | |
>> | |
>> |* 26 | HASH JOIN |
>> | 1 | 14 | 4082 |00:00:00.03 | 31 | 0 | 1185K|
>> 1185K| 1492K (0)|
>> |* 27 | FIXED TABLE FULL | X$KCCFN
>> | 1 | 14 | 4082 |00:00:00.01 | 0 | 0 | |
>> | |
>> |* 28 | TABLE ACCESS FULL | FILE$
>> | 1 | 3693 | 4082 |00:00:00.01 | 31 | 0 | |
>> | |
>> |* 29 | FIXED TABLE FIXED INDEX | X$KCCFE
>> (ind:1) | 4082 | 1 | 4082 |00:00:04.33 | 0 | 0 |
>> | | |
>> |* 30 | FIXED TABLE FIXED INDEX | X$KTFBHC
>> (ind:1) | 4082 | 1 | 4082 |00:00:00.24 |4082 | 0 |
>> | | |
>> | 31 | TABLE ACCESS CLUSTER | TS$
>> | 4082 | 1 | 4082 |00:00:00.05 |4086 | 0 | |
>> | |
>> |* 32 | INDEX UNIQUE SCAN | I_TS#
>> | 4082 | 1 | 4082 |00:00:00.02 | 4 | 0 | |
>> | |
>> | 33 | VIEW PUSHED PREDICATE |
>> | 4072 | 1 | 292 |00:17:16.05 | 41M| 505 | |
>> | |
>> |* 34 | FILTER |
>> | 4072 | | 292 |00:17:16.05 | 41M| 505 | |
>> | |
>> | 35 | SORT AGGREGATE |
>> | 4072 | 1 | 4072 |00:17:16.04 | 41M| 505 | |
>> | |
>> | 36 | VIEW | DBA_FREE_SPACE
>> | 4072 | 5 | 8368 |00:17:16.01 | 41M| 505 | |
>> | |
>> | 37 | UNION-ALL |
>> | 4072 | | 8368 |00:17:16.01 | 41M| 505 | |
>> | |
>> | 38 | NESTED LOOPS |
>> | 4072 | 1 | 0 |00:00:00.06 | 16335 | 0 |
>> | | |
>> | 39 | NESTED LOOPS |
>> | 4072 | 1 | 0 |00:00:00.06 | 16335 | 0 |
>> | | |
>> | 40 | TABLE ACCESS BY INDEX ROWID | FILE$
>> | 4072 | 1 | 4072 |00:00:00.03 |8191 | 0 | |
>> | |
>> |* 41 | INDEX UNIQUE SCAN | I_FILE1
>> | 4072 | 1 | 4072 |00:00:00.01 |4119 | 0 | |
>> | |
>> |* 42 | TABLE ACCESS CLUSTER | FET$
>> | 4072 | 1 | 0 |00:00:00.02 |8144 | 0 | |
>> | |
>> |* 43 | INDEX UNIQUE SCAN | I_TS#
>> | 4072 | 1 | 4072 |00:00:00.01 |4072 | 0 | |
>> | |
>> |* 44 | TABLE ACCESS CLUSTER | TS$
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> |* 45 | INDEX UNIQUE SCAN | I_TS#
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> | 46 | NESTED LOOPS |
>> | 4072 | 1 | 8368 |00:17:15.54 | 41M| 497 | |
>> | |
>> | 47 | NESTED LOOPS |
>> | 4072 | 1 | 8368 |00:17:15.52 | 41M| 497 | |
>> | |
>> | 48 | TABLE ACCESS BY INDEX ROWID | FILE$
>> | 4072 | 1 | 4072 |00:00:00.02 |8191 | 0 | |
>> | |
>> |* 49 | INDEX UNIQUE SCAN | I_FILE1
>> | 4072 | 1 | 4072 |00:00:00.01 |4119 | 0 | |
>> | |
>> *|* 50 | FIXED TABLE FIXED INDEX | X$KTFBFE
>> (ind:1) | 4072 | 1 | 8368 |00:17:15.49 | 41M| 497 |
>> | | |*
>> |* 51 | TABLE ACCESS CLUSTER | TS$
>> | 8368 | 1 | 8368 |00:00:00.02 |8663 | 0 | |
>> | |
>> |* 52 | INDEX UNIQUE SCAN | I_TS#
>> | 8368 | 1 | 8368 |00:00:00.01 | 295 | 0 | |
>> | |
>> | 53 | NESTED LOOPS |
>> | 4072 | 1 | 0 |00:00:00.08 | 12263 | 0 |
>> | | |
>> | 54 | NESTED LOOPS |
>> | 4072 | 1 | 0 |00:00:00.07 | 12263 | 0 |
>> | | |
>> | 55 | NESTED LOOPS |
>> | 4072 | 1 | 0 |00:00:00.07 | 12263 | 0 |
>> | | |
>> | 56 | TABLE ACCESS BY INDEX ROWID | FILE$
>> | 4072 | 1 | 4072 |00:00:00.05 |8191 | 0 | |
>> | |
>> |* 57 | INDEX UNIQUE SCAN | I_FILE1
>> | 4072 | 1 | 4072 |00:00:00.03 |4119 | 0 | |
>> | |
>> | 58 | TABLE ACCESS BY INDEX ROWID BATCHED| RECYCLEBIN$
>> | 4072 | 1 | 0 |00:00:00.02 |4072 | 0 | |
>> | |
>> |* 59 | INDEX RANGE SCAN | RECYCLEBIN$_TS
>> | 4072 | 1 | 0 |00:00:00.01 |4072 | 0 | |
>> | |
>> |* 60 | TABLE ACCESS CLUSTER | TS$
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> |* 61 | INDEX UNIQUE SCAN | I_TS#
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> |* 62 | FIXED TABLE FIXED INDEX | X$KTFBUE
>> (ind:1) | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
>> | | |
>> | 63 | NESTED LOOPS |
>> | 4072 | 1 | 0 |00:00:00.17 | 157K| 8 | |
>> | |
>> | 64 | NESTED LOOPS |
>> | 4072 | 1 | 0 |00:00:00.17 | 157K| 8 | |
>> | |
>> | 65 | NESTED LOOPS |
>> | 4072 | 1 | 0 |00:00:00.17 | 157K| 8 | |
>> | |
>> | 66 | TABLE ACCESS BY INDEX ROWID | FILE$
>> | 4072 | 1 | 4072 |00:00:00.01 |8191 | 0 | |
>> | |
>> |* 67 | INDEX UNIQUE SCAN | I_FILE1
>> | 4072 | 1 | 4072 |00:00:00.01 |4119 | 0 | |
>> | |
>> | 68 | TABLE ACCESS CLUSTER | UET$
>> | 4072 | 1 | 0 |00:00:00.15 | 149K| 8 | |
>> | |
>> |* 69 | INDEX RANGE SCAN | I_FILE#_BLOCK#
>> | 4072 | 1 | 139K|00:00:00.04 |9745 | 0 | |
>> | |
>> |* 70 | TABLE ACCESS BY INDEX ROWID BATCHED | RECYCLEBIN$
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> |* 71 | INDEX RANGE SCAN | RECYCLEBIN$_TS
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> |* 72 | TABLE ACCESS CLUSTER | TS$
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> |* 73 | INDEX UNIQUE SCAN | I_TS#
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> | 74 | NESTED LOOPS |
>> | 4072 | 1 | 0 |00:00:00.10 | 20407 | 0 |
>> | | |
>> | 75 | NESTED LOOPS |
>> | 4072 | 1 | 0 |00:00:00.09 | 20407 | 0 |
>> | | |
>> | 76 | TABLE ACCESS BY INDEX ROWID | FILE$
>> | 4072 | 1 | 4072 |00:00:00.01 |8191 | 0 | |
>> | |
>> |* 77 | INDEX UNIQUE SCAN | I_FILE1
>> | 4072 | 1 | 4072 |00:00:00.01 |4119 | 0 | |
>> | |
>> |* 78 | TABLE ACCESS FULL |
>> NEW_LOST_WRITE_EXTENTS$ | 4072 | 1 | 0 |00:00:00.08 | 12216 |
>> 0 | | | |
>> |* 79 | TABLE ACCESS CLUSTER | TS$
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>> |* 80 | INDEX UNIQUE SCAN | I_TS#
>> | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |
>> | |
>>
>> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> 1 - filter("RNUM">=1)
>> 2 - filter(ROWNUM<=30)
>> 4 - filter(ROWNUM<=30)
>> 6 - access("F"."TABLESPACE_NAME"="TS"."NAME")
>> 10 - filter(("TS"."ONLINE$"<>3 AND
>> DECODE("TS"."CONTENTS$",0,DECODE(BITAND("TS"."FLAGS",4503599627370512),16,'UNDO',4503599627370496,'LOST
>> WRITE
>> PROTECTION','PERMANENT'),1,'TEMPORARY')='PERMANENT' AND
>> BITAND("TS"."FLAGS",2048)<>2048 AND
>> BITAND("TS"."FLAGS",16777216)<>16777216))
>> 11 - access("TS"."TS#"="TSATTR"."TSID")
>> 12 - access("TS"."TS#"="SVC"."TS#")
>> 17 - access("FNFNO"="F"."FILE#")
>> 18 - filter("F"."SPARE1" IS NULL)
>> 19 - filter(("FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND "FNNAM" IS
>> NOT NULL AND BITAND("FNFLG",4)<>4))
>> 20 - filter(("X$KCCFE"."FENUM"="F"."FILE#" AND
>> ("CON_ID"=TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID')) OR "CON_ID" IS NULL)))
>> 22 - access("F"."TS#"="TS"."TS#")
>> 26 - access("FNFNO"="F"."FILE#")
>> 27 - filter(("FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND "FNNAM" IS
>> NOT NULL AND BITAND("FNFLG",4)<>4))
>> 28 - filter("F"."SPARE1" IS NOT NULL)
>> 29 - filter(("X$KCCFE"."FENUM"="F"."FILE#" AND
>> ("CON_ID"=TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID')) OR "CON_ID" IS NULL)))
>> 30 - filter("FNFNO"="HC"."KTFBHCAFNO")
>> 32 - access("HC"."KTFBHCTSN"="TS"."TS#")
>> 34 - filter(COUNT(*)>0)
>> 41 - access("FI"."FILE#"="F"."FILE_ID")
>> 42 - filter("F"."FILE#"="FI"."RELFILE#")
>> 43 - access("F"."TS#"="FI"."TS#")
>> 44 - filter("TS"."BITMAPPED"=0)
>> 45 - access("TS"."TS#"="F"."TS#")
>> 49 - access("FI"."FILE#"="F"."FILE_ID")
>> *50 - filter(("F"."KTFBFETSN"="FI"."TS#" AND
>> "F"."KTFBFEFNO"="FI"."RELFILE#"))*
>> 51 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0
>> AND "TS"."BITMAPPED"<>0 AND
>> BITAND("TS"."FLAGS",4503599627370496)<>4503599627370496))
>> 52 - access("TS"."TS#"="F"."KTFBFETSN")
>> 57 - access("FI"."FILE#"="F"."FILE_ID")
>> 59 - access("RB"."TS#"="FI"."TS#")
>> 60 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0
>> AND "TS"."BITMAPPED"<>0 AND
>> BITAND("TS"."FLAGS",4503599627370496)<>4503599627370496))
>> 61 - access("TS"."TS#"="RB"."TS#")
>> 62 - filter(("U"."KTFBUESEGTSN"="RB"."TS#" AND
>> "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#" AND
>> "U"."KTFBUEFNO"="FI"."RELFILE#"))
>> 67 - access("FI"."FILE#"="F"."FILE_ID")
>> 69 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
>> 70 - filter(("U"."SEGFILE#"="RB"."FILE#" AND
>> "U"."SEGBLOCK#"="RB"."BLOCK#"))
>> 71 - access("U"."TS#"="RB"."TS#")
>> 72 - filter("TS"."BITMAPPED"=0)
>> 73 - access("TS"."TS#"="U"."TS#")
>> 77 - access("FI"."FILE#"="F"."FILE_ID")
>> 78 - filter("F"."EXTENT_DATAFILE_TSID"="FI"."TS#")
>> 79 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND
>> BITAND("TS"."FLAGS",4503599627370496)=4503599627370496 AND
>> "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0))
>> 80 - access("TS"."TS#"="F"."EXTENT_DATAFILE_TSID")
>>
>>
>> 155 rows selected.
>>
>
> I've found the line 50 spent much more time.
>
> *|* 50 | FIXED TABLE FIXED INDEX | X$KTFBFE
>> (ind:1) | 4072 | 1 | 8368 |00:17:15.49 | *
>
>
> Next checking the statistics of X$KTFBFE,
>
> *select LAST_ANALYZED, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN from
>> dba_tab_statistics where owner = 'SYS' and table_name='X$KTFBFE';*
>>
>> LAST_ANALYZED NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
>> ________________ ___________ _________ _______________ ______________
>> *28-JUN-22* *121 *
>> 34
>
>
> But X$KTFBFE has *14128* lines.
>
> *select count(*) from X$KTFBFE;*
>>
>> COUNT(*)
>> --------------
>> *14128*
>>
>
> So gathering its statistics.
>
> *EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SYS', DEGREE => 40,
>> TABNAME => 'X$KTFBFE', ESTIMATE_PERCENT => 100, NO_INVALIDATE => FALSE);*
>>
>
> Rechecking its latest statistics.
>
> select LAST_ANALYZED, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN from
>> dba_tab_statistics where owner = 'SYS' and table_name='X$KTFBFE';
>
>
>> LAST_ANALYZED NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
>> ________________ ___________ _________ _______________ ______________
>> *09-JAN-23* *13679*
>> 33
>
>
> After running that SQL, still need *17 minutes*.
>
> From the preceding SQL execution plan, I've also found out the *starts is
> 4072* for DBA_FREE_SPACE's some base table. So the *inline view "s"*
> (about DBA_FREE_SPACE) in the SQL seems like not to return an entire query
> result set once, afterwards adding the hint "/*+ no_merge */ in the inline
> "s", nevertheless there has no effect.
>
> Have to say, dba_data_files has *4082* lines.
>
> *select count(*) from dba_data_files;*
>>
>> COUNT(*)
>> --------------
>> * 4082*
>>
>
> Has any other good tuning thoughts for you? Thanks for advance!
>
> Best Regards
> Quanwen Zhao
>

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





text/plain attachment: 2c4s3xc4fuujy_execution_plan.txt