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:12:43 +0800
Message-ID: <CABpiuuQ_W4PaD0Ni11xhq=cJks+jfz2ub4UH=juZ3yTVHCLZeQ_at_mail.gmail.com>
>
Date: Fri, 13 Jan 2023 23:12:43 +0800
Message-ID: <CABpiuuQ_W4PaD0Ni11xhq=cJks+jfz2ub4UH=juZ3yTVHCLZeQ_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 | | | |
> | 37 | UNION-ALL |
> | 4072 | | 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* > WHERE f.tablespace_name = t.tablespace_name > AND *f.file_id = s.file_id* > AND t.contents = 'PERMANENT'
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*
WHERE f.tablespace_name = t.tablespace_name
> AND *f.file_id = s.file_id*
> AND t.contents = 'PERMANENT'
Nice, only took *5 seconds* returning the result.
Thanks for your help.
Best Regards
Quanwen Zhao
Quanwen Zhao <quanwenzhao_at_gmail.com> 于2023年1月13日周五 23:02写道：
> 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 | | | | >> | 37 | UNION-ALL | >> | 4072 | | 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* >> WHERE f.tablespace_name = t.tablespace_name >> AND *f.file_id = s.file_id* >> AND t.contents = 'PERMANENT' > > > 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* > > WHERE f.tablespace_name = t.tablespace_name >> AND *f.file_id = s.file_id* >> AND t.contents = 'PERMANENT' > > > Nice, only took *5 seconds* returning the result. > > Thanks for your help. > > Best Regards > Quanwen Zhao > > Quanwen Zhao <quanwenzhao_at_gmail.com> 于2023年1月10日周二 20:30写道： > >> Using with ... as (...) to rewrite the query is still spending so long. >> >> So I've also run that SQL in my test environment (rac 19.13, cdb), >> especially created about 200 tablespaces, each one contains 1 datafile >> only. Very similar case, the query is really slow. Deeply suspects whether >> it is an oracle bug. select count(*) from dba_data_files took 5 minutes to >> return 192 rows. >> >> >> 在 2023年1月10日星期二，Gmail <nilosegura_at_gmail.com> 写道： >> > Try with the optimizer set to 12.1.0.2 with an alter session set >> optimizer_features_enable=‘12.1.0.2’; >> > And then run the query … >> > Regards >> > >> > Sent from my iPhone >> > >> > On 9 Jan 2023, at 17:06, Quanwen Zhao <quanwenzhao_at_gmail.com> wrote: >> > >> > 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#")) >> >>>>> 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 >> >> >> >> -- >> >> Niall Litchfield >> >> Oracle DBA >> >> http://www.orawin.info > >
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 13 2023 - 16:12:43 CET