目前数据库查询表空间使用率非常慢,甚至要半个小时才会出结果。查询语句的执行计划如下:
SQL>explain plan for select t.* from (select d.tablespace_name, space "sum_space(m)", blocks sum_blocks, space - nvl(free_space, 0) "used_space(m)", round((1 - nvl(free_space, 0) / space) * 100, 2) "used_rate(%)", free_space "free_space(m)" from (select tablespace_name, round(sum(bytes) / (1048576), 2) space, sum(blocks) blocks from dba_data_files group by tablespace_name) d, (select tablespace_name, round(sum(bytes) / (1048576), 2) free_space from dba_free_space group by tablespace_name) f where d.tablespace_name = f.tablespace_name(+) union all --if have tempfile select d.tablespace_name, space "sum_space(m)", blocks sum_blocks, used_space "used_space(m)", round(nvl(used_space, 0) / space * 100, 2) "used_rate(%)", space - used_space "free_space(m)" from (select tablespace_name, round(sum(bytes) / (1048576), 2) space, sum(blocks) blocks from dba_temp_files group by tablespace_name) d, (select tablespace, round(sum(blocks * 32768) / (1048576), 2) used_space from v$sort_usage group by tablespace) f where d.tablespace_name = f.tablespace(+)) t order by "used_rate(%)" desc; SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE')); Plan hash value: 1739573977 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 246 | 4386 (5)| 00:00:53 | | 1 | SORT ORDER BY | | 3 | 246 | 4386 (5)| 00:00:53 | | 2 | VIEW | | 3 | 246 | 4385 (5)| 00:00:53 | | 3 | UNION-ALL | | | | | | |* 4 | HASH JOIN OUTER | | 2 | 146 | 4382 (5)| 00:00:53 | | 5 | VIEW | | 2 | 86 | 5 (20)| 00:00:01 | | 6 | HASH GROUP BY | | 2 | 70 | 5 (20)| 00:00:01 | | 7 | VIEW | DBA_DATA_FILES | 2 | 70 | 4 (0)| 00:00:01 | | 8 | UNION-ALL | | | | | | | 9 | NESTED LOOPS | | 1 | 359 | 2 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 343 | 1 (0)| 00:00:01 | | 11 | NESTED LOOPS | | 1 | 330 | 1 (0)| 00:00:01 | |* 12 | FIXED TABLE FULL | X$KCCFN | 1 | 310 | 0 (0)| 00:00:01 | |* 13 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 20 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 0 (0)| 00:00:01 | |* 15 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | 0 (0)| 00:00:01 | | 16 | TABLE ACCESS CLUSTER | TS$ | 1 | 16 | 1 (0)| 00:00:01 | |* 17 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 | | 18 | NESTED LOOPS | | 1 | 402 | 2 (0)| 00:00:01 | | 19 | NESTED LOOPS | | 1 | 386 | 1 (0)| 00:00:01 | | 20 | NESTED LOOPS | | 1 | 373 | 1 (0)| 00:00:01 | | 21 | NESTED LOOPS | | 1 | 362 | 0 (0)| 00:00:01 | |* 22 | FIXED TABLE FULL | X$KCCFN | 1 | 310 | 0 (0)| 00:00:01 | |* 23 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 | 0 (0)| 00:00:01 | |* 24 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | 1 (0)| 00:00:01 | |* 25 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 0 (0)| 00:00:01 | |* 26 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | 0 (0)| 00:00:01 | | 27 | TABLE ACCESS CLUSTER | TS$ | 1 | 16 | 1 (0)| 00:00:01 | |* 28 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 | | 29 | VIEW | | 51 | 1530 | 4377 (5)| 00:00:53 | | 30 | HASH GROUP BY | | 51 | 1122 | 4377 (5)| 00:00:53 | | 31 | VIEW | DBA_FREE_SPACE | 18911 | 406K| 4376 (5)| 00:00:53 | | 32 | UNION-ALL | | | | | | | 33 | NESTED LOOPS | | 1 | 66 | 4 (0)| 00:00:01 | | 34 | NESTED LOOPS | | 1 | 59 | 4 (0)| 00:00:01 | | 35 | TABLE ACCESS FULL | FET$ | 1 | 39 | 4 (0)| 00:00:01 | |* 36 | TABLE ACCESS CLUSTER | TS$ | 1 | 20 | 0 (0)| 00:00:01 | |* 37 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 | |* 38 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| 00:00:01 | | 39 | NESTED LOOPS | | 67 | 4824 | 22 (0)| 00:00:01 | | 40 | NESTED LOOPS | | 67 | 4355 | 22 (0)| 00:00:01 | |* 41 | TABLE ACCESS FULL | TS$ | 51 | 1326 | 22 (0)| 00:00:01 | |* 42 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 1 | 39 | 0 (0)| 00:00:01 | |* 43 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| 00:00:01 | |* 44 | HASH JOIN | | 18842 | 1214K| 3127 (6)| 00:00:38 | |* 45 | TABLE ACCESS FULL | TS$ | 51 | 1326 | 22 (0)| 00:00:01 | |* 46 | HASH JOIN | | 18985 | 741K| 3105 (6)| 00:00:38 | | 47 | INDEX FULL SCAN | I_FILE2 | 146 | 1022 | 1 (0)| 00:00:01 | | 48 | NESTED LOOPS | | 887K| 27M| 3100 (6)| 00:00:38 | | 49 | TABLE ACCESS FULL | RECYCLEBIN$ | 887K| 10M| 2940 (1)| 00:00:36 | |* 50 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 21 | 0 (0)| 00:00:01 | | 51 | NESTED LOOPS | | 1 | 91 | 1223 (1)| 00:00:15 | | 52 | NESTED LOOPS | | 49300 | 91 | 1223 (1)| 00:00:15 | | 53 | NESTED LOOPS | | 1 | 79 | 23 (0)| 00:00:01 | | 54 | NESTED LOOPS | | 1 | 72 | 23 (0)| 00:00:01 | | 55 | TABLE ACCESS FULL | UET$ | 1 | 52 | 23 (0)| 00:00:01 | |* 56 | TABLE ACCESS CLUSTER | TS$ | 1 | 20 | 0 (0)| 00:00:01 | |* 57 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 | |* 58 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| 00:00:01 | |* 59 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 49300 | | 336 (0)| 00:00:05 | |* 60 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 2 | 24 | 1200 (1)| 00:00:15 | |* 61 | HASH JOIN OUTER | | 1 | 73 | 3 (67)| 00:00:01 | | 62 | VIEW | | 1 | 43 | 2 (50)| 00:00:01 | | 63 | HASH GROUP BY | | 1 | 417 | 2 (50)| 00:00:01 | | 64 | NESTED LOOPS | | 1 | 417 | 1 (0)| 00:00:01 | | 65 | NESTED LOOPS | | 1 | 365 | 1 (0)| 00:00:01 | | 66 | NESTED LOOPS | | 1 | 349 | 0 (0)| 00:00:01 | |* 67 | FIXED TABLE FULL | X$KCCFN | 1 | 297 | 0 (0)| 00:00:01 | |* 68 | FIXED TABLE FIXED INDEX | X$KTFTHC (ind:1) | 1 | 52 | 0 (0)| 00:00:01 | | 69 | TABLE ACCESS CLUSTER | TS$ | 1 | 16 | 1 (0)| 00:00:01 | |* 70 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 | |* 71 | FIXED TABLE FIXED INDEX | X$KCCTF (ind:1) | 1 | 52 | 0 (0)| 00:00:01 | | 72 | VIEW | | 1 | 30 | 1 (100)| 00:00:01 | | 73 | HASH GROUP BY | | 1 | 172 | 1 (100)| 00:00:01 | | 74 | NESTED LOOPS | | 1 | 172 | 0 (0)| 00:00:01 | | 75 | NESTED LOOPS | | 1 | 159 | 0 (0)| 00:00:01 | | 76 | MERGE JOIN CARTESIAN | | 100 | 8800 | 0 (0)| 00:00:01 | |* 77 | FIXED TABLE FULL | X$KTSSO | 1 | 62 | 0 (0)| 00:00:01 | | 78 | BUFFER SORT | | 100 | 2600 | 0 (0)| 00:00:01 | | 79 | FIXED TABLE FULL | X$KSLWT | 100 | 2600 | 0 (0)| 00:00:01 | |* 80 | FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) | 1 | 71 | 0 (0)| 00:00:01 | |* 81 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("D"."TABLESPACE_NAME"="F"."TABLESPACE_NAME"(+)) 12 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4) 13 - filter("F"."SPARE1" IS NULL) 14 - access("FNFNO"="F"."FILE#") 15 - filter("FE"."FENUM"="F"."FILE#") 17 - access("F"."TS#"="TS"."TS#") 22 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4) 23 - filter("FNFNO"="HC"."KTFBHCAFNO") 24 - filter("F"."SPARE1" IS NOT NULL) 25 - access("FNFNO"="F"."FILE#") 26 - filter("FE"."FENUM"="F"."FILE#") 28 - access("HC"."KTFBHCTSN"="TS"."TS#") 36 - filter("TS"."BITMAPPED"=0) 37 - access("TS"."TS#"="F"."TS#") 38 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#") 41 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4)) 42 - filter("TS"."TS#"="F"."KTFBFETSN") 43 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#") 44 - access("TS"."TS#"="RB"."TS#") 45 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4)) 46 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#") 50 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#") 56 - filter("TS"."BITMAPPED"=0) 57 - access("TS"."TS#"="U"."TS#") 58 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#") 59 - access("U"."TS#"="RB"."TS#") 60 - filter("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#") 61 - access("D"."TABLESPACE_NAME"="F"."TABLESPACE"(+)) 67 - filter("V"."FNNAM" IS NOT NULL AND "V"."FNTYP"=7) 68 - filter("V"."FNFNO"="HC"."KTFTHCTFNO") 70 - access("HC"."KTFTHCTSN"="TS"."TS#") 71 - filter("TF"."TFDUP"<>0 AND BITAND("TF"."TFSTA",32)<>32 AND "V"."FNFNO"="TF"."TFNUM" AND "TF"."TFFNH"="V"."FNNUM") 77 - filter("X$KTSSO"."INST_ID"=USERENV('INSTANCE')) 80 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0 AND "KTSSOSES"="S"."ADDR" AND "KTSSOSNO"="S"."KSUSESER" AND "S"."INDX"="W"."KSLWTSID") 81 - filter("W"."KSLWTEVT"="E"."INDX")
|
分析执行计划发现DBA_FREE_SPACE消耗了大量资源,DBA_FREE_SPACE是个视图,通过查看其创建视图的语句发现该视图关联了RECYCLEBIN$表。
RECYCLEBIN$是Oracle回收站,用来存放DROP删除的对象信息,查询发现该表有八十多万条记录,因此判断是回收站太大引起了表空间查询缓慢,建议对回收站进行定期清理。