ITPub博客

首页 > 数据库 > Oracle > 复盘eygle在甲骨文大会上演讲中的示例,看看什么是大师的由点及面

复盘eygle在甲骨文大会上演讲中的示例,看看什么是大师的由点及面

Oracle 作者:pyzwj 时间:2015-08-03 10:46:50 0 删除 编辑

盖总(eygle)在刚结束的甲骨文大会的演讲中,通过一个简单的UPDATE语句,为我们展示了什么叫由点及面的优化,什么叫由点及面的知识覆盖度,不在于这个案具体如何操作,更应关注或更值得我们借鉴的是这种学习态度和方法思路,大师是如何炼成的?我想这个案例可以带给我们一些启迪。

 

下面就复盘一下这个案例的整个过程,注:版权归盖总(eygle)所有~

 

问题描述:

问题的标题是:“并行更新成为系统瓶颈”

SQL:

点击(此处)折叠或打开

  1. UPDATE /*+ parallel(a, 8) */ tbl_a a
  2. SET name = (SELECT name FROM tbl_b WHERE id = a.id),
  3.         class = (SELECT class FROM tbl_b WHERE id = a.id)
  4. WHERE a.id IN (SELECT /*+ parallel(b, 8) */ id FROM tbl_b b);


现象是这条SQL执行时间非常长,从介绍看是有2.5分钟。

 

优化过程:

1. 为了以下可以更清楚地说明问题,对这个SQL做了简化处理,我们需要优化的是这条SQL:

点击(此处)折叠或打开

  1. UPDATE tbl_a a
  2. SET name = (SELECT name FROM tbl_b WHERE id = a.id),
  3.         class = (SELECT class FROM tbl_b WHERE id = a.id)
  4. WHERE a.id IN (SELECT id FROM tbl_b b);


我们创建两张模拟表:

点击(此处)折叠或打开

  1. SQL> create table tbl_a(
  2.           id number,
  3.           name varchar2(5),
  4.           class varchar2(5));
  5. Table created.

  6. SQL> create table tbl_b(
  7.           id number,
  8.           name varchar2(5),
  9.           class varchar2(5));
  10. Table created.

  11. SQL> create sequence seq_a cache 1000;
  12. Sequence created.

  13. SQL> create sequence seq_b cache 1000;
  14. Sequence created.
插入一些随机数据:

点击(此处)折叠或打开

  1. begin
  2.   for i in 1 .. 100000 loop
  3.     insert into tbl_a values (seq_a.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));
  4.   end loop;
  5.   commit;
  6. end;
  7. /
  8. PL/SQL procedure successfully completed.

  9. SQL> select count(*) from tbl_a;
  10.   COUNT(*)
  11. ------------
  12.      100000

  13. begin
  14.   for i in 1 .. 10000 loop
  15.     insert into tbl_b values (seq_b.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));
  16.   end loop;
  17.   commit;
  18. end;
  19. /
  20. PL/SQL procedure successfully completed.

  21. SQL> select count(*) from tbl_b;
  22.   COUNT(*)
  23. ------------
  24.       10000

2. 执行原SQL语句

点击(此处)折叠或打开

  1. SQL> set timing on
  2. SQL> UPDATE tbl_a a
  3.           SET name = (SELECT name FROM tbl_b WHERE id = a.id),
  4.                  class = (SELECT class FROM tbl_b WHERE id = a.id)
  5.           WHERE a.id IN (SELECT id FROM tbl_b b);
  6. 10000 rows updated.

  7. Elapsed: 00:00:07.42


需要7秒多的时间(虽然和示例中2.5分钟有差距,但仅为了说明优化的问题,时间上的差距可以忽略)。

3. 第一次优化

我们从这个SQL中可以看到,更新TBL_A表的ID列,但TBL_B表的SELECT有三次,即三次的全表扫描,那么要是能减少TBL_B表检索的次数,执行时间肯定可以减少。

点击(此处)折叠或打开

  1. SQL> UPDATE tbl_a a
  2.           SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)
  3.           WHERE a.id IN (SELECT id FROM tbl_b b);
  4. 10000 rows updated.

  5. Elapsed: 00:00:04.04
