Re: Why the SQL query about oracle datafile used rate is slow?
From: Quanwen Zhao <quanwenzhao_at_gmail.com>
Date: Tue, 10 Jan 2023 00:05:06 +0800
Message-ID: <CABpiuuR_5NZT1pOHaq9qsfh5vXerFvT5y1=4+qwGtPyh=a0P=Q_at_mail.gmail.com>
>>
>> Is the *actual* query the inner one
>>
>> SELECT
>> F.FILE_ID
>> , F.FILE_NAME
>> , F.TABLESPACE_NAME
>> , ROUND( F.BYTES / 1024 / 1024, 2) AS FILESIZE_MB
>> , F.BLOCKS
>> , ROUND((F.BYTES - NVL(S.BYTES, 0)) / 1024 / 1024, 2) AS 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;
>>
>>
>> if not I don't see the point of the query at all.:)
>>
>> The threats to free space queries are usually objects in the recycle bin
as Dom says or lots of pockets of free space among the data files, several thousand data files is perhaps surprising in the era of bigfile tablespaces. But honestly, my first thought would be does the use of DBA_TABLESPACE_USAGE_METRICS make sense for the client? There have been versions (12.1 for example) where the output of that view was unreliable and/or excluded tablespaces entirely :(, but I believe that would be the best starting point for tablespace usage monitoring on current versions..
>> On Mon, Jan 9, 2023 at 2:10 PM Quanwen Zhao <quanwenzhao_at_gmail.com>
wrote:
>>>
>>> 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
>>>> | 0 | SELECT STATEMENT |
>>>>
>>>>
>>>> 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
>>>> 70 - filter(("U"."SEGFILE#"="RB"."FILE#" AND
>>>> 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
>>>> 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
>>> 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
>>
>> --
>> Niall Litchfield
>> Oracle DBA
>> http://www.orawin.info
Date: Tue, 10 Jan 2023 00:05:06 +0800
Message-ID: <CABpiuuR_5NZT1pOHaq9qsfh5vXerFvT5y1=4+qwGtPyh=a0P=Q_at_mail.gmail.com>
Thanks, everyone!
Recycle bin is zero. JL's suggestion is probably good, I'll rewrite to try it. Now (4072) s inner join (4082) dba_data_files.
在 2023年1月9日星期一， <niall.litchfield_at_gmail.com> 写道： > I think I'd use GATHER_DICTIONARY_STATS rather than gather_table_stats as well.
> On Mon, Jan 9, 2023 at 2:51 PM <niall.litchfield_at_gmail.com> wrote:
>>
>> Is the *actual* query the inner one
>>
>> SELECT
>> F.FILE_ID
>> , F.FILE_NAME
>> , F.TABLESPACE_NAME
>> , ROUND( F.BYTES / 1024 / 1024, 2) AS FILESIZE_MB
>> , F.BLOCKS
>> , ROUND((F.BYTES - NVL(S.BYTES, 0)) / 1024 / 1024, 2) AS 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;
>>
>>
>> if not I don't see the point of the query at all.:)
>>
>> The threats to free space queries are usually objects in the recycle bin
as Dom says or lots of pockets of free space among the data files, several thousand data files is perhaps surprising in the era of bigfile tablespaces. But honestly, my first thought would be does the use of DBA_TABLESPACE_USAGE_METRICS make sense for the client? There have been versions (12.1 for example) where the output of that view was unreliable and/or excluded tablespaces entirely :(, but I believe that would be the best starting point for tablespace usage monitoring on current versions..
>> On Mon, Jan 9, 2023 at 2:10 PM Quanwen Zhao <quanwenzhao_at_gmail.com>
wrote:
>>>
>>> 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#"))>>>> 69 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
>>>> 67 - access("FI"."FILE#"="F"."FILE_ID")
>>>> 70 - filter(("U"."SEGFILE#"="RB"."FILE#" AND
"U"."SEGBLOCK#"="RB"."BLOCK#"))>>>> 78 - filter("F"."EXTENT_DATAFILE_TSID"="FI"."TS#")
>>>> 71 - access("U"."TS#"="RB"."TS#")
>>>> 72 - filter("TS"."BITMAPPED"=0)
>>>> 73 - access("TS"."TS#"="U"."TS#")
>>>> 77 - access("FI"."FILE#"="F"."FILE_ID")
>>>> 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
>>
>> --
>> Niall Litchfield
>> Oracle DBA
>> http://www.orawin.info
> > -- > Niall Litchfield > Oracle DBA > http://www.orawin.info
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 09 2023 - 17:05:06 CET