文档章节

PL/SQL优化案例之一

hnairdb
 hnairdb
发布于 2018/11/02 17:20
字数 1498
阅读 34
收藏 0

有个存储过程从周五晚上跑了到了周一还没有跑完,存储过程代码如下:

TMP_NBR_NO_XXXX共有400w行数据,180MB。

For in 后面的查询 select nli.*, .......   and ns2l.nbr_level_id between 201 and 208 order by nl2i.priority; 查询返回43行数据。

 

在优化sql的时候,我们第一眼要找出可能出现性能的地方:

1、这个案例逻辑是比较复杂的,loop套loop,这就造成了 笛卡尔乘积,避免少扫描,优化逻辑。(可以使用循环表的方式来减少loop循环次数)

2、where 条件过滤 是否走索引了,避免全表扫描,

3、select * 要优化掉,避免查询数据字典

4、找到驱动表,尽量小表在前(这里跟sql语句不一样,CBO 会自动选择驱动表,但是在plsql里面loop嵌套loop ,逻辑都固定死了,哪个在前哪个就是驱动表)

5、内层循环为啥要排序?有必要吗?

6、regexp_like 正则表达式不走索引,优化掉。(业务逻辑需要,这个不能优化)

7、最内层的update 是否有索引?如没有,那么将会全表扫描很多次。

8、这里值得一提的是 开发写了批量提交,在大量DML操作的时候 这个是一个很好的方法。

9、是否收集了统计信息?(这个不一定,统计信息收集是要根据执行计划来判断的,有的sql收集了统计信息反而慢了)

有问题的地方我都圈起来了,如下:

嵌套循环就是一个loop套loop相当于笛卡尔积。该PLSQL代码中有loop套loop的情况,这就导致UPDATE TMP_NBR_NO_XXXX要执行400w*43次,TMP_NBR_NO_XXXX.no列没有索引,TMP_NBR_NO_XXXX每次更新都要进行全表扫描。这就是为什么存储过程从周五跑到周一还没跑完的原因。

有读者可能会问,为什么不用MERGE进行改写呢?在PLSQL代码中是用regexp_like关联的.无法走hash连接,也无法走排序合并连接,两表只能走嵌套循环并且被驱动表无法走索引。如果强行使用MERGE进行改写,因为该SQL执行时间很长,会导致UNDO不释放,因此,没有采用MERGE INTO对代码进行改写。

    有读者可能也会问,为什么不对TMP_NBR_NO_XXXX.no建立索引呢?因为关联更新可以采用ROWID批量更新,所以没有采用建立索引方法优化。

下面采用ROWID批量更新方法改写上面PLSQL,为了方便读者阅读PLSQL代码,先创建一个临时表用于存储43记录:

create table TMP_DATE_TEST as

  select  nli.expression, nl.nbr_level_id, priority   from tmp_xxx_item

  ...... and ns2l.nbr_level_id between 201 and 208;

 创建另外一个临时表,用于存储要被更新的表的ROWID以及no字段:

create table TMP_NBR_NO_XXXX_TEXT as

select rowid rid, nbn.no from TMP_NBR_NO_XXXX nbn

 Where nbn.level_id=1 and length(nbn.no)= 8;  

 改写之后的PLSQL代码:

declare
  type rowid_table_type is table of rowid index by pls_integer;
  updateCur sys_refcursor;v_rowid rowid_table_type;
begin
  for c_no_data in (select t.expression, t.nbr_level_id, t.priority   from TMP_DATE_TEST t order by 3) loop
    open updateCur for  select rid   from TMP_NBR_NO_XXXX_TEXT nbn
       where regexp_like(nbn.no, c_no_data.expression);
    loop
      fetch updateCur bulk collect  into v_rowid LIMIT 20000;
      forall i in v_rowid.FIRST .. v_rowid.LAST
      update TMP_NBR_NO_XXXX  set level_id = c_no_data.nbr_level_id   
       where rowid = v_rowid(i); commit;
      exit when updateCur%notfound;
    end loop;
    CLOSE updateCur;
  end loop;
end;

循环嵌套的逻辑改成了循环表,循环表总比循环嵌套游标要好的多,改写后的PLSQL能在4小时左右跑完。有没有什么办法进一步优化呢?单个进程能在4小时左右跑完,如果开启8个并行进程,那应该能在30分钟左右跑完。但是PLSQL怎么开启并行呢?正常情况下PLSQL是无法开启并行的,如果直接在多个窗口中执行同一个PLSQL代码,会遇到锁争用,如果能解决锁争用,在多个窗口中执行同一个PLSQL代码,这样就变相实现了PLSQL开并行功能。可以利用ROWID切片变相实现并行:

select DBMS_ROWID.ROWID_CREATE(1,c.oid,e.RELATIVE_FNO,e.BLOCK_ID,0) minrid,
       DBMS_ROWID.ROWID_CREATE(1,c.oid,e.RELATIVE_FNO,e.BLOCK_ID+e.BLOCKS-1,     10000) maxrid from dba_extents e,
(select max(data_object_id)oid from dba_objects where object_name= 
'TMP_NBR_NO_XXXX_TEXT' and owner='RESXX2')and data_object_id is not null) c
 where e.segment_name='TMP_NBR_NO_XXXX_TEXT'and e.owner = 'RESXX2';

但是这时发现,切割出来的数据分布严重不均衡,这是因为创建表空间的时候没有指定uniform size 的Extent所导致的。于是新建一个表空间,指定采用uniform size方式管理Extent:

create tablespace TBS_BSS_FIXED datafile '/oradata/bs_bss_fixed_500.dbf' 
       size 500M extent management local uniform size 128k;

重建一个表用来存储要被更新的ROWID:

create table RID_TABLE
( rowno  NUMBER,  minrid VARCHAR2(18),  maxrid VARCHAR2(18)) ;  

将ROWID插入到新表中:

insert into rid_table 
select rownum rowno, 
 DBMS_ROWID.ROWID_CREATE(1,c.oid,e.RELATIVE_FNO,e.BLOCK_ID, 0) minrid,
 DBMS_ROWID.ROWID_CREATE(1,c.oid,e.RELATIVE_FNO,e.BLOCK_ID + e.BLOCKS - 1, 10000) maxrid  from dba_extents e,
(select max(data_object_id)oid from dba_objects where object_name= 
'TMP_NBR_NO_XXXX_TEXT' and owner='RESXX2')and data_object_id is not null) c
 where e.segment_name='TMP_NBR_NO_XXXX_TEXT'and e.owner = 'RESXX2';

 这样RID_TABLE中每行指定的数据都很均衡,大概4035条数据。最终更改的PLSQL代码:

create or replace  procedure  pro_phone_grade(flag_num in number)
as 
 type rowid_table_type is table of  rowid index  by  pls_integer;  
 updateCur  sys_refcursor;v_rowid  rowid_table_type;
 v_rowid2  rowid_table_type;