这样的调整是符合SQL语法的,执行时间变为了4秒多,效果显著。

 

4. 第二次优化

虽然执行时间减少了接近一半,但SQL中还是对TBL_B执行了两次扫描,是否还可以减少一次?

点击(此处)折叠或打开

  1. SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
  2.                       FROM tbl_a a, tbl_b b
  3.                       WHERE a.id = b.id)
  4.           SET name = b_name, class = b_class;
  5. SET name = b_name, class = b_class
  6.     *
  7. ERROR at line 4:
  8. ORA-01779: cannot modify a column which maps to a non key-preserved table

  9. Elapsed: 00:00:00.01


这样就做到了只扫描一次TBL_B表,直接对子查询更新,但此时报了一个错误,ORA-01779,

这就引出了non key-preserved table的概念。非键值保存表,杨长老的博客(http://blog.itpub.net/4227/viewspace-195889/)中提到过这个错误:

“造成这个错误的原因是更新的列不是事实表的列,而是维度表的列。换句话说,如果两张表关联,其中一张表的关联列是主键,那么另一张表就是事实表,也就是说另一张表中的列就是可更新的;除非另一张表的关联列也是主键,否则这张表就是不可更新的,如果更新语句涉及到了这张表,就会出现ORA-1799错误。如果是两张表主键关联,那么无论更新那个表的字段都可以。

其实这个限制的真正原因是Oracle要确保连接后更新的内容可以写到一张表中,而这就要求连接方式必须是1对N或者1对1的连接。这样才能确保连接后的结果集数量和事实表一致。从而使得Oracle对连接后子查询的更新可以顺利的更新到事实表中。”

a.id=b.id,我们是用TBL_B的id列作为条件更新,需要确保这列只会对应到TBL_B表的一行记录,可以为表TBL_B的id列设置主键、唯一索引或唯一约束,三种操作,这里选择设置唯一约束:

点击(此处)折叠或打开

  1. SQL> alter table tbl_b add constraint uq_b_id unique(id);
  2. Table altered.

再次执行:

点击(此处)折叠或打开

  1. SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
  2.                       FROM tbl_a a, tbl_b b
  3.                       WHERE a.id = b.id)
  4.           SET name = b_name, class = b_class;
  5. 10000 rows updated.

  6. Elapsed: 00:00:00.12


执行时间一下仅为0.12秒。

上面如果TBL_A的ID列设置为主键,则为1对1的连接,如果仅是TBL_B的ID列为唯一约束,则为1对N的连接。

 

总结:

通过两次优化,执行时间从7秒降到了0.12秒,虽然这里的示例数据未必和实际情况一致,但成比例的缩放足以说明这个问题,从这个案例可以看出,优化的本质就是少做事,原始SQL执行三次全表扫描,那目标就是减少全表扫描的次数,第一次优化的操作可能相对容易想到,但第二次优化的操作,就需要知道可以有这种语法,而且出现了ORA-01799的错误,还需要知道这种错误的根本原因是什么,才能有可行的解决方法。

 

问题还没完,以上说明了SQL语句的优化,下面就是针对这条SQL展开的知识。

假设上面的TBL_A和TBL_B表是属于用户bisal的,此时新建一个用户phibisal,并授予最简单的权限:

点击(此处)折叠或打开

  1. SQL> create user phibisal identified by phibisal;
  2. User created.

  3. SQL> grant create session to phibisal;
  4. Grant succeeded.
bisal用户创建这两张表的public同义词:

点击(此处)折叠或打开

  1. SQL> create public synonym tbl_a for bisal.tbl_a;
  2. Synonym created.

  3. SQL> create public synonym tbl_b for bisal.tbl_b;
  4. Synonym created.

然后授予phibisal用户对TBL_A表的读和更新权限:

点击(此处)折叠或打开

  1. SQL> grant select, update on tbl_a to phibisal;
  2. Grant succeeded.


此时phibisal登录后执行:

点击(此处)折叠或打开

  1. sqlplus phibisal/phibisal

  2. SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
  3.                       FROM tbl_a a, tbl_b b
  4.                       WHERE a.id = b.id)
  5.           SET name = b_name, class = b_class;
  6.                 FROM tbl_a a, tbl_b b
  7.                                     *
  8. ERROR at line 2:
  9. ORA-00942: table or view does not exist


