Coskan’s Approach to Oracle

March 6, 2007

Tuning Physical Standby part I

Filed under: High Availability — coskan @ 3:04 pm

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)

About these ads

4 Comments »

  1. 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

  2. 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

  3. 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: