Oracle RMAN 11g Backup and Recovery (151 page)

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