震惊一个简单的sql执行十天还没执行完！！！

周五的下午总是过的有点漫长，伴随着写周报周计划啥的，还是很慢。看看微信群里的吹水，不能卷。

划水看看某库的top sql，只是随便瞅一眼，这sql挺简单的，但是怎么执行这么慢，查看下 sql monitor，震惊体，居然从4月2日8点开始执行的，

今天是4月12日，哇。。。十天了，看来最近水划的有点严重了。

我在本地准备了测试表来展示这次案例：

--创建测试表create table dwtest1_1 as select * from dba_objects;
create table dwtest1_2 as select * from dba_objects;
create table dwtest2_1as select * from dba_objects;

收集统计信息

exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'DWTEST1_1);
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'DWTEST1_2);
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'DWTEST2_1);

生产上的类似sql

select count(1)
  from dwtest1_1 a, dwtest1_2 b, dwtest2_1 c
 where a.object_id = B.object_id
   and b.object_name = c.object_name
   and a.OWNER != :1
   AND B.STATUS != :2
    OR C.OWNER = :3;

F5查看下执行计划

 Plan Hash Value  : 2860411116 

---------------------------------------------------------------------------------------------------------------
| Id   | Operation                 | Name      | Rows           | Bytes            | Cost         | Time      |
---------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT          |           |              1 |               79 | 107606009778 | 999:59:59 |
|    1 |   SORT AGGREGATE          |           |              1 |               79 |              |           |
|    2 |    CONCATENATION          |           |                |                  |              |           |
|    3 |     MERGE JOIN CARTESIAN  |           | 27049976911056 | 2136948175973424 | 107606008466 | 999:59:59 |
|    4 |      MERGE JOIN CARTESIAN |           |      312405896 |      21243600928 |      1245612 |  04:09:08 |
|  * 5 |       TABLE ACCESS FULL   | DWTEST2_1 |           3608 |           111848 |          346 |  00:00:05 |
|    6 |       BUFFER SORT         |           |          86587 |          3203719 |      1245267 |  04:09:04 |
|    7 |        TABLE ACCESS FULL  | DWTEST1_2 |          86587 |          3203719 |          345 |  00:00:05 |
|    8 |      BUFFER SORT          |           |          86586 |           952446 | 107606008120 | 999:59:59 |
|    9 |       TABLE ACCESS FULL   | DWTEST1_1 |          86586 |           952446 |          344 |  00:00:05 |
| * 10 |     HASH JOIN             |           |          69641 |          5501639 |         1312 |  00:00:16 |
| * 11 |      TABLE ACCESS FULL    | DWTEST1_1 |          82978 |           912758 |          346 |  00:00:05 |
| * 12 |      HASH JOIN            |           |          69642 |          4735656 |          965 |  00:00:12 |
| * 13 |       TABLE ACCESS FULL   | DWTEST1_2 |          43294 |          1601878 |          347 |  00:00:05 |
| * 14 |       TABLE ACCESS FULL   | DWTEST2_1 |          82984 |          2572504 |          346 |  00:00:05 |
---------------------------------------------------------------------------------------------------------------

预估行数 27049976911056，cost 107606009778，27万亿行，这是应用需要的数据？估计他是想得到表关联并且 a.OWNER != :1 AND B.STATUS != :2 OR C.OWNER = :3的数据。这样的写法结果也不对，必须要改写sql才能实现了，而为什么会有这样的执行计划啊，or的时候数据库优化器会尝试改写，本例中是把a.object_id = B.object_id and b.object_name = c.object_name and a.OWNER != :1 AND B.STATUS != :2这部分作为一部分的条件来执行，这里没有问题，而or后面的C.OWNER = :3作为一部分来执行时，没有关联条件了，就产生了笛卡尔连接了。

接下来尝试改写，根据上面的思路，可以补上表关联条件：

select count(1)
  from dwtest1_1 a, dwtest1_2 b, dwtest2_1 c
 where a.object_id = B.object_id
   and b.object_name = c.object_name
   and a.OWNER != :1
   AND B.STATUS != :2
    OR (C.OWNER = :3 and a.object_id = B.object_id and
       b.object_name = c.object_name)；

其执行计划如下：

 Plan Hash Value  : 3449380611 

---------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows   | Bytes   | Cost | Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |      79 | 1420 | 00:00:18 |
|   1 |   SORT AGGREGATE       |           |      1 |      79 |      |          |
| * 2 |    HASH JOIN           |           |  72435 | 5722365 | 1420 | 00:00:18 |
|   3 |     TABLE ACCESS FULL  | DWTEST1_1 |  86586 |  952446 |  346 | 00:00:05 |
| * 4 |     HASH JOIN          |           | 144621 | 9834228 | 1072 | 00:00:13 |
|   5 |      TABLE ACCESS FULL | DWTEST2_1 |  86592 | 2684352 |  346 | 00:00:05 |
|   6 |      TABLE ACCESS FULL | DWTEST1_2 |  86587 | 3203719 |  347 | 00:00:05 |
---------------------------------------------------------------------------------

以内连接的改写方式：

select count(1)
  from dwtest1_1 a
 inner join dwtest1_2 b
    on a.object_id = B.object_id
 inner join dwtest2_1 c
    on b.object_name = c.object_name
 where a.OWNER != :1
   AND B.STATUS != :2
    OR C.OWNER = :3;

其执行计划如下：

 Plan Hash Value  : 3449380611 

---------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows   | Bytes   | Cost | Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |      79 | 1420 | 00:00:18 |
|   1 |   SORT AGGREGATE       |           |      1 |      79 |      |          |
| * 2 |    HASH JOIN           |           |  72435 | 5722365 | 1420 | 00:00:18 |
|   3 |     TABLE ACCESS FULL  | DWTEST1_1 |  86586 |  952446 |  346 | 00:00:05 |
| * 4 |     HASH JOIN          |           | 144621 | 9834228 | 1072 | 00:00:13 |
|   5 |      TABLE ACCESS FULL | DWTEST2_1 |  86592 | 2684352 |  346 | 00:00:05 |
|   6 |      TABLE ACCESS FULL | DWTEST1_2 |  86587 | 3203719 |  347 | 00:00:05 |
---------------------------------------------------------------------------------

两个改写方式的Plan Hash Value 都为3449380611，执行计划一致，带入了变量执行结果也是一致的。

给自己的总结：关于or的改写最近遇到了好多，应用开始很多时候写sql都是根据自己的逻辑来写代码而不知道数据库内部的执行方式，导致了慢sql，而本次生产上的sql幸好没有并发，不然那就上质量分析会了！这个sql是我遇到最慢的sql了，特来记录下。遇到or更多的时候是产生FILTER连接，通过LNNVL + union all 改写，而有时候会因为 or 是子查询方式，就需要用逻辑方式来评估了，分析其可能出现的情况来 union all了。