oracle SGA与PGA区别:
SGA:用于存储数据库信息的内存区,该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配,如果实际内存不够再往虚拟内存中写。
PGA:包含单个服务器进程或单个后台进程的数据和控制信息,与几个进程共享的SGA 正相反,PGA 是只被一个进程使用的区域,PGA 在创建进程时分配,在终止进程时回收。
1.SGA
SGA:系统全局域(System Global Area)是Oracle Instance的基本组成部分,在实例启动时分配;SGA主要由三部分构成:共享池、数据缓冲区、日志缓冲区。
共享池:Shared Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义,主要包括:Library cache(共享SQL区)和Data dictionary cache(数据字典缓冲区)。 共享SQL区是存放用户SQL命令的区域,数据字典缓冲区存放数据库运行的动态信息。
缓冲区高速缓存:Database Buffer Cache用于缓存从数据文件中检索出来的数据块,可以大大提高查询和更新数据的性能。
大型池:Large Pool是SGA中一个可选的内存区域,它只用于shared server环境。
Java池:ava Pool为Java命令的语法分析提供服务。
SGA与操作系统、内存大小、cpu、同时登录的用户数有关。可占OS系统物理内存的1/2到1/3,当然,如果服务器上只有oracle的话,可以分配的更大一些,如果还有其他服务,如IIS等,那就要分的小一些。
1)共享池 Shared Pool:
查看共享池大小Sql代码:
SQL> show parameter shared_pool_size;
查看共享SQL区的使用率Sql代码:
select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache;
LIBRARY命中率应该在90%以上,否则需要增加共享池的大小。
查看数据字典缓冲区的使用率Sql代码:
select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache;
这个使用率也应该在90%以上,否则需要增加共享池的大小。
修改共享池大小的Sql代码:
ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
2)缓冲区高速缓存 Database Buffer Cache:
查看其大小的Sql代码:
show parameter db_cache_size。
查看数据库数据缓冲区的使用情况:
SELECT name,value FROM v$sysstat WHERE name IN('DB BLOCK GETS','CONSISTENT GETS','PHYSICAL READS') order by name;
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%以上,否则需要增加数据缓冲区的大小。
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,显示造成高速缓存不命中的数据请求数。
Hit ratio与wait events:
select value from v$pgastat where name ='cache hit percentage';
Hit ratio与wait events是此消彼涨,在执行类似于
select col1,col2,.. from tab1 a where exists (
select 1 from tab2 where a.col3 = b.col3
)
where ....
的语句的时候,如果tab1的记录很多的话,你会发现系统的hit ratio会有很大的提高,wait events是否会改观呢.
3)日志缓冲区
查看日志缓冲区使用情况的Sql代码:
SELECT name, value FROM v$sysstat WHERE name IN ('redo entries','redo log space requests');
查询出的结果可以计算出日志缓冲区的申请失败率:
申请失败率=requests/entries,申请失败率应该接近于0,否则说明日志缓冲区开设太小,需要增加ORACLE数据库的日志缓冲区。
4)大型池
可以减轻共享池的负担,可以为备份、恢复等操作来使用,不使用LRU算法来管理。其大小由数据库的'共享模式/db模式'如果是共享模式的话,要分配的大一些。
指定Large Pool的大小:
ALTER SYSTEM SET LARGE_POOL_SIZE=64M;
5)Java池
在安装和使用Java的情况下使用。
2.PGA
PGA:Program Global Area是为每个连接到Oracle database的用户进程保留的内存。
1)PGA_AGGREGATE_TARGET初始化设置
PGA_AGGREGATE_TARGET的值应该基于Oracle实例可利用内存的总量来设置,这个参数可以被动态的修改。假设Oracle实例可分配4GB的物理内存,剩下的内存分配给操作系统和其它应用程序。你也许会分配80%的可用内存给Oracle实例,即3.2G。现在必须在内存中划分SGA和PGA区域。
在OLTP(联机事务处理)系统中,典型PGA内存设置应该是总内存的较小部分(例如20%),剩下80%分配给SGA。
OLTP:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
在OLAP(在线分析系统)或DSS(决策支持系统)系统中,由于会运行一些很大的查询,典型的PGA内存最多分配70%的内存。
OLAP/ DSS:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
若oracle实例总内存为4G,OLTP系统,可以设置PGA_AGGREGATE_TARGET为655MB,OLAP/DSS系统则为1600MB。
2)配置PGA自动管理
不用重启数据库,直接在线修改。
SQL> alter system set workarea_size_policy=auto scope=both;
System altered.
SQL> alter system set pga_aggregate_target=512m scope=both;
System altered.
SQL> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO --这个设置成AUTO
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 536870912
ORACLE建议一个数据库服务器,分80%的内存给数据库,20%的内存给操作系统,那怎么给一个数据库服务器配内存呢?
SQL> select * from v$pgastat;
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 104857600 bytes
-----这个值等于参数PGA_AGGREGATE_TARGET的值,如果此值为0,表示禁用了PGA自动管理。
aggregate PGA auto target 75220992 bytes
-----表示PGA还能提供多少内存给自动运行模式,通常这个值接近pga_aggregate_target-total pga inuse.
global memory bound 20971520 bytes
-----工作区执行的最大值,如果这个值小于1M,马上增加PGA大小
total PGA inuse 30167040 bytes
-----当前分配PGA的总大小,这个值有可能大于PGA,如果PGA设置太小.这个值接近select sum(pga_used_mem) from v$process.
total PGA allocated 52124672 bytes
-----工作区花费的总大小
maximum PGA allocated 67066880 bytes
total freeable PGA memory 0 bytes ----没有了空闲的PGA
process count 23 ----当前一个有23个process
max processes count 25
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 8891392 bytes
maximum PGA used for auto workareas 22263808 bytes
total PGA used for manual workareas 0 bytes ---为0自动管理
maximum PGA used for manual workareas 0 bytes ---为0自动管理
over allocation count 0
如果PGA设置太小,导致PGA有时大于PGA_AGGREGATE_TARGET的值,此处为0,说明PGA没有扩展大于TARGET的值,如 果此值出现过,那么增加PGA大小。
bytes processed 124434432 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent ---命中率为100%,如果太小增加PGA
recompute count (total) 6651
19 rows selected