一条SQL引发的“血案”：与SQL优化相关的4个案例.docx

一条SQL引发的〃血案〃：与SQL优化相关的4个案导读：本文通过几个案例探讨一下SQL优化的相关问题。

案例1 一条SQL引发的“血案”1.案例说明
某大型电商公司数据仓库系统，正常情况下每天0〜9点会执行大量作业，生成前一天 的业务报表，供管理层分析使用。但某天早晨6点开始，监控人员就频繁收到业务报警， 大批业务报表突然出现大面积延迟。原本8点前就应跑出的报表，一直持续到10点仍 然没有结果。公司领导非常重视，严令在11点前必须解决问题。
DBA紧急介入处理，通过TOP命令查看到某个进程占用了大量资源，杀掉后不久还会 再次出现。经与开发人员沟通，这是由于调度机制所致，非正常结束的作业会反复执行。
暂时设置该作业无效，并从脚本中排查可疑SQLO同时比照从线上提供的ASH/AWR
案例3规范SQL写法好处多1.案例说明
某大型电商公司数据仓库系统，开发人员反映作业运行缓慢。经检查是一个新增业务中 某条SQL语句导致。经分析是非标准的SQL引起优化器判断异常，将其修改成标准写 法后，SQL恢复正常。
1）具体分析看下面的代码：
select .・・ from .・। yyyy - mm - d
TABLE ACCESS BY GLOBAL INDEX ROWID
XXXX |5 (0) | 00:00:01 | ROlL [ ROW L

L [ ROW L
3 | NESTED LOOPS|| 2470K (1) | 08:14:11
6 |HASH GROUP BY ||2470K (1)| 08:14:10

7 TABLE ACCESS BY GLOBAL INDEX ROW工| XXXX |5 (0)| 00:00:01 | ROW L
8 |NESTEDLOOPS||2470K(1)|08：14：10|
9 |SORTUNIQUE||2340K(2)|07：48：11|I|2340K(2)|07:48:11|1
| XXXX I2340K(2)107:48:11|112INDEX RANGE SCAN

12INDEX RANGE SCAN
13 | INDEX RANGE SCAN | XXXX |3 (0)| 00:00:01
这个SQL中涉及的主要表是一个分区表，从执行计划(Pstart. Pstop )中可见，扫描
了所有分区，分区裁剪特性没有起效。
2 )解决方法见下面的代码:
from .・
order_creation_date >= to_date(20120208Jyyyy-mm-dcT ) and
order_creation_date<to_date(20120209^'yyyy-mm-ddSORT AGGREGATE
CONCATENATION
||17393 (1)| 00:03:29 |
57
TABLE ACCESS FULL
)from ..
send_date>= to_date(20120208 Jyyyy-mm-dcT )
send_date〈to_date(20120209 Jyyyy-mm-dd '尝试通过引入union all来分解查询，以便于优化器做出更准确的判断。采用这个方法
后，确实起效了，当然不可防止会扫描两遍表。
select
/hereorder_creation_date>= to_date(20120208J,yyyymmdd1) send_date>= to_date(20120208J,yyyymmdd')
EflId

Id
Operation
Name | Cost(%CPU)|Time
Pstart

PstopSELECT STATEMENT

SELECT STATEMENT
42358 (1)| 00:08:29

57

通过调整日期FORMAT格式，优化器很精准地判断了分区(Pstart=57、Pstop=57 ), 整体SQL性能得到了很大的提高，作业运行时间从8个多小时缩减到8分钟。
3)分析结论对于非标准的日期格式，Oracle在复杂逻辑判断的情况下分区裁剪特性无法识别，不 起作用。这种情况下，会走全表扫描，结果是正确的，但是执行效率会很低。通过使用 union all ,简化了条件判断。使得Oracle在非保准日期格式下也能使用分区裁剪特性， 但最正确修改方式还是规范SQL的写法。
2.给我们的启示
规范的SQL写法，不但利于提高代码可读性，还有利于优化器生成更优的执行 计划。
分区功能是Oracle应对大数据的利器，但在使用中要注意是否真正会用到分区 特性；否那么，可能适得其反，使用分区会导致效率更差。
案例4 "月底难过".案例说明
某大型电商公司数据仓库系统经常出现在月底运行缓慢的情况，但在平时系统运行却非 常正常。这是因为月底往往有月报等大批量作业运行，而就在这个时间点上，常常会出 现缓慢情况，所以业务人员一到月底就非常紧张。这也成了一个老大难问题，困扰了很 长时间。
DBA介入处理，发现一个很奇怪的现象：某条主要SQL是造成执行缓慢的主因，其执 行计划是不确定的，也就是说因为执行计划的改变，导致其运行效率不同。而往往较差 的执行计划发生在月底几天，且由于月底大批作业的影响，整体性能比拟饱和，更突显 了这个问题。
针对某个出现问题的时间段做了进一步分析,结果说明是由于统计信息的缺失导致了优 化器产生了较差的执行计划，并据此指定了人工策略，彻底解决了这个问题。
1）具体分析先来看下面的代码：
xxx a xxx b a.ordejid 二 b .
xxx c b.gysid 二二whereb.cdate>= to_date(' 2012-03-311'yyyy-mm-dd1) - 3 and
a・send_date<to_date('2012-03-31', 'yyyy-mm-dd');

Id | Operation|Name | Rows | Bytes | Cost (%CPU) |Psta

|0 | SELECT STATEMENT||1 |104 |9743(1”
|1 | HASH JOIN OUTER||1 |104 |9743(1)1|XXXX I1 |22 I0(0)1118
|3 | NESTED LOOPS ||1 |94 |9739(1”
4
PARTITION RANGE ITERATOR

1032
74304
9739(1)1 12
TABLE ACCESS FULL
XXXX
1032 |74304
9739(1”12
3 | 518
6 I
PARTITION RANGE SINGLE
0(0) 118
9 | 1189
7
INDEX RANGE SCAN
XXXX
0(0)1 118

1189
8| XXXX I

| XXXX I
183 |1830
3(0)

执行计划中，多表关联使用了嵌套循环，这点对于OLAP系统来说是比拟少见的。一般 优化器更倾向于使用SM和HJO进一步检查发现其本钱竟然是0，怪不得优化器使用 了嵌套循环。
1 ）深入分析检查发现索引数据统计信息异常，这是分区索引，仅两天的分区统计信息都是0。导致 优化器认为嵌套循环的执行效率更高，而不是使用哈希连接。结合业务发现，月底是业 务高峰期，对于系统统计信息的作业收集，在指定的时间窗口内无法完成。最后导致统 计信息不完整，优化器采用了错误的执行计划。
3）解决方法解决的代码如下：
exec dbms_stats . gather_index_stats(
partname二)'PART_xxx '
estimate_percent 二)分析完对象的统计信息即恢复正常。
2.给我们的启示
统计信息是优化器优化的重要参考依据，一个完整、准确的统计信息是必要条 件。往往在优化过程中，第一步就是查看相关对象的统计信息。
分区机制是Oracle针对大数据的重要解决手段，但也很容易造成所谓"放大效 应"。即对于普通表而言，统计信息更新不及时可能不会导致执行计划偏差过大；但对 于分区表、索引来说，很容易出现因更新不及时出现0的情况，进而导致执行计划产生 严重偏差。
-END -
报告，最终定位到某条SQL比拟可疑。
经与开发人员确认系一新增功能，因上线紧急，只做了简单的功能测试。正是因为这一条SQL,导致整个系统运行缓慢，大量作业受到影响，修改SQL后系统恢复正常。
具体分析SELECT /*+ INDEX (Al xxxxx) */ SUM(A2.CRKSL), SUM(A2.CRKSL*A2.DJ)...
V^1ER^A2^：RKF LAG=xxxAN^2^D4TE>=xx>^N^^CD?TE<xxx^^^^^^^H这是一个很典型的两表关联语句，两张表的数据量都较大。下面来看看执行计划，如图
1所示。
执行计划触目惊心，优化器评估返回的数据量为3505T条记录，计划返回量127P字节，总本钱9890G ,返回时间999:59:59O

▲图1执行计划
分析结论从执行计划中可见，两表关联使用了笛卡儿积的关联方式。我们知道笛卡儿连接是指两
表没有任何条件限制的连接查询。一般情况下应尽量防止笛卡儿积，除非某些特殊场合,否那么再强大的数据库也无法处理。
这是一个典型的多表关联缺乏连接条件，导致笛卡儿积，引发性能问题的案例。
2 .给我们的启示从案例本身来讲并没有什么特别之处，不过是开发人员疏忽导致了一条质量很差的SQL。 但从更深层次来讲，这个案例可以给我们带来如下启示。
开发人员的一个疏忽造成了严重的后果，原来数据库竟是如此的脆弱。需要对 数据库保持“敬畏”之心。
电脑不是人脑，它不知道你的需求是什么，只能根据写好的逻辑进行处理。
不要去责怪开发人员，谁都会犯错误，关键是如何从制度上保证不再发生类似 的问题。
3 .解决之道) SQL开发规范
加强对数据库开发人员的培训工作，提高其对数据库的理解能力和SQL开发水平。将 局部SQL运行检查的职责前置，在开发阶段就能规避很多问题。要向开发人员灌输SQL 优化的思想，在工作中逐步积累，这样才能提高公司整体开发质量，也可以防止很多低 级错误。
1 ) SQL Review 制度对于SQL Review ,怎么强调都不过分。从业内来看，很多公司也都在自己的开发流程 中纳入了这个环节，甚至列入考评范围，对其重视程度可见一斑。其常见典型做法是利 用SQL分析引擎(商用或自研)进行分析或采取半人工的方式进行审核。审核后的结 果可作为持续改进的依据。
SQL Review的中间结果可以保存，作为系统上线后的比照分析依据，进而可将SQL 的审核、优化、管理等功能集成起来，完成对SQL整个生命周期的管理。
2 ）限流/资源控制有些数据库提供了丰富的资源限制功能，可以从多个维度限制会话对资源（CPU、 MEMORY. 10）的使用，可防止发生单个会话影响整个数据库的运行状态。
对于一些开源数据库，局部技术实力较强的公司还通过对内核的修改实现了限流功能, 控制资源消耗较多的SQL运行数量，从而防止拖慢数据库的整体运行。
案例2糟糕的结构设计带来的问题1.案例说明
这是某公司后台的ERP系统，系统已经上线运行了 10多年。随着时间的推移，累积的 数据量越来越大。随着公司业务量的不断增加，数据库系统运行缓慢的问题日益凸显。 为提高运行效率，公司计划有针对性地对局部大表进行数据清理。在DBA对某个大表 进行清理时出现了问题。这个表本身有数百吉字节，按照指定的清理规那么只需要根据主 键字段范围（运算符为＞=）选择出一定比例（不超过10% ）的数据进行清理即可。
但在实际使用中发现，该SQL是全表扫描，执行时间大大超出预期。DBA尝试使用强 制指定索引方式清理数据，依然无效，整个SQL语句的执行效率达不到要求。为了避 免影响正常业务运行，不得不将此次清理工作放在半夜进行，还需要协调库房等诸多单 位进行配合，严重影响正常业务运行。
为了尽量减少对业务的影响，DBA求助笔者帮助协同分析。这套ERP系统是由第三方 公司开发的，历史很久远，相关的数据字典等信息都已经找不到了，只能从纯数据库的 角度进行分析。这是一个普通表（非分区表），按照主键字段的范围查询一批记录并进 行清理。
按照正常理解，执行索引范围扫描应该是效率较高的一种处理方式，但实际情况都是全表扫描。进一步分析发现，该表的主键是没有业务含义的，仅仅是自增长的数据，其来
源是一个序列。
但奇怪的是，这个主键字段的类型是变长文本类型，而不是通常的数字类型。当初定义 该字段类型的依据，现在已经无从考证，但实验说明正是这个字段的类型"异常"，导 致了错误的执行路径。
下面通过一个实验重现这个问题。
1）数据准备两个表的数据类型相似（只是ID字段类型不同），各插入了 320万数据，ID字段范围 为 1〜3200000。
tl* dba_objectstlprimary
t2* dba_objectsalter table t2 add id varchar2Q0) primary
select 'test1 1 test1 ? 1 test',, rownum, rownum'test')sysdate, sysdate, 'test',1 test1, 1 1/
select 1 test1 1 test1 1 test1, rownum, rownum., "test', sysdate^ sysdate J test1connect by
execdbms_stats.gather_table_stats(ownname => 'hf"tabname => 'tl'jcascaexecdbms_stats.gather_table_stats(ownname => 'hf1^tabname => 't2',cased
2 ）模拟场景相关代码如下：
* tlj>= 3199990；

Id | Operation| Name|Rows |Bytes|Cost (%CPU)|

0 SELECT STATEMENT|| 11| 693 |4 (0) | 0
1 | TABLE ACCESS BY INDEX ROIaIID| TlI H I 693 fl 4 (0) |
00:00:01|* 2 | INDEX RANGE SCAN| SYSJZ00252941 11||3 (0) | 0

1calls
6 consistent gets ^^phvsica^readsl对于普通的采用数值类型的字段，范围查询就是正常的索引范围扫描，执行效率很高。
* from t2 where id>= '31999901;755565 rows selected.
Id

Id
Operation
Name | Rows | Bytes | Cost (%CPU)| Time
|* 1 I TABLE ACCESS FULL| T2 |2417K|149M|8927(2)| 00:01:4
tatistics
0 北
82568 consistent gets
对于文本类型字段的表，范围查询就是对应的全表扫描，效率较低是显而易见的。
3)分析结论
字符类型在索引中是“乱序”的，这是因为字符类型的排序方式与我们的预期 不同。从"select * from t2 where id >= '3199990'”执行返回 755 565 条记录可见， 不是直观上的10条记录。这也是当初在做表设计时，开发人员没有注意的问题。
字符类型还导致了聚簇因子很大，原因是插入顺序与排序顺序不同。详细点说， 就是按照数字类型插入(1.3200000 ),按字符类型(T...’32000000' ) t排序。
table_name_, index_name_, leaf_blocks_, num_rows,, clustering_f actorTABLE_NAME工NDEX_NAMELEAF_BLOCKS NUM_ROWS CLUSTERING

T1SYSJZ002529462753200000
且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式。
4）解决方法具体的解决方法如下：
select * from t2 where id between 131999901 and '32000001;

Id | Operation| Name|Rows|Bytes |Cost(%CPU

|0 | SELECT STATEMENT||6|390 |5 (0)|0
|1 | TABLE ACCESS BY INDEX ROIaIID| T2|6|390 |5 (0|* 2 | INDEX RANGE SCAN| SYSJ20025295 |6||3 (0) |

callsj
0 db block13gets
^^ThvsRa^TeadsH将SQL语句由开放区间扫描（ >=）修改为封闭区间（between xxx and max_value ）e 使得数据在索引局部顺序是"对的"。如果采用这种方式仍然走全表扫描，还可以进一 步细化分段或者采用"逐条提取+批绑定”的方法。
2.给我们的启示这是一个典型的由不好的数据类型带来的执行计划异常的例子。它给我们带来如下启示： 糟糕的数据结构设计往往是致命的，后期的优化只是补救措施。只有从源头上
加以杜绝，才是优化的根本。
在设计初期能引入数据库审核，可以起到很好的作用。