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 in 9.189 seconds
而且从inc_file_seq的分布情况看，取值1的列值不是合乎比例的取值。如果我们替换上其他数据量值（如358），执行时间呈现出不可出结果的状态。
SQL> select inc_file_seq, count(*) from bsd_ticket group by inc_file_seq;
INC_FILE_SEQ COUNT(*)
-------------- ----------
352 477616
729001 2
1 19
357 5
358 1885
124119 8
根据该种情况，笔者计划首先从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 in 2.402 seconds
执行时间由原来的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，再集成到应用中，减少日后的优化压力。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-704528/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-704528/
634
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
