查看: 2585|回复: 5

这么一句存储过程该如何优化?

[复制链接]
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2010-9-19 13:44 | 显示全部楼层 |阅读模式
本来我一般是用profiler来优化存储过程的,但是如下这句就是用它提取不出没步执行和消耗的信息
请大家帮忙看看如何优化才好?
create or replace procedure localmart.SP_GET_WD_VALUE(p_number out number) is

  v_dw         dwv_dict_tables%rowtype;
  l_count      number(10) := 0;
  v_sql_create varchar2(4000);
  v_sql_insert varchar2(4000);
  v_comments   varchar2(255);
  v_db_links   varchar2(50) := '@locmart2td';

  cursor c_2 is
    select *
      from dwv_dict_tables
     where comments <> 'OK';
begin
  p_number := 0;
  update dwv_dict_tables
     set STARTTIMESTAMP = null, LASTALTERTIMESTAMP = null, comments = 'N';
  commit;

  open c_2;
  loop
    fetch c_2
      into v_dw;
    exit when c_2%notfound;
  
    update dwv_dict_tables
       set STARTTIMESTAMP = sysdate
     where DATABASENAME = v_dw.databasename
       and tablename = v_dw.tablename;
  
    if v_dw.rename_flag = 1 then
      select count(1)
        into l_count
        from user_tables
       where table_name = upper(v_dw.tablename) || '_TMP';
   
      if l_count > 0 then
        begin
          execute immediate 'truncate table ' || v_dw.tablename || '_TMP';
        
          v_sql_insert := 'insert into ' || v_dw.tablename || '_TMP ' ||
                          v_dw.sql_txt || v_db_links;
          execute immediate v_sql_insert;
          commit;
        
          update dwv_dict_tables
             set comments = 'OK', LASTALTERTIMESTAMP = sysdate
           where DATABASENAME = v_dw.databasename
             and tablename = v_dw.tablename;
          commit;
        
          execute immediate 'rename ' || v_dw.tablename || ' to ' ||
                            v_dw.tablename || '_TMP1';
        
          execute immediate 'rename ' || v_dw.tablename || '_TMP to ' ||
                            v_dw.tablename;
        
          execute immediate 'rename ' || v_dw.tablename || '_TMP1 to ' ||
                            v_dw.tablename || '_TMP';
        exception
          when others then
            v_comments := substr(sqlerrm, 1, 255);
            update dwv_dict_tables
               set comments = v_comments
             where DATABASENAME = v_dw.databasename
               and tablename = v_dw.tablename;
            commit;
        end;
      else
        begin
          v_sql_create := 'create table ' || v_dw.tablename || '_TMP as ' ||
                          v_dw.sql_txt || v_db_links;
          execute immediate v_sql_create;
        
          update dwv_dict_tables
             set comments = 'OK', LASTALTERTIMESTAMP = sysdate
           where DATABASENAME = v_dw.databasename
             and tablename = v_dw.tablename;
          commit;
        
          execute immediate 'rename ' || v_dw.tablename || ' to ' ||
                            v_dw.tablename || '_TMP1';
        
          execute immediate 'rename ' || v_dw.tablename || '_TMP to ' ||
                            v_dw.tablename;
        
          execute immediate 'rename ' || v_dw.tablename || '_TMP1 to ' ||
                            v_dw.tablename || '_TMP';
        exception
          when others then
            v_comments := substr(sqlerrm, 1, 255);
            update dwv_dict_tables
               set comments = v_comments
             where DATABASENAME = v_dw.databasename
               and tablename = v_dw.tablename;
            commit;
        end;
      end if;
    else
      select count(1)
        into l_count
        from user_tables
       where table_name = upper(v_dw.tablename);
   
      if l_count > 0 then
        begin
          execute immediate 'truncate table ' || v_dw.tablename;
        
          v_sql_insert := 'insert into ' || v_dw.tablename || ' ' ||
                          v_dw.sql_txt || v_db_links;
          execute immediate v_sql_insert;
          commit;
        
          update dwv_dict_tables
             set comments = 'OK', LASTALTERTIMESTAMP = sysdate
           where DATABASENAME = v_dw.databasename
             and tablename = v_dw.tablename;
          commit;
        exception
          when others then
            v_comments := substr(sqlerrm, 1, 255);
            update dwv_dict_tables
               set comments = v_comments
             where DATABASENAME = v_dw.databasename
               and tablename = v_dw.tablename;
            commit;
        end;
      else
        begin
          v_sql_create := 'create table ' || v_dw.tablename || ' as ' ||
                          v_dw.sql_txt || v_db_links;
          execute immediate v_sql_create;
        
          update dwv_dict_tables
             set comments = 'OK', LASTALTERTIMESTAMP = sysdate
           where DATABASENAME = v_dw.databasename
             and tablename = v_dw.tablename;
          commit;
        exception
          when others then
            v_comments := substr(sqlerrm, 1, 255);
            update dwv_dict_tables
               set comments = v_comments
             where DATABASENAME = v_dw.databasename
               and tablename = v_dw.tablename;
            commit;
        end;
      end if;
    end if;
  end loop;
  commit;
  close c_2;
