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

I guess that basically could be something like:

with my_dba_free_space as (
  select /*+ materialize */

     file_id, relative_fno, tablespace_name, sum(blocks) from dba_fre_space
     group by ...

Regards
Jonathan Lewis

On Mon, 9 Jan 2023 at 15:06, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> The index is on tablespace number (KTFBFETSN), so if you have a larger
> number of files for a small number of tablespaces that might explain the
> amount of time required to run the scan 4,000 times. You could also check
> the number of rows per tablespace, and rows per tablespace and file in the
> x$.
>
> If you can rewrite the query you could introduce a WITH subquery with the
> /*+ materialize */ hint to calculate the tablespace number, file number and
> sum of free space just once.
>
> Regards
> Jonathan Lewis
>
>
>
> On Mon, 9 Jan 2023 at 14:15, Quanwen Zhao <quanwenzhao_at_gmail.com> wrote:
>
>> 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