jonas_li 发表于 2012-9-23 21:56
要优化,lz可以先在没步记录一个日志,看每步的实践分布情况,看程序主要耗时的那几步,处看程序,
1 dblink处理 ...
消耗时间的sql 已经跟踪出来了
第一个是下面的
INSERT/*+append*/ INTO ZDRY_DATA
(id, version, hjdpcsdm, zdrylbbj, ywxm, rylx, xzdpcsmc, csrq, xm, hjdqh_id, xb_id, jzdxz,
zdry_id, sfzh, yjcsjb, zt, jzdqh_id, mz_id, jlbgsj, xzdpcsdm, xmpy, yxx, jlxzsj, hjdxz,
jg_id, gj_id, hjdpcsmc,zdrybh,xzbs
)
SELECT zdry_data_seq.NEXTVAL,0 as v,rj.hjdpcs,rj.zdrylbbj,rj.ywxm,'bsxf' AS rylx,rj.hjdpcs,
rj.csrq,rj.xm,'' AS hjdqh,'' AS xb,rj.hjdxz,NULL,trim(rj.sfzh),'' yjczlb,'wfp','' AS hjdqh,'' AS mz,
trim(rj.jlbgsj),rj.hjdpcs,rj.xmpy,yxx,trim(rj.jlxzsj),rj.hjdxz,'' AS jg,'' AS gj,rj.hjdpcs,trim(zdrybh),'1'
FROM vw_zdry_jxxx@st_zdry.regress.rdbms.dev.us.oracle.com rj
WHERE JLXZSJ> v_maxdate;
------------------------------------------------
还有一个消耗时间的地方 (这个消耗的时间也比较多。因为是全表插入。我可以根据逻辑关系只插入需要用到的部分,这个是能解决的。主要还是上面的那个)
insertintot_zdry_jxxx select trim(zdrybh),trim(yxx) from vw_zdry_jxxx@st_zdry.regress.rdbms.dev.us.oracle.com;
——————————————————
求解决!!!!
|