RMAN – V$BACKUP_ASYNC_IO

SELECT TYPE,TO_CHAR(OPEN_TIME,’dd/mm/yyyy hh:mi’),TO_CHAR(CLOSE_TIME,’dd/mm/yyyy hh:mi’), SHORT_WAITS,LONG_WAITS,IO_COUNT,LONG_WAITS/IO_COUNT,effective_bytes_per_second, FILENAME
FROM V$BACKUP_ASYNC_IO

You can check the RMAN CHANNEL PERFORMANCE using this query

Wait Event – Info

Some of the most common wait events, and what they mean:

 

Wait Event Description
enqueue The process is waiting on an enqueue (a lock you can see in v$lock).
This commonly occurs when one user is trying to update a row in a table
that is currently being updated by another user.
library cache pin  The process wants to pin an object in memory in the library cache for
examination, ensuring no other processes can update the object at the same
time. This happens when you are compiling or parsing a PL/SQL object or
a view.
library cache load lock  The process is waiting for the opportunity to load an object or a piece
of an object into the library cache. (Only one process can load an object
or a piece of an object at a time.)
latch free  The process is waiting for a latch held by another process. (This wait
event does not apply to processes that are spinning while waiting for a
latch; when a process is spinning, it is not waiting.)
buffer busy waits  The process wants to access a data block that is currently not in memory,
but another process has already issued an I/O request to read the block
into memory. (The process is waiting for the other process to finish bringing
the block into memory.)
control file sequential read  The process is waiting for blocks to be read from a control file.
control file parallel write The process has issued multiple I/O requests in parallel to write blocks
to all control files, and is waiting for all of the writes to complete.
log buffer space  The process is waiting for space to become available in the log buffer
(Space becomes available only after LGWR has written the current contents
of the log buffer to disk.) This typically happens when applications generate
redo faster than LGWR can write it to disk.
log file sequential read  The process is waiting for blocks to be read from the online redo log
into memory. This primarily occurs at instance startup and when the ARCH
process archives filled online redo logs.
log file parallel write  The process is waiting for blocks to be written to all online redo
log members in one group. LGWR is typically the only process to see this
wait event. It will wait until all blocks have been written to all members.
log file sync  The process is waiting for LGWR to finish flushing the log buffer to
disk. This occurs when a user commits a transaction. (A transaction is
not considered committed until all of the redo to recover the transaction
has been successfully written to disk.)
db file scattered read  The process has issued an I/O request to read a series of contiguous
blocks from a data file into the buffer cache, and is waiting for the operation
to complete. This typically happens during a full table scan or full index
scan.
db file sequential read  The process has issued an I/O request to read one block from a data
file into the buffer cache, and is waiting for the operation to complete.
This typically happens during an index lookup or a fetch from a table by
ROWID when the required data block is not already in memory. Do not be
misled by the confusing name of this wait event!
db file parallel read  The process has issued multiple I/O requests in parallel to read blocks
from data files into memory, and is waiting for all requests to complete.
The documentation says this wait event occurs only during recovery, but
in fact it also occurs during regular activity when a process batches many
single block I/O requests together and issues them in parallel. (In spite
of the name, you will not see this wait event during parallel query or
parallel DML. In those cases wait events with PX in their names occur instead.)
db file parallel write  The process, typically DBWR, has issued multiple I/O requests in parallel
to write dirty blocks from the buffer cache to disk, and is waiting for
all requests to complete.
direct path read, direct path write The process has issued asynchronous I/O requests that bypass the buffer
cache, and is waiting for them to complete. These wait events typically
involve sort segments.

HowTo: Remove parameters from the spfile

* How do you remove an SPFILE parameter (not change the value of, but actually purge it outright)?

* Use “ALTER SYSTEM RESET …” (For database versions 9i and up)

Syntax:

* ALTER SYSTEM RESET PARAMETER SID=’SID|*’
* ALTER SYSTEM RESET “_TRACE_FILES_PUBLIC” SCOPE=SPFILE SID=’*’;

NOTE: The “SID=’SID|*'” argument is REQUIRED!

