Oracle对接受到的描述性SQL语句，要进行一系列的验证处理工作。其中，有一个重要过程称为“SQL Transformation”，作用就是在不改变原有数据集结果的情况下，对SQL语句进行规则化改写，使之可以生成更好的执行计划。对一些执行计划较差的SQL进行改写，配合优化器的SQL变换功能，很多时候可以为SQL寻找到更好的执行计划。

1、问题提出

早上使用AWR报告进行开发环境诊断，发现开发组正进行开发模块中出现问题SQL。从AWR存储中抽取出SQL如下：

select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc

from bsd_ticket a

where a.inc_file_seq = :1

and exists (select null

from bsd_ticket t

where t.inc_file_seq <> :2

and a.tdnr = t.tdnr

and a.tacn = t.tacn

and t.del_flag = 'N'

and t.doctype_code <> 30

group by t.tdnr, t.tacn

having count(*) > 1)

and not exists (select null

from bsd_trans_error e

where e.trans_id = a.trans_id

and e.err_code = '239')

and a.doctype_code = '10'

从诊断情况来看，该SQL执行的时间user wait过长，而且带来大量的物理逻辑读。在开发环节进行关键用例、关键SQL的优化帮助，是笔者工作范畴中的内容。

首先从业务需求入手，从开发组获知，该SQL的作用是在大作业Job中进行的重复票证检查。要求查找出“不同输入文件inc_file_seq中，票号相同出现两次的重复票信息”。重复票证条件就是tdnr和tacn两个字段相同，inc_file_seq使用的绑定变量，在实际中输入的是相同的文件编号值。

从AWR中抽取出绑定变脸的peeking值，两个均为数字number类型1。填补绑定变量位置之后，单独执行发现执行时间的确过长。

SQL> select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc

2 from bsd_ticket a

3 where a.inc_file_seq = 1

4 and exists (select null

5 from bsd_ticket t

6 where t.inc_file_seq <> 1

7 and a.tdnr = t.tdnr

8 and a.tacn = t.tacn

9 and t.del_flag = 'N'

10 and t.doctype_code <> 30

11 group by t.tdnr, t.tacn

12 having count(*) > 1)

13 and not exists (select null

14 from bsd_trans_error e

15 where e.trans_id = a.trans_id

16 and e.err_code = '239')

17 and a.doctype_code = '10'

18 ;

TRANS_ID TRNN TDNR AGENT_CODE TACN TRNC

-------------- ------ --------------- ---------- ----- ----

Executed in9.189seconds

而且从inc_file_seq的分布情况看，取值1的列值不是合乎比例的取值。如果我们替换上其他数据量值（如358），执行时间呈现出不可出结果的状态。

SQL> select inc_file_seq, count(*) from bsd_ticket group by inc_file_seq;

INC_FILE_SEQ     COUNT(*)

-------------- ----------

根据该种情况，笔者计划首先从SQL语句角度进行修改改写。

2、问题分析

首先，使用AWR获取到该SQL使用的执行计划和执行路径信息。

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

Plan hash value: 4086380271

-----------------------------------------------------------------------

| Id | Operation | Name | Rows | B

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 |

|* 1 | FILTER | | |

| 2 | NESTED LOOPS ANTI | | 29 |

|* 3 | TABLE ACCESS BY INDEX ROWID| BSD_TICKET | 42 |

|* 4 | INDEX RANGE SCAN | IDX_BSD_TICKET_INC_FILESEQ | 42 |

|* 5 | TABLE ACCESS BY INDEX ROWID| BSD_TRANS_ERROR | 5 |

|* 6 | INDEX RANGE SCAN | IDX_BSD_TRANS_ERROR_TRANS_ID | 1 |

|* 7 | FILTER | | |

| 8 | SORT GROUP BY NOSORT | | 1 |

|* 9 | TABLE ACCESS FULL | BSD_TICKET | 1 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( EXISTS (SELECT 0 FROM "BSD_TICKET" "T" WHERE "T"."TDNR"=:B1 AND "

TO_NUMBER("T"."DOCTYPE_CODE")<>30 AND "T"."DEL_FLAG"='N' AND "T"."

"T"."TDNR","T"."TACN" HAVING COUNT(*)>1))

3 - filter("A"."DOCTYPE_CODE"='10')

4 - access("A"."INC_FILE_SEQ"=1)

