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

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

        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

        SELECT
            FILE_ID
          , SUM(BYTES) BYTES
        FROM
            DBA_FREE_SPACE
        GROUP BY
            FILE_ID
    )               S

    F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND F.FILE_ID = S.FILE_ID
AND T.CONTENTS = 'PERMANENT'

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#"))
>> 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
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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