6 积分	1 好友	2 主题

[SQL调优] oracle 10g goldengate进程同步Lag time 不减

1^#

发表于 2013-4-10 10:20:26 | 查看: 2682| 回复: 1

goldengate包名：fbo_ggs_Linux_x64_ora10g_64bit.tar
数据库版本：10.2.0.5
操作系统版本：CentOS 6.4
基本情况，由于业务需求不能使用分区表删除的方法，采用批量删除。

create or replace procedure sys_pro_his_table(table_name varchar2,
condition varchar2) is
type mycursor_type is ref cursor;
mycursor mycursor_type;
type rowid_table_type is table of rowid index by pls_integer;
v_rowid rowid_table_type;
v_sql_getrowid varchar2(400);
v_sql_del_table varchar2(400);
BEGIN
execute immediate 'insert /*+ append*/ into ' ||table_name|| '_HIS select * from ' || table_name || ' t WHERE ' ||condition;
commit;
v_sql_getrowid := 'SELECT ROWID FROM ' || table_name || ' t WHERE ' ||condition || ' order by rowid ';
v_sql_del_table := 'delete from ' || table_name || ' t WHERE rowid = :1';
open mycursor for v_sql_getrowid;
loop
fetch mycursor bulk collect--------每次处理5000行，也就是每5000行一提交
into v_rowid limit 5000;
exit when v_rowid.count = 0;
forall i in v_rowid.first .. v_rowid.last
execute immediate v_sql_del_table using v_rowid(i);
commit;
end loop;
close mycursor;
END;
begin
sys_pro_his_table( 'COMMENT_RECORD_BAK','exists (select 1 from comment_ad_item where status = ''1'' and id = t.comment_ad_item_id) and t.comment_time<=trunc(sysdate)');
end;
/

复制代码

job

--1、任务 JOB_sys_pro_his_table
declare
JOBNAME varchar2(100) := 'JOB_sys_pro_his_table';
JOB_CNT int;
begin
select count(*) into JOB_CNT from user_scheduler_jobs uj where upper(uj.JOB_NAME) = upper(JOBNAME);
if JOB_CNT >= 1 then
begin dbms_scheduler.drop_job(job_name => JOBNAME); end;
end if;
end;
/
begin
dbms_scheduler.create_job
(
job_name => 'JOB_sys_pro_his_table',
job_type => 'PLSQL_BLOCK',
job_action => 'begin sys_pro_his_table( ''COMMENT_RECORD'',''exists (select 1 from comment_ad_item where status = ''''1'''' and id = t.comment_ad_item_id) and t.comment_time<=trunc(sysdate)'');END;',
repeat_interval => 'FREQ=DAILY;BYHOUR=3;byminute=30',
enabled => true
);
end;
/
--使job生效
exec DBMS_SCHEDULER.enable('JOB_sys_pro_his_table');