exception
  when others then
    p_number := sqlcode;
    rollback;
    dbms_output.put_line(substr(sqlerrm, 1, 255));
end sp_get_wd_value;
论坛徽章:
13
数据库板块每日发贴之星
日期:2010-08-24 01:01:012012新春纪念徽章
日期:2012-01-04 11:57:13ITPUB十周年纪念徽章
日期:2011-11-01 16:25:51数据库板块每日发贴之星
日期:2011-07-11 01:01:01ITPUB伯乐
日期:2011-06-16 10:11:39ITPUB季度 技术新星
日期:2011-01-17 11:30:46授权会员
日期:2010-12-28 19:29:32ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2010-09-07 01:01:01数据库板块每日发贴之星
日期:2010-08-28 01:01:01
发表于 2010-9-19 13:54 | 显示全部楼层
太长了!

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2010-9-19 14:03 | 显示全部楼层


你代码中的要执行的动态语句太多了, 尤其还有不少DDL语句需要动态来执行, 因此无法使用高速库缓存, 并且还使用了DBLINK, 这更进一步造成你的这个存储过程执行效率非常差, 如果游标的数据量非常小, 可能还过得去, 但是数据量非常大时, 那就非常糟糕了, 估计你也没什么好优化的, 需求和设计决定程序的性能!

使用道具 举报

回复
论坛徽章:
21
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44生肖徽章2007版:兔
日期:2011-01-20 12:58:492011新春纪念徽章
日期:2011-02-18 11:42:47ITPUB十周年纪念徽章
日期:2011-11-01 16:20:282012新春纪念徽章
日期:2012-01-04 11:50:44法拉利
日期:2013-09-10 14:11:32本田
日期:2014-02-16 22:57:59奔驰
日期:2014-02-16 23:04:212014年新春福章
日期:2014-02-18 16:41:112011新春纪念徽章
日期:2011-01-04 10:24:58
发表于 2010-9-19 16:05 | 显示全部楼层
收益,受用!

使用道具 举报

回复
论坛徽章:
30
ITPUB社区千里马徽章
日期:2013-06-21 13:15:17itpub13周年纪念徽章
日期:2014-09-28 10:55:55马上有房
日期:2014-12-26 16:11:152015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39秀才
日期:2017-02-22 15:16:26乌索普
日期:2017-02-28 15:30:00蒙奇·D·路飞
日期:2017-08-28 16:16:37罗罗诺亚·索隆
日期:2017-09-08 13:32:34ITPUB15周年纪念
日期:2017-10-23 13:45:27
发表于 2013-5-3 16:45 | 显示全部楼层
bell6248 发表于 2010-9-19 14:03
你代码中的要执行的动态语句太多了, 尤其还有不少DDL语句需要动态来执行, 因此无法使用高速库缓存,  ...

顶一个

使用道具 举报

回复
论坛徽章:
25
ITPUB元老
日期:2005-02-28 12:57:00咸鸭蛋
日期:2013-02-07 11:51:42咸鸭蛋
日期:2013-02-08 09:48:51蜘蛛蛋
日期:2013-02-21 15:47:392013年新春福章
日期:2013-02-25 14:51:24咸鸭蛋
日期:2013-02-28 17:08:42蜘蛛蛋
日期:2013-03-29 16:17:14双黄蛋
日期:2013-04-11 16:11:04咸鸭蛋
日期:2013-05-07 11:55:14咸鸭蛋
日期:2013-05-28 10:46:24
发表于 2013-5-3 17:20 | 显示全部楼层
太j长了!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表