I have come across of many forums & mailing list where people asking for the optimal value (size) for online redo logs files.
I would say, prior to 10gR2, you need to estimate its value upon system redo generation and other database setting factors.
Although, log file size is not the candidate to measure or improve LGWR performance, but, it (log file size) can affect to DBWR and ARCH bakground process performance. Therefore, log file size should be large enough in order to avoid the frequent checkpointing in the database.
Prior to Oracle10R2, there was no direct hint by oracle that you need to set this value as an optimal,or at least start with this value.
In Oracle10gR2, the optimal value for log file can be obtained from v$instance_recovery dyanmic view.
select optimal_logfile_size from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------
453 (size measured by MB)
Note : The above value is considered optimial based on the current setting of
FAST_START_MTTR_TARGET.
Oracle documentation suggest the following:A rough guide is to switch logs at most once every twenty minutes.Size your online redo log files according to the amount of redo your system generates.
Happy reading,
Jaffar
10 comments:
Hi,
What do you think about the value suggested by the view ? Is it in accordance with what your experience gave you ?
Best regards,
Hi,
What do you think about the value suggested by the view ? Is it in accordance with what your experience gave you ?
Best regards,
Hi,
What do you think about the value suggested by the view ? Is it in accordance with what your experience gave you ?
Best regards,
Nicolas,
As I said earlier, the value also depends on your MTTR settings.
Though, its difficiult to give you the exact optimal value for redo log file, but, its a good point at least to start from.
Jaffar
This is why I love blogs. I would ordinarily (or historically) dismiss this topic because "it should be obvious" that the main sizing guideline for online redo logs is that size which ensures checkpointing is not "too aggressive" and ARCH is able to clear logs to the archivelog dest before the wrap-around.
It occurs to me after this rambling of mine that I need to go off and think about it.
In fact, this is the great thing about Oracle--it is a never ending learning curve.
Nice blog!
OK...back to my efforts to blog about something interesting myself at:
kevinclosson.wordpress.com
Kevin,
You are right, its always never ending learning story with Oracle.
Let me tell you one thing, I am one of the regular visitor to your blog and now your blog has been added to my favourate blog roll.
Jaffar
Goood one ...good learning from a too grneral a topic ...
thanks
Pankaj
http://blogs.oracle.com/pankaj
Hi,
But when I tried in my 10gR2 DB, it didn't showed any value. May I know the reason.
SQL> select optimal_logfile_size from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------
SQL>
Thanks
Manjusha
@Manjusha
You need to set fast_start_mttr_target
then only you will get value in OPTIMAL_LOGFILE_SIZE.
Hi Jaffar,
I'm newbie DBA.My current online logfile is set to 50M.But the below query shows
SQL> SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;
OPTIMAL_LOGFILE_SIZE
--------------------
782
Do you suggest me to increase logfile size to this much.
And fast_start_mttr_target is set to 30.Please suggest your recommendation.
Post a Comment