RE: MTTR and Optimal Logfile Size
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "coloradodba@xxxxxxxxx" <coloradodba@xxxxxxxxx>, "backseatdba@xxxxxxxxx" <backseatdba@xxxxxxxxx>
- Date: Fri, 16 Jan 2015 10:42:49 +0000
It's interesting that Oracle still has that "ca. 20 minutes" as a best
practice. It probably came about from the days when a log file switch triggered
an immediate checkpoint to clear the log file, which meant you got a spike in
database writes on the switch. Over the years and versions Oracle has become
increasingly "lazy" about clearing log files, so you could find that although
the checkpoints are nominally triggered on each switch you could see a long lag
(measured in terms of number of outstanding log files available) before dbwr
exhibited any urgency in writing to clear an old log switch checkpoint.
As far as Jeff is concerned the 8GB optimal could be Oracle working an outdated
principle backwards. If he's not interested in recovering in less than an hour
Oracle may be estimating file size on the basis of needing to roll forward an
entire log file for instance recovery, and has decided that it could recover
8GB in that time. With the largest possible log file for the time there is a
possibility that writes due to checkpoints will be kept to a minimum.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Brent Day [coloradodba@xxxxxxxxx]
Sent: 16 January 2015 09:37
To: backseatdba@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: MTTR and Optimal Logfile Size
Jeff,
How many logfile switches per hour occur at the heaviest switching hour? Here
is a query that can help you find that information:
select trunc(first_time,'hh24'), count(*)
from v$archived_log
where first_time >= sysdate - 30
group by trunc(first_time,'hh24')
order by 2;
Ideally you want a logfile switch about every 20 minutes (Oracle's documented
best practice) and have your redo log files multiplexed. With current numbers
provided this would be closer to 50GB.
Brent
On Thu, Jan 15, 2015 at 11:06 AM, Jeff C
<backseatdba@xxxxxxxxx<mailto:backseatdba@xxxxxxxxx>> wrote:
I set my fast_start_mttr_target to 3600 to get a baseline but I noticed that it
suggest the optimal logfile size to 8561mb which seems very high. Currently my
logfile size is 50mb and I have been getting a some more frequent log switch
than in the past so I was going to increase it. And I never had mttr set so I
set it to see what it suggest. But why would it suggest an 8gb log file? If I
have 3 groups that is 24 gigs of a logs for a 30gb database.
Thanks,
Jeff
Other related posts:
