背景

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 '%&parameter%';

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資料庫效能分析與最佳化

中國聯通CRM資料庫效能最佳化

中國移動10086電商平臺資料庫部署及最佳化

湖南老百姓大藥房ERR資料庫sql最佳化專案

四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化

四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化

北京高鐵訊號監控系統RAC資料庫部署及最佳化

河南宇通客車資料庫效能最佳化

中國電信電商平臺核心採購模組表模型設計及最佳化

中國郵政儲蓄系統資料庫效能最佳化及sql最佳化

北京302醫院資料庫遷移實施

河北廊坊新奧data guard部署及最佳化

山西公安廳身份證審計資料庫系統故障評估

國家電網上海災備專案4 node rac+adg
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案

聯絡方式：

手機：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/

oracle10g在sga手工管理mmm模式如何配置大池large_pool_size(一）

背景

結論

測試

個人簡介

相關文章