www.Orasql.Com
 
用户名: 密码:  验证码: 注册|找回密码 oracleawen's blog  
 
         当前位置 >> 技术论坛  发表帖子     论坛版块     查找帖子
一个dblink的sql执行慢问题-技术论坛||Oracle高手之路—Orasql.Com!
            一个dblink的sql执行慢问题
 
发贴人:oracle_awen
oracleawen@gmail.com448315233127.0.0.1
用户级别:管理员
发贴总数:1245
注册时间:2011-05-05 09:23:03
2011-06-01 08:15:45
一个dblink的sql执行慢问题 一个sql大致结构如下 --------------------------------- insert into table1 values (select xx1, xx2, xx3, sysdate from (select yy1,yy2 from table2@dblink where ...) a, table3@dblink b, table4@dblink c where ...) --------------------------------- 运行很慢,需要2个小时左右才跑完。 我做了相关的一些测试,结果如下 1. 如上sql执行很慢(2小时) 2. 删除掉sql中的sysdate后(同时删除table1的date类型column)插入就很快 3. 将sysdate改成to_char(同时将table1的date类型column改为varchar2类型)后就很快 4. 将sysdate改成自定义时间的to_date后又很慢 5. 去掉insert而单独运行select(包含sysdate)就很快 6. table1重建也无效 7. 去除dblink后在远程db运行(远程db上创建table1一样的表)就很快 8. 从pl/sql developer上的f5查看执行计划(根据是否删除syadate)做对比,发现结果一样 通过对运行包含该sql的procedure做了level 12的10046 trace,tkprof后相关内容如下 insert into kpi.kpi_ct_all_spc2 (select to_char(start_time, 'yyyy') year, to_number(to_char(start_time, 'mm'), '99') mon, data_area_id module, dcop_group, dcop_name dcop, dcop_desc, param_name, spec_low, spec_high, target, unit, param_group, avg(value) mean, stddev(value) std, max(value) - min(value) range, count(distinct lot_id) lots, decode(stddev(value), 0, -1, (avg(value) - spec_low) / (3 stddev(value))) cpl, decode(stddev(value), 0, -1, (spec_high - avg(value)) / (3 stddev(value))) cpu, decode(stddev(value), 0, -1, (spec_high - spec_low) / (6 stddev(value))) cp, sysdate lm_time from (select hst.start_time, hst.product_id, hst.lot_id, param.param_name, param.param_group, rest.site_name, rest.value, spec.target, spec.spec_low, spec.spec_high, spec.unit, spec.control_high, spec.control_low, hst.equip_id, dcop.dcop_name, dcop.dcop_group, dcop.data_area_id, dcop.dcop_desc, lot.lot_type from met_wafer_hst_t@edaprod hst, met_param_t@edaprod param, met_result_t@edaprod rest, met_spec_t@edaprod spec, met_dcop_t@edaprod dcop, (select lot_id, lot_type from cmn_lot_t@edaprod where 1 = 1) lot where rest.met_wafer_index = hst.met_wafer_index and rest.met_param_index = param.met_param_index and hst.start_time between b2 -11/36 and b1 -11/36 and spec.met_spec_index = (select spec1.met_spec_index from met_spec_t@edaprod spec1 where spec1.met_param_index = param.met_param_index and spec1.spec_active_time = (select max(spec2.spec_active_time) from met_spec_t@edaprod spec2 where spec2.met_param_index = param.met_param_index and spec2.spec_active_time <= (select max(hst1.start_time) from met_wafer_hst_t@edaprod hst1 where hst.met_wafer_index = hst1.met_wafer_index) and spec2.target is not null group by spec2.met_param_index)) and dcop.met_dcop_index = param.met_dcop_index and lot.lot_id = hst.lot_id and hst.met_wafer_index = (select max(met_wafer_index) from met_wafer_hst_t@edaprod where lot_id = hst.lot_id and wafer_no = hst.wafer_no and step_index = hst.step_index)) a group by to_char(start_time, 'yyyy'), to_number(to_char(start_time, 'mm'), '99'), data_area_id, dcop_group, dcop_name, dcop_desc, param_name, spec_low, spec_high, target, unit, param_group) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- parse 1 0.00 0.32 0 9 1 0 execute 1 5854.84 6825.73 0 12 157 352 fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 5854.84 6826.05 0 21 158 352 misses in library cache during parse 1 optimizer goal choose parsing user id 60 (recursive depth 1) elapsed times include waiting on following events event waited on times max. wait total waited ---------------------------------------- waited ---------- ------------ sqlnet message to dblink 5699725 0.00 3.33 sqlnet message from dblink 5699725 0.38 1178.09 sqlnet more data from dblink 17341718 0.03 69.81 从trace也看不出什么有用的信息,且跟绑定的变量应该没有关系,不影响结果。真疑惑了…… 是oracle 9i的bug?从metalink也查不到什么类似问题的bug,而且该sql我在oracle 10g上也跑了一次(远程db是同一个),结果一样慢。 请高手们帮我分析分析!!

      
 
  
 Orasql.Com|E-Mail:oracleawen@gmail.com|Phone:15271942915|Copyright © 2011|All Rights Reserved|