大量drop table 產生 insert recyclebin$ 導致 High version count 問題

忙到好久沒時間寫東西。

某天收到一個發過來的問提，反映資料庫緩滿且有很多sys的table 被lock，且CPU資源消耗過高。



資料庫緩慢可以由很多方式查詢，如等待事件、AWR、IO、CPU等，但這一次我由CPU消耗過高的開始查詢，定位到12740 process。


當下想先由 trace file 了解看看 process 12740 所以看一下trace資料夾是否有關於 12740 的相關檔案，有發現 而且該檔案異常的大，99G的trace 檔案確實是一個非常可怕的數字。


由於檔案99G無法使用vi or cat 的方式抓取該檔案，所以我用head 取出前500行的指令去看，發現過高的High version count，且竟然是 insert into RecycleBin$等語句所引起的!!




也透過另外一個trace file去追到是由我們程式在排drop 某些表格所引起的。


由於過很久才想到要記錄這個問題，所以有點忘記是怎麼對應process的，但結論可以看出兩個問題。
  1 . 大量 drop table的處裡方式，由於11g有 RecycleBin，所以 drop table 是否要加上purge 由我們程式人員去煩惱。
  2 . High version count問題，由下圖看，Parent cursor能共用但衍生出很多的Child cursor，查到 Bug:8946311有在描述這問題。


該Bug表示當一個Parent cursor有超出一定範圍的Chile cursor則該Parent cursor age out 並重起一個Parent cursor，但11.2.0.1 11.2.0.2 沒有這特性??

雖然11.2.0.1 11.2.0.2 可以打 Patch 後設定 _cursor_obsolete_threshold 閥值來避免這問題發生，但考慮要停機，所以先改成事解決，但由於這個語法是無法避免的 High Version count，recycle bin的調整不談，短時間內大量的drop table勢必會產生此問題，所以最好還是得上Patch。

11.2.0.3 預設有 _cursor_obsolete_threshold 閥值 100。

This will introduce unpublished Bug 10187168.

The error ORA-600 [15206] means that we are trying to create the 65536th child cursor. This upper limit was introduced in the fix for Bug:8946311.

This patch allows parent cursors to be obsoleted if the version count exceeds a set threshold.
It introduces the new hidden parameter "_cursor_obsolete_threshold".

In versions PRIOR TO 11.2.0.3: event 106001 is used instead of "_cursor_obsolete_threshold"

High version counts can lead to mutex contention and other contention issues, even if the high version count is for a valid reason.