回收站引发Oracle查询表空间使用缓慢

一个同事问我，他们查询Oracle 表空间使用率跑了一个多小时，这个太坑爹了，让我帮忙优化一下。SQL语句如下select * from
（
select ts.tablespace_name,ts.contents "TABLESPACE_TYPE",
to_char（sysdate,"yyyy-mm-dd hh24:mi:ss"） monitor_date,
datafiles,total_gb "TOTAL_SIZE_GB",
（total_gb-free_gb） "USED_SIZE_GB",
free_gb "FREE_SIZE_GB",
round（（100-free_gb/total_gb*100）,2） "USED_PCT",
round（free_gb/total_gb*100,2） "FREE_PCT"
from dba_tablespaces ts,
（select tablespace_name,round（sum（bytes）/1024/1024/1024,2） free_gb
from dba_free_space group by tablespace_name） fr1,
（select tablespace_name,round（sum（bytes）/1024/1024/1024,2） total_gb,count（*） datafiles
from dba_data_files group by tablespace_name） df1
where ts.tablespace_name=fr1.tablespace_name and ts.tablespace_name=df1.tablespace_name order by "FREE_PCT"
）
union all
select "TOTAL SUMMARY:","ALL",to_char（sysdate,"yyyy-mm-dd hh24:mi:ss"） monitor_date,datafiles,
total_gb "TOTAL_SIZE_GB",
round（total_gb-free_gb,2） "USED_SIZE_GB",
free_gb "FREE_SIZE_GB",
round（（100-free_gb/total_gb*100）,2） "USED_PCT",
round（free_gb/total_gb*100,2） "FREE_PCT"
from （select round（sum（bytes）/1024/1024/1024,2） free_gb from dba_free_space） fr2,
（select count（*） datafiles,round（sum（bytes）/1024/1024/1024,2） total_gb from dba_data_files） df2
union all
select null,null,null,null,null,null,null,null,null from dual;这个SQL挺简单的，就是访问几个数据字典而已，执行计划如下SQL> set autotrace traceonly;
SQL> select * from
2 （
3 select ts.tablespace_name,ts.contents "TABLESPACE_TYPE",
4 to_char（sysdate,"yyyy-mm-dd hh24:mi:ss"） monitor_date,
5 datafiles,total_gb "TOTAL_SIZE_GB",
6 （total_gb-free_gb） "USED_SIZE_GB",
7 free_gb "FREE_SIZE_GB",
8 round（（100-free_gb/total_gb*100）,2） "USED_PCT",
9 round（free_gb/total_gb*100,2） "FREE_PCT"
from dba_tablespaces ts,
10 11 （select tablespace_name,round（sum（bytes）/1024/1024/1024,2） free_gb
12 from dba_free_space group by tablespace_name） fr1,
13 （select tablespace_name,round（sum（bytes）/1024/1024/1024,2） total_gb,count（*） datafiles
14 from dba_data_files group by tablespace_name） df1
15 where ts.tablespace_name=fr1.tablespace_name and ts.tablespace_name=df1.tablespace_name order by "FREE_PCT"
16 ）
17 union all
18 select "TOTAL SUMMARY:","ALL",to_char（sysdate,"yyyy-mm-dd hh24:mi:ss"） monitor_date,datafiles,
19 total_gb "TOTAL_SIZE_GB",
20 round（total_gb-free_gb,2） "USED_SIZE_GB",
21 free_gb "FREE_SIZE_GB",
22 round（（100-free_gb/total_gb*100）,2） "USED_PCT",
23 round（free_gb/total_gb*100,2） "FREE_PCT"
24 from （select round（sum（bytes）/1024/1024/1024,2） free_gb from dba_free_space） fr2,
25 （select count（*） datafiles,round（sum（bytes）/1024/1024/1024,2） total_gb from dba_data_files） df2
26 union all
27 select null,null,null,null,null,null,null,null,null from dual;
31 rows selected.Elapsed: 00:50:32.18Execution Plan
----------------------------------------------------------
Plan hash value: 3463738489--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost （%CPU）| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 140 | 15495 | | 5104K（100）| 17:00:53 |
| 1 | UNION-ALL | | | | | | |
| 2 | VIEW | | 138 | 15456 | | 2552K（100）| 08:30:27 |
| 3 | SORT ORDER BY | | 138 | 13248 | | 2552K（100）| 08:30:27 |
|* 4 | HASH JOIN | | 138 | 13248 | | 2552K（100）| 08:30:27 |
| 5 | NESTED LOOPS | | 2 | 132 | | 7 （15）| 00:00:01 |
| 6 | VIEW | | 2 | 86 | | 5 （20）| 00:00:01 |
| 7 | HASH GROUP BY | | 2 | 60 | | 5 （20）| 00:00:01 |
| 8 | VIEW | DBA_DATA_FILES | 2 | 60 | | 4 （0）| 00:00:01 |
| 9 | UNION-ALL | | | | | | |
| 10 | NESTED LOOPS | | 1 | 236 | | 2 （0）| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 215 | | 1 （0）| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 202 | | 1 （0）| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 （0）| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 20 | | 1 （0）| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 （0）| 00:00:01 |
|* 16 | FIXED TABLE FIXED INDEX | X$KCCFE （ind:1） | 1 | 13 | | 0 （0）| 00:00:01 |
| 17 | TABLE ACCESS CLUSTER | TS$ | 1 | 21 | | 1 （0）| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 （0）| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 279 | | 2 （0）| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 258 | | 1 （0）| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 245 | | 1 （0）| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 234 | | 0 （0）| 00:00:01 |
|* 23 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 （0）| 00:00:01 |
|* 24 | FIXED TABLE FIXED INDEX | X$KTFBHC （ind:1） | 1 | 52 | | 0 （0）| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | | 1 （0）| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 （0）| 00:00:01 |
|* 27 | FIXED TABLE FIXED INDEX | X$KCCFE （ind:1） | 1 | 13 | | 0 （0）| 00:00:01 |
| 28 | TABLE ACCESS CLUSTER | TS$ | 1 | 21 | | 1 （0）| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 （0）| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 23 | | 1 （0）| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_TS1 | 1 | | | 0 （0）| 00:00:01 |
| 32 | VIEW | | 3507 | 102K| | 2552K（100）| 08:30:27 |
| 33 | HASH GROUP BY | | 3507 | 102K| | 2552K（100）| 08:30:27 |
| 34 | VIEW | DBA_FREE_SPACE | 3507 | 102K| | 2552K（100）| 08:30:27 |
| 35 | UNION-ALL | | | | | | |
| 36 | NESTED LOOPS | | 1 | 71 | | 4 （0）| 00:00:01 |
| 37 | NESTED LOOPS | | 1 | 46 | | 3 （0）| 00:00:01 |
| 38 | TABLE ACCESS FULL | FET$ | 1 | 39 | | 3 （0）| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 （0）| 00:00:01 |
|* 40 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | | 1 （0）| 00:00:01 |
| 41 | NESTED LOOPS | | 70 | 5390 | | 11 （0）| 00:00:01 |
| 42 | NESTED LOOPS | | 70 | 4900 | | 11 （0）| 00:00:01 |
|* 43 | TABLE ACCESS FULL | TS$ | 29 | 899 | | 11 （0）| 00:00:01 |
|* 44 | FIXED TABLE FIXED INDEX | X$KTFBFE （ind:1） | 2 | 78 | | 0 （0）| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 （0）| 00:00:01 |
| 46 | NESTED LOOPS | | 3435 | 385K| | 2552K（100）| 08:30:26 |
| 47 | NESTED LOOPS | | 96156 | 9M| | 2552K（100）| 08:30:26 |
|* 48 | HASH JOIN | | 354K| 14M| 8632K| 1538 （1）| 00:00:19 |
| 49 | TABLE ACCESS FULL | RECYCLEBIN$ | 368K| 4314K| | 1103 （1）| 00:00:14 |
|* 50 | TABLE ACCESS FULL | TS$ | 29 | 899 | | 11 （0）| 00:00:01 |
|* 51 | FIXED TABLE FIXED INDEX | X$KTFBUE （ind:1） | 1 | 65 | | 7 （100）| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 （0）| 00:00:01 |
|* 53 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 12 | | 2 （0）| 00:00:01 |
| 54 | NESTED LOOPS | | 1 | 96 | | 17 （0）| 00:00:01 |
| 55 | NESTED LOOPS | | 1 | 84 | | 15 （0）| 00:00:01 |
| 56 | NESTED LOOPS | | 1 | 59 | | 14 （0）| 00:00:01 |
| 57 | TABLE ACCESS FULL | UET$ | 1 | 52 | | 14 （0）| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 （0）| 00:00:01 |
|* 59 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | | 1 （0）| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 （0）| 00:00:01 |
|* 61 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 61368 | | | 2 （0）| 00:00:01 |
| 62 | NESTED LOOPS | | 1 | 39 | | 2552K（100）| 08:30:27 |
| 63 | VIEW | | 1 | 13 | | 2552K（100）| 08:30:27 |
| 64 | SORT AGGREGATE | | 1 | 13 | | | |
| 65 | VIEW | DBA_FREE_SPACE | 3507 | 45591 | | 2552K（100）| 08:30:27 |
| 66 | UNION-ALL | | | | | | |
| 67 | NESTED LOOPS | | 1 | 57 | | 4 （0）| 00:00:01 |
| 68 | NESTED LOOPS | | 1 | 46 | | 3 （0）| 00:00:01 |
| 69 | TABLE ACCESS FULL | FET$ | 1 | 39 | | 3 （0）| 00:00:01 |
|* 70 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 （0）| 00:00:01 |
|* 71 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | | 1 （0）| 00:00:01 |
| 72 | NESTED LOOPS | | 70 | 4410 | | 11 （0）| 00:00:01 |
| 73 | NESTED LOOPS | | 70 | 3920 | | 11 （0）| 00:00:01 |
|* 74 | TABLE ACCESS FULL | TS$ | 29 | 493 | | 11 （0）| 00:00:01 |
|* 75 | FIXED TABLE FIXED INDEX | X$KTFBFE （ind:1） | 2 | 78 | | 0 （0）| 00:00:01 |
|* 76 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 （0）| 00:00:01 |
| 77 | NESTED LOOPS | | 3435 | 338K| | 2552K（100）| 08:30:26 |
| 78 | NESTED LOOPS | | 96156 | 8826K| | 2552K（100）| 08:30:26 |
|* 79 | HASH JOIN | | 354K| 9M| 8632K| 1538 （1）| 00:00:19 |
| 80 | TABLE ACCESS FULL | RECYCLEBIN$ | 368K| 4314K| | 1103 （1）| 00:00:14 |
|* 81 | TABLE ACCESS FULL | TS$ | 29 | 493 | | 11 （0）| 00:00:01 |
|* 82 | FIXED TABLE FIXED INDEX | X$KTFBUE （ind:1） | 1 | 65 | | 7 （100）| 00:00:01 |
|* 83 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 （0）| 00:00:01 |
|* 84 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 12 | | 2 （0）| 00:00:01 |
| 85 | NESTED LOOPS | | 1 | 82 | | 17 （0）| 00:00:01 |
| 86 | NESTED LOOPS | | 1 | 70 | | 15 （0）| 00:00:01 |
| 87 | NESTED LOOPS | | 1 | 59 | | 14 （0）| 00:00:01 |
| 88 | TABLE ACCESS FULL | UET$ | 1 | 52 | | 14 （0）| 00:00:01 |
|* 89 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 （0）| 00:00:01 |
|* 90 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | | 1 （0）| 00:00:01 |
|* 91 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 （0）| 00:00:01 |
|* 92 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 61368 | | | 2 （0）| 00:00:01 |
| 93 | VIEW | | 1 | 26 | | 4 （0）| 00:00:01 |
| 94 | SORT AGGREGATE | | 1 | 13 | | | |
| 95 | VIEW | DBA_DATA_FILES | 2 | 26 | | 4 （0）| 00:00:01 |
| 96 | UNION-ALL | | | | | | |
| 97 | NESTED LOOPS | | 1 | 222 | | 2 （0）| 00:00:01 |
| 98 | NESTED LOOPS | | 1 | 215 | | 1 （0）| 00:00:01 |
| 99 | NESTED LOOPS | | 1 | 202 | | 1 （0）| 00:00:01 |
|*100 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 （0）| 00:00:01 |
|*101 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 20 | | 1 （0）| 00:00:01 |
|*102 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 （0）| 00:00:01 |
|*103 | FIXED TABLE FIXED INDEX | X$KCCFE （ind:1） | 1 | 13 | | 0 （0）| 00:00:01 |
| 104 | TABLE ACCESS CLUSTER | TS$ | 1 | 7 | | 1 （0）| 00:00:01 |
|*105 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 （0）| 00:00:01 |
| 106 | NESTED LOOPS | | 1 | 265 | | 2 （0）| 00:00:01 |
| 107 | NESTED LOOPS | | 1 | 258 | | 1 （0）| 00:00:01 |
| 108 | NESTED LOOPS | | 1 | 245 | | 1 （0）| 00:00:01 |
| 109 | NESTED LOOPS | | 1 | 234 | | 0 （0）| 00:00:01 |
|*110 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 （0）| 00:00:01 |
|*111 | FIXED TABLE FIXED INDEX | X$KTFBHC （ind:1） | 1 | 52 | | 0 （0）| 00:00:01 |
|*112 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 11 | | 1 （0）| 00:00:01 |
|*113 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 （0）| 00:00:01 |
|*114 | FIXED TABLE FIXED INDEX | X$KCCFE （ind:1） | 1 | 13 | | 0 （0）| 00:00:01 |
| 115 | TABLE ACCESS CLUSTER | TS$ | 1 | 7 | | 1 （0）| 00:00:01 |
|*116 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 （0）| 00:00:01 |
| 117 | FAST DUAL | | 1 | | | 2 （0）| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 4 - access（"TS"."NAME"="FR1"."TABLESPACE_NAME"）
13 - filter（"FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV（"INSTANCE"） AND BITAND（"FNFLG",4）<>4）
14 - filter（"F"."SPARE1" IS NULL）
15 - access（"FNFNO"="F"."FILE#"）
16 - filter（"FE"."FENUM"="F"."FILE#"）
18 - access（"F"."TS#"="TS"."TS#"）
23 - filter（"FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV（"INSTANCE"） AND BITAND（"FNFLG",4）<>4）
24 - filter（"FNFNO"="HC"."KTFBHCAFNO"）
25 - filter（"F"."SPARE1" IS NOT NULL）
26 - access（"FNFNO"="F"."FILE#"）
27 - filter（"FE"."FENUM"="F"."FILE#"）
29 - access（"HC"."KTFBHCTSN"="TS"."TS#"）
30 - filter（"TS"."ONLINE$"<>3 AND BITAND（"FLAGS",2048）<>2048）
31 - access（"TS"."NAME"="DF1"."TABLESPACE_NAME"）
39 - access（"F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#"）
40 - filter（"TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#"）
43 - filter（（"TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4） AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0）
44 - filter（"TS"."TS#"="F"."KTFBFETSN"）
45 - access（"F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#"）
48 - access（"TS"."TS#"="RB"."TS#"）
50 - filter（（"TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4） AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0）
51 - filter（"U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#"）
52 - access（"RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#"）
53 - filter（"U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#"）
58 - access（"U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#"）
59 - filter（"TS"."BITMAPPED"=0）
60 - access（"TS"."TS#"="U"."TS#"）
61 - access（"U"."TS#"="RB"."TS#"）
70 - access（"F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#"）
71 - filter（"TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#"）
74 - filter（（"TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4） AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0）
75 - filter（"TS"."TS#"="F"."KTFBFETSN"）
76 - access（"F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#"）
79 - access（"TS"."TS#"="RB"."TS#"）
81 - filter（（"TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4） AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0）
82 - filter（"U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#"）
83 - access（"RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#"）
84 - filter（"U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#"）
89 - access（"U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#"）
90 - filter（"TS"."BITMAPPED"=0）
91 - access（"TS"."TS#"="U"."TS#"）
92 - access（"U"."TS#"="RB"."TS#"）
100 - filter（"FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV（"INSTANCE"） AND BITAND（"FNFLG",4）<>4）
101 - filter（"F"."SPARE1" IS NULL）
102 - access（"FNFNO"="F"."FILE#"）
103 - filter（"FE"."FENUM"="F"."FILE#"）
105 - access（"F"."TS#"="TS"."TS#"）
110 - filter（"FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV（"INSTANCE"） AND BITAND（"FNFLG",4）<>4）
111 - filter（"FNFNO"="HC"."KTFBHCAFNO"）
112 - filter（"F"."SPARE1" IS NOT NULL）
113 - access（"FNFNO"="F"."FILE#"）
114 - filter（"FE"."FENUM"="F"."FILE#"）
116 - access（"HC"."KTFBHCTSN"="TS"."TS#"）
Statistics
----------------------------------------------------------
18098002 recursive calls 1800W 的递归调用
7676 db block gets
3986596 consistent gets 398W 逻辑读
790235 physical reads 79W 物理读
0 redo size
2730 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts （memory）
0 sorts （disk）
31 rows processed

Oracle 11g使用deferred_segment_creation 延迟段创建特性时遇到的问题总结MongoDB在PHP下的应用学习笔记相关资讯 Oracle查询 Oracle查询表空间

MySQL、SQL Server、Oracle数据库（08/16/2015 10:43:52）
Oracle分段查询（02/08/2015 11:29:11）
Oracle 查询锁之间的依赖关系（09/11/2014 06:21:54）

Oracle查询数据库对象所属用户（03/25/2015 21:03:12）
Oracle 子查询因子化浅谈（with的（01/13/2015 18:46:53）
Oracle常用系统查询（07/18/2014 16:03:01）

本文评论查看全部评论（0）