从75到6000

这篇文章的标题颇有些标题党的味道,但这不是我的初衷。

其实我想表达的是,基于RDBMS的应用系统的性能问题绝大多数都是和SQL直接相关,而基于Oracle数据库的SQL优化能否奏效、效果的好与坏归根结底比拼的还是对CBO和执行计划的理解程度,这也正是我在我的新书《基于OracleSQL优化》中提出来的SQL优化方法论的第一点——OracleSQL优化的本质是基于对CBO和执行计划的深刻理解

   

    整个事情是这样的……

快下班的时候去打卡,兄弟部门的同事拦住了我,说他们有一个测试系统,性能一直上不去,现在每分钟仅能跑75Job,想让我帮忙看一下。

简单的用TOPVMSTAT看了一下,发现在他们并发跑Job的时候,CPU占用率接近100%IDLE值接近零,看上去瓶颈像是在CPU上。

我让他们将事发时间段的AWR报告发给我,然后我就回家了。

 

第二天早上来到公司,我打开了这份AWR报告。

如下是这份AWR报告的部分内容:

DB Name

DB Id

Instance

Inst num

Startup Time

Release

RAC

GALT

3872212397

galt

1

17-Apr-13 20:04

11.2.0.1.0

NO

 

Host Name

Platform

CPUs

Cores

Sockets

Memory (GB)

riserver2

Linux x86 64-bit

4

4

1

15.50

 

 

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

277

22-Apr-13 18:00:45

40

1.8

End Snap:

278

22-Apr-13 19:00:58

91

1.9

Elapsed:

 

60.22 (mins)

 

 

DB Time:

 

1,413.75 (mins)

 

 

 

Top 5 Timed Foreground Events

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

latch: cache buffers chains

894,797

22,111

25

26.07

Concurrency

DB CPU

 

11,130

 

13.12

 

direct path write temp

53,039

1,877

35

2.21

User I/O

db file sequential read

14,467

145

10

0.17

User I/O

latch free

6,208

141

23

0.17

Other

   

从上述显示内容中我们可以看出,上述数据库在上述采样时间段是非常繁忙的,Cache Buffers Chains894797次等待,平均等待时间25毫秒,占了26.07%DB time

 

Cache Buffers Chains等待通常说明存在热点块,这可能是由于Oracle自身的Bug(例如“Bug 10396041 – ‘cache buffers chains’ latch contention [ID 10396041.8]”),当然更多的情形是由于应用的某些SQL不恰当的多读了一些本不应该它们读的数据块。所以通常情况下缓解Cache Buffers Chains的方法就是调整SQL,缩减目标SQL所需要读的数据块的数量,这样Cache Buffers Chains等待的数量自然就下去了。

   

从上述AWR报告中SQL ordered by Gets部分我们可以很轻易的定位出待调整的目标SQLSQL ID为“73qyztauub2a1和“74hnt3vjjm85a”的这两条SQL

clip_image002

 

SQL ID为“73qyztauub2a1SQL文本为如下所示:

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.en_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 ID为“73qyztauub2a1的目标SQL的资源消耗和执行计划为如下所示:

Stat Name

Statement Total

Per Execution

% Snap Total

Elapsed Time (ms)

80,758,772

1,009,484.65

95.21

CPU Time (ms)

10,583,843

132,298.04

95.09

Executions

80

 

 

Buffer Gets

2,032,606,807

25,407,585.09

93.88

Disk Reads

419,668

5,245.85

61.45

Parse Calls

80

1.00

1.01

Rows

8

0.10

 

User I/O Wait Time (ms)

1,729,574

 

 

Cluster Wait Time (ms)

0

 

 

Application Wait Time (ms)

0

 

 

Concurrency Wait Time (ms)

0

 

 

Invalidations

0

 

 

Version Count

15

 

 

Sharable Mem(KB)

482

 

 

clip_image004

    从上述显示内容中我们可以看出,SQL ID为“73qyztauub2a1的目标SQL在采样时间段每次执行平均只返回0.1行记录,但平均每次执行需要耗时1009秒,平均每次执行所消耗的逻辑读为25407585

