My Favourite Oracle Blog

Some features in this post require a Diagnostics Pack license.

I think there a quite a few decent Oracle blogs around. There are links to some of them over there on the right. But by far my favourite this year has been Kerry Osborne's. I think there are a number of reasons.

1) We have similar interests in Oracle performance in general and the Diagnostics Pack features in particular.

2) His posts are nearly always technical but not just for the sake of it or to impress. I like to learn new and deep stuff as much as the next guy, but if the number of times I send links to colleagues who are trying to solve a problem is any indication of technical value, then Kerry's is almost at the top of my list.

3) I think he writes really well, in a readable, conversational style and always shows examples that are rigorous and reflect most people's day-to-day IT reality.

4) He shares useful scripts. I'm going to revisit that point in a future post about some of the tools I find useful.

I know I've mentioned Kerry's blog before, but no apologies for recommending it again. It's how I'd like my blog to be, really. Not jealous; just inspired! Oh, and for the avoidance of any doubt, we're not mates or anything, but I may buy him a couple of beers at Hotsos to thank him for those occasions when his stuff has helped.

But there is a technical point in here somewhere. I noticed his recent post about tracking Oracle parameter changes in AWR and it reinforced nicely a point I make when I'm teaching people about AWR. Never under-estimate how useful it is in DBA trouble-shooting of problems which are entirely unrelated to performance. AWR is often relevant for solving problems where a history might be useful. I've used the parameter change example in the past myself - who set this parameter, when and why? It won't answer most of those questions, but once you know the when, it might help answer the other questions too. Another use I've found is in looking back at when precisely (well, to the nearest hour) the free space in a tablespace dropped dramatically so that we could try to narrow the reasons why.

All I would say is that if you have the licenses and something like this crops up, check to see if there's a DBA_HIST view that might help. (Oh, and if you don't, just use Statspack instead. Doesn't have quite as much information, but pretty close. Or just write your own similar utility)

Kerry also talks about AWR retention and I know a lot of people increase the retention from 7 days to something like 35 days, to cover an entire monthly workload cycle. It depends on your system, of course. However, I am a big fan of long AWR retention periods, much longer than I find people using, because the history of a system is so useful when tracking performance changes over time. I've lost count of the number of times when I'm looking at gradual performance degredation over time and wished I could have some more AWR data to hand.

As for sizing, there's a pretty simple solution - just use the supplied $ORACLE_HOME/rdbms/admin/utlsyxsz.sql script. What I like about it is that it supplies very sensible defaults for your system, based on existing data and gives you pretty detailed information about what is going on inside your SYSAUX tablespace. It's one of those little, apparently simple things that I find people are still unaware of for some reason.