oracle 内存的优化
oracle 的内存可以按照共享和私有的角度分为系统全局区和进程全局区 SGA和PGA
对于SGA部分，我们通过sqlplus 中查询可以看到：
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 1222192
Variable Size 289409488
Database Buffers 545259520
Redo Buffers 2969600
其中：
Fixed Size：存储了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(不同环境可能不一样)。参考如下内容
SQL> select substr(name,1,10) name,substr(value,1,10) value
2 from v$parameter where name = 'log_buffer';
NAME VALUE
-------------------- --------------------
log_buffer 2923520
SGA的大小：
设置SGA时要问自己的几个问题
一：物理内存多大
二：操作系统估计需要使用多少内存
三：数据库是使用文件系统还是裸设备
四：有多少并发连接
五：应用是OLTP 类型还是OLAP 类型
1。物理内存好确定
2。操作系统一般使用256m
3。文件系统比较大，raw不用考虑
4。并发数由pga决定
5对于OLTP类型oracle 倾向于可使用MTS,对于OLAP 类型使用独立模式，同时OLAP 还可能涉及到大
量的排序操作的查询，这些都影响到我们内存的使用。那么所有的问题综合起来，实际上主
要反映在UGA的大小上。UGA主要包含以下部分内存设置
SQL> show parameters area_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 1048576
sort_area_size integer 524288
workarea_size_policy string AUTO
在1G 的内存的服务器上，我们能分配给SGA 的内存大约为400—500M。若是2G 的内存，大约可以分到1G
的内存给SGA，8G 的内存可以分到5G的内存给SGA。当然我们这里是以默认的排序部分
内存sort_area_size=64k进行衡量的，假如我们需要调大该参数和hash_area_size等参数，然
后我们应该根据并发的进程的数量，来衡量考虑这个问题。
注：事实上，通常我们更习惯通过直观的公式化来表达这样的问题：
OS使用内存+SGA+并发执行进程数*(sort_area_size+hash_ara_size+2M) < 0.7*总内存
在我们的实际应用中，假如采用的是裸设备，我们可适当的增大SGA(如果需要的话)。
由于目前几乎所有的操作系统都使用虚拟缓存，所以实际上如果就算SGA 设置的比较大也
不会导致错误，而是可能出现频繁的内存页的换入与换出(page in/out)。在操作系统一级如
果观察到这个现象，那么我们就需要调整内存的设置。
SGA参数说明
Log_buffer
对于日志缓冲区的大小设置，通常我觉得没有过多的建议，因为参考LGWR写的触发
条件之后，我们会发现通常超过3M意义不是很大。作为一个正式系统，可能考虑先设置这
部分为log_buffer=1—3M 大小，然后针对具体情况再调整。
Large_pool_size(大型池)
对于大缓冲池的设置，假如不使用MTS（Multi-Threaded Server），建议在20—30M 足够了。这部分主要用来
保存并行查询时候的一些信息，还有就是RMAN 在备份的时候可能会使用到。如果设置了
MTS，则由于UGA部分要移入这里，则需要具体根据server process数量和相关会话内存参
数的设置来综合考虑这部分大小的设置。
Java_pool_size(JAVA池)
假如数据库没有使用JAVA，我们通常认为保留10—20M大小足够。事实上可以更少，
甚至最少只需要32k，但具体跟安装数据库的时候的组件相关(比如http server)。
shared_pool_size（共享池）
于是我们假定一个1G内存的系统，可能考虑设置该参数为100M，
2G 的系统考虑设置为150M,8G 的系统可以考虑设置为200—300M。
Da
现在我们来谈数据缓冲区，在确定了SGA 的大小并分配完了前面部分的内存后，其余
的，都分配给这部分内存。通常，在允许的情况下，我们都尝试使得这部分内存更大。这部
分内存的作用主要是缓存 DB BLOCK，减少甚至避免从磁盘上获取数据，在8i中通常是由
db_block_buffers*db_block_size 来决定大小的。如果我们设置了buffer_pool_keep 和
buffer_pool_recycle，则应该加上后面这两部分内存的大小。
PGA 的调整
关于内存参数的调整：
对于内存的调整，相对来说简单一些，我们首先可以针对数据缓冲区的大小来看。首先
观察命中率：
SQL> select value from v$sysstat where name ='physical reads';
VALUE
----------
7086
SQL> select value from v$sysstat where name ='physical reads direct';
VALUE
----------
15
SQL> select value from v$sysstat where name ='physical reads direct (lob)';
VALUE
----------
0
SQL> select value from v$sysstat where name ='consistent gets';
VALUE
----------
77894
SQL> select value from v$sysstat where name = 'db block gets';
VALUE
----------
11138
这里命中率的计算应该是
令 x = physical reads direct + physical reads direct (lob)
命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100
通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区
共享池的命中率：
SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache;
hit radio
----------
87.1601668
假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var ）或者增加内存
show parameter shared_pool_size;
调整shared_pool_size 大小
关于排序部分：
SQL> select name,value from v$sysstat where name like '%sort%';
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 3110
sorts (disk) 0
sorts (rows) 15703
假如我们发现sorts (disk)/ (sorts (memory)+ sorts (disk))的比例过高，则通常意味着
sort_area_size 部分内存较小，可考虑调整相应的参数。
关于log_buffer：
SQL> select name,value from v$sysstat
2 where name in('redo entries','redo buffer allocation retries');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 668
redo buffer allocation retries 0
假如 redo buffer allocation retries/ redo entries 的比例超过1%我们就可以考虑增大log_buffer
32bit 和 64bit 的问题
对于oracle 来说，存在着32bit与64bit的问题。这个问题影响到的主要是SGA的大小。
在32bit的数据库下，通常oracle只能使用不超过1.7G的内存，即使我们拥有12G的内存，
但是我们却只能使用1.7G，这是一个莫大的遗憾。假如我们安装64bit的数据库,我们就可以
使用很大的内存，我们几乎不可能达到上限。但是64bit 的数据库必须安装在64bit 的操作
系统上，可惜目前windows 上只能安装32bit的数据库，我们通过下面的方式可以查看数据
库是32bit 还是64bit：
SQL> select * from v$version;
————————————————————————————————————————————
前言
近来公司技术,研发都在问我关于内存参数如何设置可以优化oracle的性能,所以抽时间整理了这篇文档,以做参考.
目的
希望通过整理此文档,使大家对oracle内存结构有一个全面的了解,并在实际的工作中灵活应用,使oracle的内存性能达到最优配置，提升应用程序反应速度，并进行合理的内存使用.
内容
实例结构
oracle实例=内存结构+进程结构
oracle实例启动的过程，其实就是oracle内存参数设置的值加载到内存中，并启动相应的后台进程进行相关的服务过程。
进程结构
oracle进程=服务器进程+用户进程
几个重要的后台进程：
DBWR：数据写入进程.
LGWR:日志写入进程.
ARCH:归档进程.
CKPT:检查点进程(日志切换；上一个检查点之后，又超过了指定的时间；预定义的日志块写入磁盘；例程关闭，DBA强制产生,表空间offline)
LCKn（0-9）：封锁进程.
Dnnn:调度进程.
内存结构(我们重点讲解的)
内存结构=SGA（系统全局区）+PGA（程序全局区）
SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写
我们重点就是设置SGA，理论上SGA可占OS系统物理内存的1/2——1/3
原则：SGA+PGA+OS使用内存<总物理RAM
SGA=((db_block_buffers*blocksize)+(shared_pool_size+large_pool_size+java_pool_size+log_buffers)+1MB
1、SGA系统全局区.(包括以下五个区)
A、数据缓冲区:（db_block_buffers）存储由磁盘数据文件读入的数据。
大小: db_block_buffers*db_block_size
Oracle9i设置数据缓冲区为：Db_cache_size
原则：SGA中主要设置对象，一般为可用内存40%。
B、共享池:（shared_pool_size）:数据字典，sql缓冲，pl/sql语法分析.加大可提速度。
原则：SGA中主要设置对象，一般为可用内存10%
C、日志缓冲区:（log_buffer）存储数据库的修改信息.
原则：128K ---- 1M 之间，不应该太大
D 、JAVA池（Java_pool_size）主要用于JAVA语言的开发.
原则：若不使用java，原则上不能小于20M，给30M通常就够了
E、 大池（Large_pool_size） 如果不设置MTS，主要用于数据库备份恢复管理器RMAN。
原则：若不使用MTS,5---- 10M 之间，不应该太大
SGA=. db_block_buffers*db_block_size+ shared_pool_size+ log_buffer+Java_pool+size+large_pool_size
原则： 达到可用内存的55-58%就可以了.
2、PGA程序全局区
PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA 正相反PGA 是只被一个进程使用的区域，PGA 在创建进程时分配在终止进程时回收.
A、Sort_area_size 用于排序所占内存
B、Hash_area_size 用于散列联接，位图索引
这两个参数在非MTS下都是属于PGA ，不属于SGA,是为每个session单独分配的，在我们的服务器上除了OS + SGA,一定要考虑这两部分原则：OS 使用内存+SGA+并发执行进程数*(sort_area_size+hash_ara_size+2M) < 0.7*总内存
实例配置
一：物理内存多大
二：操作系统估计需要使用多少内存
三：数据库是使用文件系统还是裸设备
四：有多少并发连接
五：应用是OLTP 类型还是OLAP 类型
基本掌握的原则是， db_block_buffer 通常可以尽可能的大，shared_pool_size 要适度，log_buffer 通常大到几百K到1M就差不多了
A、如果512M RAM 单个CPU db_block_size 是8192 bytes
SGA=0.55*512M=280M左右
建议 shared_pool_size = 50M, db_block_buffer* db_block_size = 200M具体: shared_pool_size =52428800 #50M
db_block_buffer=25600 #200M
log_buffer = 131072 # 128k (128K*CPU个数)
large_pool_size=7864320 #7.5M
java_pool_size = 20971520 # 20 M
sort_area_size = 524288 # 512k (65k--2M)
sort_area_retained_size = 524288 # MTS 时 sort_area_retained_size = sort_area_size
B、如果1G RAM 单个CPU db_block_size 是8192 bytes
SGA=0.55*1024M=563M左右建议 shared_pool_size = 100M , db_block_buffer* db_block_size = 400M具体: shared_pool_size=104857600 #100M
db_block_buffer=51200 #400M
log_buffer = 131072 # 128k (128K*CPU个数)
large_pool_size=15728640 #15M
java_pool_size = 20971520 # 20 M
sort_area_size = 524288 # 512k (65k--2M)
sort_area_retained_size = 524288 # MTS 时 sort_area_retained_size = sort_area_size
C、如果2G 单个CPU db_block_size 是8192 bytes
SGA=0.55*2048M=1126.4M左右建议 shared_pool_size = 200M , db_block_buffer *db_block_size = 800M
具体: shared_pool_size=209715200 #200M
db_block_buffer=103192 #800M
log_buffer = 131072 # 128k (128K*CPU个数)
large_pool_size= 31457280 #30M
java_pool_size = 20971520 # 20 M
sort_area_size = 524288 # 512k (65k--2M)
sort_area_retained_size = 524288 # MTS 时 sort_area_retained_size = sort_area_size
假定64 bit ORACLE
内存4G
shared_pool_size = 200M , da
内存8G
shared_pool_size = 300M , da
内存 12G
shared_pool_size = 300M-----800M , da
参数更改方式
oracle8i：
主要都是通过修改oracle启动参数文件进行相关的配置
参数文件位置：
d:oracleadminDB_Namepfileinit.ora
按以上修改以上参数值即可。
Oracle9i:
两种方式：第一种是修改oracle启动参数文件后，通过此参数文件再创建服务器参数文件
第二种是直接运行oracle修改命令进行修改。
SQL>alter system set db_cache_size=200M scope=spfile;
SQL>alter system set shared_pool_size=50M scope=spfile;
另附：
内存优化：
SGA包括三个部分组成。数据缓冲区、日志缓冲区及共享池
数据缓冲区：大小由DB_Cache_Size参数决定。select name,value from v$parameter where name in('db_cache_size','db_block_size','shared_pool_size','sort_area_size');
select name value from v$sysstat where name in ('db block gets','consistent gets','physical reads');
其中理想的情况：physical reads接近为0 也就是说数据全部来自数据缓冲区。
db block gets表示从内存读取数据 consistent gets表示读取一致性数据。
select child#,sleeps/gets ratio from v$latch_children where name ='cache buffers lru chain';
可以检测到数据缓冲区是否有空闲，得到的空闲冲突比例应该接近0
数据缓冲区内存是否足够大，效率如何，可以通过计算命中率来确定。用磁盘物理读的数据与内在缓冲区的数据进行计算，就得出了命中率。一般要求在90％以上，否则必须增加数据缓冲区的大小。
共享池大小由参数shared_pool_size决定。在共享池里，SQL区比数据缓存消耗较大的内存区域。
select name ,bytes from v$sgastat order by name;
select * from v$sgastat where name = 'free memory';
free memory是空闲内存，是共享池中可以使用的字节数，dictionary cache 是数据字典缓冲区，library cache是库缓冲区，sql area是SQL缓冲区。
空闲百分比 ＝ （空闲内存/共享池字节数）×100％ 如果小于20％ 则应增大共享池参数Shared_Cache_size
排序区调整：
select name,value from v$sysstat wehre name like '%sort%"; sorts(memory)表示内存排序量， sorts(disk)表示磁盘序量。
失败率 ＝ sort(disks)/sorts(memory) ×100％ 排序区的失败率不得小于5％，否则必须增加排序sort_area_size的大小，使得Oracle尽量使用内存排序。
磁盘IO的调整就是把数据文件放在多个不同的磁盘上。
减少了IO的操作次数就减少了CPU的需求。
下面是我一些实际操作的过程：
alter system set db_cache_size=4G scope=spfile;
alter system set open_cursors=2048 scope=spfile;
alter system set pga_aggregate_target=512M scope=spfile;
alter system set sga_max_size=12G scope=spfile;
alter system set shared_pool_size=1G scope=spfile;
重启一下Oracle就可以了。注意这里配置的机器内存是24G。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
