A-呵呵哒 2021-10-08 10:18 采纳率: 50%
浏览 54
已结题

优化group by ,group by导致sql性能下降

在优化如下sql的时候,并查看其执行计划,发现整体sql的性能主要卡在group by,应该如何优化?
具体执行计划如下:

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time       | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |       |  3136M(100)|       |       |       |
|   1 |  HASH GROUP BY                 |                   |   188K|  6802K|    10M|  3136M  (1)|999:59:59 |       |       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   FILTER                 |                   |       |       |       |        |       |       |       |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| mpos_aaa_his       |   197K|  7119K|       |     5   (0)| 00:00:01 |    18 |    18 |
|*  4 |     INDEX RANGE SCAN             | ID_POSXXXX_ZHSY       |     1 |       |       |     4   (0)| 00:00:01 |       |       |
|   5 |    NESTED LOOPS              |                   |     1 |    21 |       | 16710   (1)| 00:03:21 |       |       |
|*  6 |     TABLE ACCESS FULL             | aas_agent_ccc_info |     1 |    11 |       | 16704   (1)| 00:03:21 |       |       |
|   7 |     INLIST ITERATOR             |                   |       |       |       |        |       |       |       |
|*  8 |      INDEX RANGE SCAN             | IDX1_XXXX_AGENT_INFO       |     1 |    10 |       |     7   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   2 - filter( IS NULL)
   4 - access("T"."ORG_ID"=195116 AND "T"."ROUTE_FLAG"='1' AND "T"."POS_CND"='XXX' AND "T"."TRADE_STATUS"='00' AND
          "T"."TRADE_DATE">=TIMESTAMP' 2021-08-01 00:00:00.000000000' AND "T"."TRADE_DATE"<TIMESTAMP' 2021-09-01 00:00:00.000000000')
   6 - filter(LNNVL("AI"."SHOP_ID"<>:B1))
   8 - access((("A"."TOP_ORG_ID"=49694 OR "A"."TOP_ORG_ID"=52359 OR "A"."TOP_ORG_ID"=53858 OR "A"."TOP_ORG_ID"=56983 OR
          "A"."TOP_ORG_ID"=56986 OR "A"."TOP_ORG_ID"=57067 OR "A"."TOP_ORG_ID"=57290)) AND "AI"."ORG_ID"="A"."ORG_ID")

sql如下:


select 
   count(*) 交易笔数,to_char(t.trade_date ,'yyyy-mm-dd') as 交易日期,sum(t.trade_amount) 交易金额
    from   mpos_aaa_his t where 1=1
   and t.trade_status = '00'
   and t.org_id in 195116
   and t.POS_CND = 'XXXX'
   and t.route_flag = '1'
   and t.shop_id not  in
   (
       select ai.shop_id from aas_agent_ccc_info ai, aas_bbb_info a where ai.org_id = a.org_id and a.top_org_id in 
       (
              49694,
              52359,
              53858,
              56983,
              57067,
              56986,
              57290
       )
   )
   and (t.trade_date >= to_timestamp('2021-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and t.trade_date < to_timestamp('2021-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  group by  to_char(t.trade_date ,'yyyy-mm-dd');
  • 写回答

4条回答 默认 最新

  • 唯一的小彬哥 2021-10-11 14:04
    关注

    你这个问题我认为不是出在group by上。问题大概也有两点
    1.大数据量group by走了嵌套循环我认为不太不合适
    2.看上去你使用了分区表,并且使用了全局索引,这不太合适,试试看能不能改成本地索引

    针对以上两点,我建议你做以下修改。and t.shop_id not in 后面的sql提取出来形成一个内联视图,然后把这个内联视图和mpos_aaa_his进行关联之后做left join或right join 替代not in 记得加提示/+ use_hash(内联视图的名称或别名 t) no_merge(内联视图名)/
    想了解更多的数据库知识可以关注我的公众号:唯一的小彬哥
    大致的代码就像下面这样,你自己按上面思路调整一下。

    select /*+ use_hash(t b) no_merge(b)*/    --前面/*+ use_hash(t b) no_merge(b)*/是提示,不是注释,不能去掉
       count(*) 交易笔数,to_char(t.trade_date ,'yyyy-mm-dd') as 交易日期,sum(t.trade_amount) 交易金额
        from   mpos_aaa_his t left join (
           select  distinct ai.shop_id from aas_agent_ccc_info ai, aas_bbb_info a where ai.org_id = a.org_id and a.top_org_id in 
           (
                  49694,
                  52359,
                  53858,
                  56983,
                  57067,
                  56986,
                  57290
           )
       ) b on and t.shop_id = b.shop_id
       
         where 1=1
      
       and t.trade_status = '00'
       and t.org_id in 195116
       and t.POS_CND = 'XXXX'
       and t.route_flag = '1'
       and b.shop_id is null--这里新加的
       
       and (t.trade_date >= to_timestamp('2021-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and t.trade_date < to_timestamp('2021-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
      group by  to_char(t.trade_date ,'yyyy-mm-dd');
    
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

问题事件

  • 系统已结题 10月20日
  • 已采纳回答 10月12日
  • 修改了问题 10月9日
  • 创建了问题 10月8日