Oracle資料表空間增長異常解決又一例

Oracle資料表空間增長異常解決又一例

描述：客戶環境某台資料庫暫存資料表空間異常增長（日期隨機），觀察下來可能是下列兩個Oracle自身的定時job引起，
 
時間：2014/01/14  淩晨1點左右
 
        2014/01/08  淩晨4點左右 
 
已知的暫存資料表空間增長時間，和trace檔案錯誤吻合， 請幫忙分析原因，及解決辦法。
 
如需要登入伺服器，請電話聯絡陸耀祺，他將電話告知相關資訊。
 
 
 
附件為相關日誌，和一個自訂跟蹤資料檔案大小日誌，間隔為1小時。 

 

調查：我們發現客戶環境有一條SQL語句的運行導致了暫存資料表空間的異常增長。
 
這條SQL語句的意思貌似是查詢最近一個星期的DB time和DB CPU。
 
麻煩跟開發確認一下這條語句的作用，是否可以進行最佳化。

 

後續調查：
 
查看dba_registry中CATPROC狀態為invalid狀態。同時導致在dba_objects中看到收集統計資訊的dbms_stats也是invalid狀態。
 
手動運行@?/rdbms/admin/catproc.sql編譯一下這個組件（execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;）；
 
接著重新編譯一下dbms_stats這個包
 
再重新編譯上面三個WRH$的三個基表。

 

原因：問題已經查明，那條SQL語句為SYS使用者在晚上執行定時任務時所執行。
 
之所以佔用較大的暫存資料表空間是因為該語句所涉及的三張表：
 
WRH$_STAT_NAME
 
WRM$_SNAPSHOT
 
WRH$_SYS_TIME_MODEL
 
統計資訊不準，導致產生了較差的執行計畫。
 
手工收集統計資訊以後，該語句可以很快完成，而且佔用暫存資料表空間也很小。
 
這兩個資料庫的CATPROC組件（Oracle Database Packages and Types）之前處於INVALID狀態，造成dbms_stats預存程序處於不可用狀態，因此無法自動收集統計資訊。目前dbms_stats已經處於可用狀態。

Oracle 資料表空間異常增長過快解決方案

Oracle教程：執行個體故障恢複

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2