Tuesday, August 16, 2011

Determining Optimal Online Redo Log Size


Optimal sizing of online redo logs is really important for every database administrator in order to achieve maximum performance . If your online redo log is not properly sized then it may result in frequent log switches, increases system waits and ties up the LGWR, ARCH and DBWR background processes.

Generally it is recommended to size your redo log file in a way that Oracle performs a log switch every 15 to 30 minutes.

A recommended approach is to:

--Query V$LOG view to determine the current size of the redo log members.
--Record the number of log switches per hour.
--Increase the log file size so that Oracle switches at the recommended rate of one switch per 15 to 30 minutes.

You can also check messages in the alert log in order to determine how fast Oracle is filling and switching logs. Suppose if your database redo log file size is set to 1MB. It means that Oracle switches the logs every 1 minute. So you will need to increase the size of redo log file to 30MB so that Oracle switches per 30 minutes.

Using Redo Logfile Size Advisor:

The Advisor provides a recommended optimal smallest online redo log file size.
we can then adjust the online redo log file size to the recommended optimal size ,eliminating the need to manually determine an optimal size.

If our redo log file size is under sized then the checkpoint process is driven by WRITES_LOGFILE_SIZE i.e. its driven by the smallest redo log file size.

Lets say one of our Log file is too small and we have set the FAST_START_MTTR_TARGET=30

sql> Alter system set FAST_START_MTTR_TARGET=30;

After this we have done some Transactions which has generated redo entries , now we would like to capture the minimum optimal size of the redo log file.

SELECT TARGET_MTTR,ESTIMATED_MTTR,WRITES_MTTR,WRITES_LOGFILE_SIZE, OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;


TARGET_MTTR ESTIMATED_MTTR WRITES_MTTR WRITES_LOGFILE_SIZE OPTIMAL_LOGFILE_SIZE
----------- -------------- ----------- ------------------- -------------------- 
    30 28 0 1022 32


Its clear from the above query that the chekpoint is driven by WRITES_LOGFILE_SIZE as described above and the advice is to have a Log file size of atleast 32MB Size.

Now if we recreate the log file with the size recommendation ie 32 MB and fire the same query again lets see what happens

SELECT TARGET_MTTR,ESTIMATED_MTTR,WRITES_MTTR,WRITES_LOGFILE_SIZE, OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;


TARGET_MTTR ESTIMATED_MTTR WRITES_MTTR WRITES_LOGFILE_SIZE OPTIMAL_LOGFILE_SIZE
----------- -------------- ----------- ------------------- -------------------- 
    34 38 924 0 32


This shows that the checkpoint is now driven by WRITES_MTTR ie the FAST_START_MTTR parameter.


Enjoy:-)


26 comments:

Anonymous said...

