recyclebin造成的問題分析

今天檢查一套系統時發現有sql很慢，分析應用發現這是對資料庫表空間使用率進行監控的一條sql。

select sum(bytes/1024/1024) from dba_free_space

我們來看看它的執行計劃

Execution Plan

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 55320 (100)| |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | VIEW | DBA_FREE_SPACE | 3255 | 42315 | 55320 (100)| 00:11:04 |

| 3 | UNION-ALL | | | | | |

| 4 | NESTED LOOPS | | 1 | 56 | 4 (0)| 00:00:01 |

| 5 | NESTED LOOPS | | 1 | 45 | 3 (0)| 00:00:01 |

| 6 | TABLE ACCESS FULL | FET$ | 1 | 39 | 3 (0)| 00:00:01 |

| 7 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| |

| 8 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | 1 (0)| 00:00:01 |

| 9 | NESTED LOOPS | | 78 | 4836 | 6 (0)| 00:00:01 |

| 10 | NESTED LOOPS | | 78 | 4368 | 6 (0)| 00:00:01 |

| 11 | TABLE ACCESS FULL | TS$ | 15 | 255 | 6 (0)| 00:00:01 |

| 12 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 5 | 195 | 0 (0)| |

| 13 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| |

| 14 | NESTED LOOPS | | 3175 | 306K| 55294 (100)| 00:11:04 |

| 15 | NESTED LOOPS | | 98165 | 8915K| 55286 (100)| 00:11:04 |

| 16 | HASH JOIN | | 4533 | 123K| 22 (5)| 00:00:01 |

| 17 | TABLE ACCESS FULL | RECYCLEBIN$ | 4618 | 50798 | 15 (0)| 00:00:01 |

| 18 | TABLE ACCESS FULL | TS$ | 15 | 255 | 6 (0)| 00:00:01 |

| 19 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 22 | 1430 | 12 (100)| 00:00:01 |

| 20 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| |

| 21 | NESTED LOOPS | | 1 | 80 | 16 (0)| 00:00:01 |

| 22 | NESTED LOOPS | | 1 | 69 | 15 (0)| 00:00:01 |

| 23 | NESTED LOOPS | | 1 | 58 | 14 (0)| 00:00:01 |

| 24 | TABLE ACCESS FULL | UET$ | 1 | 52 | 14 (0)| 00:00:01 |

| 25 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| |

| 26 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 1 | 11 | 1 (0)| 00:00:01 |

| 27 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 660 | | 1 (0)| 00:00:01 |

| 28 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | 1 (0)| 00:00:01 |

| 29 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |

-----------------------------------------------------------------------------------------------------

從執行計劃可以看到，RECYCLEBIN$ 這張表有4600多條記錄，最後做nested loop用了11秒鐘，當被刪除表增多後肯定會更慢。RECYCLEBIN$ 是存放被刪除表的地方，是10g的新功能。看來又是應用把真正的表當臨時表在用，導致大量表被刪除。除了搖頭輕嘆外，只能動手敲下purge dba_recyclebin，同時制定了每天定時清理回收站的job。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11088128/viewspace-688204/，如需轉載，請註明出處，否則將追究法律責任。

recyclebin造成的問題分析

相關文章