极限优化:从75到2000,由技能到性能提升岂止80倍

崔华,网名 dbsnake

Oracle ACE Director,ACOUG 核心专家

编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracle”社区投稿。

崔华手记:这篇文章的标题颇有些标题党的味道,但这不是我的初衷。其实我想表达的是,基于RDBMS的应用系统的性能问题绝大多数都是和SQL直接相关,而基于Oracle数据库的SQL优化能否奏效、效果的好与坏归根结底比拼的还是对CBO和执行计划的理解程度,这也正是我在《基于Oracle的SQL优化》一书中提出来的SQL优化方法论的第一点——Oracle里SQL优化的本质是基于对CBO和执行计划的深刻理解。

刚才我们只调整了SQL ID为“73qyztauub2a1”的目标SQL,现在我们再来调整剩下的那个SQL ID为“74hnt3vjjm85a”的目标SQL。

SQL ID为“74hnt3vjjm85a”的SQL文本为如下所示:

SELECT s.LOCATOR_ID, s.segment_ID, n.en_name

FROM ANA_SEG_CLASS S, ana_segment a, ana_name n

where A.SEGMENT_ID = S.SEGMENT_ID

and s.locator_id = n.locator_id

and n.cn_name = :1

and ((a.orgn_city = :2 and a.start_time > :3 and a.start_time < :4) or

(a.dstn_city = :5 and a.end_time > :6 and a.end_time < :7))

and n.person_id_count > 0

从对应采样时间段的AWR SQL Report中我们可以看出,上述SQL对应有三个Child Cursor:

#

Plan Hash Value

Total Elapsed Time(ms)

Executions

1st Capture Snap ID

Last Capture Snap ID

1

2064935335

2,663,311

1,368

295

295

2

105883452

327,086

506

295

295

3

1495819153

0

0

295

295

这三个Child Cursor,真正被Oracle在采样时间段使用的有两个执行计划(分别是Plan Hash Value为2064935335和105883452所对应的执行计划),这两个执行计划所对应的资源消耗情况和执行计划的具体内容为如下所示:

Plan 1(PHV: 2064935335)

Stat Name

Statement Total

Per Execution

% Snap Total

Elapsed Time (ms)

2,663,311

1,946.86

79.43

CPU Time (ms)

612,884

448.01

68.63

Executions

1,368

Buffer Gets

168,610,877

123,253.57

88.24

Disk Reads

10,557

7.72

3.88

Parse Calls

1,369

1.00

0.81

Rows

1,256

0.92

User I/O Wait Time (ms)

7,637

Cluster Wait Time (ms)

0

Application Wait Time (ms)

0

Concurrency Wait Time (ms)

0

Invalidations

0

Version Count

10

Sharable Mem(KB)

38

Execution Plan

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

0

SELECT STATEMENT

6721 (100)

1

NESTED LOOPS

2

NESTED LOOPS

20

2020

6721 (1)

3

NESTED LOOPS

1179

83709

3247 (1)

4

TABLE ACCESS FULL

ANA_SEGMENT

180

7920

2116 (1)

5

TABLE ACCESS BY INDEX ROWID

ANA_SEG_CLASS

7

189

8 (0)

6

INDEX RANGE SCAN

IDX_ANA_SEG_CLASS

7

2 (0)

7

INDEX RANGE SCAN

IDX_ANA_NAME_LOCATOR_ID

2

2 (0)

8

TABLE ACCESS BY INDEX ROWID

ANA_NAME

1

30

3 (0)

Plan 2(PHV: 105883452)

Stat Name

Statement Total

Per Execution

% Snap Total

Elapsed Time (ms)

327,086

646.42

9.75

CPU Time (ms)

83,901

165.81

9.39

Executions

506

Buffer Gets

9,477,916

18,731.06

4.96

Disk Reads

2,399

4.74

0.88

Parse Calls

505

1.00

0.30

Rows

667

1.32

User I/O Wait Time (ms)

1,623

Cluster Wait Time (ms)

0

Application Wait Time (ms)

0

Concurrency Wait Time (ms)

0

Invalidations

0

Version Count

10

Sharable Mem(KB)

38

Execution Plan

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

0

SELECT STATEMENT

5063 (100)

1

NESTED LOOPS

2

NESTED LOOPS

1

101

5063 (1)

3

NESTED LOOPS

2

114

5059 (1)

4

TABLE ACCESS FULL

ANA_NAME

2

60

5051 (1)