从该SQL的执行计划我们可以看出,CBO评估出来执行该SQL所对应的成本值为2,这是一个明显异常的值,假如成本值真的像CBO评估的那样为2的话,那么该SQL就不可能平均每次执行需要消耗超过2500万的逻辑读。

 

很明显,这里很可能是上述SQL所涉及到的对象的统计信息出现了问题,我们现在来检查一下:

ANA_NAME的统计信息为如下所示:

clip_image006

    从上述显示内容中我们可以看出,表ANA_NAME的相关统计信息值全部为0,但实际上表ANA_NAME是一个有300多万数据量的大表:

SQL> select count(*) from ana_name;

 

  COUNT(*)

———-

   3180474

   

所以很明显这里是由于表ANA_NAME的统计信息严重不准而导致CBO选错了执行计划。分析清楚了问题的原因,解决起来就很简单了,我们只需要重新对上述SQL所涉及到的表用如下命令重新收集一下统计信息就好了:

SQL> exec dbms_stats.gather_table_stats(ownname => ‘GALT’,tabname => ‘ANA_NAME’,cascade => true,no_invalidate => false);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(ownname => ‘GALT’,tabname => ‘ANA_SEGMENT’,cascade => true,no_invalidate => false);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(ownname => ‘GALT’,tabname => ‘ANA_SEG_CLASS’,cascade => true,no_invalidate => false);

 

PL/SQL procedure successfully completed

 

重新收集完统计信息后表ANA_NAME的相关统计信息已经有了值:

clip_image008

 

同时,上述SQL ID为“73qyztauub2a1的目标SQL的执行计划也已经恢复了正常:

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.en_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> col plan_table_output for a200

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

clip_image010

   

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

ANA_NAME的那300多万的数据早就存在了,并不是今天早上才导入的,为什么经过了昨晚的自动收集统计信息作业,其各项统计信息的值还是为0

 

是不是因为这个库的自动统计信息收集作业被禁掉了?

我们来检查一下:

SQL> select client_name,status from dba_autotask_task;

 

CLIENT_NAME                                                      STATUS

—————————————————————- ——–

sql tuning advisor                                               ENABLED

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

 

SQL> select window_name,autotask_status from dba_autotask_window_clients;

 

WINDOW_NAME                    AUTOTASK_STATUS

—————————— —————

MONDAY_WINDOW                  ENABLED

TUESDAY_WINDOW                 ENABLED

WEDNESDAY_WINDOW               ENABLED

THURSDAY_WINDOW                ENABLED

FRIDAY_WINDOW                  ENABLED

SATURDAY_WINDOW                ENABLED

SUNDAY_WINDOW                  ENABLED

 

7 rows selected

 

SQL> select window_name,repeat_interval from dba_scheduler_windows where enabled=’TRUE’;

 

WINDOW_NAME       REPEAT_INTERVAL                                      

—————-  ——————————————————

MONDAY_WINDOW     freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0

TUESDAY_WINDOW    freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0

WEDNESDAY_WINDOW  freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0

THURSDAY_WINDOW   freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0

FRIDAY_WINDOW     freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0

SATURDAY_WINDOW   freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 

SUNDAY_WINDOW     freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 

                

7 rows selected 

 

SQL> select window_name, job_name, job_start_time from (select * from dba_autotask_job_history where client_name=’auto optimizer stats collection’ order by window_start_time desc) where rownum<4;

 

WINDOW_NAME      JOB_NAME               JOB_START_TIME                     

—————  ———————  ————————————

TUESDAY_WINDOW  ORA$AT_OS_OPT_SY_1  09-4 -13 10.00.00.825901 下午 PRC

 

SQL> select window_name,window_next_time from dba_autotask_window_clients;                    

 

WINDOW_NAME                    WINDOW_NEXT_TIME                    

—————————— ————————————-

MONDAY_WINDOW                  15-4 -13 10.00.00.000000 下午 PRC 

TUESDAY_WINDOW                 16-4 -13 10.00.00.000000 下午 PRC 

