ITPub博客

首页 > 数据库 > Oracle > Oracle日常问题-临时表过多导致exp速度慢

Oracle日常问题-临时表过多导致exp速度慢

原创 Oracle 作者:chenoracle 时间:2020-02-17 14:46:29 0 删除 编辑

 

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",共同学习,共同成长!!!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2675881/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle 11g OCP、Oracle 11g OCM、OCMU 用户组成员,微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    452
  • 访问量
    909340