5

TABLE ACCESS BY INDEX ROWID

ANA_SEG_CLASS

1

27

4 (0)

6

INDEX RANGE SCAN

IDX_ANA_SEG_CLASS_LOCATOR_ID

1

2 (0)

7

INDEX UNIQUE SCAN

PK_ANA_SEGMENT

1

1 (0)

8

TABLE ACCESS BY INDEX ROWID

ANA_SEGMENT

1

44

2 (0)

显然,上述SQL的这两个执行计划均不是最优解,它们分别对表ANA_SEGMENT和ANA_NAME做了不必要的全表扫描。

注意到上述SQL和之前我们处理的第一条SQL在SQL文本上的区别仅仅是SQL文本中的“and n.en_name = :1”和“and n.cn_name = :1”的不同,其它部分都是一样的。

从表ANA_NAME的统计信息显示结果中我们可以看到,列EN_NAME和CN_NAME的可选择性都非常好,在列EN_NAME上存在一个名为IDX_ANA_NAME的单键值B树索引,但在列CN_NAME上并不存在任何索引:

所以这里我们只需要在列CN_NAME上创建一个单键值的B树索引,就可以让该SQL走出和之前第一个SQL那样一模一样的执行计划:

SQL> create index idx_ana_cname on ana_name(cn_name);

Index created

SQL> explain plan for SELECT s.LOCATOR_ID, s.segment_ID, n.en_name FROM ANA_SEG_CLASS S, ana_segment a , ana_name n where A.SEGMENT_ID = S.SEGMENT_ID and s.locator_id = n.locator_id and n.cn_name = :1 and ( ( a.orgn_city = :2 and a.start_time > :3 and a.start_time < :4 ) or ( a.dstn_city=:5 and a.end_time> :6 and a.end_time < :7 ) ) and n.person_id_count > 0;

Explained

SQL> select * from table(dbms_xplan.display);

从上述显示内容中我们可以看到,我们的目的已经实现了。

是不是这就完事了?——事情没有这么简单。

根据同事的反馈,当我们在列CN_NAME上创建一个单键值的B树索引IDX_ANA_CNAME后,现在每分钟能跑的Job的数量反而从之前的2000下降到了现在的800。

为什么会这样?

此时的AWR SQL Report显示,Oracle此时同时启用了两个执行计划,这两个执行计划所对应的资源消耗情况和执行计划的具体内容为如下所示:

#

Plan Hash Value

Total Elapsed Time(ms)

Executions

1st Capture Snap ID

Last Capture Snap ID

1

2268138873

5,929,600

1,183

297

297

2

2110666080

1,886

25

297

297

3

2064935335

0

0

297

297

4

1495819153

0

0

297

297

Plan 1(PHV: 2268138873)

Stat Name

Statement Total

Per Execution

% Snap Total

Elapsed Time (ms)

5,929,600

5,012.34

64.55

CPU Time (ms)

837,076

707.59

61.62

Executions

1,183

Buffer Gets

55,477,410

46,895.53

53.63

Disk Reads

0

0.00

0.00

Parse Calls

1,192

1.01

2.02

Rows

369

0.31

User I/O Wait Time (ms)

1

Cluster Wait Time (ms)

0

Application Wait Time (ms)

0

Concurrency Wait Time (ms)

0

Invalidations

0

Version Count

155

Sharable Mem(KB)

34

Execution Plan

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0

SELECT STATEMENT

12726 (100)

1

HASH JOIN

156

15756

12726 (1)

00:02:33

2

HASH JOIN

9270

642K

7674 (1)

00:01:33

3

TABLE ACCESS FULL

ANA_SEGMENT

1413

62172

2116 (1)

00:00:26

4

TABLE ACCESS FULL

ANA_SEG_CLASS

2710K

69M

5549 (1)

00:01:07

5

TABLE ACCESS FULL

ANA_NAME

34841

1020K

5051 (1)

00:01:01

Plan 2(PHV: 2110666080)

Stat Name

Statement Total

Per Execution

% Snap Total

Elapsed Time (ms)

1,886

75.43

0.02

CPU Time (ms)

374

14.96

0.03

Executions

25

Buffer Gets

64,535

2,581.40

0.06

Disk Reads

124

4.96

0.10

Parse Calls

16

0.64

0.03

Rows

12

0.48

User I/O Wait Time (ms)

15

Cluster Wait Time (ms)

0

Application Wait Time (ms)

0

