Oracle 内存调整

名词解释

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池：java Pool为Java命令的语法分析提供服务

PGA：Program Global Area是为每个连接到Oracle database的用户进程保留的内存。
查询SGA和PGA
获得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 580M
sga_target big integer 580M

获得pga_aggregate_target的值

SQL> show parameter pga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 192M

SGA空闲空间

SQL> select pool,name, round(sgasize/1024/1024,2) "Allocated(M)",
　　　 round(bytes/1024,2) "自由空间(K)", round(bytes/sgasize*100, 2) "自由空间百分比(%)"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = 'free memory';

POOL NAME Allocated(M) 自由空间(K) 自由空间百分比(%)
------------ -------------------------- ------------ --------------- ------------------------
shared pool free memory 578.35 426.5 0.07
large pool free memory 578.35 3214.98 0.54
java pool free memory 578.35 4096 0.69

查看 Oracle SGA 命中率
数据缓冲区高速缓存

SELECT physical_reads, db_block_gets, consistent_gets, NAME,
100 * ( 1 - ( physical_reads / (consistent_gets + db_block_gets - physical_reads))) "Data Buffer Hit Ratio"
FROM v$buffer_pool_statistics;

PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS NAME Data Buffer Hit Ratio
-------------- ------------- --------------- -------------------- ---------------------
3673479 173945154 1543853168 DEFAULT 99.7856936141494

重做日志缓冲区

SELECT a.VALUE redo_entries, b.VALUE redo_buffer_allocation_retries,
ROUND ((1 - b.VALUE / a.VALUE) * 100, 4) log_buffer_ratio
FROM v$sysstat a, v$sysstat b
WHERE a.NAME = 'redo entries' AND b.NAME = 'redo buffer allocation retries';

REDO_ENTRIES REDO_BUFFER_ALLOCATION_RETRIES LOG_BUFFER_RATIO
------------ ------------------------------ ----------------
88910432 5581 99.9937

共享池缓冲区命中率

建议大于90%

SELECT SUM (pinhits) / SUM (pins) * 100 "hit radio" FROM v$librarycache;

hit radio
----------
96.7194470

数据字典高速缓存命中率

建议大于90%

SELECT TO_CHAR (ROUND ((1 - SUM (getmisses) / SUM (gets)) * 100, 1)) || '%' "Dictionary Cache Hit Ratio" FROM v$rowcache;

Dictionary Cache Hit Ratio
-----------------------------------------
99.5%

排序

SELECT a.VALUE disk_sort, b.VALUE memory_sort, ROUND ((1 - a.VALUE / (a.VALUE + b.VALUE)) * 100, 4) sort_ratio
FROM v$sysstat a, v$sysstat b
WHERE a.NAME = 'sorts (disk)' AND b.NAME = 'sorts (memory)';

DISK_SORT MEMORY_SORT SORT_RATIO
---------- ----------- ----------
0 6034093 100

修改
参考值

对于32 BIT系统，有SGA 1.7G限制

现场服务器是8G内存，SGA设置为4G，PGA设置为1G
现场服务器是16G内存，SGA设置为10G，PGA设置为2G
现场服务器是32G内存，SGA设置为20G，PGA设置为4G
修改方法
1、更改之前，需要备份，修改之后，很容易启动不了数据库。
pfile

sga_max_size=4G
sga_target=4G
pga_aggregate_target=1G

spfile

SQL> alter system set sga_max_size=4G scope=spfile;
SQL> alter system set sga_target=4G scope=spfile;
SQL> alter system set pga_aggregate_target=1G scope=spfile;