优化oracle读写任务

 

 查读盘次数最多的前十个sql操作：

SELECT *
  FROM (select PARSING_USER_ID,
               EXECUTIONS,
               SORTS,
               COMMAND_TYPE,
               DISK_READS,
               sql_text
          FROM v$sqlarea
         order BY disk_reads DESC)
 where ROWNUM < 10;

 

 发现有一条是垃圾操作。

 

select * from v$sqlarea where sql_text like '%PM_TD_PERFCDL_temp%'

select * from v$sql where sql_text like '%PM_TD_PERFCDL_temp%'

查到这条命令的执行时间：

 

 

 

 

 我猜他是定时操作：

在user_job中找到相应时间的任务：

 

 

 

发现是一个名为DT_PM_EXTENDEDGL();的过程：

create or replace procedure DT_PM_EXTENDEDGL is
  del_time  date;
  tablename varchar2(100);
  plsql     varchar2(400);
  cursor perftableCur is
    select distinct a.tablename
    from pm_tablename_vs_groupid a,
         (select distinct b.group_id
            from pm_guideline b
           where b.net_type = 'TD'
             and b.type = 'NEDEF') c
   where a.group_id = c.group_id
     and a.net_type = 'TD'
     order by a.tablename;
begin
  del_time := sysdate - 1 / 24;
  dbms_output.put_line('delete time is:' ||
                       to_char(del_time, 'yyyy-mm-dd hh24:mi:ss'));

  for perftableCurRec in perftableCur loop
    tablename := perftableCurRec.tablename;
    plsql     := 'delete from ' || tablename ||
                 '_temp where begin_time <=(sysdate - 1 / 24)';
    --dbms_output.put_line(plsql);
    execute immediate plsql;
    commit;
  end loop;

end DT_PM_EXTENDEDGL;

 

 

执行前面的select语句：

 

select distinct a.tablename
  from pm_tablename_vs_groupid a,
       (select distinct b.group_id
          from pm_guideline b
         where b.net_type = 'TD'
           and b.type = 'NEDEF') c
 where a.group_id = c.group_id
   and a.net_type = 'TD'
 order by a.tablename;

 

 

 

 结果都是废弃的表。

 

可以把这个任务停掉？