• 博客访问: 7450169
  • 博文数量: 542
  • 用 户 组: 普通用户
  • 注册时间: 2010-11-30 20:36
  • 认证徽章:
个人简介

求道~

文章存档

2017年(5)

2016年(15)

2015年(59)

2014年(65)

2013年(87)

2012年(73)

2011年(212)

2010年(26)

分类: Linux操作系统

2011-08-09 21:09:00

 

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,再集成到应用中,减少日后的优化压力。

阅读(3616) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册