Re: Why the SQL query about oracle datafile used rate is slow?
From: Quanwen Zhao <quanwenzhao_at_gmail.com>
Date: Fri, 13 Jan 2023 23:27:12 +0800
Message-ID: <CABpiuuSSCx3KtfieU7kZFDfa0CM5jvd7E_J0GSJjpod4Zeeimw_at_mail.gmail.com>
Date: Fri, 13 Jan 2023 23:27:12 +0800
Message-ID: <CABpiuuSSCx3KtfieU7kZFDfa0CM5jvd7E_J0GSJjpod4Zeeimw_at_mail.gmail.com>
Yep, it's not a bug! I went to the client's office yesterday. Observed the
system has about *40 tablespaces*, each tablespace has contained more than *300
number of datafiles*.
The biggest tablespace has *15 TB* and the smallest one has *1 TB*.
From the real execution plan, I've found the following lines:
| 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 | | | |
As you can see line 33 and 36, *VIEW PUSHED PREDICATE* to VIEW *DBA_FREE_SPACE* suddenly attracts my attention.
The original SQL is as below:
SELECT ...... 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' >
Thus, f.file_id = s.file_id has been pushed to s but we expect oracle not to push predicate f.file_id = s.file_id to s. Then I added the hint *no_merge* and *no_push_pred* to the original SQL.
SELECT */*+ no_merge(s) no_push_pred(s) */* ......
FROM dba_data_files f, dba_tablespaces t, (SELECT file_id, SUM(bytes) bytes > FROM dba_free_space GROUP BY file_id) s > ......
Unfortunately it has not any effect (still spent *17 minutes*) on my client's production environment, next adjusted the same hint in the s.
SELECT ...... FROM dba_data_files f, dba_tablespaces t, (SELECT /*+ no_merge no_push_pred > */ file_id, SUM(bytes) bytes FROM dba_free_space GROUP BY file_id) s
......
Nice, only took *5 seconds* returning the result.
Thanks for your help.
Best Regards
Quanwen Zhao
Jonathan Lewis <jlewisoracle_at_gmail.com> 于2023年1月9日周一 23:14写道：
>
> 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-lReceived on Fri Jan 13 2023 - 16:27:12 CET