Re: Long term AWR retention
- From: Gaja Krishna Vaidyanatha <gajav@xxxxxxxxx>
- To: "sacrophyte@xxxxxxxxx" <sacrophyte@xxxxxxxxx>, "kerry.osborne@xxxxxxxxxxx" <kerry.osborne@xxxxxxxxxxx>
- Date: Thu, 10 Nov 2011 17:44:45 -0800 (PST)
Hi Oracle-L,
Given that the historical tables of AWR are partitioned by date, there should
be no issues with regards to performance of during "normal data retrieval". I
suppose, if your snapshot ranges for a given AWR report spans 10 years, that
may take a little longer...:) I know of a company which has been retaining AWR
history for approximately 8 years ( August/September 2003 was approximately the
month/year when 10gR1 was released). I am not aware of any issues have been
reported related to the long retention period. Additional disk usage is the
obvious side effect of this.
On the subject of disk usage, one thing to keep in mind is the size of the
SYSAUX tablespace. It is going to be bigger than usual. So if you have not done
already, you need to factor the "time to restore" a bigger than usual SYSAUX
tablespace (during the relevant failure/recovery scenario). This will impact
your MTTR (Mean Time To Recover) calculations for the database in question. I
have seen this as THE blindspot that a longer-than-normal retention period
creates. Kudos to the folks at Server Technologies at Oracle Corp., who have
actually done a very good job with the partitioning and maintenance design and
implementation of the AWR historical tables.
It is also useful to note that EVEN if you are only licensed for Oracle
Standard Edition (where database partitioning is not included), the relevant
AWR tables and indexes are in fact partitioned. The script that creates the
relevant WRH$ tables, sets an event for the session that is creating the
tables/indexes to "disable the partition check". This enables the creation of
partitioned tables/indexes for AWR.
For the record, I do NOT in any way recommend that this event be utilized to
avoid licensing costs for the partitioning option. The information provided
here, is purely for understanding and academic purposes.
Cheers,
Gaja
Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com
Phone - +1-650-743-6060
http://www.linkedin.com/in/gajakrishnavaidyanathaCo-author:Oracle
Insights:Tales of the Oak Table -
http://www.apress.com/book/bookDisplay.html?bID14
Co-author:Oracle Performance Tuning 101 -
http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766
________________________________
From: Charles Schultz <sacrophyte@xxxxxxxxx>
To: kerry.osborne@xxxxxxxxxxx
Cc: Brandon.Allen@xxxxxxxxxxx; "mark.teehan@xxxxxxxxxxxxxxxxx"
<mark.teehan@xxxxxxxxxxxxxxxxx>; "oracle-l@xxxxxxxxxxxxx"
<oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, November 10, 2011 9:38 AM
Subject: Re: Long term AWR retention
Sorry, I am late to this discussion. I see that Kerry's blog post was from
Jan 2010.
So the max retention is 100 years. Kerry "knows a guy" who did 7 years.
What is the real downside to setting retention to something, like, 10
years? Has anyone collected metrics on that? Surely at some point
performance will be negatively impacted, but is it noticeable?
7 days... sheesh.
On Fri, Jun 25, 2010 at 08:22, Kerry Osborne <kerry.osborne@xxxxxxxxxxx>wrote:
> I am not aware of any issues with long retention times either. I have
> worked (am working) with several systems that have over a year retention
> with 1 hour or half hour snapshot frequency. And I know if a guy that has
> plans to never purge and AWR data (I think his retention period is 7
> years). Anyway, I did a blog post about reasons for having a very long
> retention period (one being having a permanent record of parameter changes)
> here:
> http://kerryosborne.oracle-guy.com/2009/12/tracking-parameter-changes/There
> was some pretty good discussion on the issue and Doug Burns followed
> up with a post pointing out that there was a built in script for estimating
> sizing of AWR data. I personally think long retention times should be
> implemented (45 days at an absolute minimum 13 months more reasonable). The
> 7 day default is frustratingly short.
>
> Kerry Osborne
> Enkitec
> blog: kerryosborne.oracle-guy.com
>
>
>
>
>
>
> On Jun 24, 2010, at 10:44 PM, Allen, Brandon wrote:
>
> I ran a system with AWR snapshots at 15 minute intervals and 45 day
> retention for a couple years and never noticed any problems.****
> ** **
> Regards,****
> Brandon****
> ** **
>
> ------------------------------
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do
> not consent to Internet email for messages of this kind. Opinions,
> conclusions and other information in this message that do not relate to the
> official business of this company shall be understood as neither given nor
> endorsed by it.
>
>
>
--
Charles Schultz
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
