在生产环境中，相信大对都会对表空间使用情况进行统计，监控。
然而，在生产环境中，监控我们一台版本为 12.2.0.1 物理备库时，性能极着。主要是在进行单块读。
通过v$active_session_history 定位到具体性能后，然后与性能正常的执行计划进行对比。发现性能慢是因为对 X$KTFBUE 进行全表扫描， 性能快的却是走的索引。
SQL 信息如下：
set linesize 1000
set pagesize 1000
col TABLESPACE_NAME for a25
col autoextensible for a13
col SUM_SPACE(M) for a13
col SUM_BLOCKS for a20
col USED_SPACE(M) for a13
col USED_RATE(%) for a13
col FREE_SPACE(M) for a13
SELECT D.TABLESPACE_NAME,
autoextensible,
SPACE || 'M' "SUM_SPACE(M)",
to_char(BLOCKS) "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS,
AUTOEXTENSIBLE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ,autoextensible) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
正确的执行计划：
Execution Plan ---------------------------------------------------------- Plan hash value: 395386842 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 380 | 28 (11)| 00:00:01 | |* 1 | HASH JOIN OUTER | | 5 | 380 | 28 (11)| 00:00:01 | | 2 | VIEW | | 5 | 230 | 10 (10)| 00:00:01 | | 3 | HASH GROUP BY | | 5 | 185 | 10 (10)| 00:00:01 | | 4 | VIEW | DBA_DATA_FILES | 5 | 185 | 9 (0)| 00:00:01 | | 5 | UNION-ALL | | | | | | | 6 | NESTED LOOPS | | 1 | 89 | 3 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 74 | 2 (0)| 00:00:01 | | 8 | NESTED LOOPS | | 1 | 23 | 2 (0)| 00:00:01 | |* 9 | TABLE ACCESS FULL | FILE$ | 1 | 20 | 2 (0)| 00:00:01 | |* 10 | FIXED TABLE FIXED INDEX| X$KCCFE (ind:1) | 1 | 3 | 0 (0)| 00:00:01 | |* 11 | FIXED TABLE FULL | X$KCCFN | 1 | 51 | 0 (0)| 00:00:01 | | 12 | TABLE ACCESS CLUSTER | TS$ | 1 | 15 | 1 (0)| 00:00:01 | |* 13 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 | | 14 | NESTED LOOPS | | 4 | 380 | 6 (0)| 00:00:01 | | 15 | NESTED LOOPS | | 4 | 320 | 2 (0)| 00:00:01 | | 16 | NESTED LOOPS | | 4 | 308 | 2 (0)| 00:00:01 | | 17 | NESTED LOOPS | | 4 | 240 | 2 (0)| 00:00:01 | |* 18 | TABLE ACCESS FULL | FILE$ | 5 | 45 | 2 (0)| 00:00:01 | |* 19 | FIXED TABLE FULL | X$KCCFN | 1 | 51 | 0 (0)| 00:00:01 | |* 20 | FIXED TABLE FIXED INDEX| X$KTFBHC (ind:1) | 1 | 17 | 0 (0)| 00:00:01 | |* 21 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 3 | 0 (0)| 00:00:01 | | 22 | TABLE ACCESS CLUSTER | TS$ | 1 | 15 | 1 (0)| 00:00:01 | |* 23 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 | | 24 | VIEW | | 6 | 180 | 18 (12)| 00:00:01 | | 25 | HASH GROUP BY | | 6 | 126 | 18 (12)| 00:00:01 | | 26 | VIEW | DBA_FREE_SPACE | 62 | 1302 | 17 (6)| 00:00:01 | | 27 | UNION-ALL | | | | | | | 28 | NESTED LOOPS | | 1 | 64 | 5 (0)| 00:00:01 | | 29 | NESTED LOOPS | | 1 | 45 | 4 (0)| 00:00:01 | | 30 | TABLE ACCESS FULL | FET$ | 1 | 39 | 4 (0)| 00:00:01 | |* 31 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| 00:00:01 | |* 32 | TABLE ACCESS CLUSTER | TS$ | 1 | 19 | 1 (0)| 00:00:01 | | 33 | NESTED LOOPS | | 59 | 2419 | 4 (0)| 00:00:01 | | 34 | NESTED LOOPS | | 59 | 2065 | 4 (0)| 00:00:01 | |* 35 | TABLE ACCESS FULL | TS$ | 5 | 125 | 4 (0)| 00:00:01 | |* 36 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 11 | 110 | 0 (0)| 00:00:01 | |* 37 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| 00:00:01 | | 38 | NESTED LOOPS | | 1 | 88 | 4 (25)| 00:00:01 | | 39 | NESTED LOOPS | | 1 | 82 | 4 (25)| 00:00:01 | | 40 | NESTED LOOPS | | 1 | 64 | 3 (0)| 00:00:01 | | 41 | TABLE ACCESS FULL | RECYCLEBIN$ | 1 | 39 | 2 (0)| 00:00:01 | |* 42 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | 1 (0)| 00:00:01 | |* 43 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 | |* 44 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 18 | 1 (100)| 00:00:01 | #正常的执行计划是走的 FIXED TABLE FIXED INDEX |* 45 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| 00:00:01 | #而性能慢的 是走的 FIXED TABLE FULL | 46 | NESTED LOOPS | | 1 | 116 | 4 (0)| 00:00:01 | | 47 | NESTED LOOPS | | 1 | 97 | 3 (0)| 00:00:01 | | 48 | MERGE JOIN CARTESIAN | | 1 | 45 | 3 (0)| 00:00:01 | | 49 | TABLE ACCESS FULL | RECYCLEBIN$ | 1 | 39 | 2 (0)| 00:00:01 | | 50 | BUFFER SORT | | 5 | 30 | 1 (0)| 00:00:01 | | 51 | INDEX FULL SCAN | I_FILE2 | 5 | 30 | 1 (0)| 00:00:01 | | 52 | TABLE ACCESS CLUSTER | UET$ | 1 | 52 | 0 (0)| 00:00:01 | |* 53 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 | |* 54 | TABLE ACCESS CLUSTER | TS$ | 1 | 19 | 1 (0)| 00:00:01 | |* 55 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------
坏的执行计划：
查询了相关表信息：
SYS@PROD1>select TABLE_NAME ,INDEX_NUMBER,COLUMN_NAME,COLUMN_POSITION from V$INDEXED_FIXED_COLUMN where TABLE_NAME like 'X$KTFBUE%'; TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION ------------------------------ ------------ ------------------------------ --------------- X$KTFBUE 1 KTFBUESEGTSN 0 X$KTFBUE 1 KTFBUESEGFNO 1 X$KTFBUE 1 KTFBUESEGBNO 2 SYS@PROD1>select NAME,OBJECT_ID,TYPE,TABLE_NUM from V$FIXED_TABLE where name like 'X$KTFBUE%'; NAME OBJECT_ID TYPE TABLE_NUM ------------------------------ ---------- ----- ---------- X$KTFBUE 4294951517 TABLE 476
注意：由于是FIXED OBJECT (固定对象)，在dba_objects,dba_indexes 都查不到相关信息。
解决思路： 1. 清理回收站
2. 检查是否有类似 bug
3. 通过 hint 方式，强制让执行计划走 索引（测试，）
4. 通过 收集统计信息 ，让其自行改变执行计划走索引
解决： 1. 清理回收站
说明：由于是物理备库，所在以主库上进行清理 回收站后 purge dba_recyclebin ， select count(*) from dba_recyclebin ;
a. 性能果然有所提升 ，但是仍然是走的全表扫描。
b. 当回收站 又有对象时，性能又马上降下来。
注意：1. 模拟回收站时， 需要以普通用户进行drop， sys 用户drop 的对象不进入回收站 ；
2. 系统表空间不放入回收站，Flashback Drop 用于非系统表空间和本地管理的表空间
总结： 回收站会影响 DBA_FREE_SPACE 视图的统计 ， 同时由于执行计划没有变，且问题反复出现，所有根本问题没有解决。
2. 检查是否有类似 bug
说明，在MOS 上确实是有类似bug ,X$KTFBUE FULL SCAN 的bug, 但是该类BUG 主要是针对12.1 及以前的版本。
3.准备手动创建 DBA_FREE_SPACE2 视图，以强制X$KTFBUE 走索引 ，目前通过测试，还没有达到效果。
4. 尝试收集统计信息。
说明，由于是物理备库，在收集的时候就报错了，所以在主库上收集
a. 由于是fixed 表， 所以我首先执行的是 exec dbms_stats.GATHER_FIXED_OBJECTS_STATS() ;
但是，执先结束，测试，仍然没有走索引。
b. 单独指定该表收集， exec dbms_stats.gather_table_stats('SYS','X$KTFBUE') ;
在次执行的时候 ，主备库都已走索引。（原来主库统计容量的时候也是走的全表）
总结： 1. fixed 类型的性能问题， 尽量通过收集相应统计信息来优化。
2. 统计信息 记录系统表空间里， 相当于 物理备库 也会同步主库的 统计信息