Dec 13: My Favourite Oracle Blog
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.
Tracked: Dec 29, 16:40
Excellent point, Doug! I had been meaning to blog about DBA_HIST_RESOURCE_LIMIT and tracking processes/sessions. This is another great idea, tracking parameter changes.
I've pushed a number of times for the AWR retention here to be pushed out from 7 days but so difficult to get things changed once you're running with certain settings.
Something like 35 days much more useful when maybe examining a problem via DBA_HIST_ACTIVE_SESS_HISTORY and wanting to ask a question like "does a similar performance problem happen every Tuesday at 11:30am?"
I would venture that 7 days is next to useless. You need to
a) Discover a problem (and you might already have lost 2 days if it happened late on a Friday)
b) Be assigned the time to analyse it
c) Analyse it.
Now in a lot of organisations I can think of, that's going to add up to 5 or 6 days easily
On top of that, it doesn't allow you to compare similar periods in any type of changing workload pattern which has a cycle longer than 7 days.
You know all this already, of course, but it's one of my pet peeves and something I spend the early weeks of any new contract trying to have rectified.
The whole point is that it's a repository.
The whole point is that you have *history*.
I share your opinion about Kerry's blog. His blog is very useful and easy to understand/read.
I like the most his topics about bind variable peeking.
Your blog is great also, especially when you write reports from various Oracle meetings. I don't have a chance to participate on that events so your posts are giving me nice insight what is happening there.
Keep on good work
Wow! Thanks. I'll definitely take you up on the beer in Dallas.
P.S. The utlsyxsz.sql script is very nice. Thanks for pointing that out.
Setting retention to be greater than a month is so useful! We dicovered this when we first migrated from 8i to 10g a few years back - and had a hideous time with performance issues due to just a couple of (key and frequently used) sql statement changing plan to non-optimal execution plans. A week later (having fixed the issue )we wanted to compare wait times with the same time the previous week (during the post-upgrade-nightmare) only to discover it was gone.... Doh!
Definitely 35 day retention plus I would say increase the snapshot frequency to 15 minute intervals; Ok so it takes up more of your sysaux tablespace but is a few Gb extra really an issue these days? Probably not.
Believe me, Bob, I've seen a few GB appear to be an issue in expensive managed storage land, but it's all about of the value you'll get out of it one day!
As far as I'm concerned, the answer is 42.