WEDNESDAY_WINDOW               10-4 -13 10.00.00.000000 下午 PRC 

THURSDAY_WINDOW                11-4 -13 10.00.00.000000 下午 PRC 

FRIDAY_WINDOW                  12-4 -13 10.00.00.000000 下午 PRC 

SATURDAY_WINDOW                13-4 -13 06.00.00.000000 上午 PRC 

SUNDAY_WINDOW                  14-4 -13 06.00.00.000000 上午 PRC 

 

7 rows selected

 

    从上述显示内容中我们可以看出,上述库的自动统计信息收集作业并没有被禁掉,只是出于某种原因,自49晚上10点最后一次运行完后就再没有被执行过。

Oracle 11g里的自动统计信息收集作业每次运行时会先生成名为ORA$AT_OS_OPT_XXXJob,然后再执行这个Job。所以有一种可能就是虽然自动统计信息收集作业没有被禁掉,但Oracle在生成Job的时候碰到了问题,比如参数job_queue_processes的值被设成了0

 

从如下查询结果中我们可以看到,上述库的job_queue_processes的值果然是0

SQL> show parameter job;

 

NAME                    TYPE        VALUE

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

job_queue_processes      integer          0

 

从上述库的alert log里我们看到了如下内容:

Wed Apr 10 14:09:50 2013

ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;

Wed Apr 10 14:10:26 2013

ALTER SYSTEM SET aq_tm_processes=0 SCOPE=BOTH;

ALTER DATABASE OPEN

Wed Apr 10 14:10:35 2013

 

    这说明上述库的参数job_queue_processesaq_tm_processes410下午2点多的时候被修改成了0难怪上述库的自动统计信息收集作业自49晚上10点最后一次运行完后就再没有被成功执行过。

 

我们将上述两个参数的值给改回来:

SQL> alter system set job_queue_processes = 1000 scope=both;

 

System altered

 

SQL> alter system set aq_tm_processes = 1 scope=both;

 

System altered

 

SQL> show parameter job_queue_processes;

 

NAME                    TYPE        VALUE

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

job_queue_processes      integer        1000

 

SQL> show paramete aq_tm_processes;

 

NAME                    TYPE        VALUE

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

aq_tm_processes         integer           1

 

从如下查询结果中我们可以看到,参数job_queue_processes恢复成大于0的值后,上述库的自动统计信息收集作业的各个窗口所对应的WINDOW_NEXT_TIME的值也随之恢复了正常:

SQL> select window_name,window_next_time from dba_autotask_window_clients;                    

 

WINDOW_NAME                    WINDOW_NEXT_TIME                   

—————————— ————————————

MONDAY_WINDOW                  29-4 -13 10.00.00.000000 下午 PRC

TUESDAY_WINDOW                 23-4 -13 10.00.00.000000 下午 PRC

WEDNESDAY_WINDOW               24-4 -13 10.00.00.000000 下午 PRC

THURSDAY_WINDOW                25-4 -13 10.00.00.000000 下午 PRC

FRIDAY_WINDOW                  26-4 -13 10.00.00.000000 下午 PRC

SATURDAY_WINDOW                27-4 -13 06.00.00.000000 上午 PRC

SUNDAY_WINDOW                  28-4 -13 06.00.00.000000 上午 PRC

 

7 rows selected

 

接下来,我们对上述SQL所涉及到的schema重新收集了一下统计信息(以避免再次出现有目标表的统计信息严重不准的情形):

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

 

PL/SQL procedure successfully completed

   

此时第一阶段的调整就结束了。

根据同事的反馈,现在每分钟能跑的Job的数量已经从之前的75上升到了现在的2000,但是这个还不能满足他们的要求,他们希望能在此基础上再提升23倍。

   

刚才我们只调整了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 Value2064935335105883452所对应的执行计划),这两个执行计划所对应的资源消耗情况和执行计划的具体内容为如下所示:

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_SEGMENTANA_NAME做了不必要的全表扫描。

 

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

 

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

clip_image012

   

所以这里我们只需要在列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);

clip_image014

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

 

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

