一、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;