TUNING RMAN PART-II

In continuation with my earlier post Tuning RMAN Part-I,, in this post, I will demonstrate the usage of asynchronous i/o to improve the performance of RMAN operations.

RMAN can perform i/o in two ways : Synchronous and Asynchr nous.

We are aware that synchronous i/o is a bottleneck and we should always aim at having asynchronous i/o for better performance. Some platforms support asynchronous by default while others don’t. On the platforms which do not support asynchronous i/o, we can simulate asynch i/o by setting parameter dbwr_io_slaves.

If i/o is performed asynchronously, we will have records in v$backup_async_io

If i/o is performed synchronously, we will have records in v$backup_sync_io

Let’s have a demonstration :

The parameter disk_asynch_io decides whether asynchronous I/O is enabled at O/S level. By default its value is true if OS supported asynch i/o.

Currently my database is runing on windows server 2008 and it supports asynchronous i/o.

Let’s verify by looking at the default value of disk_asynch_io parameter.

SYS>sho parameter disk_asynch_io;NAME
TYPE VALUE

———————————————– ———–

disk_asynch_io boolean TRUE

When asynchronous i/o is performed at the O/S level, the buffers needed by RMAN are allocated from PGA. Let’s verify this by checking the session pga memory as a backup progresses.

– Take backup using multiplexing level = 4 – this needs to allocate 16m buffers from pga

–

– While backup is going on , issue the next query repeatedly which monitors pga usage by rman session. Note that pga consumption increases as backup progresses.

rman>RUN
{
allocate channel c1 device type disk;
backup datafile 1,2,3,4 filesperset 4 ;
}

– Query the pga memory usage by rman session .

– Note that pga consumption increases as backup progresses.

– ALso note that size of buffers allocated = 18 MB (41-23) which is slightly > 16 MB

SQL> col name for a30
set line 500
select s.sid, n.name , s.value/1024/1024 session_pga_mb
from v$statname n, v$sesstat s
where s.sid = (select sess.SID
FROM V$PROCESS p, V$SESSION sess
WHERE p.ADDR = sess.PADDR
AND CLIENT_INFO LIKE ‘%rman%’)
and n.name = ‘session pga memory’
and s.statistic# = n.statistic#;

SID NAME SESSION_PGA_MB

—————————————- ————–

14 session pga memory 23.1208076

SQL> /

SID NAME SESSION_PGA_MB

—————————————- ————–

14 session pga memory 41.1833076

If OS does not support asynchronous I/O, we can simulate by setting parameter dbwr_io_slaves to a non zero value.

4 slave processes will be allocated irrespective of the value of the parameter dbwr_io_Slaves. IN this case, buffers for RMAN will be allocated from large pool.

If large pool is sized to a value lower than the size of the buffers required, RMAN will switch to synchronous I/O and write a message to the alert log.

Let’s verify this.

– Check current values of sga_target/memory_target which will be used later to return to the original state.

- Note the values

SQL>sho parameter sga_target
sho parameter memory_target

– Disable AMM/ASMM

– Disable asynchronous I/O at OS level –

– SImulate async IO by configuring slave processes –

– Size large pool to a size smaller than 16M (say 8M)

SQL>alter system set “_memory_imm_mode_without_autosga”=false scope=both;

alter system set memory_target = 0;

alter system set sga_target = 0;

alter system set disk_asynch_io=false scope=spfile;

alter system set dbwr_io_slaves=4 scope=spfile;

alter system set large_pool_size=8m scope = spfile;

startup force;

– Check that the parameters have been set to the specified values

SQL> sho parameter sga_targetsho parameter memory_target

sho parameter disk_asynch_io

sho parameter dbwr_io_slaves

sho parameter large_pool_size

NAME TYPE VALUE

———————————————– ———–

sga_target big integer 0

memory_target big integer 0

disk_asynch_io boolean FALSE

dbwr_io_slaves integer 4

large_pool_size big integer 8M

– Take backup using multiplexing level = 4

– this needs to allocate 16m buffers from large pool –

rman>backup datafile 2,3,4,5 filesperset 4;

– Check that i/o is not asynchronous –

SQL>select filename, buffer_size, buffer_count, type, status from v$backup_async_io
where status=’IN PROGRESS';

no rows selected

– check that synchronous i/o is taking place although async i/o was simulated –

SQL> col filename for a30 word_wrapped

select FILENAME, BUFFER_SIZE, BUFFER_COUNT, TYPE,
STATUS from v$backup_sync_io

where status = ‘IN PROGRESS';

FILENAME BUFFER_SIZE BUFFER_COUNT TYPE STATUS