会提示TBL_B不存在,因为用户没有该表的任何权限,(注:此处和eygle的示例中反馈不同,他提示的是ORA-01031: insufficient privileges)
如果授予phibisal对TBL_B表的读权限,

点击(此处)折叠或打开

  1. SQL> grant select on tbl_b to phibisal;
  2. Grant succeeded.


此时可以完成更新:

点击(此处)折叠或打开

  1. sqlplus phibisal/phibisal

  2. SQL> UPDATE tbl_a a
  3.           SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)
  4.           WHERE a.id IN (SELECT id FROM tbl_b b);
  5. 10000 rows updated.


但用如下SQL会提示权限错误:

点击(此处)折叠或打开

  1. UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
  2.                       FROM tbl_a a, tbl_b b
  3.                       WHERE a.id = b.id)
  4.           SET name = b_name, class = b_class;
  5.                 FROM tbl_a a, tbl_b b
  6.                                     *
  7. ERROR at line 2:
  8. ORA-01031: insufficient privileges

即这种子查询更新会因没有TBL_B表的UPDATE权限报错。
但如果使用如下with语法,则可以正常执行:

点击(此处)折叠或打开

  1. SQL> UPDATE
  2. (WITH tmp AS (
  3.               SELECT b.name b_name, b.class b_class, a.name, a.class
  4.               FROM tbl_a a, tbl_b b
  5.               WHERE a.id = b.id)
  6.              )
  7. SET name = b_name, class = b_class;
  8. 10000 rows updated.


做得更彻底一些:

点击(此处)折叠或打开

  1. SQL> revoke update on tbl_a from phibisal;
  2. Revoke succeeded.

撤消了phibisal用户对TBL_A的更新权限,按理说,phibisal用户不应该能再更新TBL_A表了。
使用上面两个调整后的SQL,确实如此:

点击(此处)折叠或打开

  1. sqlplus phibisal/phibisal

  2. SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
  3.                       FROM tbl_a a, tbl_b b
  4.                       WHERE a.id = b.id)
  5.           SET name = b_name, class = b_class;
  6.                 FROM tbl_a a, tbl_b b
  7.                                     *
  8. ERROR at line 2:
  9. ORA-01031: insufficient privileges

  10. SQL> UPDATE tbl_a a
  11.           SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)
  12.           WHERE a.id IN (SELECT id FROM tbl_b b);
  13. UPDATE tbl_a a
  14.        *
  15. ERROR at line 1:
  16. ORA-01031: insufficient privileges

但是,奇怪的是如下SQL可以执行:

点击(此处)折叠或打开

  1. SQL> UPDATE
  2. (WITH tmp AS (
  3.               SELECT b.name b_name, b.class b_class, a.name, a.class
  4.               FROM tbl_a a, tbl_b b
  5.               WHERE a.id = b.id)
  6.               SELECT * FROM tmp
  7.              )
  8. SET name = b_name, class = b_class;
  9. 10000 rows updated.

这就从原理规则上,违背了权限控制,看下版本:

点击(此处)折叠或打开

  1. SQL> select banner from v$version where rownum=1;
  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

这就是2014年7月提出的一个bug,在11.2.0.3、11.2.0.4、12.1等版本中都存在的一个问题,需要修正这个bug,相当于使用with语法,可以绕过用户权限,对没有权限的表进行DML操作。

总结:

精髓不在于这个bug,而是在于从一条简单的UPDATE语句,可以派生出如此丰富的知识,可谓举一反三,受益匪浅。一方面需要我们能够从原理上理解每一个概念,另一方面也要培养自己举一反三,知识点由点及面的想法,做到真正的触类旁通,这样才能逐渐向大师靠拢,向大师学习。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28194062/viewspace-1760147/,如需转载,请注明出处,否则将追究法律责任。

下一篇: linux增大swap空间
请登录后发表评论 登录
全部评论

注册时间:2012-09-25

  • 博文量
    63
  • 访问量
    273326