oracle查询sga以及pga的大小,SGA/PGA的设置与调整

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方式时该参数不起作用

相关日志