—————————————– ———— ——— ———–

C:\APP\ADMINISTRATOR\ORADATA\O 1048576 4 INPUT IN PROGRESS

RCL\SYSAUX01.DBF

C:\APP\ADMINISTRATOR\ORADATA\O 1048576 4 INPUT IN PROGRESS

RCL\UNDOTBS01.DBF

C:\APP\ADMINISTRATOR\ORADATA\O 1048576 4 INPUT IN PROGRESS

RCL\EXAMPLE01.DBF

C:\APP\ADMINISTRATOR\ORADATA\O 1048576 4 INPUT IN PROGRESS

RCL\USERS01.DBF

– check that alert log indicates that sync i/o will be used –

ORA-04031: unable to allocate 1056812 bytes of shared memory (“large pool”,”unknown

object”,”large pool”,”KSFQ Buffers”)

ksfqxcre: failure to allocate shared memory means sync I/O will be used

Now let us increase the size of large pool to a value > 16M (say 20M) and the issue the

same backup. This time asynchronous i/o should be used. we will verify this by checking

that records appear in v$backup_async_io

SQL>alter system set large_pool_size=20m scope=spfile;

startup force;

sho parameter large_pool_size;

NAME TYPE VALUE

———————————————– ————-

large_pool_size big integer 20M

– Take backup using multiplexing level = 4

- this needs to allocate 16m buffers from large pool –

rman>backup datafile 2,3,4,5 filesperset 4;

– Check that i/o is not asynchronous –

SQL> select filename, buffer_size, buffer_count, type, status from v$backup_async_io

where status=’IN PROGRESS';

FILENAME BUFFER_SIZE BUFFER_COUNT TYPE STATUS

—————————————– ———— ——— ———–

C:\APP\ADMINISTRATOR\ORADATA\O 1048576 4 INPUT IN PROGRESS

RCL\SYSAUX01.DBF

C:\APP\ADMINISTRATOR\ORADATA\O 1048576 4 INPUT IN PROGRESS

RCL\UNDOTBS01.DBF

C:\APP\ADMINISTRATOR\FLASH_REC 1048576 4 OUTPUT IN PROGRESS

OVERY_AREA\ORCL\BACKUPSET\2012

_09_14\O1_MF_NNNDF_TAG20120914

T100407_855DY50Z_.BKP

Conclusion:

- The parameter disk_asynch_io decides whether asynchronous I/O is enabled at O/S level. By default its value is true if OS supported asynch i/o.

- When asynchronous i/o is performed at the O/S level, the buffers needed by RMAN are allocated from PGA.

- If OS does not support asynchronous I/O, we can simulate it by setting parameter dbwr_io_slaves to a non zero value. 4 slave processes will be allocated irrespective of the value of the parameter dbwr_io_Slaves.

- In the case of simulated asynchronous i/o, , buffers for RMAN will be allocated from large pool.

If large pool is sized to a value more than the required buffers, asynchronous i/o will be performed.

If large pool is sized to a value lower than the size of the buffers required, RMAN will switch to synchronous I/O and write a message to the alert log.

- The no. of buffers allocated by RMAN per file depends upon the level of multiplexing.

Multiplexing level Allocation rule

Level <= 4 1 MB buffers are allocated so that the total buffer size for all input files is 16 MB.

4 < Level <= 8 512 KB are allocated so that the total buffer size for all files is less than 16 MB.

Level > 8 RMAN allocates four 128 KB disk buffers per channel for each file, so that the total size is 512

KB per channel for each file.

- We can calculate the size of the buffers required by RMAN as follows:

Multiply the total bytes for each data file by the number of data files that are being concurrently accessed by the channel, and then multiply this number by the number of channels.

Assume that you use one channel to back up four data files, and use the settings that are shown above. In this case, multiply as follows to obtain the total size of the buffers that are allocated for the backup:

4 MB per data file * 1 channel * 4 data files per channel = 16 MB

- If asynchronous i/o is supported by the O/s, we should size PGA to a value more than the size of the buffers required by RMAN.

- If asynchronous i/o is not supported by O/S and we are simulating it, we should size large pool to a value more than the size of the buffers required by RMAN.

— CLEANUP —

SQL>drop tablespace rman1 including contents and datafiles;

drop tablespace rman2 including contents and datafiles;

drop tablespace rman3 including contents and datafiles;

drop tablespace rman4 including contents and datafiles;

alter system set disk_asynch_io=true scope=spfile;

alter system set memory_target = <> scope=spfile;

alter system set sga_target = <> scope=spfile;

startup force;

————————————————————————————————

Related links:

Home