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

上篇文章FF009的SQL_text有一处符号错误, 有学员发现, 已修正, 并奖励学员大红包一个.

今天的这种案例, 处理时间可能花一小时, 但是写出来能让人看懂并印象深刻, 我至少要花一上午(包括制作test case给大家自己动手重现故障现象). 如果没有付费阅读, 可能大家看不到这篇文章.

我曾经把这个案例优化前后的SQL monitor文件发到200多人的学员群让他们分析, 没有人告诉我到底是哪里做了优化.

还有两个学员也问过我类似的问题, 其中一个还引出了一篇关于性能优化方法论的文章, 更让我印象深刻, 文末有截图.

下面开始正题.

客户的数据库从11g升级到了12.1后(升级到19c也是一样), 原来执行不到2分钟的SQL, 执行了9个多小时还没有结束. 我的同事在现场,这么复杂的SQL一时半会他也没有头绪, 于是收集了sqlhc向我求助.

SQL很复杂, 包含了很多的写法特征: 

标量子查询(聚合,case when+聚合,不带聚合等多种形式), connect by, not in , union ,not exists(带rownum), 分页 等, sql_text 如下所示:

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
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 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