5 - filter("E"."ERR_CODE"='239')

6 - access("E"."TRANS_ID"="A"."TRANS_ID")

7 - filter(COUNT(*)>1)

9 - filter("T"."TDNR"=:B1 AND "T"."TACN"=:B2 AND TO_NUMBER("T"."DOCTYPE_CODE"

"T"."DEL_FLAG"='N' AND "T"."INC_FILE_SEQ"<>1)

从原SQL的本意看，开发者意识到了该SQL的两个难点：

首先是存在两张以上的重票。这就意味着无论如何不容易逃开子查询exists中的group by。BSD_TICKET数据表是票库表，数据量巨大，进行group by操作要消耗大量的时间和空间；

进行重票检索的时候，相当于进行表的自连接操作；

于是，从开发者的角度，进行这样的设计：首先使用连接条件深入到exists子查询语句中，用来剔除一部分的数据集合。之后再进行group by操作，用exists来判断。

但是从实际的执行计划来看，开发者用心的考量似乎没有起作用。在上面的执行计划中，操作顺序为：4-3-5-6-2-9-8-7-1-0。结合Predication Information中每个步骤的操作内容条件，可以看出执行计划如下：

step4+step3：走索引路径，将bsd_ticket表中对应输入文件的票证记录全部检索出来。由于bsd_ticket上存在索引，索引使用index range scan；

绕开bsd_ticket数据表的条件，回溯到bsd_trans_error数据表。执行step5+step6操作，将具有’239’错误的交易编号获取到；

上面两个步骤的结果，进行step2：nest loop anti的逆向不匹配操作。找到没有对应239错误的交易；

step9：对数据表bsd_ticket进行第二次全表扫描，加入了抽象连接条件和文件编号条件。再对结果进行group by操作；

两大部分结果集合，进行count(*)>1条件的筛查和其他检索的应用；

该执行计划在数据集合大的时候，特别是inc_file_seq文件中票量稍稍增加之后，都会带来nest loop操作巨大的性能抖动。这也就是为什么我们替换inc_file_seq之后，不容易跑出结果的原因。

同时，我们应该注意到：执行的SQL语句和我们输入的SQL执行顺序有所差异。这就是Oracle内部的SQL Transformation过程的结果。我们希望进行的连接Join，在执行计划中没有出现，特别是没有在子查询中出现。取代Join的是纯Filter操作。

3、问题解决

笔者尝试对该SQL进行改写。首先就是group by能否去除，因为group by操作对性能的影响巨大，如果没有特殊的理由，我们通常是不要轻易的group by或者sort的。

但是需求方面很明确，要求看重票两次以上的票证。所以考虑将连接条件拿出subquery，尝试一下能不能带来性能的提升。因为一边进行group by，一边进行连接会增加SQL的复杂程度，反而不容易让优化器入手。

select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc

from bsd_ticket a

where a.inc_file_seq = 1

and(tdnr, tacn) in (select tdnr, tacn

from bsd_ticket t

where t.inc_file_seq <> 1

and t.del_flag = 'N'

and t.doctype_code <> '30'

group by tdnr, tacn

having count(*) > 1)

and not exists (select null

from bsd_trans_error e

where e.trans_id = a.trans_id

and e.err_code = '239')

and a.doctype_code = '10';

改写SQL最大的变化，就是将子查询内部的链接条件迁移出去，替代为一个集合in操作配比。group by 和having条件没有变化。我们首先观察一下执行效率，采用相同的inc_file_seq取值。

SQL> select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc

2 from bsd_ticket a

3 where a.inc_file_seq = 1

4 and (tdnr, tacn) in (select tdnr, tacn

5 from bsd_ticket t

6 where t.inc_file_seq <> 1

9 and t.del_flag = 'N'

10 and t.doctype_code <> '30'

11 group by tdnr, tacn

12 having count(*) > 1)

13 and not exists (select null

14 from bsd_trans_error e

15 where e.trans_id = a.trans_id

16 and e.err_code = '239')

17 and a.doctype_code = '10';

TRANS_ID     TRNN     TDNR     AGENT_CODE     TACN TRNC

-------------- ------ --------------- ---------- ----- ----

Executed in2.402seconds

执行时间由原来的9秒多减少到2.4秒。下面我们来看下执行计划：

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------

Plan hash value: 1971600865

----------------------------------------------------------------------------

| Id | Operation | Name | Rows |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 |

|* 1 | FILTER | |

| 2 | HASH GROUP BY | | 2 |

|* 3 | HASH JOIN | | 29 |

| 4 | NESTED LOOPS ANTI | | 29 |

|* 5 | TABLE ACCESS BY INDEX ROWID| BSD_TICKET | 42 |

|* 6 | INDEX RANGE SCAN | IDX_BSD_TICKET_INC_FILESEQ | 42 |

|* 7 | TABLE ACCESS BY INDEX ROWID| BSD_TRANS_ERROR | 5 |

|* 8 | INDEX RANGE SCAN | IDX_BSD_TRANS_ERROR_TRANS_ID | 1 |

|* 9 | TABLE ACCESS FULL | BSD_TICKET | 477K|

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(COUNT(*)>1)

3 - access("TDNR"="TDNR" AND "TACN"="TACN")

5 - filter("A"."DOCTYPE_CODE"='10')

6 - access("A"."INC_FILE_SEQ"=1)

7 - filter("E"."ERR_CODE"='239')

8 - access("E"."TRANS_ID"="A"."TRANS_ID")

9 - filter("T"."DOCTYPE_CODE"<>'30' AND "T"."DEL_FLAG"='N' AND "T"."INC_FILE_

在执行计划中，我们发现了Hash Join和Hash group by的操作痕迹。在CBO时代，Hash Join是实用性较好的链接方式。执行计划的顺序为：6-5-8-7-4-9-3-2-1。具体来看，如下：

step6-step4：内容和功能和修改前SQL的内容一样。都是获取指定文件中没有出现’239’编号错误的交易列表。nested loop anti操作是一种not in操作的体现；

step9中：开始对bsd_ticket数据表操作，此时没有直接的group by，而是将一些常量筛选条件加以应用；

在step3中，上面步骤中的获取到的两个数据集合，进行hash join操作，也就是进行连接操作；

最后，在step2-step1中，才对数据集合进行group by和count(*)条件的采用；

综合来看，改写后的SQL执行计划也被改写。之前的SQL中，我们尝试将连接条件写入子查询，期望以连接的方式减少一部分的数据集合。但是执行计划中没有出现join操作。而之后的SQL中，我们没有显示的进行连接描述，但是执行计划中出现了Hash Join操作。这些都意味着SQL在输入优化器之后，进行了SQL Transformation操作，对执行计划影响重大。

原先SQL另一个重要问题就是当数据集合偏大的时候，性能变化剧烈。我们新改写SQL如何呢？

SQL> select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc

2 from bsd_ticket a

3 where a.inc_file_seq =358

4 and (tdnr, tacn) in (select tdnr, tacn

5 from bsd_ticket t

6 where t.inc_file_seq <>358

7 and t.del_flag = 'N'

8 and t.doctype_code <> '30'

9 group by tdnr, tacn

10 having count(*) > 1)

11 and not exists (select null

12 from bsd_trans_error e

13 where e.trans_id = a.trans_id

14 and e.err_code = '239')

15 and a.doctype_code = '10';

已选择1885行。

已用时间: 00: 00: 01.40

使用Autotrace对比，发现执行时间抖动性弱，比较适应数据变化。这也是Hash Join的特点。

4、结论与思考

这个案例给我们最大思考就是在于优化器的SQL变换功能。从DSI404中的信息看，此处Oracle使用了Subquery Unnested技术，对输入的SQL进行了改写。Subquery Unnested主要是针对in、exists后出现的子查询操作进行的查询展开和合并。这个过程是很负责的内部改写过程。

在我们的案例中，原有SQL虽然将连接条件写入了子查询中，期望能够借用连接条件减少数据集合处理量，从而提高性能。但是事与愿违，执行计划中没有出现Join的连接信息，说明Oracle在改写中将这个连接改写出了子查询，而且将group by留置其中。

改写的SQL中，我们没有强调连接条件。只是通过平缓化子查询的职能，给了Oracle SQL Transformation发挥的空间。在改写过的执行计划中，出现了Hash Join操作。

Oracle是一个复杂的系统。在CBO时代，执行计划路径受到诸多因素的作用和影响。对一些关键SQL，我们最好的手段是先尝试书写好合适的SQL，再集成到应用中，减少日后的优化压力。