recyclebin$里表太多引发的问题(三)
|* 77 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| 00:00:01 |
|* 78 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 12 | 1 (0)| 00:00:01 |
|* 79 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 12874 | | 1 (0)| 00:00:01 |
|* 80 | TABLE ACCESS CLUSTER | TS$ | 1 | 21 | 1 (0)| 00:00:01 |
|* 81 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."TABLESPACE_NAME"="C"."TABLESPACE_NAME")
10 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4)
11 - filter("F"."SPARE1" IS NULL)
12 - access("FNFNO"="F"."FILE#")
13 - filter("FE"."FENUM"="F"."FILE#")
15 - access("F"."TS#"="TS"."TS#")
20 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4)
21 - filter("FNFNO"="HC"."KTFBHCAFNO")
22 - filter("F"."SPARE1" IS NOT NULL)
23 - access("FNFNO"="F"."FILE#")
24 - filter("FE"."FENUM"="F"."FILE#")
26 - access("HC"."KTFBHCTSN"="TS"."TS#")
27 - access("A"."TABLESPACE_NAME"="B"."TABLESPACE_NAME")
35 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4)
36 - filter("F"."SPARE1" IS NULL)
37 - access("FNFNO"="F"."FILE#")
38 - filter("FE"."FENUM"="F"."FILE#")
40 - access("F"."TS#"="TS"."TS#")
45 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4)
46 - filter("FNFNO"="HC"."KTFBHCAFNO")
47 - filter("F"."SPARE1" IS NOT NULL)
48 - access("FNFNO"="F"."FILE#")
49 - filter("FE"."FENUM"="F"."FILE#")
51 - access("HC"."KTFBHCTSN"="TS"."TS#")
59 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
60 - filter("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#")
63 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR
"TS"."ONLINE$"=4))
64 - filter("TS"."TS#"="F"."KTFBFETSN")
65 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
68 - access("TS"."TS#"="RB"."TS#")
70 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR
"TS"."ONLINE$"=4))
71 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#")
72 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
77 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
78 - filter("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
79 - access("U"."TS#"="RB"."TS#")
80 - filter("TS"."BITMAPPED"=0)
81 - access("TS"."TS#"="U"."TS#")
137 rows selected.
发现RECYCLEBIN$里记录太多，在做NESTED LOOPS的时候，耗费太大，RECYCLEBIN$记录多，是由于drop的表过多引起，于是用PURGE DBA_RECYCLEBIN清空回收站后，解决此问题，然后做执行计划如下
[sql]
----------------------------------------------------------------------------