begin
for  rowid_cur in (select  *  from  rid_table  where mod(rowno, 8)=flag_num
 loop
    for c_no_data in (select t.expression, t.nbr_level_id, t.priority  from TMP_DATE_TEST t order by 3 ) 
       loop
         open  updateCur  for  select rid,rowid  from TMP_NBR_NO_XXXX_TEXT  nbn
           where rowid between rowid_cur.minrid and rowid_cur.maxrid  
          and regexp_like(nbn.no, c_no_data.expression);
          loop
            fetch updateCur  bulk collect  into  v_rowid, v_rowid2  LIMIT 20000;
              forall i in v_rowid.FIRST ..v_rowid.LAST
              update TMP_NBR_NO_XXXX  set  level_id = c_no_data.nbr_level_id          
               where rowid = v_rowid(i);    commit;           
            exit when  updateCur%notfound;
         end loop; 
         CLOSE updateCur; 
       end loop;
   end loop;
end;

然后在8个窗口中同时运行上面PLSQL代码:

Begin  pro_phone_grade(0); end; ..... Begin pro_phone_grade(7); end;

最终能在29分左右跑完所有存储过程。本案例技巧就在于ROWID切片实现并行,并且考虑到了数据分布对并行的影响,其次还使用了ROWID关联更新技巧。

© 著作权归作者所有

hnairdb
粉丝 23
博文 126
码字总数 124984
作品 0
深圳
数据库管理员
私信 提问
周末好福利:你离数据库大神也许只差一本专攻好书

在时间与实战的校验下,各类数据库顺应市场需求不断迭代,由此发展出了层出不穷的新能力……于是,想要把握技术趋势,掌握最优技能,读书无疑是IT人精进的优选方式。 为了帮助大家巩固、提高...

dbaplus社群
2018/09/03
0
0
PL/SQL-2 复合数据类型

-------------------------------------------------------------------------- ************************************************************************** 第二部分:复合数据类型 ****......

晨曦之光
2012/04/19
142
0
聚合和成员函数

--案例01:简单测试 declare type studenttabletype is table of varchar(10) index by binary_integer; stu1 studenttabletype ; begin stu1(1):='tom'; stu1(2):='wind'; dbms_output.put_......

晨曦之光
2012/04/19
78
0
每周一书《Oracle 12 c PL(SQL)程序设计终极指南》

本周为大家送出的书是《Oracle 12 c PL(SQL)程序设计终极指南》,此书由机械工业出版社出版, 孙风栋,王澜,郭晓惠 著。 内容简介: 《Oracle 12c PL/SQL程序设计终极指南》志在打造PL/SQL领...

zting科技
2017/12/11
0
0
韩顺平玩转oracle之pl/sql听课笔记《三》

有些惭愧,距离第2次听课笔记远了。这一讲主要是关于函数,包,还有变量的解释。 1 函数和过程的区别 1)函数:返回特定值,通常是一个; 过程:执行特定操作 2)建立函数时: 在函数头须包含...

长平狐
2012/09/19
45
0

没有更多内容

加载失败,请刷新页面

加载更多

java通过ServerSocket与Socket实现通信

首先说一下ServerSocket与Socket. 1.ServerSocket ServerSocket是用来监听客户端Socket连接的类,如果没有连接会一直处于等待状态. ServetSocket有三个构造方法: (1) ServerSocket(int port);...

Blueeeeeee
今天
6
0
用 Sphinx 搭建博客时,如何自定义插件?

之前有不少同学看过我的个人博客(http://python-online.cn),也根据我写的教程完成了自己个人站点的搭建。 点此:使用 Python 30分钟 教你快速搭建一个博客 为防有的同学不清楚 Sphinx ,这...

王炳明
昨天
5
0
黑客之道-40本书籍助你快速入门黑客技术免费下载

场景 黑客是一个中文词语,皆源自英文hacker,随着灰鸽子的出现,灰鸽子成为了很多假借黑客名义控制他人电脑的黑客技术,于是出现了“骇客”与"黑客"分家。2012年电影频道节目中心出品的电影...

badaoliumang
昨天
14
0
很遗憾,没有一篇文章能讲清楚线程的生命周期!

(手机横屏看源码更方便) 注:java源码分析部分如无特殊说明均基于 java8 版本。 简介 大家都知道线程是有生命周期,但是彤哥可以认真负责地告诉你网上几乎没有一篇文章讲得是完全正确的。 ...

彤哥读源码
昨天
15
0
jquery--DOM操作基础

本文转载于:专业的前端网站➭jquery--DOM操作基础 元素的访问 元素属性操作 获取:attr(name);$("#my").attr("src"); 设置:attr(name,value);$("#myImg").attr("src","images/1.jpg"); ......

前端老手
昨天
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部