Read Oracle RMAN 11g Backup and Recovery Online
Authors: Robert Freeman
Chapter 18: Performance Tuning RMAN Backup and Recovery Operations
457
V$ACTIVE_SESSION_HISTORY
Oracle Database offers a feature called Active Session History (ASH) that provides historical session-related information on Oracle database operations. ASH is a very powerful tool that can be used to review historical runtime information, providing information that can be effective for use when tuning RMAN operations. One use of ASH is to look at wait times that various sessions have experienced, and what the associated waits are. Here is an example of such a query:
-- First we use V$SESSION to get the session
-- specific information if possible.
select sid, serial#, program
from v$session
where lower(program) like '%rman%';
SID SERIAL# PROGRAM
------ ---------- ----------
125 149 rman.exe
128 130 rman.exe
134 164 rman.exe
-- Note that runtime session information may not always be available.
Set lines 132
Column session id format 999 heading "SESS|ID"
Column session serial# format 999 heading "SESS|SER|#"
Column event format a40
Column total waits format 9,999,999,999 heading "TOTAL|TIME|WAITED|MICRO"
Select session id, session serial#, Event, sum(time waited) total waits
From v$active session history
Where sample time > sysdate - 1
-- The next line can be remarked out if you don't know
-- the session information.
And session id||session serial# in (120102, 128102, 134129)
And program like '%rman%'
And session state 'WAITING' And time waited > 0
Group by session id, session serial#, Event
Order by session id, session serial#, total waits desc;
TOTAL
SESS TIME
SESS SER WAITED
ID # EVENT MICRO
---- ---- ---------------------------------------- --------------
125 149 control file single write 1,388,961
125 149 control file sequential read 45,964
125 149 control file parallel write 3,789
128 130 RMAN backup & recovery I/O 192,263,005
128 130 control file single write 1,095,253
128 130 control file parallel write 529,012
V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO
The V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO views contain detailed information on RMAN asynchronous and synchronous backup operations. These views are transitory in nature and are cleared each time the database is shut down. These views contain a row for each asynchronous or synchronous backup or recovery operation. Perhaps the biggest benefit from
458
Part III: Using RMAN Effectively
this view is the EFFECTIVE_BYTES_PER_SECOND column in rows where the TYPE column is set to AGGREGATE. This column represents the rate at which the objects are being backed up or recovered in bytes per second. This number should be close to the listed read/write rate of your backup hardware. If the EFFECTIVE_BYTES_PER_SECOND column value is much lower than the rated speed of your backup hardware, then you should be looking for some sort of problem with your backup process. The problem could be caused by any number of things, from an overburdened CPU to a saturated network, or perhaps a configuration issue with the MML
interface to your vendor’s backup solution.
NOTE
If you see data in V$BACKUP_SYNC_IO, this implies that you are
not doing asynchronous backups. If this is the case, you need to
investigate why your backups are occurring in synchronous fashion.
Here is an example of a query against V$BACKUP_ASYNC_IO and its results after a database backup has been completed:
select device type "Device", type, filename,
to char(open time, 'mm/dd/yyyy hh24:mi:ss') open,
to char(close time, 'mm/dd/yyyy hh24:mi:ss') close,
elapsed time ET, effective bytes per second EPS
from v$backup async io
where close time > sysdate 30
order by close time desc;
Device TYPE
FILENAME
OPEN CLOSE ET EPS
DISK INPUT
/oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 10
/o1 mf nnndf TAG20090910T110421 5blddpg9 .bkp
09/10/2009 11:15:35 09/10/2009 11:16:38 6300 4078836
DISK OUTPUT
/ora01/oracle/rob1/rob1/system01.dbf
09/10/2009 11:15:35 09/10/2009 11:16:37 6200 11838761
DISK AGGREGATE
09/10/2009 11:15:35 09/10/2009 11:16:37 6200 21986271
DISK OUTPUT
/ora01/oracle/rob1/rob1/sysaux01.dbf
09/10/2009 11:15:36 09/10/2009 11:16:24 4800 13107200
Chapter 18: Performance Tuning RMAN Backup and Recovery Operations
459
DISK AGGREGATE
09/10/2009 11:10:54 09/10/2009 11:11:06 1200 42269355
DISK OUTPUT
/ora01/oracle/rob1/rob1/sysaux01.dbf
09/10/2009 11:10:55 09/10/2009 11:11:06 1100 9245789
DISK INPUT
/oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 10
/o1 mf nnndf TAG20090910T110421 5blddpg9 .bkp
09/10/2009 11:10:54 09/10/2009 11:11:05 1100 1429132
DISK OUTPUT
/ora01/oracle/rob1/rob1/system01.dbf
09/10/2009 11:10:54 09/10/2009 11:11:05 1100 9341114
DISK OUTPUT
/ora01/oracle/rob1/rob1/undotbs01.dbf
09/10/2009 11:10:55 09/10/2009 11:11:00 500 58720256
DISK OUTPUT
/ora01/oracle/rob1/rob1/users01.dbf
09/10/2009 11:10:55 09/10/2009 11:10:56 100 9175040
10 rows selected.
In this case, we can see the effective transfer rate from the database to the backup set by RMAN. Further, we can see the name of the datafile that was backed up and the actual start and stop time of the backup itself.
Another way to measure the efficiency of your backup process is to use the V$BACKUP_
ASYNC_IO view. This view has several columns of interest, which are listed and described in Table 18-1.
Column Name
Represents
IO_COUNT
The total number of I/O counts
READY
The number of asynchronous I/O calls for which a buffer was available
immediately
SHORT_WAITS
The number of times that a buffer was requested and not available but
became available after a nonblocking poll for I/O completion
LONG_WAITS
The number of times that a buffer was requested and not available and
Oracle had to wait for the I/O device
TABLE 18-1
V$BACKUP_ASYNC_IO Column Descriptions
460
Part III: Using RMAN Effectively
To determine whether there is an I/O problem, we can look at the ratio of I/Os to long waits (LONG_WAITS/IO_COUNTS), as shown in the following code segment:
select b.io count, b.ready, b.short waits, b.long waits,
b.long waits/b.io count, b.filename
from v$backup async io b;
IO COUNT READY SHORT WAITS LONG WAITS B.LONG WAITS/B.IO COUNT
---------- ---------- ----------- ---------- -----------------------
FILENAME
----------------------------------------
2 1 0 1 .5
D:\ORACLE\ADMIN\RECOVER\ARCH\ARC00052.001
2 1 0 1 .5
D:\ORACLE\ADMIN\RECOVER\ARCH\ARC00046.001
2 1 0 1 .5
D:\ORACLE\ADMIN\RECOVER\ARCH\ARC00051.001
2 1 0 1 .5
D:\ORACLE\ADMIN\RECOVER\ARCH\ARC00050.001
171 107 12 52 .304093567
D:\ORACLE\ORADATA\RECOVER\SYSTEM01.DBF
11 8 2 1 .090909091
D:\ORACLE\ORADATA\RECOVER\RECOVER UNDOTBS 01.DBF
6 4 0 2 .333333333
D:\ORACLE\ORADATA\RECOVER\TOOLS01.DBF
6 3 0 3 .5
D:\ORACLE\ORADATA\RECOVER\USERS01.DBF
6 4 1 1 .166666667
D:\ORACLE\ORADATA\RECOVER\RECOVER TESTRBS 01.DBF
3 1 0 2 .666666667
D:\ORACLE\ORADATA\RECOVER\INDX01.DBF
2 1 0 1 .5
D:\ORACLE\ORADATA\RECOVER\TOOLS02.DBF
The numbers returned by this query clearly indicate some sort of I/O bottleneck is causing grief (in this case, it’s an overly taxed, single CPU).
Tracing RMAN Sessions
Sometimes using views is not enough to track down problems. Sometimes you need to get down to the nitty and the gritty. This means tracing the Oracle sessions related to the RMAN operation.
This can be somewhat complex, because RMAN will actually create a number of Oracle sessions in order to complete its work. In this section, we will introduce you to the notion of tracing, and how to start tracing. Tracing, like tuning, is a topic unto itself. If you find yourself needing to actually trace RMAN sessions, then you have a serious problem. In these cases, you will want to do some more research on Oracle tracing, and consult with Oracle.
Depending on the nature of the RMAN problem, you may need to trace one or all of those sessions. There are several ways to start tracing RMAN sessions. Generally, we try to start with the easiest method and then move to the more complicated method as required.
Tracing in Oracle is done by enabling an Oracle event. An Oracle event is something a DBA or developer “sets” in order to get Oracle to do something that it does not normally do. Each Chapter 18: Performance Tuning RMAN Backup and Recovery Operations
461