耗时数小时，‘Not in’ SQL 优化 |

Crq

1164
文章

0
评论

2024年11月4日14:27:50

评论23 2184字阅读7分16秒

摘要

在 DBA 所优化的数据库环境中，绝大多数性能问题其实是由于 SQL 编写不当导致的。SQL 的世界无奇不有，今天我们一起见识一条让你绝对想吐血的杀手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);

查看执行计划：