背景
oracle10g sga自动或手工管理情况下，在一些特定情况下，还要需要你指定大池，JAVA池，流池的大小，以更好发挥数据库性能；本文我们主要学习如何在基于RMAN备份恢复时，配置大池大小
结论
1,默认情况是开启磁盘异步IO的2,从官方手册可知，如果磁盘不支持异步IO，必须要配置dbwr_io_slaves，调整磁盘异步IO方式，必须要重启库方可生效
3,大池具体使用分配，可从v$sgastat查询，其占用的组件为KSFQ Buffers
4,启用大池必须同时配置large_pool_size及disk_asynch_io和dbwr_io_slaves
5,ORACLE知识点联系是非常紧密的，呈网状结构，有时为了处理一个问题，与其相关的知识点全部都要了解或精通方可，不然易卡住
6,本文仅基于RMAN情况下如何配置大池，不涉及并行查询以及共享服务器模式，其在其它文章会有论述
7,对于RMAN磁盘备份，其大池配置计算公式为(man备份分配的通道个数 * (16 + 4)) +16 Mb
对于RMAN磁带备份，其大池配置计算公式为rman备份分配的通道个数*(16mb + (4 * 备份磁带的buffer大小))
8,如果以PFILE启动，移除大池配置，则大池配置为0
9,大池分配最小值为32K
测试
1,大池是一个动态参数，可实时调整，它是一个SGA中的可用组件，仅在使用时才配置它
SQL> show parameter large_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 16M
SQL> select name,bytes/1024/1024 as mb,RESIZEABLE as mb from v$sgainfo;
NAME MB MB
-------------------------------- ---------- ---
Large Pool Size 16 Yes
2,即使要配置大池，先了解下何时需要配置大池
大池和共享池不同，不会使用LRU列表，也就是说大池中的数据不会老化。
在如下几种情况可能要考虑配置大池：
A,并行查询
如果在未配置大池情况下，ORACLE会从共享池中拿出一部分内存，用于缓存并行查询产生的一些消息信息
B，RMAN使用
如果在未配置大池情况下，RMAN备份恢复会从共享池中拿出一部分内存，用于RMAN IO读写操作(这样作的目的， 就是防止对于共享池的冲击)
如果alert发现：
ksfqxcre: failure to allocate shared memory means sync I/O will be used whenever async I/O to file not supported natively
表明未开启disk io slave,即未配置dbwr_io_slaves.且共享内存不足;可配置large_pool_size
如何配置large_pool_size?
1,可选项：查看v$sgastat.pool确认对象驻存在内存哪个pool,到底是shared pool或是large pool
(如未配置后者rman备份采用前者)
2,alter sysetm set large_pool_size=分配的备份channels*(16mb + (4 * size_of_tape_buffer));
对于磁盘备份
LARGE_POOL_SIZE = (man备份分配的通道个数 * (16 + 4)) +16 Mb
对于磁带备份
(解释下：即大池=rman备份分配的通道个数*(16mb + (4 * 备份磁带的buffer大小))
3,配置完后重启rman
C,启用共享服务器
在这种模式下，每个客户端会话对应的一些会话内存，也会占用共享池中一部分内存
SQL> select pool,name,bytes/1024/1024 as mb from v$sgastat where pool='large pool';
POOL NAME MB
------------ -------------------------- ----------
large pool free memory 16
3，在RMAN备份时，发现没有使用大池
SQL> select pool,name,bytes/1024/1024 as mb from v$sgastat where pool='large pool';
POOL NAME MB
------------ -------------------------- ----------
large pool free memory 16
RMAN> backup database format='/home/ora10g/rman_backup/%u_%t.bak';
Starting backup at 21-SEP-15
using target database control file instead of recovery catalog
内容略
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 21-SEP-15
channel ORA_DISK_1: finished piece 1 at 21-SEP-15
piece handle=/home/ora10g/rman_backup/03qhoekc_891042444.bak tag=TAG20150921T234625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 21-SEP-15
SQL> select component,current_size/1024/1024 as current_size,min_size/1024/1024 as min_size,max_size/1024/1024 as max_size,oper_count,last_oper_type,last_oper_mode from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
------------------------------ ------------ ---------- ---------- ---------- ------------- ---------
shared pool 112 112 112 0 STATIC
large pool 16 16 16 0 STATIC
肯定是有什么隐含参数控制，或者还有其它参数要配置,大池方可生效
从如下隐含参数可知，大池最小分配大小_large_pool_min_alloc 为64k,默认并行查询不会使用大池，所以必须要配置_PX_use_large_pool 为true，仍未找到与RMAN操作相关的大池配置参数
_backup_io_pool_size指定大池必须空闲物理内存为1M
select
ksppinm as name_1,
ksppstvl as value_1,
ksppdesc as desc1
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and lower(x.ksppinm) like '%¶meter%';
NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
__large_pool_size 33554432 Actual size in bytes of large pool
large_pool_size 33554432 size in bytes of large pool
_large_pool_min_alloc 65536 minimum allocation size in bytes for the large allocation pool
_PX_use_large_pool FALSE Use Large Pool as source of PX buffers
_backup_io_pool_size 1048576 memory to reserve from the large pool
配置大池参数但仍未在RMAN期间使用大池，经查阅官方册也可能与磁盘异步IO有关
Oracle? Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)
Part Number B14191-02
原文内容如下：
Step 2: If You Use Synchronous Disk I/O, Set DBWR_IO_SLAVES
If and only if your disk does not support asynchronous I/O, then try setting the DBWR_IO_SLAVES initialization parameter to a nonzero value. Any nonzero value for DBWR_IO_SLAVES causes a fixed number (four) of disk I/O slaves to be used for backup and restore, which simulates asynchronous I/O. If I/O slaves are used, I/O buffers are obtained from the SGA. The large pool is used, if configured. Otherwise, the shared pool is used.
Note: By setting DBWR_IO_SLAVES, the database writer processes will use slaves as well. You may need to increase the value of the PROCESSES initialization parameter.
Step 3: If You Fail to Allocate Shared Memory, Set LARGE_POOL_SIZE
Set this initialization parameter if the database reports an error in the alert.log stating that it does not have enough memory and that it will not start I/O slaves. The message should resemble the following:
ksfqxcre: failure to allocate shared memory means sync I/O will be used whenever async I/O to file not supported natively
When attempting to get shared buffers for I/O slaves, the database does the following:
If LARGE_POOL_SIZE is set, then the database attempts to get memory from the large pool. If this value is not large enough, then an error is recorded in the alert log, the database does not try to get buffers from the shared pool, and asynchronous I/O is not used.
If LARGE_POOL_SIZE is not set, then the database attempts to get memory from the shared pool.
If the database cannot get enough memory, then it obtains I/O buffer memory from the PGA and writes a message to the alert.log file indicating that synchronous I/O is used for this backup.
The memory from the large pool is used for many features, including the shared server (formerly called multi-threaded server), parallel query, and RMAN I/O slave buffers. Configuring the large pool prevents RMAN from competing with other subsystems for the same memory.
Requests for contiguous memory allocations from the shared pool are usually small (under 5 KB) in size. However, it is possible that a request for a large contiguous memory allocation can either fail or require significant memory housekeeping to release the required amount of contiguous memory. Although the shared pool may be unable to satisfy this memory request, the large pool is able to do so. The large pool does not have a least recently used (LRU) list; the database does not attempt to age memory out of the large pool.
Use the LARGE_POOL_SIZE initialization parameter to configure the large pool. To see in which pool (shared pool or large pool) the memory for an object resides, query V$SGASTAT.POOL.
The formula for setting LARGE_POOL_SIZE is as follows:
LARGE_POOL_SIZE = number_of_allocated_channels *
(16 MB + ( 4 * size_of_tape_buffer ) )
默认是启用磁盘异步IO
SQL> show parameter asyn
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE
tape_asynch_io boolean TRUE
关闭磁盘异步IO
SQL> show parameter async
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE
tape_asynch_io boolean TRUE
SQL> show parameter large_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 16M
SQL>
SQL> alter system set disk_asynch_io=false;
alter system set disk_asynch_io=false
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
关闭磁盘异步IO，还是不使用大池
SQL> alter system set disk_asynch_io=false scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 2095800 bytes
Variable Size 352322888 bytes
Database Buffers 771751936 bytes
Redo Buffers 31457280 bytes
Database mounted.
Database opened.
SQL> show parameter large_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 16M
SQL> show parameter async
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean FALSE
再试下根据大池配置公式，看行不行
对于磁盘备份
LARGE_POOL_SIZE = (man备份分配的通道个数 * (16 + 4)) +16 Mb
SQL> alter system set large_pool_size=40m;
System altered.
SQL> show parameter large_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 48M
SQL>
仍旧没有使用大池，肯定还是有其它的参数没有配置，或者配置不对
即使关闭了磁盘异步IO，则要打开DBWR的SLAVE子进程
参考如下的官方手册，所以即使关闭了磁盘异步IO，此时就启用磁盘的同步IO，所以就要同时配置参数backup_tape_io_slaves 为非0，此参数默认为0
Step 2: If You Use Synchronous Disk I/O, Set DBWR_IO_SLAVES
If and only if your disk does not support asynchronous I/O, then try setting the DBWR_IO_SLAVES initialization parameter to a nonzero value. Any nonzero value for DBWR_IO_SLAVES
causes a fixed number (four) of disk I/O slaves to be used for backup and restore, which simulates asynchronous I/O. If I/O slaves are used, I/O buffers are obtained from the SGA.
The large pool is used, if configured. Otherwise, the shared pool is used.
Note: By setting DBWR_IO_SLAVES, the database writer processes will use slaves as well. You may need to increase the value of the PROCESSES initialization parameter.
SQL> show parameter slave
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
backup_tape_io_slaves boolean FALSE
dbwr_io_slaves integer 0
SQL> alter system set dbwr_io_slaves=2;
alter system set dbwr_io_slaves=2
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set dbwr_io_slaves=2 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 2095800 bytes
Variable Size 352322888 bytes
Database Buffers 771751936 bytes
Redo Buffers 31457280 bytes
Database mounted.
Database opened.
SQL> show parameter disk_asynch_io
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean FALSE
SQL> show parameter dbwr_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dbwr_io_slaves integer 2
SQL>
SQL> show parameter large_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 48M
这下终于使用大池了
SQL> select pool,name,bytes/1024/1024 as mb from v$sgastat where pool='large pool';
POOL NAME MB
------------ -------------------------- ----------
large pool free memory 27.9140625
large pool KSFQ Buffers 20.0859375 --使用大池当前数量
可见大池使用是动态调整的，如果RMAN使用完毕，则会回收出来
SQL> select pool,name,bytes/1024/1024 as mb from v$sgastat where pool='large pool';
POOL NAME MB
------------ -------------------------- ----------
large pool free memory 43.9804688
large pool KSFQ Buffers 4.01953125
7，如果使用PFILE启动，不配置大池参数，大池参数值是多少呢,可见直接禁用了大池，参数值变成0
SQL> create pfile='/home/ora10g/non_large.ora' from spfile;
File created.
[ora10g@seconary ~]$ more /home/ora10g/non_large.ora|grep -i --color large_pool
#ora10g.__large_pool_size=16777216
#*.large_pool_size=16777216
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/ora10g/non_large.ora'
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 2095800 bytes
Variable Size 352322888 bytes
Database Buffers 771751936 bytes
Redo Buffers 31457280 bytes
Database mounted.
Database opened.
SQL> show parameter large_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0
SQL>
SQL> select name,bytes/1024/1024 as mb,RESIZEABLE as mb from v$sgainfo;
NAME MB MB
-------------------------------- ---------- ---
Fixed SGA Size 1.99871063 No
Redo Buffers 30 No
Buffer Cache Size 736 Yes
Shared Pool Size 112 Yes
Large Pool Size 0 Yes
SQL> select pool,name,bytes/1024/1024 as mb from v$sgastat where pool='large pool';
no rows selected
个人简介
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通4G数据库性能分析与优化
中国联通4G数据库性能分析与优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1806323/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1806323/
4135
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