Concurrency Wait Time (ms)

0

Invalidations

0

Version Count

155

Sharable Mem(KB)

1,300

Execution Plan

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

0

SELECT STATEMENT

15 (100)

1

NESTED LOOPS

2

NESTED LOOPS

1

101

15 (0)

3

NESTED LOOPS

2

114

11 (0)

4

TABLE ACCESS BY INDEX ROWID

ANA_NAME

2

60

5 (0)

5

INDEX RANGE SCAN

IDX_ANA_CNAME

2

3 (0)

6

TABLE ACCESS BY INDEX ROWID

ANA_SEG_CLASS

1

27

3 (0)

7

INDEX RANGE SCAN

IDX_ANA_SEG_CLASS_LOCATOR_ID

1

2 (0)

8

INDEX UNIQUE SCAN

PK_ANA_SEGMENT

1

1 (0)

9

TABLE ACCESS BY INDEX ROWID

ANA_SEGMENT

1

44

2 (0)

这里为什么每分钟能跑的Job的数量反而下降的原因是显而易见的——因为对于上述SQL而言,Oracle此时一共执行了1208次,其中只有25次走的是我们想让Oracle走的理想执行计划;剩下的1183次Oracle选择的是走哈希连接,分别对表ANA_SEGMENT、ANA_SEG_CLASS和ANA_NAME做了全表扫描,这样单次的平均执行时间一下子递增为5.012秒,所以相当于是执行时间更长、更慢了,于是并发执行的Job数量就降了下来。

现在的关键问题是——上述SQL明明使用了绑定变量且绑定变量窥探在默认情况下已经被开启,那为什么这里Oracle还会同时启用两个执行计划?

我们再来看一下表ANA_NAME的统计信息:

注意到此时列CN_NAME是有直方图统计信息的(显然是Height Balanced类型的直方图),再结合这个数据库的版本(Oracle 11.2.0.1),我们之前的疑惑就有了答案。

在Oracle 11g之前,绑定变量窥探的副作用就在于使用了绑定变量的目标SQL就只会沿用之前硬解析时所产生的解析树和执行计划,即使这种沿用完全不适合于当前的情形。

在Oracle 10g及其后续的版本中,Oracle会自动收集直方图统计信息,这意味着和之前的版本相比,在Oracle 10g及其后续的版本中Oracle有更大的概率会知道目标列实际数据的分布情况,这意味着绑定变量窥探的副作用将会更加明显。

例如:如果表T1的列COL1没有直方图统计信息,

  1. 同时在列COL1上又存在一个单键值的B树索引IDX_T1,
  2. 则不管列COL1的实际数据分布情况是否均衡,
  3. Oracle在执行目标SQL“select count(*) from t1 where col1 = ”时都会走固定的执行计划(很可能是走对索引IDX_T1的索引范围扫描),这个时候绑定变量窥探实际上是没有副作用的;

但假如列COL1上有了直方图统计信息,

  1. 同时列COL1的实际数据分布又是极度不均衡的,
  2. 那么这个时候当Oracle执行绑定变量窥探操作时绑定变量x所对应的输入值是否具有代表性就至关重要了(这里“代表性”的含义是指该输入值所对应的执行计划和该SQL在大多数情况下的执行计划相同);
  3. 因为这会直接决定上述SQL在硬解析时所选择的执行计划,进而会决定后续以软解析/软软解析重复执行时所沿用的执行计划。

为什么会发生这样的变化?

是因为一旦列COL1上有了直方图统计信息,Oracle就知道了列COL1的实际数据分布情况,这样在列COL1的实际数据分布又是极度不均衡的前提条件下,Oracle就能随着上述SQL中对列COL1的输入值的不同而调整上述SQL的执行计划。

这种现象的本质是因为当列COL1没有直方图统计信息时,CBO在计算上述谓词条件的可选择率时会根据列COL1的distinct值的数量来计算,这意味着这种情况下该谓词条件的可选择率是固定的;而一旦列COL1上有了直方图统计信息,CBO就会根据列COL1的直方图统计信息来计算上述谓词条件的可选择率,这就决定了该谓词条件的可选择率是不固定的,并且可能会随着对列COL1的输入值的变化而变化,而这正好给了绑定变量窥探发挥其副作用的机会!

为了解决上述绑定变量窥探的副作用,Oracle在11g中引入了自适应游标共享(Adaptive Cursor Sharing)。

