1. SGA

1.1 Buffer Cache

1.2 Shared Pool

2. PGA

基准SGA和PGA的设置

对OLTP系统：

SGA = 物理内存 * 80% * 80%

PGA = 物理内存 * 80% * 20%

对OLAP系统：

SGA = 物理内存 * 80% * 50%

PGA = 物理内存 * 80% * 50%

1. 1 Buffer Cache

Buffer Cache如果想手工设定，建议设定为SGA的80%

与Buffer Cache可能有关的等待事件有：

1) Latch:cache buffer chains

2) Latch:cache buffer LRU chains

3) Buffer busy waits

4) Read waits

5) Free buffer waits

对1)和2)，可能是由于存在热块数据。

查询热块：

点击(此处)折叠或打开

col owner format a10

col object_name format a10

col object_type format a10

col statistic_name format a20

SELECT * FROM ( SELECT OWNER, OBJECT_NAME,

OBJECT_TYPE, STATISTIC_NAME, SUM(VALUE)

FROM V$SEGMENT_STATISTICS

GROUP BY OWNER, OBJECT_NAME, OBJECT_TYPE,

STATISTIC_NAME

ORDER BY SUM(VALUE) DESC)

WHERE ROWNUM < 10;

OWNER      OBJECT_NAM OBJECT_TYP STATISTIC_NAME       SUM(VALUE)

---------- ---------- ---------- -------------------- ----------

BAAN       TWHWMD4004 INDEX      logical reads        3935487344

40$IDX1

BAAN       TTCIBD0014 TABLE      logical reads        2770298080

40

BAAN       TTDPUR4014 INDEX      logical reads        2599466128

40$IDX1

BAAN       TWHINH2104 TABLE      logical reads        1618469696

40

BAAN       TWHINH3124 INDEX      logical reads        1526287552

40$IDX2

BAAN       TWHWMD4004 TABLE      logical reads        1257102528

40

BAAN       TTCIBD0014 INDEX      logical reads        1225452368

40$IDX1

BAAN       TWHINR1104 TABLE      logical reads         943541312

40

BAAN       TWHINR1104 TABLE      physical reads        560351905

40

对于热块争用，最好能从应用层面解决。数据库方面，可以考虑反向键索引和哈希分区。

对于3) Buffer busy waits

表示多个进程访问同一个Buffer Cache。以下是详细解释：

当一个session想要访问/修改buffer cache的block,首先需要检查该block是否存在于buffer cache，检查该block是否存在于buffer cache中是通过HASH算法来实现的，另外检查相同的SQL语句是否存在于library cache中也是通过HASH算法实现的。要判断block是否存在于buffer cache中，就需要扫描一条chain，该chain中具有block的信息。而扫描这个chain必须获得一个latch，防止并发对chain照成破坏，如果未能获得该latch，就说明此block是hot block，同时在数据库中标记一个latch: cache buffers chains这个等待事件。如果该block存在于buffer cache中就不需要物理读，如果不存在，就需要物理读取该block到buffer cache中。为了能够读取，并修改该block，我们就需要pin住该block,防止并发对于该block造成破坏，所以如果别的session不能获得pin，Oracle就会标记一个buffer busy waits等待事件。

引起该事件可能的原因包括全表扫描、索引过多、使用了序列来做索引等。

当出现了比较严重的cache buffers chains竞争时，我们可以找出该热点块的具体对象，找出引起该竞争的SQL语句，修改不良的SQL。如果不能更改应用，可以尝试将数据分散，设置高的pctfree，使用较小的block size，查看SQL执行计划是否正确，减少逻辑读。

点击(此处)折叠或打开

SELECT object_name, value

FROM V$SEGMENT_STATISTICS

WHERE statistic_name = 'buffer busy waits'

AND value > 2000;

对于4)read waits，包括db file sequential read, db file scattered read，

前者表示扫描索引，可以检查索引是否有大量碎片；

后者表示存在全表扫描。

详细见以下博文：

http://blog.itpub.net/22621861/viewspace-1309661/

对于5) Free Buffer waits

表示将Buffer Cache写入到磁盘的速度，赶不上其他应用申请空闲Buffer Cache的速度。解决办法：

1)是否有全表扫描

2)检查db_cache_size设置

3)检查DB_WRITER_PROCESSES，建议不超过CPU个数。

共享池问题

可参考：

语句共享性问题

http://blog.itpub.net/22621861/viewspace-1307202/

PGA

对于PGA，应重点关注PGA命中率(PGA Cache Hit%)

与PGA有关的等待事件有direct path read temp和direct path write temp。

这些等待事件表示PGA空间有限，导致了临时表空间操作。

查询在PGA和临时表空间之间多次往返操作的SQL

点击(此处)折叠或打开

SELECT sql_text,

sum(onepass_executions) onepass_cnt,

sum(multipasses_executions) mpass_cnt

FROM V$SQL s, V$SQL_WORKAREA wa

WHERE s.address = wa.address

GROUP BY sql_text

HAVING sum(onepass_executions+multipasses_executions) > 0;

分析PGA使用状况

点击(此处)折叠或打开

COL NAME FORMAT A30

SELECT * FROM V$PGASTAT

NAME                                VALUE UNIT

------------------------------ ---------- ------------------------

aggregate PGA target parameter  199229440 bytes

aggregate PGA auto target       131300352 bytes

global memory bound              39845888 bytes

total PGA inuse                  53477376 bytes

total PGA allocated             107995136 bytes

maximum PGA allocated           176616448 bytes

total freeable PGA memory        12451840 bytes

process count                          27

max processes count                    36

PGA memory freed back to OS     527171584 bytes

total PGA used for auto workar     137216 bytes

NAME                                VALUE UNIT

------------------------------ ---------- ------------------------

eas

maximum PGA used for auto work    4290560 bytes

areas

total PGA used for manual work          0 bytes

areas

maximum PGA used for manual wo     268288 bytes

rkareas

NAME                                VALUE UNIT

------------------------------ ---------- ------------------------

over allocation count                   0

bytes processed                 923526144 bytes

extra bytes read/written                0 bytes

cache hit percentage                  100 percent

recompute count (total)              1969

重点关注over allocation count条目，该条目表示超出PGA_AGGREGATE_TARGET参数设置的PGA目标值。此时，应考虑增大PGA_AGGREGATE_TARGET