Oracle DBA – A lifelong learning experience

Posts Tagged ‘AWR repository’

Performance problems with OEM AWR warehouse

Posted by John Hallas on December 20, 2016

The Enterprise Manager AWR Warehouse is designed to hold performance data from multiple databases  for long-term analysis. It promoses that it will save storage and improve performance on your production systems. In that it is indeed correct. However the warehouse itself does not seem to be performant when taking in multiple sources and retaining them long-term – 400 days in our case. Why 400 days is an obvious question that might be asked. Primarily because we are a Retail organisation and Easter is variable each year.


The AWR repository database is performing poorly during the insert stage of the upload process.
Just to quickly summarise the process:
  • A dmp file is extracted on the source database and transferred across to the AWR server
  • The dmp file is then imported into a temporary schema called AWR$XXXXXX (this loads quickly)
  • This data is then inserted into the main AWR tables inside the SYS schema. Is is this stage that is slow.

In order to completely isolate the issue, we altered a parameter, so only one AWR file gets loaded at once, cutting any contention / locking issues out of the equation:

Read the rest of this entry »


Posted in 12c new features, Oracle | Tagged: , , , | 4 Comments »

Progress update on my AWR repository

Posted by John Hallas on November 20, 2014

I received an email from a team member yesterday

Have you seen this?

Interesting idea – ever thought of implementing it?

Well of course I have implemented a AWR data repository and I thought I would catch-up on where I am with it and how it is being used.

The database started out on HPUX on about 2 years ago and is now on Linux on The repository now holds a total of 139 DBIDs and we have data going back 400 days for the majority of them. The storage is over 2Tb, of which the SYSAUX tablespace contains about 95% – we are compressing some data but that is an area I need to look into further

Why 400 days you might ask – well I work in a Retail organisation and as Easter is a movable feast (pun intended) and that period allows to provide data covering Easter whenever it might fall. It is possible that we would not have data for Easter if we only kept a 365 day period and Easter is a key trading period, second only to Xmas.

I suppose the obvious question is how we have used the data and was it all worth the effort.

The repository has been used for workload forecasting, security reviews, auditing, performance reviews and validation of our existing systems.

Workload forecasting is an interesting and unexpected benefit of the repository. When I originally proposed the idea it wasn’t something that had occurred to me but it has proved very useful. The process is to review how much work a system has done a year ago, look at current usage, calculate the difference and then use that to project forward. For the system that I am discussing which looks at how long it takes us to process sales data from stores we have used a combination of metrics including db time, disk read times, both sequential and scattered to produce a magic number which is what we are working against. That forecast is now being mapped against actuals and the forecast is proving quite accurate. That has allowed us to prepare the database server to support increased volumes by changing disk, adding CPU and memory to maintain and improve against last year’s performance with increased data capture and store numbers. Read the rest of this entry »

Posted in 11g new features, 12c new features, Oracle, security | Tagged: , | 5 Comments »

UKOUG – Tech13 – day 3 review

Posted by John Hallas on December 5, 2013

Another busy day at the Manchester Central complex. An early start with Martin Nash talking about RAC connectivity issues and bugettes. Very interesting but I don’t think my brain was fully in gear at 08:30 after a big hotel breakfast.

Straight into another two-man CERN presentation on Lost Writes. The first half was  about a scenario they had seen where an index has lost some data and frankly there was no explanation for what caused it to happen. However the second part was around how you might deal with the scenario and that was very good and gave a lot of food for thought.

Jonathan Lewis then spoke for an hour about histograms and how they had developed in 12c. There were some really good points made but I suspect he lost some of the audience about half way through – including myself – I did notice a man in front of me who was doing a Sudoku throughout the presentation.

I did my AWR talk and felt it went OK but because there was no time to talk to anyone afterwards and as I did not get  much audience feedback it was quite hard to judge how well it went. The first time  I did it was at a SIG and I think the low-key, less time-pressured environment suited the talk much  more than the conference.

That was the end of the conference for me. I caught up with a  couple of people and then made my way home. All in all it was a very well run conference and the Manchester venue was better than Birmingham in my opinion.  I really cannot see the exhibition carrying on though. I did not look at one stand and I suspect there were many others who did not either. I cannot see what  the exhibitors got out of it.

I must mention the entertainment on Tuesday  evening which was a drinks reception sponsored by Enkitec and Delphix at the National Football Museum. I was very pleased to get my photograph taken holding the actual ball used in the 1966 World Cup Final. I think everyone was asked to wear gloves but it might just have been me!!


Posted in 12c new features, Oracle, UKOUG | Tagged: , , , | 3 Comments »

UKOUG featured speaker of the week – AWR presentation

Posted by John Hallas on November 4, 2013

I am speaking at the UK Oracle User Group  conference in Manchester on Weds 4th December on the subject of building and using an AWR repository but I also include the capture and long-term retention of OEM data as well.I have given this talk once already to a SIG and it got very good feedback and I have developed the ideas significantly since then.

I am also pleased to say that I have been selected as featured speaker of the week at the UKOUG site.  Caution – it does include my photograph.

The conference is normally held in Birmingham but is moving to Manchester for the first time this year. Another change is that Wednesday has a full agenda now, normally it tails off after lunch. I think the UKOUG conference is a must go to anybody who is looking for new ideas and techniques or even just to remind themselves of best practise.

The agenda is excellent  and I can see many difficult decisions on which talks to attend. I do know that whatever I decide then I will come away with a bunch of notes and follow-up ideas which will keep me busy for the next few months.

Posted in Oracle, UKOUG | Tagged: , , , | Leave a Comment »

UKOUG Conference here I come

Posted by John Hallas on August 19, 2013

I am pleased to say that I have been offered at a slot at the UKOUG Tech conference to present my paper on “The design, creation and maintenance of an AWR repository”. I have presented this once already to a SIG meeting and I felt it went down well and the feedback and comments I received afterwards supported that and allowed me to develop the ideas further.

The big day is Weds 4th December at the new venue in Manchester. For those of you who are used to the Birmingham event it should be pointed out that this year Wednesday will be a full day of presentations comparable with both Monday and Tuesday and not the half-day, wind-down that we have seen in recent years.

The formal agenda will be launched in September but for now the dates and venue are :-

UKOUG Technology Conference 2013 2nd – 4th December 2013 at the Manchester Central Convention Complex.

Posted in UKOUG | Tagged: , , , | Leave a Comment »

Building an AWR repository – UKOUG SIG presentation

Posted by John Hallas on July 10, 2013

I am pleased to be presenting at the UKOUG AIM and Database SIG  in London on Thursday 18th July. I am talking about building and using an AWR repository.

My precis reads This talk will cover the whole process of managing an AWR repository that will take feeds from many databases for long-term retention and analysis. It will cover the reasons to do this, how to size the repository and then how to move AWR snapshots into the repository. The management of the repository including partitioning advice, compression and gathering of statistics as well as how to housekeep the AWR repository will also be covered.

This presentation will be of real value to anyone who is looking for reasons to set up a repository and practical experience and lessons learnt on how best to do it. I will also provide a few example scripts of how it can be used and real-world examples of when it has proved useful.

Whilst I am not suggesting that my talk is a must see, I do think it offers good food for thought as to what might be achievable in mining AWR data. I am also hoping that audience feedback will generate other ideas.

More importantly, in my opinion, the day offers some good speakers and excellent talks, or maybe the other way around and should be attended if you possibly can. I think the UKOUG have a try before you buy offer but you need to confirm that from their website

Posted in UKOUG | Tagged: | Leave a Comment »