ITPub博客

首页 > 数据库 > Oracle > 一次SQL分析

一次SQL分析

原创 Oracle 作者:gaopengtttt 时间:2014-11-07 12:48:27 0 删除 编辑
问题描述:
类似语句如下:
with t as
 (select trans_code, trans_amount, direction, settle_channel_api
    from acctrans_account_log
   where account_no = '20130606000000589266'  and   raw_add_time>= sysdate-1000 and   raw_add_time  ),
t2 as
 (select nvl(sum(decode(t.direction, 'DEBIT_DIRECTION', TRANS_AMOUNT)), 0) trans_out,
         nvl(sum(decode(t.direction, 'CREDIT_DIRECTION', TRANS_AMOUNT)), 0) trans_in
    from t
   where t.trans_code like '3%'
      or t.trans_code like '7%'
      or t.trans_code like '2%'
      or t.trans_code like '5%1'
  --or t.trans_code in ('900003', '900013', '900006', '900016')
  ),
t7 as
 (select sum(trans_out) trans_out, sum(trans_in) trans_in
    from (select nvl(sum(decode(t.direction,
                                'CREDIT_DIRECTION',
                                -TRANS_AMOUNT)),
                     0) trans_out,
                 nvl(sum(decode(t.direction, 'DEBIT_DIRECTION', -TRANS_AMOUNT)),
                     0) trans_in
            from t
           where t.trans_code in ('900003', '900013', '900006', '900016')
          union all
          select * from t2)),
t3 as
 (select nvl(sum(decode(direction,
                        'CREDIT_DIRECTION',
                        trans_amount,
                        -trans_amount)),
             0) deposit
    from t
   where t.trans_code like '4%'
      or t.trans_code in ('900001', '900002', '900011', '900012')),
t4 as
 (select nvl(sum(decode(direction,
                        'CREDIT_DIRECTION',
                        -trans_amount,
                        trans_amount)),
             0) deposit_back
    from t
   where t.trans_code like '5%'and t.trans_code not like '5%1'
      or t.trans_code in ('900007', '900017')),
t5 as
 (select nvl(sum(decode(direction,
                        'CREDIT_DIRECTION',
                        -trans_amount,
                        trans_amount)),
             0) WITHDRAW
    from t
   where t.trans_code like '6%'
      or t.trans_code in ('900004', '900014', '900005', '900015')),
t6 as
 (select nvl(sum(decode(direction,
                        'CREDIT_DIRECTION',
                        trans_amount,
                        -trans_amount)),
             0) WITHDRAW_back
    from t
   where t.trans_code like '8%'
      or t.trans_code in ('900008', '900018'))
select * from  t3, t4, t5, t6, t7;


这个语句的执行计划中发现
select trans_code, trans_amount, direction, settle_channel_api
    from acctrans_account_log
   where account_no = '20130606000000589266'  and   raw_add_time>= sysdate-1000 and   raw_add_time 比较慢,当然由于生成了临时表,而临时表返回数据较多使用到了临时表空间,所以较慢。这种情况下对如上语句进行查看执行计划如下:
在不改变执行计划的情况下,使用COUNT(*)进行替换,注意COUNT(*)可能改变执行计划,
select count(*)
    from acctrans_account_log
   where account_no = '20130606000000589266'  and   raw_add_time>= sysdate-1000 and   raw_add_time 查看其执行计划如下:
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  2wdwnwqas1r8n, child number 0
-------------------------------------
select /*+  gather_plan_statistics */     count(*)     from
accountant.acctrans_account_log    where account_no =
'20120810000000006777'  and   raw_add_time>= sysdate-1000 and
raw_add_time

Plan hash value: 2145504150


----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |      1 |        |      1 |00:00:02.51 |   77003 |
|   1 |  SORT AGGREGATE                      |                      |      1 |      1 |      1 |00:00:02.51 |   77003 |
|*  2 |   FILTER                             |                      |      1 |        |   1666K|00:00:02.88 |   77003 |
|   3 |    PARTITION HASH SINGLE             |                      |      1 |    389 |   1666K|00:00:02.53 |   77003 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| ACCTRANS_ACCOUNT_LOG |      1 |    389 |   1666K|00:00:0
|*  5 |      INDEX RANGE SCAN                | IDX_L_ACCNT_NO_HASHN |      1 |     21 |   1935K|00:00:00.57 |    8
----------------------------------------------------------------------------------------------------




很明显的统计数据估计的基数389不对,而估计的行来自于行数*选择率,这里应该使用全分区扫描,因为大约本分区中80%的行都符合条件
'20120810000000006777'。继而查看其行的统计数据发现没有问题,那么出问题的就可能是选择率出现了问题。
对于这样一个表数据极不均匀的表,需要使用到直方图。
同时使用STA进行建议查看大致如下:




1、  - 考虑接受推荐的 SQL 概要文件。
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1995',
            task_owner => 'SYS', replace => TRUE);


