Home » RDBMS Server » Backup & Recovery » Late response time during RMAN hot backup (10g R2, AIX 5.3)
Late response time during RMAN hot backup [message #451215] Tue, 13 April 2010 03:24 Go to next message
Emrek
Messages: 3
Registered: April 2010
Junior Member
Hello,

We are running Oracle 10g R2 on AIX 5.3. Our database is approximately 20G in size.

Our users are running applications which are highly sensitive to database response times. So any significant delay in database response time(say about more than 5 seconds) has serious impact on the user applications.

Everyday we take online backups using RMAN and we recently discovered that during the online backup process we are experiencing a serious performance problems at certain periods. For example a typical query which normally takes ~0.5 secods for database to respond takes about >30 seconds for several times during the process. to observe the problem more clearly we also wrote some test scripts which make random queries to the database to measure any delays in response times, and seen that we are getting late responses at several occasions(which normally never happen even when we run the script for a day without running RMAN online backup).

We have gone through IBM's whitepaper on "Tuning AIX for Oracle" and seen that there isn't any misconfiguration that would have bad impact on oracle performance on the AIX side.

Also we have gone through the following:


http://www.oracle.com/technology/deploy/availability/pdf/rman_performance_wp.pdf
http://www.oracle.com/technology/deploy/availability/pdf/br_optimization.pdf
http://download-west.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtunin.htm#sthref1057


We normally take our backups to tape drive. In order to test for any I/O bottlenecks we experimented by taking the backups to the disk drive. We have tried DURATION and MINIMIZE LOAD parameters, and also tried setting the RATE parameter to very low values(about 2M/s which normally reaches to about 100M/s) which resulted in total elapsed time for backups to increase from 30 mins to about 4-5 hours. And we came to find out that there is no improvement about the issue in any of these scenarios.

Also we ran the ADDM reports which claimed I/O bottleneck with about 20% impact on the system, so we tried decreasing I/O using the RATE parameter and then ADDM said "low I/O bottleneck".

Database responsiveness is a vital for our system. What measures can we take to avoid such slowness?

Thanks.

[Updated on: Tue, 13 April 2010 05:11] by Moderator

Report message to a moderator

Re: Late response time during RMAN hot backup [message #451262 is a reply to message #451215] Tue, 13 April 2010 05:35 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

How about your physical memroy size ?

SGA Parameter ?

-Babu
Re: Late response time during RMAN hot backup [message #451266 is a reply to message #451262] Tue, 13 April 2010 05:56 Go to previous messageGo to next message
Emrek
Messages: 3
Registered: April 2010
Junior Member
Physical memory is 12G. SGA is 2.5G.

Re: Late response time during RMAN hot backup [message #451285 is a reply to message #451215] Tue, 13 April 2010 07:45 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
With only 12G of physical memory, why only 2.5G for SGA?
Whats the size of large_pool?
RMAN requires large pool to do its task.
Re: Late response time during RMAN hot backup [message #451659 is a reply to message #451215] Fri, 16 April 2010 00:51 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member

Monitor your server's PageScan rate before and during the RMAN Backup -- use vmstat.

Check your average wait times for 'db file scattered read' and 'db file sequential read' waits. What is filesystemio_options set to ? (is it "asynch" or "directio" ?)


Hemant K Chitale
Re: Late response time during RMAN hot backup [message #452093 is a reply to message #451215] Tue, 20 April 2010 00:48 Go to previous message
Emrek
Messages: 3
Registered: April 2010
Junior Member
Hello and thanks for the responses.

We have set SGA small to see if we have any trouble with RAM, but its set back to 6G now and the problem still exists.

We don't seem to have high pagescans, 'db file scattered read' and 'db file sequential read' waits are very small. filesystemio_options is set to asynch.

However we have found out something new. Watching iostat results during the database's slow phase we noticed 'tm_act' field for the disk which holds Oracle DBF files reaches 100% even though disk I/O utilization is far below maximum(and that can be controlled by setting the RATE parameter of RMAN). When this value falls below %100 the database is responding well again.

We tried setting High and Low watermarks for disk I/O on AIX as advised by AIX tuning docs but experimenting with different values(and also very low ones) we have come to see these won't solve the problem.

But we have verified that the problem only exists when tc_act is 100%.

Thanks again.
Previous Topic: Error in RMAN backup to tape
Next Topic: RMAN obsolete backups not obsolete?
Goto Forum:
  


Current Time: Thu Mar 28 03:51:51 CDT 2024