根据同事的反馈,当我们在列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走的理想执行计划;剩下的1183Oracle选择的是走哈希连接,分别对表ANA_SEGMENTANA_SEG_CLASSANA_NAME做了全表扫描,这样单次的平均执行时间一下子递增为5.012秒,所以相当于是执行时间更长、更慢了,于是并发执行的Job数量就降了下来。

 

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

 

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

clip_image016

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

 

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

Oracle 10g及其后续的版本中,Oracle会自动收集直方图统计信息,这意味着和之前的版本相比,在Oracle 10g及其后续的版本中Oracle有更大的概率会知道目标列实际数据的分布情况,这意味着绑定变量窥探的副作用将会更加明显。例如,如果表T1的列COL1没有直方图统计信息,同时在列COL1上又存在一个单键值的B树索引IDX_T1,则不管列COL1的实际数据分布情况是否均衡,Oracle在执行目标SQLselect count(*) from t1 where col1 = 😡”时都会走固定的执行计划(很可能是走对索引IDX_T1的索引范围扫描),这个时候绑定变量窥探实际上是没有副作用的;但假如列COL1上有了直方图统计信息,同时列COL1的实际数据分布又是极度不均衡的,那么这个时候当Oracle执行绑定变量窥探操作时绑定变量x所对应的输入值是否具有代表性就至关重要了(这里“代表性”的含义是指该输入值所对应的执行计划和该SQL在大多数情况下的执行计划相同),因为这会直接决定上述SQL在硬解析时所选择的执行计划,进而会决定后续以软解析/软软解析重复执行时所沿用的执行计划。为什么会发生这样的变化是因为一旦列COL1上有了直方图统计信息,Oracle就知道了列COL1的实际数据分布情况,这样在列COL1的实际数据分布又是极度不均衡的前提条件下,Oracle就能随着上述SQL中对列COL1的输入值的不同而调整上述SQL的执行计划。这种现象的本质是因为当列COL1没有直方图统计信息时,CBO在计算上述谓词条件的可选择率时会根据列COL1distinct值的数量来计算,这意味着这种情况下该谓词条件的可选择率是固定的;而一旦列COL1上有了直方图统计信息,CBO就会根据列COL1的直方图统计信息来计算上述谓词条件的可选择率,这就决定了该谓词条件的可选择率是不固定的,并且可能会随着对列COL1的输入值的变化而变化,而这正好给了绑定变量窥探发挥其副作用的机会!

 

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

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

 

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

 

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

 

无论是用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(包括统计信息、自动收集统计信息作业、直方图统计信息、绑定变量窥探、自适应游标共享等知识点)和执行计划的深刻理解。


CBO对于Cost值相同的索引的选择

这里我们稍微讨论一下CBO对于Cost值相同的索引的选择,可能会有朋友认为在同样Cost的情况下,Oracle会按照索引名的字母顺序来选择索引,实际上并不完全是这样,CBO对于Cost值相同的索引的选择和Oracle的版本有关

   

MOS上文章“Handling of equally ranked (RBO) or costed (CBO) indexes [ID 73167.1]”明确指出——When the CBO detects 2 indexes that cost the same, it makes the decision based on the following:

Ÿ     (up to release 9.2.06) indexes ascii name so that index ‘AAA’ would be chosen over index  ‘ZZZ’. See Bug 644757

Ÿ     (starting with 9.2.0.7 and in 10gR1) bigger NDK for fully matched indexes (not for fast full scans). See Bug 2720661

Ÿ     (in 10gR2 and above) index with lower number of leaf blocks. See Bug 6734618

   

这意味着对于Oracle 10gR2及其以上的版本,CBO对于Cost值相同的索引的选择实际上会这样:

1、    如果Cost值相同的索引的叶子块数量不同,则Oracle会选择叶子块数量较少的那个索引;

2、    如果Cost值相同的索引的叶子块数量相同,则Oracle会选择索引名的字母顺序在前面的那个索引。

 

这个非常容易验证,我们来看一个实例:

在一个11.2.0.3的环境中创建一个测试表T1

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Connected as nbs

 

