对SGA和PGA的优化建议

最新推荐文章于 2025-12-09 09:24:19 发布

转载最新推荐文章于 2025-12-09 09:24:19 发布 · 180 阅读

文章标签：

本文深入探讨了Oracle数据库中SGA和PGA的配置原则，针对不同类型的业务系统（OLTP和OLAP），提供具体的SGA和PGA设置建议。详细分析了与Buffer Cache相关的等待事件及热块数据问题，提供了从应用层面解决热块争用的方法，并解释了Bufferbusywaits、readwaits和FreeBufferwaits等等待事件的含义及其解决策略。同时，文章还讨论了PGA的使用状况和相关等待事件，提供了分析和优化PGA使用的指导。

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22621861/viewspace-1308909/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-1308909/