Oracle-监控采集表空间SQL涉及dba_free_space查询很慢 一、慢sql 二、问题处理

场景: 客户有一套Oracle 11.2.0.4 RAC ,现有一个监控软件,每天定时登录db执行一批次SQL,这几天采集程序无法正常采集就中断了;

            软件开发人员排查发现,只要查询表空间涉及dba_free_space的SQL执行时间变成了2分钟!采集程序由于查询时间过长,超时中断连接;

具体实际SQL文本不好拿出来发博客,手工执行如下sql需要2分钟!
select count(*) from dba_free_space where tablespace_name='xx';

二、问题处理

2.1  Hint 


参考 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 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.

Oracle 11.2.0.4 确实上述hint 取消了!
新建一个视图,hint加上! SQL执行时间从2分钟-> 0.015s
缺陷: 1.新建了一个视图查询dba_free_space底层基表的记录,带来了其它的风险!
2.由于新建一个视图,因此需要重新授权,以及让业务代码进行调整!!!
优点: 1.开发人员无需了解内部机制hint解决的SQL性能问题,/*+ hint语法能解决但是写入到程序代码改的越多,出错的可能性越大;
2.这种改造SQL查询的结果没有改变,实际结果还是dba_free_space的记录;

2.2 清空回收站

--继续接上面2.1的mos文档
2) Large number of entries in sys.recyclebin$ can slow down the select on dba_free_space. 
3) This is a normal behavior.

Purge the recyclebin.
For example:
SQL> purge recyclebin;
Or, as SYSDBA for system wide purging.
SQL> purge dba_recyclebin;




You can refer to the below document for more information:
10g Recyclebin Features And How To Disable it( _recyclebin )(Doc ID 265253.1)

https://blog.csdn.net/shipeng1022/article/details/77990404
注意:在ORACLE 12C中,各个PDB和CDB之间回收站是私有的,所以对每个PDB回收站进行单独的清空。
SQL> ALTER SESSION SET CONTAINER=ZHIXIN;



Note:  On 10.2/11g, the dropped table 
  does not show up in the *_TABLES views 
  does not show up in *_OBJECTS 
  does show up int the *_SEGMENT views
THE RECYCLE BIN
         The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space
as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace.
Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they
are simply renamed with a prefix of BIN$$. You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before it was dropped.
You can view your dropped tables by querying the
new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the
owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against
a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space
reclamation process if
o A user creates a new table or adds data that causes their quota to be exceeded. o The tablespace needs to extend its file size to accommodate create/insert operations. There are no issues with dropping the table, behaviour wise. It is the same as in 8i / 9i. The space is not released immediately and is
accounted for within the same tablespace / schema after the drop. When we drop a tablespace or a user there is NO recycling of the objects. o Recyclebin does not work for SYS owned objects #这个文档说明回收站功能SYS无法使用??? MOS文档Oracle Database - Enterprise Edition - Version 10.1.0.2 and later 为啥??? 测试一下 SQL> select count(*) from RECYCLEBIN$; COUNT(*) ---------- 0 SQL> create table a(id int) tablespace system; SQL> create table b(id int) tablespace sysaux; SQL> insert into a values(1); SQL> insert into b values(1); SQL> commit; SQL> drop table a; SQL> select count(*) from RECYCLEBIN$; COUNT(*) ---------- 0 SQL> drop table b; SQL> select count(*) from RECYCLEBIN$; COUNT(*) ---------- 1 SQL> select username,default_tablespace from dba_users where username='SYS'; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ SYS SYSTEM SQL> select tablespace_name,CONTENTS,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; TABLESPACE_NAME CONTENTS EXTENT_MAN SEGMEN ------------------------------ --------- ---------- ------ SYSTEM PERMANENT LOCAL MANUAL SYSAUX PERMANENT LOCAL AUTO UNDOTBS1 UNDO LOCAL MANUAL TEMP TEMPORARY LOCAL MANUAL USERS PERMANENT LOCAL AUTO 准确的理解是SYSTEM, 段空间手工管理的方式无法使用回收站的功能! 禁用回收站功能 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH; On 10gR2 and higher; recyclebin is a initialization parameter and bydefault its ON. We can disable recyclebin by using the following commands: SQL> ALTER SESSION SET recyclebin = OFF; SQL> ALTER SYSTEM SET recyclebin = OFF;

!DBA在清空回收站之后,我们再次查询dba_free_space 确实从2分钟->0.001s; 可以解决问题!
优点:1.代码无需调整;
2.开发无需干预,让DBA处理就行;
缺点:1.回收站功能长期关闭,可以避免这个问题! 但是生产环境并不建议关闭这个功能!
2.回收站功能无法关闭的情况下,无法预知什么时候有大量drop table的对象导致查询dba_free_space视图慢再次出现! 问题可能在后续不定期再次出现!

2.3 替换视图

dba_free_space查询目的是计算表空间剩余空间, 以及与dba_data_files 结合算表空间空间信息;

直接替换为dba_segments 通过tablespace_name 一样可以得到结果
SQL替换后,测试执行时间0.09s 也是很快

优点:能解决本次问题;
缺点:1.修改了代码,并且视图不一样,是否影响到了数据准确性,客户可以接收;
2.最大的问题! 当数据库容量一直上涨,其实最后的SQL执行效率会低于dba_free_space的视图查询!!!

2.4 其它可能性

Query Against DBA_FREE_SPACE is Slow After Applying 11.2.0.4 (Doc ID 1904677.1)    
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later


Some queries against the dba_free_space view perform slowly after applying the 11.2.0.4 patchset
The access path for the query shows that it performs a Full Table Scan (FTS) against the fixed table X$KTFBUE: 
STAT #4573482064 id=19 cnt=7586689 pid=18 pos=1 obj=0 op='FIXED TABLE FULL X$KTFBUE
(cr=102462 pr=22059 pw=0 time=129742890 us cost=1 size=151579380 card=7578969)
' Gathering statistics on X$KTFBUE using : EXEC dbms_stats.gather_fixed_objects_stats(); does not help. Emptying the recycle bin (RECYCLEBIN$) does not help either CHANGES Applied the 11.2.0.4 patch set. CAUSE By default, statistics are not gathered for some fixed tables(X$) and thus they use defaults; it may be too expensive to gather statistics or the volatility of the tables is such that any statistics are immediately out-dated. The sys.x$ktfbue table is one such object. SOLUTION In some cases, deleting and regenerating dictionary statistics resolves the slow performance of the queries against DBA_FREE_SPACE: exec DBMS_STATS.DELETE_DICTIONARY_STATS; exec DBMS_STATS.GATHER_DICTIONARY_STATS;

如果检查发现是SQL最慢是消耗在
TABLE FULL X$KTFBUE 基表的全表扫描执行计划中时!
1.清空回收站,无法解决这个问题;
2.收集基表统计信息无效果;
3.可以尝试删除数据字典统计信息后,在重新收集!!! 风险比较大,网上有另外一种替代的解决方法!
参考
https://my.oschina.net/u/4594060/blog/4513301

你可以重新创建一个新的view,比如tiger_free_space,

原hint:/*+ ordered use_nl(u) use_nl(fi) */

替换成下面内容:

/*+

FULL( "RB")

FULL( "TS")

FULL( "U")

INDEX_RS_ASC( "FI" ("FILE$"."TS#" "FILE$"."RELFILE#"))

LEADING( "RB" "TS" "U" "FI" )

USE_HASH( "TS")

USE_NL( "U")

USE_NL( "FI")

*/

这样相当于就不用根据MOS 进行数据字典统计信息删除、重新收集的操作!