一个报表的优化及小结

一条语句消耗比较大,应该是费用报表中的. 麻烦改一下.对于这个语句分析及引申了一些东西出来,请大家看看.  谢谢!

1. 语句
select  substr(gcc.segment2, 1, 6) substr,
       decode(substr(gcc.segment2, 1, 6),
               '660122',
               '租赁费',
               '660123',
               '物业管理费',              
               '660124',
               '水电费',
               '660126',
               '企业财产保险费',
               '660127',
               '业务宣传费',
               '660128',              
               '业务招待费',
               '660129',              
               '会议费',
               '660130',
               '培训费',
               '660132',
               '差旅费',
               '660133',
               '车船使用费',              
               '660134',
               '印刷费',
               '660135',              
               '邮电费',
               '660136',
               '电子设备运转费',
               '660137',
               '修理费',
               '660138',
               '低值易耗品',
               '660140',
               '防预费',
               '660141',
               '安全防卫费',
               '660142',
               '劳动保护费',
               '660146',
               '咨询费',
               '660148',
               '绿化费',
               '660149',
               '公杂费',
               '660159',
               '查勘费',              
               '660199',
               '其他',
               substr(gcc.segment2, 1, 6)) parents,
       gcc.segment2,
       ffvv.DESCRIPTION,
       sum(decode(gh.period_name,   :p_period_name,                 
(nvl(gl.entered_dr, 0) - nvl(gl.entered_cr, 0)),                  0)) PTD,
       sum(decode(substr(gh.period_name, 4, 4),
                substr(:p_period_name, 4, 4),
                (nvl(gl.entered_dr, 0) - nvl(gl.entered_cr, 0)),          
0)) YTD,
       sum(decode(substr(gh.period_name, 4, 4),
                 :cp_period_Y,
                 (nvl(gl.entered_dr, 0) - nvl(gl.entered_cr, 0)),
                 0)) YTD_LASTYEAR,
       sum(decode(substr(gh.period_name, 4, 4),
             substr(:p_period_name, 4, 4),
             (nvl(gl.entered_dr, 0) - nvl(gl.entered_cr, 0)),
             0)) /
       decode(sum(decode(substr(gh.period_name, 4, 4),
                         :cp_period_Y,
                         (nvl(gl.entered_dr, 0) - nvl(gl.entered_cr, 0)),
                         0)),
              0,
              1,
              sum(decode(substr(gh.period_name, 4, 4),
                         :cp_period_Y,
                         (nvl(gl.entered_dr, 0) - nvl(gl.entered_cr, 0)),
                         0))) - 1 rate
  FROM gl_code_combinations gcc,
       gl_je_headers gh,
       gl_je_lines          gl,
       fnd_flex_values_vl ffvv
 WHERE ffvv.FLEX_VALUE_SET_ID = :cp_seg2_id
   and ffvv.FLEX_VALUE = gcc.segment2
   and (gcc.segment3 = :p_dept or :p_dept is null)
   and gcc.chart_of_accounts_id = :p_coa_id
   and gh.je_header_id = gl.je_header_id
   and gh.set_of_books_id = :p_sob_id
   and gl.set_of_books_id = :p_sob_id
   and ((gh.period_name >= '01-' || substr(:p_period_name, 4, 4) and
gh.period_name <= :p_period_name) or       (gh.period_name >=
'01-'||:cp_period_Y and gh.period_name <= :cp_period_P))
   and gl.code_combination_id = gcc.code_combination_id
   and gh.actual_flag = 'A'
   and gh.status = 'P'
   and gh.je_source in ('1', '2')
   and gh.currency_code = 'CNY'
   and gcc.segment1 = :P_COMPANY
   and substr(gcc.segment2, 1, 6) in
       ('660122', '660123', '660124', '660126', '660127', '660128',
'660129', '660130', '660132', '660133', '660134', '660135', '660136',
'660137', '660138', '660140',
        '660141', '660142', '660146', '660148', '660149', '660159',
'660199')
 group by gcc.segment2, ffvv.DESCRIPTION
 ORDER BY 1 ASC, gcc.segment2


2.语句的消耗为:

  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash
Value
--------------- ------------ -------------- ------ -------- ---------
----------
    495,728,803            2  247,864,401.5   69.7  1662.95   1625.11