DBWR_IO_SLAVES & DB_WRITER_PROCESSES

Paraphrased from the Oracle Database Performance Tuning Guide for 10g
R2:
Configure multiple database writer processes, or using I/O slaves, is useful when the transaction rates are high or when the buffer cache is so large that a single DBWn process cannot keep up with the load. Multiple DB writer processes are recommended for systems with multiple CPUs (at least one DB writer for every 8 CPUs) – configure with DB_WRITER_PROCESSES.

If not practical to use multiple DBWR processes, use DBWR_IO_SLAVES to distribute the load over multiple slave processes – configure
DBWR_IO_SLAVES. DBWR_IO_SLAVES is intended for scenarios where multiple DB_WRITER_PROCESSES cannot be used, for example in systems with a single CPU. I/O slaves are also useful when asynchronous IO is not available, because the multiple I/O slaves simulate non-blocking, asynchronous requests by freeing DBWR to continue identifying blocks in the cache to be written.
Implementing DBWR_IO_SLAVES forces only one DBWR process to start.

If the system does not support asynchronous I/O, or if asynchronous I/O is already configured and there is still a DBWR bottleneck, configure multiple DBWR processes. Multiple DBWr processes should deliver more throughput than the same number of I/O slaves. I/O slaves have been depreciated.

RMAN- Backup Validate & Restore Validate

BACKUP VALIDATE reads all of the specified files but does not produce any output files. RESTORE VALIDATE reads all of the backup files that would be needed to restore the specified objects, but the objects are not actually restored to disk.”

RESTORE … VALIDATE tells RMAN to select and check if the existing available backupsets are sufficient for the operation in question.
This could be an entire database restore or a restore of a single tablespace. This operation ensures that the BACKUPSETS aren’t
corrupted and are usable:

RMAN backup – Performance

1. If the V$BACKUP_ASYNC_IO view is empty and the V$BACKUP_SYNC_IO view is not empty, then the backup is not being performed in asynchronous mode. So enable backup tape I/O slaves, and if the system does not support native asynchronous I/O, then also enable backup disk I/O slaves.
2. Query V$BACKUP_ASYNC_IO to determine the effective reading speed during the backup. This value is reported in the column
EFFECTIVE_BYTES_PER_SECOND in the row that has the TYPE column equal to AGREGGATE. This row represents the backup speed. Sometimes backups are slow not because reading or writing is slow, but because of other issues: connecting to the media manager, retrying while starting media manager processes, committing the backup of the tape, and so on. In such cases, troubleshoot the problem in the media management software.
3. Investigate the IO_COUNT, READY, SHORT_WAITS, and LONG_WAITS
columns:
· The column IO_COUNTS represents the total number of the I/O calls.
· The READY column is the number of asynchronous I/O reads or writes for which a buffer was immediately ready for use.
· The column SHORT_WAITS represents the number of times that a buffer was not immediately available, but a buffer became available after doing a nonblocking poll for I/O completion.
· LONG_WAITS is the number of times that a buffer was not immediately available, and only became available after a blocking wait was issued. In other words, the number of I/O calls where Oracle waited for the disk or tape.

select SID, SERIAL, USE_COUNT,
RMAN_STATUS_RECID, RMAN_STATUS_STAMP,DEVICE_TYPE, TYPE,
STATUS,FILENAME, SET_COUNT, SET_STAMP, BUFFER_SIZE, BUFFER_COUNT,
TOTAL_BYTES, OPEN_TIME, CLOSE_TIME, ELAPSED_TIME, MAXOPENFILES, BYTES,
EFFECTIVE_BYTES_PER_SECOND, IO_COUNT, READY, SHORT_WAITS, SHORT_WAIT_TIME_TOTAL,
SHORT_WAIT_TIME_MAX, LONG_WAITS, LONG_WAIT_TIME_TOTAL, LONG_WAIT_TIME_MAX
from gv$backup_async_io where inst_id = userenv(‘Instance’)

Archive log generated per day

select trunc(completion_time), sum(blocks*block_size)/1048576 mb
from gv$archived_log
group by trunc(completion_time)