oracle 新建优化任务,优化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;

结果都是废弃的表。

可以把这个任务停掉？

标签：group,where,读写,tablename,type,oracle,优化,id,select

来源： https://www.cnblogs.com/wangziyi0513/p/11210239.html