SGA/PGA的设置与调整

2013-04-10

SGA是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。 PGA包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA 正相反,PGA 是只被一个进程使用的区域，PGA 在创建进程时分配,在终止进程时回收。

1、基本概念
(1)SGA(SGA_TARGET)：System Global Area是Oracle Instance的基本组成部分，在实例启动时分配;系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。

(2)共享池(shared_pool_size)：Shared Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括：Library cache（共享SQL区）和Data dictionary cache（数据字典缓冲区）。 共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。

(3)缓冲区高速缓存(db_cache_size)：Database Buffer Cache用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能。

(4)大型池(large_pool_size)：Large Pool是SGA中一个可选的内存区域，它只用于shared server环境。

(5)Java池(java_pool_size)：Java Pool为Java命令的语法分析提供服务。

(6)PGA(pga_aggregate_target)：Process Global Area是为每个连接到Oracle database的用户进程保留的内存。

2、查询SGA和PGA：
SQL> show sga;

Total System Global Area 4294967296 bytes
Fixed Size                  2101808 bytes
Variable Size            2550140368 bytes
Database Buffers         1728053248 bytes
Redo Buffers               14671872 bytes

获得sga_max_size和sga_target的值

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4G
sga_target big integer 4G

获得pga_aggregate_target的值
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 6425M

SQL> select name, value from v$pgastat;

aggregate PGA auto target：表示PGA内存中共享可调整的部分，与aggregate PGA target相比，该值不能太小； over allocation count：表示oracle违反pga_aggregate_target参数的次数，当PGA太小并且oracle不能提供PGA untunable内存+执行work area workload所需的最小内存时会发生over allocation，理想情况下该值应为0；

pga调整建议
SQL> SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM
v$pga_target_advice；

3、参考值：

现场服务器是08G内存，SGA设置为04G，PGA设置为1G

现场服务器是16G内存，SGA设置为10G，PGA设置为2G

现场服务器是32G内存，SGA设置为20G，PGA设置为4G

4、调整SGA_MAX_SIZE直接修改pfile方法：

cd $ORACLE_HOME/dbs
vi initSID.ora
*.sga_max_size=4294967296
*.sga_target=4294967296

通过命令修改
create pfile from spfile;
alter system set sga_max_size=4G scope=spfile;
alter system set sga_target=4G scope=spfile;
重启oracle实例sga_max_size才能生效

5、shared_pool_size修改后可立即生效，但是需要SGA支持
查看共享池大小：
SQL> show parameter shared_pool_size
oracle 10g shared_pool_size 默认 0，因为10G 自动分配shared_pool_size所以这个值是0

查看共享SQL区的使用率：
select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache; --动态性能表
LIBRARY命中率命中率命中率命中率应该在90％以上，否则需要增加共享池的大小。

查看数据字典缓冲区的使用率：

select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache; --动态性能表
这个使用率也应该在90％以上，否则需要增加共享池的大小。

修改共享池的大小 :
create pfile from spfile;
alter system set shared_pool_size=2G scope=both;
alter system set large_pool_size=32M scope=both;
6、缓冲区高速缓存 Database Buffer Cache：
查看大小：
SQL> show parameter db_cache_size
SQL> show parameter db_cache_advice

查看数据库数据缓冲区的使用情况：
SELECT name,value FROM v$sysstat order by name WHERE name IN(''DB BLOCK GETS'',''CONSISTENT GETS'',''PHYSICAL READS'');

SELECT * FROM V$SYSSTAT WHERE NAME IN ('parse_time_cpu','parse_time_elapsed','parse_count_ hard');
计算出来数据缓冲区的使用命中率＝1-(physical reads/(db block gets+consistent gets)),这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。

调整大小：
alter system set db_cache_size =xxx;

7、PGA的调整命令
系统级更改：
SQL>　ALTER SYSTEM SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；
SQL>　ALTER SYSTEM SET PGA_AGGREGATE_TARGET=239075328;
SQL>　ALTER SYSTEM SET SORT_AREA_SIZE = 65536 SCOPE = SPFILE；-->使用AUTO方式时该参数不起作用
SQL>　ALTER SYSTEM SET HASH_AREA_SIZE = 65536 SCOPE = SPFILE；-->使用AUTO方式时该参数不起作用

会话级更改
SQL>　ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；
SQL>　ALTER SESSION SET SORT_AREA_SIZE = 65536；-->使用AUTO方式时该参数不起作用
SQL>　ALTER SESSION SET HASH_AREA_SIZE = 65536；-->使用AUTO方式时该参数不起作用

分类：数据库 | 标签： 、 |

相关日志

评论被关闭！