表空间使用率查询慢的原因及解决方法
表空间使用率查询脚本：
1 set pagesize 999 linesize 120 2 column tablespace_name format a20 3 select tbs.tablespace_name, 4 trunc(tot.bytes/1024/1024) Total_MB, 5 trunc(tot.bytes/1024/1024-sum(nvl(fre.bytes,0))/1024/1024) MB_used, 6 trunc(sum(nvl(fre.bytes,0))/1024/1024) MB_free, 7 (1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct, 8 decode( 9 greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90), 10 90, '', '*' 11 ) pct_warn 12 from dba_free_space fre, 13 (select tablespace_name, sum(bytes) bytes 14 from dba_data_files 15 group by tablespace_name) tot, 16 dba_tablespaces tbs 17 where tot.tablespace_name = tbs.tablespace_name 18 and fre.tablespace_name(+) = tbs.tablespace_name 19 group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes 20 order by 5 ;
查看DBA_FREE_SPACE视图的sql语句
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 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 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# = 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 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
这里涉及到了回收站表sys.recyclebin$，回收站里的对象，也是会占用空间的。
sys.recyclebin$里的大量对象会降低DBA_FREE_SPACE查询速度
查看CDB中回收站对象数量为0，表空间使用率查询速度非常快
1 [oracle@easdb01 ~]$ sqlplus /nolog 2 3 SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 22 10:25:39 2018 4 5 Copyright (c) 1982, 2016, Oracle. All rights reserved. 6 7 SQL> conn /as sysdba 8 Connected. 9 SQL> set timing on 10 SQL> @/backup/scripts/show_space.sql 11 12 TABLESPACE_NAME TOTAL_MB MB_USED MB_FREE PCT PC 13 -------------------- ---------- ---------- ---------- ---------- -- 14 UNDOTBS2 23825 207 23617 .869884575 15 UNDOTBS1 25140 226 24913 .900706046 16 USERS 5 1 4 20 17 SYSAUX 5210 4800 409 92.1329175 * 18 SYSTEM 910 901 9 99.010989 * 19 20 Elapsed: 00:00:00.41 21 SQL> select count(1) from dba_recyclebin; 22 23 COUNT(1) 24 ---------- 25 0 26 27 Elapsed: 00:00:00.24
切换到PDB：
1 SQL> alter session set container=easpdb1; 2 3 Session altered. 4 5 SQL> select count(1) from dba_recyclebin; 6 7 COUNT(1) 8 ---------- 9 133880
可以看到回收站对象数量有13W条，再在PDB下执行表空间使用率查询脚本：
1 SQL> set timing on 2 SQL> / 3 4 TABLESPACE_NAME TOTAL_MB MB_USED MB_FREE PCT PC 5 -------------------- ---------- ---------- ---------- ---------- -- 6 EAS_D_SURE_INDEX 65536 4 65532 .006103516 7 EAS_D_SURE_TEMP2 16384 1 16383 .006103516 8 EAS_D_APEX_INDEX 16384 1 16383 .006103516 9 EAS_D_APEX_TEMP2 16384 1 16383 .006103516 10 EAS_D_UT_TEMP2 16384 110 16273 .676727295 11 USERS 458 3 455 .749318801 12 EAS_D_YHDT_TEMP2 16384 183 16200 1.11961365 13 UNDO_2 32760 1742 31017 5.31898657 14 EAS_D_SURE_STANDARD 131072 16567 114504 12.6397133 15 EAS_INDX 147456 31346 116109 21.2583754 16 UNDOTBS1 30435 7057 23377 23.1875308 17 EAS_D_YHDT_INDEX 114688 40576 74111 35.3798458 18 EAS_D_APEX_STANDARD 65536 36103 29432 55.089283 19 SYSAUX 8192 4855 3336 59.2666626 20 EAS_D_UT_INDEX 114688 68927 45760 60.100174 21 SYSTEM 13312 8158 5153 61.2877479 22 EAS_D_YHDT_STANDARD 377282 235935 141346 62.5355669 23 EAS_D_UT_STANDARD 638976 542054 96921 84.8317464 24 EAS_DATA 212992 190966 22025 89.6588546 25 26 19 rows selected. 27 28 Elapsed: 00:19:34.41
执行了差不多20分钟才出来结果
清空PDB回收站再查询：
注意：在ORACLE 12C中，各个PDB和CDB之间回收站是私有的，所以对每个PDB回收站进行单独的清空。
1 SQL> @/backup/scripts/show_space.sql 2 3 TABLESPACE_NAME TOTAL_MB MB_USED MB_FREE PCT PC 4 -------------------- ---------- ---------- ---------- ---------- -- 5 EAS_D_SURE_INDEX 65536 4 65532 .006103516 6 EAS_D_SURE_TEMP2 16384 1 16383 .006103516 7 EAS_D_APEX_INDEX 16384 1 16383 .006103516 8 EAS_D_APEX_TEMP2 16384 1 16383 .006103516 9 EAS_D_YHDT_TEMP2 16384 66 16317 .407791138 10 EAS_D_UT_TEMP2 16384 110 16273 .676727295 11 USERS 458 3 455 .749318801 12 EAS_D_SURE_STANDARD 131072 16538 114533 12.6181126 13 EAS_INDX 147456 31346 116109 21.2583754 14 UNDO_2 32760 7978 24781 24.3543956 15 EAS_D_YHDT_INDEX 114688 40576 74111 35.3798458 16 EAS_D_APEX_STANDARD 65536 36103 29432 55.089283 17 SYSAUX 8192 4879 3312 59.5596313 18 EAS_D_UT_INDEX 114688 68927 45760 60.100174 19 SYSTEM 13312 8158 5153 61.2877479 20 EAS_D_YHDT_STANDARD 377282 237630 139651 62.9849324 21 UNDOTBS1 30435 25479 4956 83.7161163 22 EAS_D_UT_STANDARD 638976 542047 96928 84.8306803 23 EAS_DATA 212992 190943 22048 89.6483201 24 25 19 rows selected. 26 27 Elapsed: 00:07:03.21
还是执行了7分钟，汗...
重新收集SYS用户统计信息后执行：
1 SQL> exec dbms_stats.gather_schema_stats(ownname => 'sys',degree => 16); 2 3 PL/SQL procedure successfully completed. 4 5 SQL> / 6 7 TABLESPACE_NAME TOTAL_MB MB_USED MB_FREE PCT PC 8 -------------------- ---------- ---------- ---------- ---------- -- 9 EAS_D_SURE_INDEX 65536 4 65532 .006103516 10 EAS_D_APEX_TEMP2 16384 1 16383 .006103516 11 EAS_D_SURE_TEMP2 16384 1 16383 .006103516 12 EAS_D_APEX_INDEX 16384 1 16383 .006103516 13 EAS_D_YHDT_TEMP2 16384 67 16316 .414276123 14 EAS_D_UT_TEMP2 16384 110 16273 .676727295 15 USERS 458 3 455 .749318801 16 EAS_D_SURE_STANDARD 131072 16533 114538 12.6138687 17 EAS_INDX 147456 31346 116109 21.2583754 18 UNDO_2 32760 8466 24293 25.8440171 19 EAS_D_YHDT_INDEX 114688 40576 74111 35.3798458 20 EAS_D_APEX_STANDARD 65536 36103 29432 55.089283 21 SYSAUX 8192 4883 3308 59.6107483 22 EAS_D_UT_INDEX 114688 68927 45760 60.100174 23 SYSTEM 13312 8158 5153 61.2877479 24 EAS_D_YHDT_STANDARD 377282 237638 139643 62.9870528 25 UNDOTBS1 30435 25543 4892 83.9264005 26 EAS_D_UT_STANDARD 638976 542048 96927 84.8307389 27 EAS_DATA 212992 190975 22016 89.6631388 28 29 19 rows selected. 30 31 Elapsed: 00:00:01.77
