Oracle 内存分配和调优 总结
一直都想总结一下oracle内存调整方面的知识，最近正好优化一个数据库内存参数，查找一些资料并且google很多下。现在记录下来，做下备份。
概述：
Oracle 的内存可以按照共享和私有的角度分为系统全局区和进程全局区，也就是 SGA和 PGA(process global area or private global area)。对于 SGA 区域内的内存来说，是共享的全局的，在 UNIX 上，必须为 oracle 设置共享内存段（可以是一个或者多个），因为 oracle 在UNIX 上是多进程；而在 WINDOWS 上 oracle 是单进程（多个线程），所以不用设置共享内存段。PGA 是属于进程（线程）私有的区域。在 Oracle 使用共享服务器模式下（MTS）,PGA中的一部分，也就是 UGA 会被放入共享内存 large_pool_size 中。
发张图Oracle内存架构组成，按照图上面的显示可以一目了然关键的参数和参数名称：
对于 SGA 部分，我们通过 sqlplus 中查询可以看到：
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 1380840
Variable Size 1241517592
Database Buffers 1325400064
Redo Buffers 9613312
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 2188768
Variable Size 2603824182
Database Buffers 2872259379
Redo Buffers 20566016
Fixed Size:
Oracle 的不同平台和不同版本下可能不一样，但对于确定环境是一个固定的值，里面存储了 SGA 各部分组件的信息，可以看作引导建立 SGA 的区域。Variable Size :
包含了 shared_pool_size、java_pool_size、large_pool_size 等内存设置Database Buffers : 指数据缓冲区:
在 8i 中包 含 db_block_buffer*db_block_size、buffer_pool_keep、buffer_pool_recycle 三 部 分内 存 。
在 9i 中 包 含 db_cache_size 、db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size。Redo Buffers :
指日志缓冲区，log_buffer。在这里要额外说明一点的是，对于 v$parameter、v$sgastat、v$sga 查询值可能不一样。v$parameter 里面的值，是指用户在初
始化参数文件里面设置的值，v$sgastat 是 Oracle 实际分配的日志缓冲区大小（因为缓冲区的分配值实际上是离散的，也不是以 block 为最小单位进行分配的），
v$sga 里面查询的值，是在 Oracle 分配了日志缓冲区后，为了保护日志缓冲区，设置了一些保护页，通常我们会发现保护页大小大约是 11k(不同环境可能不一样)。
Oracle 内存调优办法
当项目的生产环境出现性能问题，我们如何通过判断那些参数需要调整呢？
检查ORACLE实例的Library Cache命中率:
标准:一般是大于99％
检查方式:
SQL> select 1-(sum(reloads)/sum(pins)) "Library cache Hit Ratio" from v$librarycache;
Library cache Hit Ratio
-----------------------
0.995500931581541
处理措施:
如果Library cache Hit Ratio的值低于99%，应调高shared_pool_size的大小。通过sqlplus连接数据库执行如下命令，调整shared_pool_size的大小：
SQL>alter system flush shared_pool;
SQL>alter system set shared_pool_size=设定值 scope=spfile;
检查ORACLE实例的Data Buffer（数据缓冲区）命中率
标准:一般是大于90%
检查方式:
SQL> select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO"
2 from v$sysstat cur, v$sysstat con, v$sysstat phy
3 where cur.name = 'db block gets'
4 and con.name = 'consistent gets'
5 and phy.name = 'physical reads';
HIT RATIO
----------
0.97678192
处理措施:
如果HIT RATIO的值低于90%，应调高db_cache_size的大小。通过sqlplus连接数据库执行如下命令，
调整db_cache_size的大小
SQL>alter system set db_cache_size=设定值 scope=spfile
检查ORACLE实例的Dictionary Cache命中率
标准:一般是大于95%
检查方式:
SQL> select 1 - (sum(getmisses) / sum(gets)) "Data Dictionary Hit Ratio"
2 from v$rowcache;
Data Dictionary Hit Ratio
-------------------------
0.999701677408011
处理措施:
如果Data Dictionary Hit Ratio的值低于95%，应调高shared_pool_size的大小。通过sqlplus连接数据库执行如下命令，调整shared_pool_size的大小：
SQL>alter system flush shared_pool;
SQL>alter system set shared_pool_size=设定值 scope=spfile;
检查ORACLE实例的Log Buffer命中率
标准:一般是小于1%
检查方式:
SQL> select (req.value * 5000) / entries.value "Ratio"
2 from v$sysstat req, v$sysstat entries
3 where req.name = 'redo log space requests'
4 and entries.name = 'redo entries';
Ratio
----------
0.08082257
处理措施:
如果Ratio高于1％，应调高log_buffer的大小。通过sqlplus连接数据库执行如下命令，调整log_buffer的大小：
SQL>alter system set log_buffer=设定值 scope=spfile;
检查undo_retention
标准:undo_retention 的值必须大于max(maxquerylen)的值
检查方式:
SQL> col undo_retention format a30
SQL> select value "undo_retention" from v$parameter where name='undo_retention';
undo_retention
------------------------------
900
SQL> select max(maxquerylen) From v$undostat Where begin_time>sysdate-(1/4);
MAX(MAXQUERYLEN)
----------------
1253
处理措施:
如果不满足要求，需要调高undo_retention 的值。通过sqlplus 连接数据库执行如下命
令，调整undo_retention 的大小：
SQL>alter system set undo_retention= 设定值 scope=spfile;
SQL> alter system set undo_retention=2048 scope=spfile;
System altered
简单查看ORACLE的连接情况和内存使用情况
查看连接Oracle的所有机器的连接数
-- 查看连接Oracle的所有机器的连接数
SELECT MACHINE,COUNT(*) FROM V$SESSION GROUP BY MACHINE;
SQL> SELECT MACHINE,COUNT(*) FROM V$SESSION GROUP BY MACHINE;
MACHINE COUNT(*)
---------------------------------------------------------------- ----------
WIN-Q9I6NL3EJCU 28
USER-20151208SP\USER-20151208SP 14
WORKGROUP\WIN-Q9I6NL3EJCU 6
WORKGROUP\USER-20151208SP 7
IIS APPPOOL\WIN-Q9I6NL3EJCU 14
查看连接Oracle的所有机器的连接数和状态
-- 查看连接Oracle的所有机器的连接数和状态
SELECT MACHINE,STATUS,COUNT(*) FROM V$SESSION GROUP BY MACHINE,STATUS ORDER BY STATUS;
SQL> SELECT MACHINE,STATUS,COUNT(*) FROM V$SESSION GROUP BY MACHINE,STATUS ORDER BY STATUS;
MACHINE STATUS COUNT(*)
---------------------------------------------------------------- -------- ----------
WIN-Q9I6NL3EJCU ACTIVE 26
WORKGROUP\USER-20151208SP ACTIVE 1
WORKGROUP\WIN-Q9I6NL3EJCU ACTIVE 1
IIS APPPOOL\WIN-Q9I6NL3EJCU INACTIVE 13
USER-20151208SP\USER-20151208SP INACTIVE 14
WIN-Q9I6NL3EJCU INACTIVE 4
WORKGROUP\USER-20151208SP INACTIVE 6
WORKGROUP\WIN-Q9I6NL3EJCU INACTIVE 5
8 rows selected
Oracle的PGA、SGA和process count
-- Oracle的PGA、SGA和process count
SELECT 'SGA' AS NAME,ROUND(SUM(VALUE)/1024/1024,2)||'M' AS "SIZE(M)" FROM V$SGA
UNION
SELECT 'PGA' AS NAME,ROUND(VALUE/1024/1024,2)||'M' AS "SIZE(M)" FROM V$PGASTAT WHERE NAME='TOTAL PGA ALLOCATED'
UNION
SELECT 'TOTAL' AS NAME,((SELECT ROUND(SUM(VALUE)/1024/1024,2) FROM V$SGA)+(SELECT ROUND(VALUE/1024/1024,2) FROM V$PGASTAT WHERE NAME='TOTAL PGA ALLOCATED'))||'M' AS "SIZE(M)" FROM DUAL
UNION
SELECT NAME,TO_CHAR(VALUE) FROM V$PGASTAT WHERE NAME='PROCESS COUNT';
SQL> SELECT 'SGA' AS NAME,ROUND(SUM(VALUE)/1024/1024,2)||'M' AS "SIZE(M)" FROM V$SGA
2 UNION
3 SELECT 'PGA' AS NAME,ROUND(VALUE/1024/1024,2)||'M' AS "SIZE(M)" FROM V$PGASTAT WHERE NAME='TOTAL PGA ALLOCATED'
4 UNION
5 SELECT 'TOTAL' AS NAME,((SELECT ROUND(SUM(VALUE)/1024/1024,2) FROM V$SGA)+(SELECT ROUND(VALUE/1024/1024,2) FROM V$PGASTAT WHERE NAME='TOTAL PGA ALLOCATED'))||'M' AS "SIZE(M)" FROM DUAL
6 UNION
7 SELECT NAME,TO_CHAR(VALUE) FROM V$PGASTAT WHERE NAME='PROCESS COUNT';
NAME SIZE(M)
---------------------------------------------------------------- -----------------------------------------
SGA 52245.7M
TOTAL M
