Oracle数据库性能调优实践(四)——SGA和PGA调优

摘要：系统全局域SGA（SystemGlobal Area）是OracleInstance的基本组成部分，在实例启动时分配，是共享数据块，所有进程可以访问。系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。其具体分类有，一是共享池，共享池Shared Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括：Librarycache（共享SQL区）和Datadictionarycache（数据字典缓冲区）。 共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。二是缓冲区高速缓存：DatabaseBufferCache用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能。三是大型池，Large Pool是SGA中一个可选的内存区域，它只用于shared server环境。四是Java池，Java Pool为Java命令的语法分析提供服务。

进程全局域PGA（ProcessGlobal Area）是为每个连接到Oracledatabase的用户进程保留的内存。进程全局域PGA是操作系统含义上的内存区，可以理解为操作系统在一个进程启动时，为他分配的内存空间，为专有进程服务，进程间无法数据共享，数据独占。

现将有关Oracle的SGA和PGA调优方面的知识整理出来，供参考。详细内容请看下文。

一、访问Oracle数据库

1、切换到oracle 用户： su - oracle

2、查看oracle 监控： lsnrctl status

3、访问oracle 数据库： sqlplus / as sysdba

二、SGA分析与调整

1、调整共享池Shared Pool

（1）、查看共享池大小

备注：修改共享池的代码为ALTERSYSTEMSET SHARED_POOL_SIZE =**M;

（2）、查看共享SQL区的使用率

备注：LIBRARY命中率应该在90％以上，否则需要增加共享池的大小。

（3）、查看数据字典缓冲区的使用率

备注：这个使用率也应该在90％以上，否则需要增加共享池的大小。

2、调优缓冲区高速缓存DatabaseBufferCache

（1）、查看共享池大小

（2）、查看数据库数据缓冲区的使用情况

SQL>SELECT * FROM V$SYSSTAT WHERE NAME IN ('dbblock gets','consistent gets','physicalreads');

SQL>SELECT * FROM V$SYSSTAT WHERE NAME IN('parse_time_cpu','parse_time_elapsed','parse_count_ hard');

备注：计算出来数据缓冲区的使用命中率＝1-(physicalreads/(dbblock gets+consistent gets)),这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。

3、调优日志缓冲区

（1）、查看日志缓冲区的使用情况

备注：查询出的结果可以计算出日志缓冲区的申请失败率，申请失败率＝requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区。

4、调优大型池

可使用代码ALTERSYSTEMSET LARGE_POOL_SIZE=**M；调优大型池。

5、调优Java池

在安装和使用Java的情况下使用。

三、PGA分析与调整

1、PGA_AGGREGATE_TARGET初始化设置

PGA_AGGREGATE_TARGET的值应该基于Oracle实例可利用内存的总量来设置，这个参数可以被动态的修改。假设Oracle实例可分配32GB的物理内存，剩下的内存分配给操作系统和其它应用程序。你也许会分配80％的可用内存给Oracle实例，即25.6G。

接下来在内存中划分SGA和PGA区域，分为OLTP和DSS。如下

（1）、在OLTP(联机事务处理)系统中，典型PGA内存设置应该是总内存的较小部分（例如20％），剩下80％分配给SGA。

OLTP：PGA_AGGREGATE_TARGET=(total_mem * 80%) * 20%=5.12G

（2）、在DSS(数据集)系统中，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。

DSS：PGA_AGGREGATE_TARGET=(total_mem * 80%) * 50%=12.8G

2、配置PGA自动管理

SQL>alter system set workarea_size_policy=auto scope=both;

SQL>alter system set pga_aggregate_target=1024m scope=both;

3、监控自动PGA内存管理的性能

备注：

aggregate PGA target parameter number bytes

--当前PGA_AGGREGATE_TARGET的值

aggregate PGAauto target number bytes

--当前可用于自动分配了的PGA大小，应该比PGA_AGGREGATE_TARGET小

globalmemorybound number bytes

--自动模式下工作区域的最大大小，Oracle根据工作负载自动调整。

total PGA inuse number bytes

total PGA allocated number bytes

--PGA的最大分配

maximum PGA allocated number bytes

total freeable PGAmemory number bytes

--PGA的最大空闲大小

PGAmemory freed back to OS number bytes

total PGA usedforauto workareas number bytes

--PGA分配给auto workareas的大小

maximum PGA usedforauto workareas number bytes

total PGA usedformanual workareas number bytes

maximum PGA usedformanual workareas number bytes

over allocation count

--实例启动后，发生的分配次数，如果这个值大于0，就要考虑增加pga的值

bytes processed number bytes

extra bytesread/written number bytes

cache hit percentage 96.14percent

--命中率