SGA区大小建议

关于SGA、PGA与系统内存三者间的关联,目前有一个相对通用的计算规则可供参考:

对于OLTP数据库,SGA=系统内存*70%*80%,PGA=SGA*(10%~20%)。SGA=系统内存*0.56 PGA=系统内存*(0.05~0.1

对于OLAP数据库,SGA=系统内存*80%*60%,PGA=SGA*(45%~65%)。SGA=系统内存*0.48 PGA=系统内存*(0.22~0.31

(对于32bit平台,默认情况下SGA最大可用内存有1.7GB的限制)

 
 

下面开始引入正题:

对某客户数据库巡检发现,其系统内存约为16GB,SGA分配6GB左右,PGA分配300MB左右,显然SGA分配的有点少。让我们看看分配较少SGA导致的结果,从而提醒客户增大SGA。

在物理读高峰期,客户实例效率百分比(Instance Efficiency Percentages)数据如下:

Buffer Nowait %:

99.59

Redo NoWait %:

100.00

Buffer Hit %:

89.26

In-memory Sort %:

100.00

Library Hit %:

98.79

Soft Parse %:

97.84

Execute to Parse %:

32.34

Latch Hit %:

99.54

Parse CPU to Parse Elapsd %:

86.68

% Non-Parse CPU:

96.35

Buffer cache命中率(Buffer Hit)小于90%。

观察此时间段Top 5 Timed Foreground Events数据,db file scattered read(离散读)平均等待时间为8ms:

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

db file scattered read

2,592,480

20,516

8

42.69

User I/O

db file sequential read

3,191,377

11,744

4

24.44

User I/O

DB CPU

   

8,688

   

18.08

   

read by other session

1,382,661

7,879

6

16.40

User I/O

SQL*Net break/reset to client

531

478

899

0.99

Application

在其他时刻,db file scattered read(离散读)平均等待时间约为2~3秒。

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

DB CPU

   

3,401

   

38.03

   

db file sequential read

1,148,704

3,393

3

37.95

User I/O

db file scattered read

659,530

2,135

3

23.88

User I/O

read by other session

95,092

217

2

2.43

User I/O

log file sync

265,339

114

0

1.27

Commit

 
 

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

library cache pin

12,907

32,481

2517

67.91

Concurrency

DB CPU

   

6,904

   

14.44

   

db file sequential read

2,170,554

5,760

3

12.04

User I/O

db file scattered read

1,236,535

2,910

2

6.08

User I/O

read by other session

168,017

466

3

0.97

User I/O

db file scattered read与全表扫描有关,显然大量的全表扫描导致了响应时间的成倍增长

当前Buffer Cache大小为4GB左右。

  

Begin

End

  

  

Buffer Cache:

3,952M

3,952M

Std Block Size:

8K

Shared Pool Size:

1,760M

1,760M

Log Buffer:

14,440K

 
 

通过以上数据,我们可以证明由于SGA太小(主要是Buffer cache小),导致在全表扫描时平均等待时间增加。因此建议增大SGA为9GB,注意增加Buffer cache大小。这可以从一定程度上减少db file sequential read和db file scattered read等待时间,提高数据库性能。

 
 

总结:对客户的每一个建议,都是建立在对客户数据库的详尽分析上,而非简单的根据一般原则。一般原则必有道理,但是在向客户提出建议时,我们要结合数据库的实际情况提出建议,这样客户才可欣然接受。对于SGA及buffer hit等问题,我有学到了。谢谢大师的指点!

  1. 这个我想用来做SGA增大的证明,通过buffer hit命中率下降,离散读增高来说明。
    不过话说PGA确实是小了点

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress