Re: Long term AWR retention

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Thu, 10 Nov 2011 17:44:45 -0800 (PST)
Message-ID: <1320975885.24735.YahooMailNeo_at_web83604.mail.sp1.yahoo.com>

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_at_gmail.com> To: kerry.osborne_at_enkitec.com
Cc: Brandon.Allen_at_oneneck.com; "mark.teehan_at_credit-suisse.com" <mark.teehan_at_credit-suisse.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 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_at_enkitec.com>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


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

Received on Thu Nov 10 2011 - 19:44:45 CST