2331498148
select substr(gcc.segment2,1,6) substr,        decode(substr(gcc
.segment2,1,6),               '660122',               '?§^?èμ^áè′1
',               '660123',               '?^é???^ú????^D^?è′1',
          '660124',               '?°′?^?μè′1',               '66
0126',               '??^á??^úè′￠?o§??^Yé^ù?è′1',               '660



3. 执行计划及原因分析:

SELECT STATEMENT, GOAL = CHOOSE            Cost=107   
Cardinality=1    Bytes=153
 SORT ORDER BY            Cost=107    Cardinality=1   
Bytes=153
  SORT GROUP BY            Cost=107   
Cardinality=1    Bytes=153
   NESTED LOOPS            Cost=58    Cardinality=1   
Bytes=153
    NESTED LOOPS            Cost=57   
Cardinality=1    Bytes=119
     NESTED LOOPS            Cost=51   
Cardinality=1    Bytes=98
      MERGE JOIN CARTESIAN            Cost=50   
Cardinality=1    Bytes=55
       TABLE ACCESS FULL    Object owner=GL    Object
name=GL_JE_HEADERS    Cost=48    Cardinality=1    Bytes=35
       BUFFER SORT            Cost=2   
Cardinality=27    Bytes=540
        TABLE ACCESS BY INDEX ROWID    Object owner=APPLSYS   
Object name=FND_FLEX_VALUES    Cost=2    Cardinality=27   
Bytes=540
         INDEX RANGE SCAN    Object owner=APPLSYS    Object
name=FND_FLEX_VALUES_N3    Cost=1    Cardinality=27   
      TABLE ACCESS BY INDEX ROWID    Object owner=APPLSYS   
Object name=FND_FLEX_VALUES_TL    Cost=1    Cardinality=1   
Bytes=43
       INDEX UNIQUE SCAN    Object owner=APPLSYS    Object
name=FND_FLEX_VALUES_TL_U1        Cardinality=1   
     TABLE ACCESS BY INDEX ROWID    Object owner=GL    Object
name=GL_JE_LINES    Cost=6    Cardinality=7    Bytes=147
      INDEX RANGE SCAN    Object owner=GL    Object
name=GL_JE_LINES_U1    Cost=2    Cardinality=60   
    TABLE ACCESS BY INDEX ROWID    Object owner=GL    Object
name=GL_CODE_COMBINATIONS    Cost=1    Cardinality=1   
Bytes=34
     INDEX UNIQUE SCAN    Object owner=GL    Object
name=GL_CODE_COMBINATIONS_U1        Cardinality=1   



这个语句还好不是太长,执行计划相对也比较简洁. 但是代价为什么这么大呢?  请大家注意,这个执行计划中, 表的join 顺序是
gl_je_headers -> ffvv -> gl_je_lines -> gcc  .  这是什么样的结果呢? 从语句的条件来看, 
gl_je_headers 的条件有如下几个,包括帐套,期间,币种,状态,来源等,那么这个会返回同帐套,同币种,但是多个公司,多个科目的结果,返回结果
应该是很多的. 另一方面, 这个执行计划的总的结构是nested loop, 这是个相当于for 循环的join 类型, 如果nest loop 的驱
动层有 m 条返回记录,那么就要m 次的去访问 非驱动表,并且如果这次join 的效果不是很好,返回结果仍然会很大( 假设非驱动表有n 条满足自身过滤
条件的记录,那么join 的结果趋近于 m*n ) , 这时,如果外面还有一层 nested loop,那么 仍将继续类似的逻辑,结果集越来越大(除非
在最后有大的过滤),对某些对象的访问也越来越多,形成了一个恶性的循环,所以代价就非常高.
因此,一般对于nested loop ,结果集小的表适宜做驱动表,因为这可以保证较少的去访问非驱动表位置的对象,对于非驱动表,也最好有比较简介明快的访
问路径(如unique index scan ), 这种形式是比较好的.
回到这个问题,我们的语句中有gcc, ffvv, 以及凭证明细表,其实我们条件中是输入了 公司,科目等的,最好的形式应该首先去访问gcc 表,从公司,
科目上较多的过滤数据,使的驱动位置的结果集比较小,这才是较好的选择. 这个语句是cbo 自动给的执行计划,但是我们看到并不是太理想,我测试了一下,我们
加入 rule 的hint 之后,语句就能有较好的执行计划,因此,建议 加入 rule 的hint 来解决.

and gh.set_of_books_id = :p_sob_id
   and ((gh.period_name >= '01-' || substr(:p_period_name, 4, 4) and
gh.period_name <= :p_period_name) or       (gh.period_name >=
'01-'||:cp_period_Y and gh.period_name <= :cp_period_P))
   and gh.actual_flag = 'A'
   and gh.status = 'P'
   and gh.je_source in ('1', '2')
   and gh.currency_code = 'CNY'



--加入hint 之后的计划
SELECT STATEMENT, GOAL = HINT: RULE                   
 SORT ORDER BY                   
  SORT GROUP BY                   
   NESTED LOOPS                   
    NESTED LOOPS                   
     NESTED LOOPS                   
      NESTED LOOPS                   
       TABLE ACCESS BY INDEX ROWID    Object owner=APPLSYS   
Object name=FND_FLEX_VALUES           
        INDEX RANGE SCAN    Object owner=APPLSYS    Object
name=FND_FLEX_VALUES_N3           
       TABLE ACCESS BY INDEX ROWID    Object owner=APPLSYS   
Object name=FND_FLEX_VALUES_TL           
        INDEX UNIQUE SCAN    Object owner=APPLSYS    Object
name=FND_FLEX_VALUES_TL_U1           
      TABLE ACCESS BY INDEX ROWID    Object owner=GL    Object
name=GL_CODE_COMBINATIONS           
       INDEX RANGE SCAN    Object owner=GL    Object
name=GL_CODE_COMBINATIONS_N1           
     TABLE ACCESS BY INDEX ROWID    Object owner=GL    Object
name=GL_JE_LINES           
      INDEX RANGE SCAN    Object owner=GL    Object
name=GL_JE_LINES_N1           
    TABLE ACCESS BY INDEX ROWID    Object owner=GL    Object
name=GL_JE_HEADERS           
     INDEX UNIQUE SCAN    Object owner=GL    Object
name=GL_JE_HEADERS_U1           



4.解决方案:
加入 rule 的hint


select /*+ rule*/ substr(gcc.segment2, 1, 6) substr,
       decode(substr(gcc.segment2, 1, 6),
               '660122',
               '租赁费',
               '660123',
               '物业管理费',              
               '660124',
               '水电费',



5.若干问题小结:
a.目前虽然财务系统数据量比较小,机器比较空闲,但是,我们要看到,因为财务的系统的逻辑比较复杂,一般sql 中至少有2,3个表关联,有些语句更是复杂的
去了,n 个 union all 连在一起等等, 这个结果就是每个表的数据量虽然不很大,但是, 也可以出现代价非常大的语句, 我们最近也路路续续发现了
一些代价较高的语句.
机器资源方面,平时机器资源还比较空闲,但是如果跑起来某些个消耗大的语句,机器资源的消耗短时间内会非常大,如果画个曲线的话,那么就是会有个比较大的波
动. 因此,请大家要重视程序效率以及资源消耗的问题.

b. 可能大家有个疑问, 上面的语句是cbo 选择的,但是却只是选择了个糟糕的计划,没有走更好的计划,这是为什么?  其实这是个很复杂的问题,对于
cbo 来说要考虑的因素也很多,可能一个很简单的语句,他得出的备选方案就有成百上千个, 这个问题我想oracle 估计也没法很好的解决,我们且先不找
cbo的原因.对于在程序开发,我的观点是,如果个自己对逻辑很清楚的语句,那么你就添加某些hint,来指定语句的执行计划算了, 在不很清楚语句逻辑走向,
或者考虑到后面语句的逻辑走向可能有大的变化时,不建议随意的加入hint,此时可以让cbo 自行去选择执行计划. 还有一点就是,对于 循环次数多,比较核
心的语句逻辑,一定要好好测试,从执行计划等各方面来优化语句,没有问题之后才可以上生产,对于这块,大家随时可以来找我协助.

c.从我这段时间的了解,一般我们的报表也好,查询也好,几乎都是有公司,科目等条件的,那么几乎都可以下这样的断言,首先从公司,科目等条件中去过滤信息,然
后再和其他明细表关联的计划应该是比较好的,反之,如果首先没有通过公司科目,而是走了其他的明细表,那么这个计划应该就比较糟糕. 这个希望大家在实际中注意
此条,应该会对程序的效率有所帮助.