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:-)


32 comments:

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

jeje said...

foamposites
kate spade outlet online
michael kors outlet handbags
asics running shoes
nike air max 90
hermes belt
air jordan 6
baseball jerseys
nike air force 1
nike zoom

Unknown said...

qzz0704
a bathing ape
fingerlings monkey
oakley sunglasses
gucci handbags
wellensteyn jackets
polo outlet
herve leger outlet
longchamp handbags
fred perry polo shirts
air huarache

5689 said...

zzzzz2018.8.31
coach outlet
canada goose outlet
air max 90
nike factory outlet
nike huarache
christian louboutin sale
tods shoes
ugg boots on sale 70% off
tory burch handbags
adidas outlet online

yanmaneee said...

golden goose outlet
yeezy shoes
supreme
christian louboutin outlet
adidas tubular
golden goose sneakers
timberland
russell westbrook shoes
balenciaga shoes
nike air max 270

Unknown said...

YOURURL.com dolabuy replica anonymous gucci replica handbags my response dolabuy ysl

toose said...

helpful site see it here view publisher site click here for more info see this here click here for more

Unknown said...

vous pouvez essayer ceci Dolabuy YSL sa réponse répliques de sacs à main gucci allez sur mon site dolabuy ysl

metu said...

i was reading this replica ysl article Dolabuy Balenciaga check my site you could try these out

Anonymous said...

off white outlet
kd shoes
palm angels outlet
curry shoes
supreme clothing
jordan
a bathing ape
hermes birkin
off white hoodie
nike x off white