Why the SQL query about oracle datafile used rate is slow?
Date: Mon, 9 Jan 2023 22:08:41 +0800
Message-ID: <CABpiuuTK6fdMZ-oH0PeXLJDvVBz7e=2b=nompE8+rEQh+Y4yoQ_at_mail.gmail.com>
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
running the previous SQL. (at the end of SQL, it'll show
SET PAGESIZE 400
SET SERVEROUTPUT OFF
SET FEEDBACK ON SQL_ID
ALTER SESSION SET statistics_level = all;
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
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.
Best Regards
Quanwen Zhao
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 09 2023 - 15:08:41 CET