Oracle日常問題-臨時表過多導致exp速度慢

 

Oracle日常問題 -臨時表過多導致 exp速度慢

 

問題現象：

客戶反饋資料庫日常備份速度越來越慢。

問題原因：

遠端檢視資料庫大小隻有5G ，備份卻需要 5 小時以上。

---5G

SQL> Select sum(bytes)/1024/1024/1024 from dba_segments where owner= ’ CJC ’ ; 

在進行exp 備份時，先匯出建立表的語句，在匯出表資料，檢視備份日誌，還沒有匯出表資料就開始卡住了， 猜測表資料量不大，但是表數量很大 ，導致在 exp 一開始匯出建立表語句時卡住。

最終檢視到CJC 使用者下存在67 萬張臨時表；

S QL> select count(*) from  user_tables where temporary='Y';

  COUNT(*)

----------

    673165

其中以 TEM_ 開頭的臨時表有62 萬張，以 TMPTABSUBJ% 開頭的有4 萬多張；

SQL> select count(*) from user_tables where temporary='Y' and table_name like'TEM_%';

  COUNT(*)

----------

    623866

SQL> select count(*) from user_tables where temporary='Y' and table_name like'TMPTABSUBJ%';

  COUNT(*)

----------

     47899

其中 TEM_ 開頭臨時表都是在09-14 年產生的，平均每天產生 1 萬張臨時表， 15-16 年沒有這種型別的臨時表；

SQL> select * from (select to_char(created,'yyyymmdd'),count(*)

  2  from user_tables a,user_objects b

  3  where a.table_name=b.object_name

  4  and a.temporary='Y'

  5  and a.table_name like'TEM_%'

  6  group by to_char(created,'yyyymmdd')

  7  order by 1 desc

  8  )

  9  where rownum<=1000;

TO_CHAR(CREATED,'YYYYMMDD')   COUNT(*)

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

20140920                           122

20140919                         12207

20140918                         11449

20140917                         10951

20140916                         15047

20140915                         18865

......

69 rows selected

其中 TMPTABSUBJ 開頭臨時表都是在09-13 年產生的， 14-16 年沒有這種型別的臨時表；

SQL> select * from (

  2  select to_char(created,'yyyymmdd'),count(*)

  3  from user_tables a,user_objects b

  4  where a.table_name=b.object_name

  5  and a.temporary='Y'

  6  and a.table_name like'TMPTABSUBJ%'

  7  group by to_char(created,'yyyymmdd')

  8  order by 1 desc

  9  )

 10  where rownum<=1000;

TO_CHAR(CREATED,'YYYYMMDD')   COUNT(*)

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

20130930                           109

20130929                           133

20130928                            13

......

30 rows selected

估計是應用程式使用完臨時後沒有及時自動刪除，導致臨時表資料量越來越多，在研發出補丁清理臨時表之前，可以先透過儲存過程，自動刪除幾天前的臨時表。

先備份使用者下所有表， 然後透過下面的儲存過程刪除5天前產生的 TEM_ 開頭和 TMPTABSUBJ% 開頭的臨時表；

--- 建立刪除臨時表的儲存過程

CREATE   OR   REPLACE   PROCEDURE  DROP_TEMPTAB AS

   CURSOR  a IS

     select  table_name

       from  user_tables c ,  user_objects d

      where  c.table_name =  d.object_name

        and  c.temporary =   'Y'

        and   ( c.table_name like   'TEM_%'   or  c.table_name like   'TMPTABSUBJ%' )

        and  d.object_type =   'TABLE'

        and  d.temporary =   'Y'

        and  d.CREATED <   sysdate   -   5 ;

BEGIN

   FOR  i IN  a LOOP

     EXECUTE   IMMEDIATE   'drop table '   ||  i.table_name ;

   END   LOOP ;

END ;

新增JOB ，定期執行該儲存過程，自動刪除臨時表， 每天 3 點執行 JOB ，每 2 天執行一次；

SQL> VARIABLE JOBNO NUMBER;

SQL> VARIABLE INSTNO NUMBER;

SQL>

SQL> BEGIN

  2         SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;

  3         DBMS_JOB.SUBMIT(:JOBNO,

  4                         'DROP_TEMPTAB; ',

  5                         TRUNC(SYSDATE) + 1 + 3 / 24,

  6                         'TRUNC(SYSDATE)+ 2 + 3 /24',

  7                         TRUE,

  8                         :INSTNO);

  9         COMMIT;

 10       END;

 11  /

PL/SQL procedure successfully completed

檢視JOB 是否建立成功

SQL> select * from dba_jobs;

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！