欢迎您光临本站,如有问题请及时联系我们。

杀手SQL:一条关于 ‘Not in’ SQL 的优化案例

  某保险客户,ETL 耗时数个小时,我们做了sql report发现压力主要在其中一个SQL上。

  单次执行时间:5788(秒)

  单次逻辑读:10亿(块)

  单次返回行数:21万(行)

  我们首先看SQL语句,因为比较长,此处只节选部分的

  查看其执行计划:

  我们主要关注一下从7到16行:发现存在两次全表扫描。中间做了一次filter。

  多年的经验告诉我,两个全表扫组成的Filter ,问题很严重, 因为涉及数据逐条处理。 而这个执行计划里,被驱动表还是全表扫。

  Not In/In 操作有时候的确会产生 Filter操作,在11g之前的版本,要把not in 语句转换成反连接,not in条件的列必须有Not null 属性,或者语句中带入了not null的限制,否则只能采用Filter,逐条过滤.

  我们举例说明一下:

  SQL1:CREATE TABLE T_OBJ AS SELECT OBJECT_ID,OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OWNER != ‘SEROL’;SQL2:CREATE TABLET_TABLE AS SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER!=’SEROL’;

  查看T_OBJ的属性:

  发现有在三列上都没有not null的限制。

  我们此时伪装成10G的优化器。

  SQL> alter session set optimizer_features_enable=”10.2.0.5″;

  执行以下SQL:

  SQL> set autotracetrace exp

  SQL> SELECT * FROM T_TABLE WHERE TABLE_NAME NOT IN(SELECT OBJECT_NAME FROM T_OBJ);

  此时查看执行计划,我们发现走的是filter:

  但在11g版本中,优化器可以自动把Not in操作从昂贵的Filter转换成Null-Aware-Anti-Join。

  若加个Not null 条件或者栏位属性设为not null

  SQL> alter table T_OBJ modify(OBJECT_NAME NOT NULL);

  再次执行相同语句:

  SQL> SELECT * FROM T_TABLE WHERE TABLE_NAME

  NOT IN(SELECT OBJECT_NAME FROM T_OBJ

  WHEREOBJECT_NAME IS NOT NULL);

  再次查看执行计划:

  此时我们发现,在执行计划中,走了hash join anti.

  并且,在11g里面,允许not in列没有not null 限制也可以转换Anti-Join.

  SQL> alter session set optimizer_features_enable=”11.2.0.4″;

  SQL> alter table T_OBJ modify(OBJECT_NAME NULL);

  SQ> SELECT * FROM T_TABLE WHERE TABLE_NAME

  NOT IN (SELECTOBJECT_NAMEFROM T_OBJ);

  查看执行计划:

  我们看到,此时在没有非空限制的情况下,也走了hash join anti.

  这个特性, 可通过优化器参数控制。

  SQL>alter session set “_optimizer_null_aware_antijoin”=FALSE;

  再次执行以上语句并查看执行计划:

  SQL> SELECT * FROM T_TABLE WHERE TABLE_NAME

  NOT IN (SELECTOBJECT_NAMEFROM T_OBJ);

  发现仍然走的是hash join anti.

  经过验证,不是这个参数设置问题

  Not in 的逻辑,就是结果集之间的互斥,其实有多种改写的方式,比如:

  — Not exists

  — Outer Join + is null

  — Minus

  not in与以上三种写法的区别是:not in 是会排斥空值。

  我们尝试改写。

  接下来正当你以为会发生奇迹的时候,语句报错了!

  为什么会报错呢?

  如果我们把该语句转换为not in的方式:

  根据not in的逻辑,此时在fee_code前应该加上’A.’,当然这也是没有问题的,但是,再次看这条语句就会变成:

  由于TMP_APP_xxx_PREM A 中并没有FEE_CODE字段, 所以,Not in 无法自动改成Null Aware ANTI JOIN。

  所以,至此答案揭晓,竟然是写错了?!我猜中了这开头,却没有猜中这结局。

  但在本案例中,由于SQL语句中没有显式写出表明,导致在前期分析过程中一直没有发现这个错误。

  你是不是也很无语,其实我更想问的是,你是不是也经常写出杀手SQL呢,但没关系,你有病我有药啊。(无辜脸,不要打我)

  我们都知道,在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的。

  对于未上线系统,通过前期的SQL审核管控,将80%的SQL问题消灭在萌生阶段,对于线上运行系统,发现和解决潜在的性能问题,可做到提前预防,防患于未然。

  SQL审核,让DBA由系统的急救医生转身成为系统的保健医生

  1、DBA参与应用代码开发测试过程:给开发人员提供专业的数据库开发及优化建议

  2、优化前置:在应用代码上线前根据业务需求设计高效的SQL、索引

  3、控制变更风险:预先评估应用开发中表结构变更、SQL变更对运行中应用的影响,确定合适的变更窗口,变更方案。


来源:本文由E8运维原创撰写,欢迎分享本文,转载请保留出处和链接!