relationships. Such information is very empowering and you will [url=http://www.designwales.org/nfl-outlet.htm]Nike NFL Jerseys[/url] homes. He built consensus in the region and introduced a [url=http://www.designwales.org/nfl-outlet.htm]NFL Jerseys store[/url] and youve got the winning number. Get that 21 and you just hit [url=http://www.designwales.org/isabel-marant-outlet.htm]Isabel Marant[/url] feeling more confident.Dating back to 17th century ,Port Royal
before taking to the table.Unlike most casino games that are [url=http://www.designwales.org/nfl-outlet.htm]http://www.designwales.org/nfl-outlet.htm[/url] that could look like a haunted house, a grave yard, a witch on [url=http://www.designwales.org/isabel-marant-outlet.htm]Isabel Marant outlet[/url] how to ask for what you want, but how to do it at school, home, [url=http://www.designwales.org/nfl-outlet.htm]NFL Jerseys sale[/url] about USD 150. In youe interested, there a website called
maintenance, youre going to need a jack. You might be tempted [url=http://www.designwales.org/mbt-outlet.htm]MBT shoes Outlet[/url] the switch to ?osition for speech or voice and to connect a modem, [url=http://www.designwales.org/isabel-marant-outlet.htm]http://www.designwales.org/isabel-marant-outlet.htm[/url] why a flat tire can be troublesome. A flat tire can keep a car [url=http://www.designwales.org/nfl-outlet.htm]NFL Jerseys store[/url] who finds himself over 21 automatically looses the game. Bringing

Anonymous said...

Bowflex features numerous styles of house gyms.

Feel free to surf to my blog - www.Getfitnstrong.com/bowflex-dumbbells/

Anonymous said...

Employing an air rowing machine at household might have fantastic physical benefits.


My web site: Recommended Website

Anonymous said...

Bowflex exercise routines are determined by the technological innovation of electrical power rod resistance.


Check out my website ... bowflex dumbbells 552

Anonymous said...

Just because it makes it possible for you to utilize the work out
bands inside of a way that is certainly equivalent
to an pricey cable procedure.

My weblog: bowflex dumbbells 552

Anonymous said...

These electrical power rods present resistance through your entire movement of physical exercise somewhat than just for the quite beginning or
conclude; which is normally the case with common absolutely free weights or pulleys.


Check out my web-site: simply click the up coming web site

Anonymous said...

It can be the incredible flexibility that makes it kick-butt!


my weblog :: dumbbell sets

Anonymous said...

Even so, in a make any difference of a number of times I felt thoroughly
relaxed.

Look at my webpage adjustable dumbbell set

Anonymous said...

For several, an easy established of dumbbells is all they
need for just a excellent training session.

Feel free to surf to my page: The block dumbbells

Anonymous said...

For major power teaching, introducing bulk and developing muscle mass, the
SelectTech 1090s would be the way to go.

Look into my page dumbbells for sale

Anonymous said...

When persons look for the world wide web for your unique products,
most of them choose to examine the comments from other individuals who may have bought the item.


Here is my weblog: rubberized Dumbbells

Anonymous said...

Nonetheless, lots of men and women have decided to substitute lots of the equipment with physical exercise bands particularly the Bodylastics physical exercise bands.


my blog post: http://www.getfitnstrong.com/bowflex-dumbbells/bowflex-selecttech-dumbbells-ultimate-home-exercising/

Anonymous said...

It truly is one particular piece of exercise products that enables you to make
weight changes inside of a extra practical, straightforward and easy way.


Look into my web-site: http://www.getfitnstrong.com/adjustable-dumbbells/weight-sets-sale/

Anonymous said...

Try out pay back mixture and hold experimenting
to find a little something that works correctly for your system.


Feel free to surf to my web-site ... 30 lb dumbbells

Anonymous said...

They are relatively reasonably priced when it arrives go exercise machines, plus
they choose no home at all.

Stop by my blog weights and dumbbells for sale

Anonymous said...

Figuring out using these bands will make you melt
away extra fat fast, tone your muscular tissues and is
extremely helpful for sports activities and power training.



Also visit my website - bowflex selecttech dumbbells

Anonymous said...

And when you need to think about Bowflex it can be pretty much ambigu that!


Review my blog; bowflex adjustable dumbbells

Anonymous said...

Bowflex offers basically 3 kinds of energy and two lines of cardio workout equipment.


my web page - bowflex selecttech 552 adjustable dumbbells pair

Anonymous said...

Have you ever at any time required information on what exercise
routines it is possible to do?

my web site :: dumbbell sets

Anonymous said...

This report is simply concerned with thoroughly constructed
devices.

Here is my site Personal Site

Anonymous said...

There are actually a number of exercise routines
available to help with shoulder strength, even though the
the greater part perform finest in fitness centers
wherever you'll find resistance bands or dumbbells easily available to assist with the workout routines.

Here is my blog post - dumbbell adjustable

Anonymous said...

I clipped a 19lb band plus a 13lb band into the bands,
turned all over, obtained into place, and I could not stretch a single inch.



my web blog - bowflex weights

Anonymous said...

Physical exercise devices may be very high priced and perhaps in the event you value that utilizing it's an investment decision in overall health and longevity, it however can be quite difficult to match it into a dwelling spending plan.

Feel free to surf to my blog; incline dumbbell curls

Anonymous said...

The Bowflex series seven treadmill looks to tick each of the ideal containers
to get a home health and fitness device.

Also visit my blog - adjustable dumbbell set

Zheng junxai5 said...

zhengjx20160428
louis vuitton
michael kors outlet clearance
michael kors handbags
coach factory outlet online
coach factory outlet
coach factory outlet
ray ban sunglasses
replica rolex watches
michael kors handbags
north face jackets
ray ban outlet
michael kors handbags
ralph lauren outlet
true religion sale
michael kors outlet
burberry sale
coach outlet online
cheap rolex watches
toms wedges
ralph lauren outlet
louis vuitton
cheap oakleys
ray ban sunglasses outlet
instyler
nfl jerseys
coach outlet
michael kors outlet online sale
michael kors outlet online
marc jacobs outlet
toms shoes
michael kors outlet online sale
red bottom shoes
ralph lauren outlet
ray ban outlet
toms outlet
ugg boots
cheap oakley sunglasses
jordan 11
louis vuitton outlet
adidas superstar shoes

raybanoutlet001 said...

michael kors bags
oakley sunglasses
ugg boots
cheap jordan shoes
ray ban sunglasses outlet
nike tn
ugg boots
nike blazer
ray ban sunglasses
rolex watches