2- Index Finding (see explain plans section below)
--------------------------------------------------
  通过创建一个或多个索引可以改进此语句的执行计划。


  - 考虑运行访问指导以改进物理方案设计或者创建推荐的索引。如果选择创建推荐的索引, 请考虑删除索引
    "ACCOUNTANT"."IDX_L_ACCNT_NO_HASHN", 因为它是推荐的索引的前缀。
    create index ACCOUNTANT.IDX$$_07CB0001 on
    ACCOUNTANT.ACCTRANS_ACCOUNT_LOG("ACCOUNT_NO","RAW_ADD_TIME");


EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------


1- Original With Adjusted Cost
------------------------------
Plan hash value: 2145504150


-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |     1 |    32 |   103K  (1)| 00:20:44 |       |       |
|   1 |  SORT AGGREGATE                      |                      |     1 |    32 |            |          |       |       |
|*  2 |   FILTER                             |                      |       |       |            |          |       |       |
|   3 |    PARTITION HASH SINGLE             |                      |  1555K|    47M|   103K  (1)| 00:20:44 |   197 |   197 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| ACCTRANS_ACCOUNT_LOG |  1555K|    47M|   103K  (1)| 00:20:44 |   197 |   197 |
|*  5 |      INDEX RANGE SCAN                | IDX_L_ACCNT_NO_HASHN | 85875 |       |  8539   (1)| 00:01:43 |   197 |   197 |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(SYSDATE@!-1000    4 - filter("RAW_ADD_TIME">=SYSDATE@!-1000 AND "RAW_ADD_TIME"    5 - access("ACCOUNT_NO"='20120810000000006777')


2- Using SQL Profile
--------------------
Plan hash value: 3113369923


-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name                 | Rows  | Bytes | Cost  | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                      |     1 |    32 | 10685 |       |       |
|   1 |  SORT AGGREGATE        |                      |     1 |    32 |       |       |       |
|   2 |   PARTITION HASH SINGLE|                      |  1555K|    47M| 10685 |   197 |   197 |
|*  3 |    TABLE ACCESS FULL   | ACCTRANS_ACCOUNT_LOG |  1555K|    47M| 10685 |   197 |   197 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("ACCOUNT_NO"='20120810000000006777' AND "RAW_ADD_TIME">=SYSDATE@!-1000 
              AND "RAW_ADD_TIME"





可以看到ORACLE给出了2个建议
1、接受PROFILE,固定执行计划
2、增加索引
这里也能看出为什么说STA非常有用,不管你使不使用它的方法都是一个强有力的辅助工具。
我们不考虑接受他的建议,增加索引对大表来说可能影响性能,这个分区表大约有20G,接受PROFILE是万不得已的手段。
而转为分析为什么不使用全分区扫描。


继而考虑使用10053进行分析,为什么不走全分区扫描
截取重点如下:
  Column (#2): ACCOUNT_NO(  Part#: 196
    AvgLen: 21 NDV: 4301 Nulls: 0 Density: 0.000233
 
 这里给出了他的选择率0.000233,没有考虑到直翻图,对于不均匀分布的数据需要依靠直翻图进行统计,最后进行重新收集直翻图信息。语句按照的全分区扫描执行了。
 execute dbms_stats.gather_table_stats(ownname => 'ACCOUNTANT',tabname => 'acctrans_account_log',method_opt => 'FOR COLUMNS ACCOUNT_NO SIZE AUTO',degree => '2',estimate_percent => 50);
 
 Plan hash value: 1651882679


----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                      |      1 |        |      1 |00:00:01.09 |   72792 |  72782 |
|   1 |  SORT AGGREGATE         |                      |      1 |      1 |      1 |00:00:01.09 |   72792 |  72782 |
|*  2 |   FILTER                |                      |      1 |        |   1667K|00:00:01.53 |   72792 |  72782 |
|   3 |    PARTITION HASH SINGLE|                      |      1 |   1612K|   1667K|00:00:01.17 |   72792 |  72782
|*  4 |     TABLE ACCESS FULL   | ACCTRANS_ACCOUNT_LOG |      1 |   1612K|   1667K|00:00:00.83 |   727
----------------------------------------------------------------------------------------------------


如此执行计划正确。
10053 给出如下:
  Column (#2): ACCOUNT_NO(  Part#: 196
    AvgLen: 21 NDV: 3525 Nulls: 0 Density: 0.000018
    Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 17
实际上我们也能看到经过压缩后高度均匀直翻图的桶为17,说明了有大量重复的行,这有利于数据分布判断
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-1324061/,如需转载,请注明出处,否则将追究法律责任。

下一篇: MONGODB 回滚失败
请登录后发表评论 登录
全部评论
wxh gp_22389860

注册时间:2008-10-13

  • 博文量
    645
  • 访问量
    2840118