SGA PGA

一、SGA

 1 SGA 基础

  1.1 定义
   System Global Area是Oracle Instance的基本组成部分，在实例启动时分配; SGA与操作系统、内存大小、cpu、同时登录的用户数有关。可占OS系统物理内存的1/2到1/3。
   系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。
   共享池：Shared Pool用于缓存最近被执行的语句和最近被使用的数据定义。
        主要包括：Library cache（共享SQL区）和 dictionary cache（数据字典缓冲区）
        共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息
   缓冲区高速缓存：Database Buffer Cache用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能
   大型池：Large Pool是SGA中一个可选的内存区域，它只用于shared 环境
   Java池：Java Pool为Java命令的语法分析提供服务

 1.2 查看SGA大小

    SQL> select * from v$sga;

      NAME                      VALUE
      -------------------- ----------
     Fixed Size                      453492
     Variable Size            109051904
     Database Buffers       25165824
     Redo Buffers                  667648

其中：

Fixed Size：包括了数据库与实例的控制信息、状态信息、字典信息等，启动时就被固定在SGA中，不会改变。
Variable Size：包括了shard pool、large pool、java pool、stream pool、游标区和其他结构
Database Buffers：数据库中数据块缓冲的地方，是SGA中最大的地方，决定数据库性能
Redo Buffers：提供REDO缓冲的地方，在OLAP中不需要太大

 

2 SGA 调优

 

 2.1 共享池调优

 

      修改共享池的大小：

      ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

 

      查看共享SQL区的使用率：

      select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache;--动态性能表

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

 

       查看数据字典缓冲区的使用率：
      select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache;  

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

 

 2.2 缓冲区高速缓存

 

     它的大小要根据数据量来决定：
         SGA=((db_block_buffers * block size)+    (shared_pool_size+large_pool_size+java_pool_size+log_buffers)+1MB

 

     查看数据库数据缓冲区的使用情况：
     SELECT name,value FROM v$sysstat order by name WHERE name IN(''DB BLOCK GETS'',''CONSISTENT GETS'',''PHYSICAL READS'');
计算出来数据缓冲区的使用命中率＝1-(physical reads/(db block gets+consistent gets)),这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。

 

     v$librarycache查看查询不命中数：

select sum(pins) "缓存中执行次数",sum(reloads) "不命中数",sum(reloads)/sum(pins) from v$librarycache;
其中，pins,显示在库高速缓存中执行的次数;reload,显示在执行阶段库高速缓存不命中的数目，一般 sum(reloads)/sum(pins)的值应接近于零.如果大于1%就应该增加shared_pool_size的值, 来提高数据字典高速缓存可用的内存数量,减少不命中数.

 

   通过动态性能表v$rowcache来查询数据字典高速缓存的活动：
        select sum(gets) "请求存取数",sum(getmisses) "不命中数" from v$rowcache 
        其中，gets,显示请求相应项的总数; getmisses,显示造成高速缓存不命中的数据请求数

       SQL> select sum(gets) "请求存取数",sum(getmisses) "不命中数" from v$rowcache;

        请求存取数   不命中数
        ---------- ----------
           114691       2745

 

2.3 日志缓冲区

 

   查看日志缓冲区的使用情况：

   SELECT name, value   FROM v$sysstat WHERE name IN ('redo entries','redo log space requests');  

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

 

   SQL> SELECT name, value   FROM v$sysstat WHERE name IN ('redo entries','redo log
 space requests');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                          78040
redo log space requests                                                   0

 申请失败率=0/78040=0

 

4 内存分配建议

ORACLE给的建议是: OLTP系统  PGA=(Total Memory)*80%*20%。DSS系统PGA=(Total Memory)*80%*50%。
ORACLE建议一个数据库服务器，分80%的内存给数据库，20%的内存给操作系统

 

5 查询SGA和PGA内存分配详情

 SQL> select * from v$sgastat;

 SQL> select * from v$pgastat;