大家帮我分析分析，这个SQL为什么这么慢！


大家帮我看看，这个执行计划时间主要浪费在哪里了！  下面是SQLselect 
 count(1)
  from (select 
         ab_cell,
         ceil(avg(:MGW_DOUBLE_TALK) * 100) / 100 MGW_DOUBLE_TALK,
         ceil(avg(:MGW_NOISE_LEVEL) * 100) / 100 MGW_NOISE_LEVEL,
         ceil(avg(:MGW_FLAT_NOISE_LEVEL) * 100) / 100 MGW_FLAT_NOISE_LEVEL,
         ceil(avg(:MGW_ACTIVE_SPEECH_LEVEL) * 100) / 100 MGW_ACTIVE_SPEECH_LEVEL,
         ceil(avg(:MGW_SPEECH_ACTIVITY_FACTOR_EGR) * 100) / 100 MGW_SPEECH_ACTIVITY_FACTOR_EGR,
         ceil(avg(:MGW_SPEECH_ACTIVITY_FACTOR_ING) * 100) / 100 MGW_SPEECH_ACTIVITY_FACTOR_ING
          from (select  ab_cell from (select
                 TO_CHAR(nvl(r.report_time, ''), 'YYYY-MM-DD HH24:MI:SS') report_time,
                 a_cell ab_cell,
                 MGW_DOUBLE_TALK,
                 MGW_NOISE_LEVEL,
                 MGW_FLAT_NOISE_LEVEL,
                 MGW_ACTIVE_SPEECH_LEVEL,
                 MGW_SPEECH_ACTIVITY_FACTOR_EGR,
                 MGW_SPEECH_ACTIVITY_FACTOR_ING
                  from tb_src_rtt r
                  left join tb_src_ctx c on c.ctx_orig_call_id =
                                            r.orig_call_id
                                        and c.ctx_global_call_ref =
                                            r.global_call_ref
                                        and r.call_start_time <=
                                            c.ctx_report_time
                                        and r.report_time >=
                                            c.ctx_report_time
                  left join tb_src_mgw_tdm t on c.ctx_h248_ctx_id =
                                                t.mgw_h248_ctx_id
                                            and c.ctx_vmgw_ip_address =
                                                t.mgw_vmgw_ip_address
                                            and c.ctx_vmgw_ip_port =
                                                t.mgw_vmgw_ip_port
                                            and c.ctx_context_start_time <=
                                                t.mgw_tdm_report_time
                                            and c.ctx_context_end_time >=
                                                t.mgw_tdm_report_time
                 where TNESID = 2
                   and MGW_TDM_REPORT_TIME >=
                       TO_DATE('2011-08-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                   and MGW_TDM_REPORT_TIME <=
                       TO_DATE('2011-08-03 01:00:00', 'YYYY-MM-DD HH24:MI:SS')
                union all
                select
                 TO_CHAR(nvl(r.report_time, ''), 'YYYY-MM-DD HH24:MI:SS') report_time,
                 b_cell ab_cell,
                 MGW_DOUBLE_TALK,
                 MGW_NOISE_LEVEL,
                 MGW_FLAT_NOISE_LEVEL,
                 MGW_ACTIVE_SPEECH_LEVEL,
                 MGW_SPEECH_ACTIVITY_FACTOR_EGR,
                 MGW_SPEECH_ACTIVITY_FACTOR_ING
                  from tb_src_rtt r
                  left join tb_src_ctx c on c.ctx_orig_call_id =
                                            r.orig_call_id
                                        and c.ctx_global_call_ref =
                                            r.global_call_ref
                                        and r.call_start_time <=
                                            c.ctx_report_time
                                        and r.report_time >=
                                            c.ctx_report_time
                  left join tb_src_mgw_tdm t on c.ctx_h248_ctx_id =
                                                t.mgw_h248_ctx_id
                                            and c.ctx_vmgw_ip_address =
                                                t.mgw_vmgw_ip_address
                                            and c.ctx_vmgw_ip_port =
                                                t.mgw_vmgw_ip_port
                                            and c.ctx_context_start_time <=
                                                t.mgw_tdm_report_time
                                            and c.ctx_context_end_time >=
                                                t.mgw_tdm_report_time
                 where TNESID = 2
                   and MGW_TDM_REPORT_TIME >=
                       TO_DATE('2011-08-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                   and MGW_TDM_REPORT_TIME <=
                       TO_DATE('2011-08-03 01:00:00', 'YYYY-MM-DD HH24:MI:SS')) ) cs
         group by ab_cell) ac
 where MGW_DOUBLE_TALK > 50.0
有哪位大能，有优化办法，十分感谢！！

解决方案 »

  1.   

    大体感觉sql里嵌套太多，还有期中为什么要写union all啊，感觉前后结果集数一样啊
      

  2.   

    补充下执行计划 
    图片上传不了。SELECT STATEMENT, GOAL = ALL_ROWS 耗费=49420 CPU 耗费=7611460704 时间=594 时间戳=2011-8-3 20:49:33
     SORT AGGREGATE 时间戳=2011-8-3 20:49:33
      VIEW 耗费=49420 CPU 耗费=7611460704 时间=594 时间戳=2011-8-3 20:49:33
       FILTER 时间戳=2011-8-3 20:49:33
        HASH GROUP BY 耗费=49420 CPU 耗费=7611460704 字节=26 时间=594 时间戳=2011-8-3 20:49:33
         VIEW 耗费=49419 CPU 耗费=7605640914 字节=26 时间=594 时间戳=2011-8-3 20:49:33
          UNION-ALL 时间戳=2011-8-3 20:49:33
           HASH JOIN 耗费=24709 CPU 耗费=3802820457 字节=306 时间=297 时间戳=2011-8-3 20:49:33
            PARTITION RANGE ALL 耗费=5831 CPU 耗费=569795314 字节=150 时间=70 时间戳=2011-8-3 20:49:33
             TABLE ACCESS FULL 对象名称=TB_SRC_CTX 耗费=5831 CPU 耗费=569795314 字节=150 时间=70 时间戳=2011-8-3 20:49:33
            MERGE JOIN CARTESIAN 耗费=18865 CPU 耗费=3152652443 字节=120841812 时间=227 时间戳=2011-8-3 20:49:33
             PARTITION RANGE EMPTY 耗费=2 CPU 耗费=0 字节=37 时间=1 时间戳=2011-8-3 20:49:33
              TABLE ACCESS FULL 对象名称=TB_SRC_MGW_TDM 耗费=2 CPU 耗费=0 字节=37 时间=1 时间戳=2011-8-3 20:49:33
             BUFFER SORT 耗费=18863 CPU 耗费=3152652443 字节=184361107 时间=227 时间戳=2011-8-3 20:49:33
              PARTITION RANGE ALL 耗费=18863 CPU 耗费=3152652443 字节=184361107 时间=227 时间戳=2011-8-3 20:49:33
               TABLE ACCESS FULL 对象名称=TB_SRC_RTT 耗费=18863 CPU 耗费=3152652443 字节=184361107 时间=227 时间戳=2011-8-3 20:49:33
           HASH JOIN 耗费=24709 CPU 耗费=3802820457 字节=306 时间=297 时间戳=2011-8-3 20:49:33
            PARTITION RANGE ALL 耗费=5831 CPU 耗费=569795314 字节=150 时间=70 时间戳=2011-8-3 20:49:33
             TABLE ACCESS FULL 对象名称=TB_SRC_CTX 耗费=5831 CPU 耗费=569795314 字节=150 时间=70 时间戳=2011-8-3 20:49:33
            MERGE JOIN CARTESIAN 耗费=18865 CPU 耗费=3152652443 字节=120841812 时间=227 时间戳=2011-8-3 20:49:33
             PARTITION RANGE EMPTY 耗费=2 CPU 耗费=0 字节=37 时间=1 时间戳=2011-8-3 20:49:33
              TABLE ACCESS FULL 对象名称=TB_SRC_MGW_TDM 耗费=2 CPU 耗费=0 字节=37 时间=1 时间戳=2011-8-3 20:49:33
             BUFFER SORT 耗费=18863 CPU 耗费=3152652443 字节=184361107 时间=227 时间戳=2011-8-3 20:49:33
              PARTITION RANGE ALL 耗费=18863 CPU 耗费=3152652443 字节=184361107 时间=227 时间戳=2011-8-3 20:49:33
               TABLE ACCESS FULL 对象名称=TB_SRC_RTT 耗费=18863 CPU 耗费=3152652443 字节=184361107 时间=227 时间戳=2011-8-3 20:49:33
      

  3.   

    有一个问题非常不解。我在我机器上执行这个SQL，执行30秒左右。
    到服务器上执行，需要花30分钟这个是什么原因？？
      

  4.   

    去掉union all ，加上b_cell,结果应该是一样
    select count(1)
      from (select a_cell,b_cell,
                   ceil(avg(:MGW_DOUBLE_TALK) * 100) / 100 MGW_DOUBLE_TALK,
                   ceil(avg(:MGW_NOISE_LEVEL) * 100) / 100 MGW_NOISE_LEVEL,
                   ceil(avg(:MGW_FLAT_NOISE_LEVEL) * 100) / 100 MGW_FLAT_NOISE_LEVEL,
                   ceil(avg(:MGW_ACTIVE_SPEECH_LEVEL) * 100) / 100 MGW_ACTIVE_SPEECH_LEVEL,
                   ceil(avg(:MGW_SPEECH_ACTIVITY_FACTOR_EGR) * 100) / 100 MGW_SPEECH_ACTIVITY_FACTOR_EGR,
                   ceil(avg(:MGW_SPEECH_ACTIVITY_FACTOR_ING) * 100) / 100 MGW_SPEECH_ACTIVITY_FACTOR_ING
              from (select a_cell,b_cell
                      from (select TO_CHAR(nvl(r.report_time, ''),
                                           'YYYY-MM-DD HH24:MI:SS') report_time,
                                   a_cell ,
                                   b_cell ,
                                   MGW_DOUBLE_TALK,
                                   MGW_NOISE_LEVEL,
                                   MGW_FLAT_NOISE_LEVEL,
                                   MGW_ACTIVE_SPEECH_LEVEL,
                                   MGW_SPEECH_ACTIVITY_FACTOR_EGR,
                                   MGW_SPEECH_ACTIVITY_FACTOR_ING
                              from tb_src_rtt r
                              left join tb_src_ctx c
                                on c.ctx_orig_call_id = r.orig_call_id
                               and c.ctx_global_call_ref = r.global_call_ref
                               and r.call_start_time <= c.ctx_report_time
                               and r.report_time >= c.ctx_report_time
                              left join tb_src_mgw_tdm t
                                on c.ctx_h248_ctx_id = t.mgw_h248_ctx_id
                               and c.ctx_vmgw_ip_address = t.mgw_vmgw_ip_address
                               and c.ctx_vmgw_ip_port = t.mgw_vmgw_ip_port
                               and c.ctx_context_start_time <=
                                   t.mgw_tdm_report_time
                               and c.ctx_context_end_time >= t.mgw_tdm_report_time
                             where TNESID = 2
                               and MGW_TDM_REPORT_TIME >=
                                   TO_DATE('2011-08-03 00:00:00',
                                           'YYYY-MM-DD HH24:MI:SS')
                               and MGW_TDM_REPORT_TIME <=
                                   TO_DATE('2011-08-03 01:00:00',
                                           'YYYY-MM-DD HH24:MI:SS')
                          )) cs
             group by a_cell,b_cell) ac
     where MGW_DOUBLE_TALK > 50.0
      

  5.   

    對表tb_src_rtt 建立一些索引看看.