博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5753417.html
某库数据库检查表空间大小的时候非常慢
检查其其执行计划：
SQL> set timing on
SQL> set autot on
SQL> set linesize 120
SQL> /
Elapsed: 00:01:45.55
Execution Plan
----------------------------------------------------------
Plan hash value: 2471964280
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 217 | 309 (15)| 00:00:04 |
| 1 | SORT ORDER BY | | 3 | 217 | 308 (14)| 00:00:04 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 2 | 146 | 305 (14)| 00:00:04 |
| 4 | VIEW | | 2 | 86 | 5 (20)| 00:00:01 |
| 5 | HASH GROUP BY | | 2 | 68 | 5 (20)| 00:00:01 |
| 6 | VIEW | DBA_DATA_FILES | 2 | 68 | 4 (0)| 00:00:01 |
| 7 | UNION-ALL | | | | | |
| 8 | NESTED LOOPS | | 1 | 356 | 2 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 341 | 1 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 328 | 1 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KCCFN | 1 | 310 | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 18 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 6 | 78 | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS CLUSTER | TS$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 399 | 2 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 384 | 1 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 371 | 1 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 362 | 0 (0)| 00:00:01 |
|* 21 | FIXED TABLE FULL | X$KCCFN | 1 | 310 | 0 (0)| 00:00:01 |
|* 22 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 | 0 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 9 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 0 (0)| 00:00:01 |
|* 25 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 6 | 78 | 0 (0)| 00:00:01 |
| 26 | TABLE ACCESS CLUSTER | TS$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 28 | VIEW | | 10 | 300 | 300 (14)| 00:00:04 |
| 29 | HASH GROUP BY | | 10 | 210 | 300 (14)| 00:00:04 |
| 30 | VIEW | DBA_FREE_SPACE | 2074 | 43554 | 299 (14)| 00:00:04 |
| 31 | UNION-ALL | | | | | |
| 32 | NESTED LOOPS | | 1 | 64 | 4 (0)| 00:00:01 |
| 33 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |
| 34 | TABLE ACCESS FULL | FET$ | 1 | 39 | 4 (0)| 00:00:01 |
|* 35 | TABLE ACCESS CLUSTER | TS$ | 1 | 19 | 0 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| 00:00:01 |
| 38 | NESTED LOOPS | | 87 | 6090 | 5 (0)| 00:00:01 |
| 39 | NESTED LOOPS | | 87 | 5568 | 5 (0)| 00:00:01 |
|* 40 | TABLE ACCESS FULL | TS$ | 10 | 250 | 5 (0)| 00:00:01 |
|* 41 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 9 | 351 | 0 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| 00:00:01 |
|* 43 | HASH JOIN | | 1985 | 207K| 162 (21)| 00:00:02 |
|* 44 | TABLE ACCESS FULL | TS$ | 10 | 250 | 5 (0)| 00:00:01 |
|* 45 | HASH JOIN | | 2080 | 166K| 157 (22)| 00:00:02 |
| 46 | INDEX FULL SCAN | I_FILE2 | 18 | 108 | 1 (0)| 00:00:01 |
|* 47 | HASH JOIN | | 35177 | 2610K| 156 (22)| 00:00:02 |
| 48 | TABLE ACCESS FULL | RECYCLEBIN$ | 35177 | 377K| 123 (1)| 00:00:02 |
| 49 | FIXED TABLE FULL | X$KTFBUE | 100K| 6347K| 32 (100)| 00:00:01 |
| 50 | NESTED LOOPS | | 1 | 88 | 127 (4)| 00:00:02 |
| 51 | NESTED LOOPS | | 1 | 82 | 127 (4)| 00:00:02 |
| 52 | NESTED LOOPS | | 1 | 63 | 127 (4)| 00:00:02 |
| 53 | TABLE ACCESS FULL | RECYCLEBIN$ | 35177 | 377K| 123 (1)| 00:00:02 |
| 54 | TABLE ACCESS CLUSTER | UET$ | 1 | 52 | 0 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |
|* 56 | TABLE ACCESS CLUSTER | TS$ | 1 | 19 | 0 (0)| 00:00:01 |
|* 57 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| 00:00:01 |
| 59 | NESTED LOOPS OUTER | | 1 | 71 | 3 (34)| 00:00:01 |
| 60 | VIEW | | 1 | 43 | 2 (50)| 00:00:01 |
| 61 | HASH GROUP BY | | 1 | 416 | 2 (50)| 00:00:01 |
| 62 | NESTED LOOPS | | 1 | 416 | 1 (0)| 00:00:01 |
| 63 | NESTED LOOPS | | 1 | 364 | 1 (0)| 00:00:01 |
| 64 | NESTED LOOPS | | 1 | 349 | 0 (0)| 00:00:01 |
|* 65 | FIXED TABLE FULL | X$KCCFN | 1 | 297 | 0 (0)| 00:00:01 |
|* 66 | FIXED TABLE FIXED INDEX | X$KTFTHC (ind:1) | 1 | 52 | 0 (0)| 00:00:01 |
| 67 | TABLE ACCESS CLUSTER | TS$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 68 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 69 | FIXED TABLE FIXED INDEX | X$KCCTF (ind:1) | 1 | 52 | 0 (0)| 00:00:01 |
| 70 | VIEW PUSHED PREDICATE | | 1 | 28 | 1 (0)| 00:00:01 |
| 71 | SORT GROUP BY | | 1 | 90 | 1 (0)| 00:00:01 |
| 72 | NESTED LOOPS | | 1 | 90 | 1 (0)| 00:00:01 |
|* 73 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 25 | 1 (0)| 00:00:01 |
|* 74 | INDEX UNIQUE SCAN | I_TS1 | 1 | | 0 (0)| 00:00:01 |
|* 75 | FIXED TABLE FIXED INDEX | X$KTFTHC (ind:2) | 1 | 65 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."TABLESPACE_NAME"="F"."TABLESPACE_NAME"(+))
11 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4)
12 - filter("F"."SPARE1" IS NULL)
13 - access("FNFNO"="F"."FILE#")
14 - filter("FE"."FENUM"="F"."FILE#")
16 - access("F"."TS#"="TS"."TS#")
21 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4)
22 - filter("FNFNO"="HC"."KTFBHCAFNO")
23 - filter("F"."SPARE1" IS NOT NULL)
24 - access("FNFNO"="F"."FILE#")
25 - filter("FE"."FENUM"="F"."FILE#")
27 - access("HC"."KTFBHCTSN"="TS"."TS#")
35 - filter("TS"."BITMAPPED"=0)
36 - access("TS"."TS#"="F"."TS#")
37 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
40 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR
"TS"."ONLINE$"=4))
41 - filter("TS"."TS#"="F"."KTFBFETSN")
42 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
43 - access("TS"."TS#"="RB"."TS#")
44 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR
"TS"."ONLINE$"=4))
45 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
47 - access("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#")
55 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND
"U"."SEGBLOCK#"="RB"."BLOCK#")
56 - filter("TS"."BITMAPPED"=0)
57 - access("TS"."TS#"="U"."TS#")
58 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
65 - filter("V"."FNNAM" IS NOT NULL AND "V"."FNTYP"=7)
66 - filter("V"."FNFNO"="HC"."KTFTHCTFNO")
68 - access("HC"."KTFTHCTSN"="TS"."TS#")
69 - filter("TF"."TFDUP"<>0 AND BITAND("TF"."TFSTA",32)<>32 AND "V"."FNFNO"="TF"."TFNUM" AND
"TF"."TFFNH"="V"."FNNUM")
73 - filter("TS"."CONTENTS$"=1 AND "TS"."BITMAPPED"<>0 AND "TS"."ONLINE$"=1)
74 - access("TS"."NAME"="D"."TABLESPACE_NAME")
75 - filter("HC"."KTFTHCCVAL"=0 AND "HC"."INST_ID"=USERENV('INSTANCE') AND
"TS"."TS#"="HC"."KTFTHCTSN")
Statistics --统计信息
----------------------------------------------------------
51732 recursive calls
211 db block gets
273537 consistent gets
48565 physical reads
0 redo size
1301 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed
观察发现执行计划id=53及其前后，发现对RECYCLEBIN$进行全表扫描后作为驱动表进行nested loops
同事id=48，也
对RECYCLEBIN$进行全表扫描。
于是检查回收站数据库对象
SQL> select count(*) from sys.recyclebin$;
COUNT(*)
----------
38499
发现RECYCLEBIN$里记录太多，在做NESTED LOOPS的时候，耗费太大，RECYCLEBIN$记录多，是由于drop的表过多引起，可用PURGE DBA_RECYCLEBIN清空回收站后解决。
但是，根据业务的需求，只能逐步清理
SQL> select owner#,count(*) from sys.recyclebin$ group by owner#;
OWNER# COUNT(*)
---------- ----------
34 3140
40 35359
SQL> select user_id,username,default_tablespace from dba_users where USER_ID in ('34','40');
USER_ID USERNAME DEFAULT_TABLESPACE
---------- ------------------------------ ------------------------------
34 YXJS_APP YXJS_APP
40 XYFILMZC XYFILMZC
后处理完成后，问题解决。
oracle 关于Recyclebin使用原则：
1. 使用现有的表空间的未使用空间
2. 如果没有了空闲空间，则检查回收站，对于回收站的对象按照先进先出的原则，对于最先删除的对象，
oracle在空间不足之时会最先从回收站删除以满足新分配空间的需求
3. 如果回收站也没有对象可以清理，则检查表空间是否自扩展，如果自扩展则扩展表空间，然后分配新空
间
4.如果表空间非自扩展，或者已经不能自扩展(到达最大限制)，则直接报表空间不足错误，程序终止
---the end
