FF010-参数优化案例 : 数据库升级后的SQL不仅慢, 还耗尽了temp表空间, 考验DBA的时刻来了!

SELECT *  from 
( select temp.*, rownum tmp_row_no 
from 
( select 
        s.client_code 委托方编码,
        s.client_name 委托方,
        t.service_order_no 服务单号,
        t.contact_time 接入时间,
        (select v.code_name  from sup.sys_codelist_slave v  where v.parentid='155' and v.code_value=t.order_origin) 单据来源,
        t.branch_code 中心编码,
        (select c.branch_name from sup.sup_branch c where t.org_code=c.org_code and t.branch_code=c.branch_code) 中心名称,
        (select c.zone_name  from sup.sup_branch c where t.org_code=c.org_code and t.branch_code=c.branch_code) 片区,
        t.unit_code 网点编码,
        t.unit_name 网点名称,
        t.settlement_unit_code 结算网点编码,
        t.settlement_unit_name 结算网点名称,
        t.implement_main_type_name 实施业务类型,
        (select v.code_name  from sup.sys_codelist_slave v  where v.parentid='229' and v.code_value=t.customer_level) 用户级别,
        s.prod_name 品类,
        s.brand_name 品牌,
        t.finish_time 服务完成时间,
        t.service_finish_time 工单完成时间,
        t.appoint_start_time 首约开始时间,
        t.appoint_end_time 首约结束时间,
        case when a.change_appoint_time is not null then a.appoint_start_time end 改约开始时间,
        (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no) 到达时间,
        t.service_method_name 服务方式,
        a.engineer_code 工程师编码,
        a.engineer_name 工程师名称,
        t.feedback_desc  反馈描述,
        t.feedback_main_item_name 网点一级反馈,
        t.feedback_sub_item_name 网点二级反馈,
        (select v.code_name from sup.sys_codelist_slave v where v.parentid='234' and v.code_value=a.feedback_result_code)  工程师反馈结果,
        a.feedback_main_item_name 工程师一级反馈,
        a.feedback_sub_item_name 工程师二级反馈,
        case when (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no) 
          between (t.appoint_start_time-1/24) and t.appoint_end_time 
          and  (nvl(t.finish_time,t.service_finish_time)-
        (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no))*24 <=24 
        then '1' else '0' end 快准
        ,case when (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no)
          between (t.appoint_start_time-1/24) and t.appoint_end_time 
          and  (nvl(t.finish_time,t.service_finish_time)-
          (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no))*24 >24
          and  (nvl(t.finish_time,t.service_finish_time)-
         (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no))*24 <120 
         then '1' else '0' end 不快准
        ,case when ((select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no)
          not between (t.appoint_start_time-1/24) and t.appoint_end_time) 
          and  (nvl(t.finish_time,t.service_finish_time)-
        (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no))*24 <=24 then '1'
          when (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no) is null 
            and (nvl(t.finish_time,t.service_finish_time)-nvl(t.appoint_end_time,t.contact_time))*24 <=24
          then '1'  else '0' end 快不准
       ,case when  ((select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no) 
          not between (t.appoint_start_time-1/24) and t.appoint_end_time)
        and (nvl(t.finish_time,t.service_finish_time)-
        (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no))*24 >24  
        and (nvl(t.finish_time,t.service_finish_time)-
        (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no))*24 <=120 then '1' 
        when (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no) is null
          and (nvl(t.finish_time,t.service_finish_time)-nvl(t.appoint_end_time,t.contact_time))*24 >24 
          and (nvl(t.finish_time,t.service_finish_time)-nvl(t.appoint_end_time,t.contact_time))*24 <=120  then '1'
          else '0' end 不快不准
        ,case when ((select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no)  
          between (t.appoint_start_time-1/24) and t.appoint_end_time) and (nvl(t.finish_time,t.service_finish_time)-
         (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no))*24 >120 then '1' 
          when ((select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no)
            not between (t.appoint_start_time-1/24) and t.appoint_end_time) and (nvl(t.finish_time,t.service_finish_time)-
         (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no))*24 >120 then '1'
          when (select min(c.on_site_time) from wom.wom_dispatch_order c where c.service_order_no=t.service_order_no) is null 
          and  (nvl(t.finish_time,t.service_finish_time)-nvl(t.appoint_end_time,t.contact_time))*24 >120 then '1'
            when nvl(t.finish_time,t.service_finish_time) is null 
               and (sysdate-nvl(nvl(a.appoint_end_time,t.appoint_end_time),t.contact_time))*24 >120  then '1'
          else '0' end 超五
from wom.wom_service_order t
join wom.wom_service_user_demand s on t.org_code=s.org_code and t.service_order_no=s.service_order_no and s.pub_validly='Y'
join wom.wom_dispatch_order a     
 on s.dispatch_order_id=a.dispatch_order_id  and a.pub_validly='Y'
where  t.pub_validly='Y'
    and t.order_origin not in ('11','26','30','49','25','29','36','50','34','31','54','39','21')
    and s.brand_code not in ('RONGSHIDA', 'DAMINI')
    and nvl(t.unit_code, '0') != 'W1101100059'
    and not (nvl(t.settlement_unit_name, '0') like '%苏宁%' and s.org_code = 'CS006')
    and t.service_method_code='10'
    and t.implement_main_type_code in ('10','11','13','19')
    and s.prod_code not in 
    (select pr.prod_code from sup.sup_prod_type pr
        start with pr.prod_code in ('16', '118')
        connect by prior pr.prod_code = pr.parent_node_id 
        union
     select '1022' from dual
    )
    and t.service_order_status<>'22'
    and not exists 
    (select * from 
        (select * from wom.wom_feedback_info fi
        where fi.dispatch_order_id = a.dispatch_order_id
            and a.on_site_time is null
            and (fi.feedback_result_code in ('12', '13') or fi.feedback_sub_item_code in ('FW0103', 'FW0104'))
        order by fi.feedback_time
        ) fiTmp
    where rownum <= 1
    and fiTmp.feedback_time <t.appoint_end_time
    )
    and a.dispatch_order_id not in 
        (
        select c.dispatch_order_id 
        from wom.wom_dispatch_order c  
        where c.service_order_no = a.service_order_no
            and c.dispatch_order_status = '17'
            and c.pub_validly = 'Y'
            and c.on_site_time is null
            and c.pub_modi_date < t.appoint_end_time
        )
     and s.client_code in ('CL000001') 
     and  t.pub_create_date >= to_date(' 2021-09-21 00:00:00','yyyy-mm-dd hh24:mi:ss') 
     and  t.pub_create_date <= to_date(' 2021-09-21 23:59:59','yyyy-mm-dd hh24:mi:ss')
     and t.org_code = :1   
 )  temp where rownum <= 500000
) where tmp_row_no > 0
;