Transcript Chapter 16 Tuning RMAN

Chapter 16
Tuning RMAN
1
Background



2
One of the hardest chapters to develop
material for
Tuning RMAN can sometimes be difficult
Authors tried to capture repeatable
techniques for performance tuning
Why Tune RMAN



3
Backups taking too long
Performance of the system is impacted
during RMAN backups
Restore and recovery time too long,
downtime causes the business to lose
money
One Approach to Tuning...
1. Identify measurable business performance
requirements.
2. Collect data and measure performance.
3. Identify bottlenecks.
4. Make adjustments that will alleviate the
worst bottleneck.
5. Repeat steps 1–4 until your performance
goals are achieved.
4
Systemic Influences










5
CPU
Memory
Operating system
Disk layout
Network
Database architecture
Tape technology and vendor
Application design
Design of data model and its physical implementation
Robustness of SQL statements
Views Used for Monitoring








6
V$RMAN_BACKUP_JOB_DETAILS
V$BACKUP_ASYNC_IO
V$BACKUP_SYNC_IO
V$PROCESS
V$SESSION
V$SESSION_LONGOPS
V$RECOVERY_PROGRESS
V$SESSION_WAIT
Identifying RMAN Processes

Sometimes you might want to kill an RMAN
process
SQL> SELECT b.sid, b.serial#, a.spid,
b.client_info
FROM v$process a, v$session b
WHERE a.addr = b.paddr
AND b.client_info LIKE '%rman%';
7
Measuring Backup Performance

If you can’t measure it, you can’t manage it...
SQL> SELECT session_recid,
input_bytes_per_sec_display,
output_bytes_per_sec_display,
time_taken_display, end_time
FROM v$rman_backup_job_details
ORDER BY end_time;
8
Monitoring RMAN Job Progress

Are we there yet? Are we there yet?
SQL> select sid, serial#, sofar, totalwork, opname,
round(sofar/totalwork*100,2) "% Complete"
from v$session_longops
where opname LIKE 'RMAN%'
and opname NOT LIKE '%aggregate%'
and totalwork != 0
and sofar <> totalwork;
9
Identifying I/O Bottlenecks


How do you determine I/O is a bottleneck?
Asynchronous I/O vs. synchronous
SQL> SELECT sid, serial, filename, type,
elapsed_time, effective_bytes_per_second
FROM v$backup_async_io
WHERE close_time > sysdate – 7;
10
Improving Tape I/O Performance





11
RMAN can be configured to backup directly
to tape (see MML chapter)
Tape I/O is often the bottleneck
Can be difficult to diagnose and resolve
Use an incremental backup strategy with
block change tracking
Adjust multiplexing of backup sets
Improving Tape I/O Performance






12
Many variables to consider...
MML software configuration
Network configuration
Level of tape compression
Tape streaming
Tape block size
Maximizing Throughput to Backup
Device


13
Multiplexing with RMAN means it can read
multiple files at the same time and
simultaneously write them to the same
backup piece
As of Oracle 10g, RMAN will automatically
tune the level of multiplexing
Maximizing Throughput to Backup
Device



14
Filesperset limits the number of files in each
backup set
Maxopenfiles limits the number of files
simultaneously open
Diskratio instructs RMAN to read datafiles
from a specified number of disks
Setting Large Pool Memory Size

One approach, use ASMM
1. Ensure statistics_level is set to TYPICAL (the default) or ALL
2. Set sga_target to an appropriate nonzero value for your
database not exceeding the sga_max_size
3. Set the following initialization parameters to zero:
–
–
–
–
–
15
shared_pool_size
large_pool_size
java_pool_size
db_cache_size
streams_pool_size
Tuning Media Recovery


With Oracle 10g or higher, RMAN should
automatically perform recovery in parallel
You can override the default behavior:
RMAN> recover database parallel 4;
16
Tuning Crash Recovery




17
Crash recovery is performed by SMON after
a database crash (power outage, shutdown
abort)
Why would you care how long crash
recovery takes?
Can be tuned with fast_start_mttr_target
Gives the database a target time for
performing crash recovery
One Approach to Tuning Crash
Recovery
1. Disable the initialization parameters that interfere
with fast_start_mttr_target
2. Determine the lower bound for fast_start_mttr_target
3. Determine the upper bound for fast_start_mttr_target
4. Select a value within the upper and lower bounds
5. Monitor and adjust
18
Slowing RMAN Down


Sometimes RMAN can impact other
processes running on the system
Couple of techniques:
–
–
19
The backup duration ... minimize load command
The rate clause of the allocate channel or
configure channel command
Improving Performance Through
Parallelism


Use the parallel clause of the configure
command
Doesn’t do much for single disk systems
RMAN> configure device type
disk parallelism 4;
RMAN> show channel;
20
Improving Performance Using
Incremental Features




21
RMAN has many incremental features
Incremental backups
Change tracking
Incremental update