In my opinion tuning dataguard can be analyzed under two topics
1- Tuning network
a-Is the network bandwidth sufficient for the redo rate?
If your redo size and redo generation rate is high for bandwith between primary and standby server you have to check your network if you cant decrease the size of redo and redo generation rate.
For calculating the bandwidth need you can do the following calculation.
Required bandwidth = ((Redo rate in bytes ps / .7) * 8 ) / 1000000 = bandwidth in Mbps
for example if your redo rate per second is 500 bytes then you need
= ((500 * 1024) / .7) * 8 ) / 1000000=5.8 MBps
If you bandwith below 5.8 MBps then
you can get your redo rate ps from a statspack or awr report taken for a peek time period.
b- If the network is sufficent then check that there are no network errors or collisions on the primary and the standby
Use the ‘netstat’ command on the primary and the standby to validate that no network errors or collisions are occurring. You must get zero from the output of netstat commands collision column
C:\Documents and Settings\cgundogar>netstat -e
Interface Statistics
Received Sent
Bytes 3953227193 1644532922
Unicast packets 23348932 15441759
Non-unicast packets 157696847 1061
Discards 0 0
Errors 0 0
Unknown protocols 1396632
use netstat -i for unix or solaris OS
2- Tuning log apply process
a- If you have multiple CPU on your machine then parallelize the log apply process
SQL>alter database recover managed standby database parallel N disconnect;
max(N)=number_of_CPU*2
b- You can disable DB_BLOCK_CHECKING parameter for fast redo apply.
SQL> alter system set DB_BLOCK_CHECKING=FALSE;
System altered.
c- You can increase PARALLEL_EXECUTION_MESSAGE_SIZE from 2148 to 4096. (parameter can not be set dynamically )
SQL> alter system set PARALLEL_EXECUTION_MESSAGE_SIZE=4096 scope=spfile;
System altered.
d- If your system supports ASYNC I/O then enable it with setting the parameter DISK_ASYNCH_IO to TRUE. If
ASYNC I/O is not supported then increase your db_writer processes by increasing .db_writer_processes parameter to higher value.
SQL> alter system set DISK_ASYNC_IO=true scope=spfile;
System altered.
SQL>alter system set db_writer_processes=2 scope=spfile;
System altered.
!!!!As an advice for health testing try each change one by one for seeing the effect of each parameter change operation.
Referances Used:
Metalink Note ID: 241925.1 Troubleshooting 9i Data Guard Network Issues
Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)
Very helpful…!!!
It will be great if you provide some valuable inputs/guide-lines on in-dept debugging on performance issues (OS/DB – RAC)
Comment by Jag — March 29, 2009 @ 2:25 am
Hi,
in our case the parameter DISK_ASYNC_IO is set TRUE and db_writer_processes is set to “1”, what benefit in term of performance we can achieve if we increase “db_writer_processes” values?
Thanks
Comment by Nayyar Ahmad — July 8, 2009 @ 7:51 am
Hi Nayyar,
I did not do many tests on changing db_witer_processes value. But very well known experts did. I strongly recommend you to read the series of Kevin Closson on database writer.
http://kevinclosson.wordpress.com/2007/08/10/learn-how-to-obliterate-processor-caches-configure-lots-and-lots-of-dbwr-processes/
http://kevinclosson.wordpress.com/2007/08/17/over-configuring-dbwr-processes-part-ii/
http://kevinclosson.wordpress.com/2007/08/17/over-configuring-dbwr-processes-part-iii/
http://kevinclosson.wordpress.com/2007/08/21/over-configuring-dbwr-processes-part-iv/
Comment by coskan — July 8, 2009 @ 8:13 am
Thank you a lot for sharing this with all people you actually recognize what you are speaking about! Bookmarked. Kindly additionally consult with my website =). We could have a link alternate arrangement between us
Comment by motorcycle performance tuning — December 9, 2011 @ 9:26 pm
Thanks a lot for sharing your knowledge. It is very helpful. Could you share how you tune the queries that only run on the physical standby ( never run on Primary site)? Appreciate your help
Comment by Jing Ren — February 9, 2017 @ 1:51 am