自适应游标共享可以让使用了绑定变量的目标SQL在启用了绑定变量窥探的前提条件下不再只沿用之前硬解析时所产生的解析树和执行计划,也就是说自适应游标共享可以在启用了绑定变量窥探的前提条件下让目标SQL在其可能的多个执行计划之间“自适应”的做出选择,而不再像之前那样必须得僵化的沿用该SQL硬解析时所产生的解析树和执行计划。

所以这里为什么Oracle在执行上述含绑定变量且开启了绑定变量窥探的目标SQL时依然会同时启用两个执行计划的原因就是因为自适应游标共享的副作用,而这里自适应游标之所以会被Oracle启用的本质原因又是因为上述SQL的等值查询条件“and n.cn_name = :1”中的列cn_name上有直方图统计信息(关于绑定变量窥探、自适应游标共享和直方图统计信息对可选择率的影响,可以参考《基于Oracle的SQL优化》中详细描述)。

分析清楚了根本原因,解决方法就非常简单了,我们来只需要删除直方图统计信息,不给自适应游标共享发挥副作用的机会就好了

无论是用Oracle自带的自动统计信息收集作业还是用我们自己写的shell脚本来收集统计信息,对于直方图统计信息的收集而言,我们都建议采用如下的方式:只对已经存在直方图统计信息的列重复收集直方图统计信息,而目标列的初次直方图统计信息的收集则是由了解系统的相关DBA手工来做。具体来说就是这样:

1、 设置METHOD_OPT的值为‘FOR ALL COLUMNS SIZE 1’后先收集一次统计信息,这意味着删除了所有列上的直方图;

2、 在已经删除了所有列上的直方图后,设置METHOD_OPT的值为‘FOR ALL COLUMNS SIZE REPEAT’,这意味着今后将只对已经存在直方图统计信息的列重复收集直方图统计信息;

3、 在系统使用或调优的过程中,目标列的初次直方图统计信息的收集是由了解系统的相关DBA手工来做。

按照上述原则,我们重新来处理一下上述对象所涉及到的直方图统计信息:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => ‘GALT’,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE => TRUE,METHOD_OPT => ‘FOR ALL COLUMNS SIZE 1’,no_invalidate => false);

PL/SQL procedure successfully completed

SQL> select dbms_stats.get_prefs(pname => ‘METHOD_OPT’) from dual;

DBMS_STATS.GET_PREFS(PNAME=>’M

——————————————————————————–

FOR ALL COLUMNS SIZE AUTO

SQL> exec dbms_stats.set_global_prefs(‘METHOD_OPT’,’FOR ALL COLUMNS SIZE REPEAT’);

PL/SQL procedure successfully completed

SQL> select dbms_stats.get_prefs(pname => ‘METHOD_OPT’) from dual;

DBMS_STATS.GET_PREFS(PNAME=>’M

——————————————————————————–

FOR ALL COLUMNS SIZE REPEAT

至此,我们就完成了第二阶段的调整。

这个做完没多久,就收到了同事的邮件:

崔老师,这次能每分钟完成近6000个任务了,基本上满足了我们业务的基本需求,太感激了。。。

一会您回来传授下经验!

太感谢了!

从随后产生的AWR SQL Report中我们可以看到,当我们删除直方图统计信息后,在随后的执行过程中,对于上述SQL而言,Oracle确实只启用了一个执行计划,且启用的执行计划就是我们想要的:

#

Plan Hash Value

Total Elapsed Time(ms)

Executions

1st Capture Snap ID

Last Capture Snap ID

1

2110666080

757,107

10,169

298

298

2

2064935335

0

0

298

298

3

1495819153

0

0

298

298

Plan 1(PHV: 2110666080)

Stat Name

Statement Total

Per Execution

% Snap Total

Elapsed Time (ms)

757,107

74.45

41.72

CPU Time (ms)

220,902

21.72

28.79

Executions

10,169

Buffer Gets

31,392,043

3,087.03

41.77

Disk Reads

4,166

0.41

1.38

Parse Calls

10,169

1.00

2.05

Rows

5,733

0.56

User I/O Wait Time (ms)

109

Cluster Wait Time (ms)

0

Application Wait Time (ms)

0

Concurrency Wait Time (ms)

0

Invalidations

0

Version Count

155

Sharable Mem(KB)

2,597

Execution Plan

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

0

SELECT STATEMENT

15 (100)

1

NESTED LOOPS

2

NESTED LOOPS

1

101

15 (0)

3

NESTED LOOPS

2

114

11 (0)

4

TABLE ACCESS BY INDEX ROWID

ANA_NAME

2

60

5 (0)

5

INDEX RANGE SCAN

IDX_ANA_CNAME

2

3 (0)

6

TABLE ACCESS BY INDEX ROWID

ANA_SEG_CLASS

1

27

3 (0)

7

INDEX RANGE SCAN

IDX_ANA_SEG_CLASS_LOCATOR_ID

1

2 (0)

8

INDEX UNIQUE SCAN

PK_ANA_SEGMENT

1

1 (0)

9

TABLE ACCESS BY INDEX ROWID

ANA_SEGMENT

1

44

2 (0)

并且此时整个数据库的Cache Buffers Chains的等待次数已经从最开始的894797次降到了现在的11488次:

Top 5 Timed Foreground Events

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

DB CPU

767

42.29

latch: cache buffers chains

11,488

366

32

20.19

Concurrency

db file scattered read

23,949

7

0

0.37

User I/O

db file sequential read

66,466

7

0

0.36

User I/O

log file sync

328

2

7

0.13

Commit

至此,上述测试系统的性能问题就被我们成功解决了。

总结:从整个解决过程我们可以看出,虽然最后用的解决方法很简单,但整个解决过程实际上倚赖的还是我们对CBO(包括统计信息、自动收集统计信息作业、直方图统计信息、绑定变量窥探、自适应游标共享等知识点)和执行计划的深刻理解。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-05-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏wannshan(javaer,RPC)

dubbo集群容错机制代码分析1

dubbo版本2.5.3 我们这里以zookeeper作为注册中心为例说明。 这里说的集群,可以理解为,一个接口服务对应有多个提供者。 在dubbo的调用方(...

3625
来自专栏后端之路

dubbo缓存代码分析

dubbo是Ali出品的soa框架,属于互联网企业常见的rpc选择框架。 前几篇分析了多级缓存的相关代码,本篇就dubbo的缓存进行梳理。 dubbo的缓存针...

2217
来自专栏杨建荣的学习笔记

关于interval partitioning(r3笔记65天)

今天一个开发的同事问我一个问题,说在系统中目前需要一个临时的解决方案,定义了一张表,目前想设定为分区表,因为会和外围系统交互产生大量的数据,所以需要按照小时来做...

2548
来自专栏杨建荣的学习笔记

执行计划变化导致CPU负载高的问题分析 (r8笔记第20天)

前几天碰到一个CPU负载较高的问题。从系统层面来看,情况不是很严重,但是从应用的角度来说,已经感觉到很慢了。因为前端的调用频率还是比较高。所以会把这个问题放大。...

2497
来自专栏进击的程序猿

orm 系列 之 常用设计模式 The Repository Pattern

本文是orm系列的第一篇,内容来自github上的一个Markdown,清晰的讲述了一些数据库设计上常用的设计模式,并且阐述了orm是什么?

623
来自专栏杨建荣的学习笔记

探究AWR 第二篇(r3笔记第93天)

在探究awr第一篇中介绍了awr的一些基本操作 http://blog.itpub.net/23718752/viewspace-1123134/ 在这一篇中,...

2807
来自专栏杨建荣的学习笔记

一条执行4秒的sql语句导致的系统问题(r3笔记第10天)

一般来说一条sql语句执行个4秒钟是可以接受的,没有什么问题,但是如果应该执行1秒,却执行了4秒,问题就挺大的了。 今天查看数据库负载,发现在中午12:00 ...

3478
来自专栏张善友的专栏

IBatisNet 之 自动生成主关键字

很多系统支持自动生成主关键字。一些数据库厂商预先生成(oracle),一些数据库厂商之后生成(mssal mysql).。如果你在<insert>元素中使用<s...

1718
来自专栏杨建荣的学习笔记

alert日志中的一条ora警告信息的分析(59天)

今天照例检查数据库alert日志,发现一个错误。但是也没在意,想可能有大的操作导致的,马上会释放空间的,但是转眼一想,这是生产库,而且现在时早上,泰国的运营商还...

2465
来自专栏沃趣科技

其他混杂视图 | 全方位认识 sys 系统库

在《语句效率统计视图|全方位认识 sys 系统库》中,为大家介绍了利用sys 系统库查询语句执行效率的快捷视图,本期将为大家介绍一些不便归类的混杂视图,本篇也是...

752

扫描关注云+社区