分享一个案例,一条SQL引发的“血案”!
技术人人都可以磨炼,但处理问题的思路和角度各有不同,希望这篇文章可以抛砖引玉。
以一个例子为切入点
一、问题背景
这是一个数据仓库系统,正常情况下每天0~6点会跑批,生成前一天的业务报表,供管理层分析使用。
某天凌晨,监控系统频繁发出告警,大批业务报表出现延迟。原本6点前就应跑出的报表,一直持续到10点仍然没有结果。
DBA紧急介入,排查到某个进程占用了大量资源,经与开发人员沟通,先临时杀掉进程。
同时对比从线上收集的ASH/AWR报告,最终定位到某条SQL比较可疑。
经与开发人员确认系一新增功能,因上线紧急,只做了简单的功能测试。正是因为这一条SQL,导致整个系统运行缓慢,大量作业受到影响,修改SQL后系统恢复正常。
基础环境:
- 主机类型:云环境
- 操作系统:CentOS release 7.8
- 存储:EMC
- 内存:128 G
- CPU型号:Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz ( 1 U * 8 core)
- CPU核数:32CORE
- 数据库环境:11.2.0.4
问题现象:
跑批任务延迟。
简单说明:
在很多应用场景中,SQL 的性能直接决定了系统的性能。此外,查询速度慢并不只是因为 SQL 语句本身,还可能是因为内存分配不佳、文件结构不合理、优化器判断异常等其他原因。
本文介绍一些通过调整 SQL 语句就能优化SQL的通用小技巧,优化 SQL 的方法不能解决所有的性能问题,但是却能处理很多因 SQL 写法不合理而产生的性能问题。
二、分析说明
- 通过分析定位问题,分析问题原因;
- 追溯历史数据,分析关键指标,这些关键指标可以用来做为参考指标。
- 用实际数据来验证推断,排除掉其它干扰因素,定位问题的根本原因,帮助快速修复。
三、疑问点排查及分析思路1、具体分析
问题SQL:
SELECT /*+ INDEX (T1 xxxxx) */ SUM(T2.CRKSL), SUM(T2.CRKSL*A2.DJ) ...
FROM xxxx T2, xxxx T1
WHERE T2.CRKFLAG=xxx AND T2.CDATE>=xxx AND T2.CDATE<xxx;
SQL并不复杂,两表关联,不过两张表的数据量都较大。
执行计划:
优化器评估返回的数据量为3505T条记录,计划返回量127P字节,总成本9890G,返回时间999:59:59。
大写的牛批!!!
2、分析结论
看到这个结果我第一反应就是产生笛卡尔积了。执行计划也验证了这一结论,两表关联使用了笛卡儿积的关联方式。
笛卡儿连接是指两表没有任何条件限制的连接查询。一般情况下应尽量避免笛卡儿积。
3、一些启发
从案例本身来讲并没有什么特别之处,不过是开发人员疏忽导致了一条质量很差的SQL。
但从更深层次来讲,开发人员的一个疏忽造成了严重的后果,原来数据库竟是如此的脆弱。需要对数据库保持“敬畏”之心。
也不必过分苛责开发人员,谁都会犯错误,关键是如何从制度上保证不再发生类似的问题。
四、总结
1、严格遵守SQL开发规范
加强对数据库开发人员的培训工作,提高其对数据库的理解能力和SQL开发水平,向开发人员灌输SQL优化的思想,在工作中逐步积累,这样才能提高公司整体开发质量,也可以避免很多低级错误。
2、SQL Review制度
对于SQL Review,怎么强调都不过分。
常见做法是利用SQL分析引擎(商用或自研)进行分析或采取半人工的方式进行审核。审核后的结果可作为持续改进的依据。
SQL Review的中间结果可以保留,作为系统上线后的对比分析依据,进而可将SQL的审核、优化、管理等功能集成起来,完成对SQL整个生命周期的管理。
3、限流/资源控制
有些数据库提供了丰富的资源限制功能,可以从多个维度限制会话对资源(CPU、MEMORY、IO)的使用,可避免发生单个会话影响整个数据库的运行状态。