SQL> create table t1 as select * from dba_objects;

 

Table created

 

T1增加一列object_id_1,并将其值修改成和列object_id的值一致:

SQL> alter table t1 add (object_id_1 number);

 

Table altered

 

SQL> update t1 set object_id_1=object_id;

 

83293 rows updated

 

SQL> commit;

 

Commit complete

 

分别在列object_id和列object_id_1上创建名为a_idx_t1b_idx_t1B树索引:

SQL> create index a_idx_t1 on t1(object_id);

 

Index created

 

SQL> create index b_idx_t1 on t1(object_id_1);

 

Index created

 

对表T1收集一下统计信息:

SQL> exec dbms_stats.gather_table_stats(ownname => ‘NBS’, tabname => ‘T1’, estimate_percent => 100, cascade => TRUE, no_invalidate => false);

 

PL/SQL procedure successfully completed

 

此时索引a_idx_t1b_idx_t1的统计信息显然是完全一致的(这意味着走这两个索引的同类型执行计划的Cost值会相同),从如下查询结果中我们可以看到,它们的叶子块的数量均为185

SQL> select index_name,leaf_blocks from dba_indexes where table_owner=’NBS’ and table_name=’T1′;

 

INDEX_NAME                     LEAF_BLOCKS

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

A_IDX_T1                               185

B_IDX_T1                               185

 

在当前情形下,如果我们执行目标SQLselect * from t1 where object_id=1000 and object_id_1=1000,显然此时Oracle既可以走索引a_idx_t1也可以走索引b_idx_t1

 

从如下查询结果中我们可以看到,此时Oracle选择了走索引a_idx_t1

SQL> set autotrace traceonly explain

SQL> select * from t1 where object_id=1000 and object_id_1=1000;

 

执行计划

———————————————————-

Plan hash value: 3036955902

 

—————————————————————————————-

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

—————————————————————————————-

|   0 | SELECT STATEMENT            |          |     1 |   103 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |   103 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | A_IDX_T1 |     1 |       |     1   (0)| 00:00:01 |

—————————————————————————————-

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter(“OBJECT_ID_1″=1000)

   2 – access(“OBJECT_ID”=1000)

    这就验证了我们之前提到的结论——对于Oracle 10gR2及其以上的版本,如果Cost值相同的索引的叶子块数量相同,则Oracle会选择索引名的字母顺序在前面的那个索引。

   

现在我们把索引b_idx_t1的叶子块数量从之前的185改为现在的184

SQL> exec dbms_stats.set_index_stats(ownname => ‘NBS’, indname => ‘B_IDX_T1’, numlblks => 184);

 

PL/SQL procedure successfully completed

 

从如下查询结果中我们可以看到,上述改动生效了:

SQL> select index_name,leaf_blocks from dba_indexes where table_owner=’NBS’ and table_name=’T1′;

 

INDEX_NAME                     LEAF_BLOCKS

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

A_IDX_T1                               185

B_IDX_T1                               184

 

然后我们再次执行上述目标SQL

SQL> select * from t1 where object_id=1000 and object_id_1=1000;

 

执行计划

———————————————————-

Plan hash value: 606792237

 

—————————————————————————————-

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

—————————————————————————————-

|   0 | SELECT STATEMENT            |          |     1 |   103 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |   103 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | B_IDX_T1 |     1 |       |     1   (0)| 00:00:01 |

—————————————————————————————-

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter(“OBJECT_ID”=1000)

   2 – access(“OBJECT_ID_1″=1000)

        从上述显示内容中我们可以看到,上述SQL的执行计划从之前的走对索引a_idx_t1的索引范围扫描变为了现在的走对索引b_idx_t1的索引范围扫描,这就验证了我们之前提到的结论——对于Oracle 10gR2及其以上的版本,如果Cost值相同的索引的叶子块数量不同,则Oracle会选择叶子块数量较少的那个索引。

 

    至于RBO对于Cost值相同的索引的选择,我在我新书《基于OracleSQL优化》的第一章 Oracle里的优化器中会详细描述,这里不再赘述。