RE: MTTR and Optimal Logfile Size
Date: Fri, 16 Jan 2015 16:56:20 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D928268A6E_at_EXMBX01.thus.corp>
Mladen,
I was offering a possible answer to the original question, viz: "why would Oracle choose such a large value for the log file size?": Paraphrasing myself, my suggestion was simply: "if Oracle thinks you're not in a hurry to recover quickly maybe it's going suggest the largest log file that it thinks it can recover in the requested time".
Any considerations of hardware and speed of devices may, or may not, be relevant to the way that Oracle produces this suggestion - just as the level of caching is not relevant to the optimizer when it's calculating the cost of a particular execution path.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Mladen Gogala [dmarc-noreply_at_freelists.org] Sent: 16 January 2015 16:35
To: oracle-l_at_freelists.org
Subject: Re: MTTR and Optimal Logfile Size
Hi Jonathan,
The speed of recovery may also depend on the way he's doing backup and the speed of IO devices. If the backup type is storage snapshot and if the archive logs are on solid state devices, 8GB log files sound perfectly reasonable and the recovery will be very quick.
Also, recovery from backup is always the last line of defense. The primary line of defense is RAC, standby or a recovered copy in the flashback area. Recovery from backup is only done if everything else fails, in which case it is probably not expected for the recovery to last less than 1 hour. The only case of full recovery from backup, with the tapes fetched from the Iron Mountain, in my last 5 years of work, was caused by the lady named Sandy, which has visited the New York City area in the year 2012. To paraphrase Marlon Brando from the "Godfather", RAC was sleeping with the fishes.
On 01/16/2015 05:42 AM, Jonathan Lewis wrote:
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Brent Day [coloradodba_at_gmail.com<mailto:coloradodba_at_gmail.com>] Sent: 16 January 2015 09:37
To: backseatdba_at_gmail.com<mailto:backseatdba_at_gmail.com> Cc: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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_at_gmail.com<mailto:backseatdba_at_gmail.com>> 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
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 16 2015 - 17:56:20 CET