Re: archivelog performance issues - Windchill app.

Thanks for the thorough response Mark. I agree with you and the others who responded and the first item on my to-do list on this db when I took over was to enable archivelog mode and implement RMAN (the previous DBA left a script to do cold backups, which I intend to keep since the maintenance window allows for it, but we run the risk of course of losing ~2000 committed transactions if the failure occurs at the end of the day).

By the way, this database supports a PTC Windchill application.

I am just trying to figure out what the previous DBA knows about performance impact of archivelog mode that I don't. I think we are all on the same page on this issue (except of course for my predecessor!).

Bobak, Mark wrote:
>
> I agree with Joseph.
>
>
>
> Additionally, in my mind, the decision as to whether you run in
> archivelog mode should really be about recoverability and uptime
> needs. Noarchivelog implies cold backups. You must take a down time
> to do the backup. Noarchivelog means you can only recover up to the
> point in time of the last good cold backup. With archivelog mode, you
> can do hot backups and recoverability is to the point in time of the
> last committed transaction. There's lots more flexibility that
> archivelog mode offer you, as well. Those are just the two most
> important points.
>
>
>
> Seems to me, you should make the decision as to whether archivelog
> mode is a requirement, and then, if needed, make changes to support
> your required performance with it enabled.
>
>
>
> In my opinion, for **almost all** production databases, archivelog
> mode is a foregone conclusion. In my opinion, there are very few
> production databases that can really be adequately served with
> noarchivelog mode.
>
>
>
> In conclusion, as Joseph mentioned, for the size of the database you
> have and the transaction volume you're talking about, switching
> archivelog mode on shouldn't even noticeable at all.
>
>
>
> Some things to check before you switch to archive log mode:
>
>
>
> - How frequently are you seeing log switches, at peak load
> time? Size your online redo so that you get a switch about every
> 15-30 minutes, at peak.
>
> - If your load variaes greatly from peak to non-peak, you may
> want to use archive_lag_target to get more frequent switches during
> off-peak times.
>
> - Make sure you have at least three redo log groups.
>
>
>
> Hope that helps,
>
>
>
> -Mark
>
>
>
> *From:* oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Joey D'Antoni
> *Sent:* Tuesday, July 07, 2009 12:09 PM
> *To:* jachang_at_lanl.gov; oracle-l_at_freelists.org
> *Subject:* Re: archivelog performance issues
>
>
>
> How is your storage arranged? In general, with a database of that
> size, I can't imagine a significant performance impact from running in
> archivelog mode, unless the storage was horribly misconfigured.
>
>
>
> Joseph D'Antoni
>
> Synthes USA
>
>
>
> ------------------------------------------------------------------------
>
> *From:* Julio Aguilar-Chang <jachang_at_lanl.gov>
> *To:* Oracle-L Freelists <oracle-l_at_freelists.org>
> *Sent:* Tuesday, July 7, 2009 12:06:33 PM
> *Subject:* archivelog performance issues
>
> Hello:
> What are the performance issues I should be aware of if I decide to
> enable archivelog mode on an OLTP database? I just inherited this
> database (about 40GB in size, ~200 users, ~2000 transactions per day),
> it is not in archivelog mode, and the manager who owns it (not well
> versed in Oracle) tells me that the previous DBA who just left did not
> enable archiving for database performance reasons. The Oracle server
> is an HP DL385, dual core, with 16GB of memory.
> Is there a good reason based on performance for not enabling
> archivelog mode?
>
> --
> *****************************************
> Julio Aguilar-Chang
> Geophysics Group, EES-17
> Los Alamos National Laboratory
>
> jachang_at_lanl.gov <mailto:jachang_at_lanl.gov>
> (505) 667-1004 - work
> (505) 665-3687 - fax
> *****************************************
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 

*****************************************
Julio Aguilar-Chang
Geophysics Group, EES-17
Los Alamos National Laboratory

jachang_at_lanl.gov
(505) 667-1004 - work
(505) 665-3687 - fax
*****************************************


--
http://www.freelists.org/webpage/oracle-l