这个是一个常见的问题,理论上应该也算是一个bug,在oracle10g,到19c,我都曾经遇到过;今天在给两套新建的19C RAC添加监控脚本时,又发现了这个问题,在这里记录一下。
Symptoms
环境:Centos 7.9,Oracle 19.24 RAC
在给两套rac添加tablespace监控脚本,在确认脚本是否正常,其中一套执行查询tablespace使用情况,可以秒出结果,但是另外一套执行查询确要耗时接近1分钟。
检查tablespace 使用情况脚本sql如下
SELECT t.*
FROM (SELECT a.tablespace_name
,a.unalloc_size
,nvl(f.free_size, 0) free_size
,a.used_size - nvl(f.free_size, 0) used_size
,round((a.used_size - nvl(f.free_size, 0)) /
(a.unalloc_size + a.used_size)
,2) capacity
FROM (SELECT tablespace_name
,round(SUM(bytes) / 1024 / 1024) free_size
FROM dba_free_space
GROUP BY tablespace_name) f
,(SELECT tablespace_name
,round(SUM(user_bytes) / 1024 / 1024) used_size
,round(SUM(decode(autoextensible
,'YES'
,decode(sign(maxbytes - user_bytes)
,-1
,0
,maxbytes - user_bytes)
,0)) / 1024 / 1024) unalloc_size
FROM dba_data_files
GROUP BY tablespace_name) a
WHERE 1 = 1
AND a.tablespace_name = f.tablespace_name(+)) t
WHERE capacity >= 0.85
AND (unalloc_size + free_size) < 4000
AND (unalloc_size + free_size) < used_size / 2
ORDER BY capacity DESC;
异常查询耗时接近1分钟
考虑到前面的sql只使用到了DBA_FREE_SPACE和DBA_DATA_FILES两个视图,分别单独查询这两个视图,发现单独查询DBA_FREE_SPACE时有问题,也就是问题出在这个视图上
查看DBA_FREE_SPACE具体的sql语句 如下 ,看到这里的视图使用sys.recyclebin$,这样我想起了之前曾经处理过这个问题,就是和recyclebin有关!
SQL> set long 10000
SQL> select text from dba_views where view_name='DBA_FREE_SPACE';
TEXT
--------------------------------------------------------------------------------
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select
ts.name, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
TEXT
--------------------------------------------------------------------------------
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
and bitand(ts.flags, 4503599627370496) <> 4503599627370496
union all
select
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
TEXT
--------------------------------------------------------------------------------
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and u.ktfbuefno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
and bitand(ts.flags, 4503599627370496) <> 4503599627370496
union all
select ts.name, fi.file#, u.block#,
u.length * ts.blocksize, u.length, u.file#
TEXT
--------------------------------------------------------------------------------
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0
union all
select
ts.name, fi.file#, f.extent_start,
TEXT
--------------------------------------------------------------------------------
(f.extent_length_blocks_2K /(ts.blocksize/2048)) * ts.blocksize,
(f.extent_length_blocks_2K / (ts.blocksize/2048)), fi.relfile#
from sys.ts$ ts, sys.new_lost_write_extents$ f, sys.file$ fi
where ts.ts# = f.extent_datafile_tsid
and f.extent_datafile_tsid = fi.ts#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
and bitand(ts.flags, 4503599627370496) = 4503599627370496
再检查sql的执行计划
SQL> set serverout off
SQL> select /*+ gather_plan_statistics */ tablespace_name,
2 sum(bytes) tot_free,
3 count(*) chunks
4 from dba_free_space
5 group by tablespace_name ;
TABLESPACE_NAME TOT_FREE CHUNKS
-------------------- ---------- ----------
SYSTEM 3866624 2
DEMO 103809024 1
SYSAUX 103677952 1
UNDOTBS1 2698706944 270
ASKTOM 122945536 102
USERS 198705152 38
LARGETS 1.3993E+10 8
7 rows selected.
SQL_ID db1x4q4n8kgrs, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ tablespace_name, sum(bytes)
tot_free, count(*) chunks from dba_free_space group by
tablespace_name
Plan hash value: 190806552
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 |00:00:47.22 | 699K| 244K| | | |
| 1 | HASH GROUP BY | | 1 | 9 | 7 |00:00:47.22 | 699K| 244K| 1056K| 1056K| 1013K (0)|
| 2 | VIEW | DBA_FREE_SPACE | 1 | 14 | 422 |00:00:00.01 | 699K| 244K| | | |
| 3 | UNION-ALL | | 1 | | 422 |00:00:00.01 | 699K| 244K| | | |
| 4 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 12 | 0 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 12 | 0 | | | |
|* 6 | INDEX FULL SCAN | I_FILE2 | 1 | 3 | 7 |00:00:00.01 | 1 | 0 | | | |
|* 7 | TABLE ACCESS CLUSTER | FET$ | 7 | 1 | 0 |00:00:00.01 | 11 | 0 | | | |
|* 8 | INDEX UNIQUE SCAN | I_TS# | 7 | 1 | 7 |00:00:00.01 | 4 | 0 | | | |
|* 9 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 10 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 11 | HASH JOIN | | 1 | 1 | 307 |00:00:00.01 | 67 | 0 | 1797K| 1797K| 1188K (0)|
| 12 | NESTED LOOPS | | 1 | 1 | 307 |00:00:00.01 | 50 | 0 | | | |
|* 13 | FIXED TABLE FULL | X$KTFBFE | 1 | 8 | 307 |00:00:00.01 | 46 | 0 | | | |
|* 14 | INDEX UNIQUE SCAN | I_FILE2 | 307 | 1 | 307 |00:00:00.01 | 4 | 0 | | | |
|* 15 | TABLE ACCESS FULL | TS$ | 1 | 1 | 7 |00:00:00.01 | 17 | 0 | | | |
| 16 | NESTED LOOPS | | 1 | 10 | 115 |00:01:55.93 | 699K| 244K| | | |
|* 17 | HASH JOIN | | 1 | 17 | 46 |00:00:00.01 | 22 | 0 | 1316K| 1316K| 778K (0)|
| 18 | NESTED LOOPS | | 1 | 19 | 46 |00:00:00.01 | 5 | 0 | | | |
| 19 | NESTED LOOPS | | 1 | 72 | 46 |00:00:00.01 | 4 | 0 | | | |
|* 20 | INDEX FULL SCAN | I_FILE2 | 1 | 3 | 7 |00:00:00.01 | 1 | 0 | | | |
|* 21 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 7 | 24 | 46 |00:00:00.01 | 3 | 0 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 46 | 7 | 46 |00:00:00.01 | 1 | 0 | | | |
|* 23 | TABLE ACCESS FULL | TS$ | 1 | 9 | 7 |00:00:00.01 | 17 | 0 | | | |
|* 24 | FIXED TABLE FULL | X$KTFBUE | 46 | 1 | 115 |00:00:47.14 | 699K| 244K| | | |
| 25 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 284 | 0 | | | |
| 26 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 284 | 0 | | | |
| 27 | MERGE JOIN CARTESIAN | | 1 | 138 | 336 |00:00:00.01 | 3 | 0 | | | |
|* 28 | INDEX FULL SCAN | I_FILE2 | 1 | 3 | 7 |00:00:00.01 | 1 | 0 | | | |
| 29 | BUFFER SORT | | 7 | 48 | 336 |00:00:00.01 | 2 | 0 | 2048 | 2048 | 2048 (0)|
| 30 | TABLE ACCESS FULL | RECYCLEBIN$ | 1 | 48 | 48 |00:00:00.01 | 2 | 0 | | | |
| 31 | TABLE ACCESS CLUSTER | UET$ | 336 | 1 | 0 |00:00:00.01 | 281 | 0 | | | |
|* 32 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 336 | 1 | 41 |00:00:00.01 | 240 | 0 | | | |
|* 33 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 34 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 35 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 36 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 37 | TABLE ACCESS FULL | NEW_LOST_WRITE_EXTENTS$ | 1 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 38 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 39 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 40 | INDEX RANGE SCAN | I_FILE2 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(("FI"."TS#" IS NOT NULL AND "FI"."RELFILE#" IS NOT NULL))
7 - filter("F"."FILE#"="FI"."RELFILE#")
8 - access("F"."TS#"="FI"."TS#")
9 - filter("TS"."BITMAPPED"=0)
10 - access("TS"."TS#"="F"."TS#")
11 - access("TS"."TS#"="KTFBFETSN")
13 - filter(("CON_ID"=0 OR "CON_ID"=3))
14 - access("KTFBFETSN"="FI"."TS#" AND "KTFBFEFNO"="FI"."RELFILE#")
filter(("FI"."TS#" IS NOT NULL AND "FI"."RELFILE#" IS NOT NULL))
15 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND
BITAND("TS"."FLAGS",4503599627370496)<>4503599627370496))
17 - access("TS"."TS#"="RB"."TS#")
20 - filter(("FI"."TS#" IS NOT NULL AND "FI"."RELFILE#" IS NOT NULL))
21 - access("RB"."TS#"="FI"."TS#")
23 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND
BITAND("TS"."FLAGS",4503599627370496)<>4503599627370496))
24 - filter((INTERNAL_FUNCTION("CON_ID") AND "KTFBUESEGBNO"="RB"."BLOCK#" AND "KTFBUEFNO"="FI"."RELFILE#" AND "KTFBUESEGFNO"="RB"."FILE#" AND
"KTFBUESEGTSN"="RB"."TS#"))
28 - filter(("FI"."TS#" IS NOT NULL AND "FI"."RELFILE#" IS NOT NULL))
32 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
filter(("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#"))
33 - filter("TS"."BITMAPPED"=0)
34 - access("TS"."TS#"="U"."TS#")
38 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND BITAND("TS"."FLAGS",4503599627370496)=4503599627370496 AND "TS"."CONTENTS$"=0 AND
"TS"."BITMAPPED"<>0))
39 - access("TS"."TS#"="F"."EXTENT_DATAFILE_TSID")
40 - access("F"."EXTENT_DATAFILE_TSID"="FI"."TS#")
filter("FI"."TS#" IS NOT NULL)
注意,第16行是一个嵌套LOOP,它有244K读取的累积总数。然后,我将其跟踪到第24行,这是对X$KTFBUE结构的完整扫描,但关键是该扫描执行了46次。 第22行,您可以看到这46次执行是来自我的RECYCLEBIN$。以上基本可以看出是和回收站有关了。
Cause
查询MOS 检索DBA_FREE_SPACE select slow就可以找到如下文档
参考文档Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)
1) In release 10g, the view dba_free_space is modified to access sys.recyclebin$ also.
SQL> select text from dba_views where view_name='DBA_FREE_SPACE';
TEXT
--------------------------------------------------------------------------------
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
ts.name, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = u.ktfbuesegtsn
and u.ktfbuesegtsn = fi.ts#
and u.ktfbuesegfno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, u.block#,
u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0
2) Large number of entries in sys.recyclebin$ can slow down the select on dba_free_space.
3) This is a normal behavior.
Solution
Note for 11g database: the dba_free_space view doesn't contain hint which could also affected by the issue. In case there is only few entries in recyclebin, you may want to gather underlying stats of tables/dictionary to get better performance.
Purge the recyclebin.
For example:
SQL> purge recyclebin;
Recyclebin purged.
Or, as SYSDBA for system wide purging.
SQL> purge dba_recyclebin;
Recyclebin purged.
虽然oracle10g和19c 视图DBA_FREE_SPACE稍有变化,但是都是通过recyclebin来实现的,理论上出现的问题也是一样的,我在出问题的环境中执行了 purge recyclebin;再次查询就恢复正常了
后记:
这应该算是一个bug,而且贯穿了oracle 10-19这个几个大版本,我这套rac还是打了几乎最新的19.24的补丁(最新为19.25),新上线没几周的库,回收站也没有什么东西,还是触发了这个问题,如果遇到这个问题处理也比较简单,清空一下回收站即可。