42
If you know of Douglas Adams you will know that the answer to life, the universe and everything is 42**.
February 28, 2008
February 25, 2008
IOTs and 10053
I’ve probably got a couple of comments about 10053 trace files lurking somewhere on this blog and on my website - and when I mention the 10053 I usually remember to say that it’s a last resort that I only use when I think there may be a bug that needs to be nailed.
So here’s a bit of a 10053 - which I only looked at because I thought it was going to show me a bug.
(more…)
February 22, 2008
Serious Analysis
Following a recent posting from Dan Fink on the “presentation” that Mogens Norgaard gave at RMOUG this year I got involved in an interesting email exchange with (amongst others) Graham Wood, who many of you will probably recognise as the “father” of AWR and ADDM.
In the course of the exchange, Graham made a comment which I thought was worth passing on to the Oracle world at large: “The design of the rule applied for buffer busy waits is about three pages long.”
It’s an important thought to carry around - and ties in nicely with the paper from Connie Green that I referenced earlier on this week - there’s a lot more to interpreting the statistics than translating one number into a simple-minded action, and far too many people seem to think that that’s all you have to do to fix any Oracle problem.
February 18, 2008
Analysing Statspack
It’s been a few years since I last read this (pdf) article from Connie Dialeris Green of Oracle about how to use Statspack - and I’d forgotten how good it was.
(more…)
February 17, 2008
Pushing Predicates
Some time ago I wrote a note (on my website) about the push_pred() and no_push_pred() hints. I’ve recently discovered a bug in the 9.2 optimizer that means you may find that Oracle will not use “join predicate pushing” when it is obviously a good idea.
This note discusses a sequence of execution plans, taken from a system running 9.2.0.8, to demonstrate the point.
(more…)
February 13, 2008
Statistics problem
The worst type of Oracle bug is the one that seems to appear randomly and can’t be reproduced on demand. (Such as when Oracle support says “please send us a reproducible test case”).
Here’s one such (probable) bug that showed up at a client site that was reporting performance problems with a query that, on random days, chose a bad execution plan. The client was running one of the earlier versions of 9.2, and using the following call to dbms_stats to collect fresh table stats for each table in turn every night.
(more…)
February 9, 2008
Index Rebuild 10g
I’ve written a few notes about the pros and cons of index rebuilds in the past. A comment on Richard Foote’s blog describes a bug in 10g (reminiscent of an old index root block bug in early versions of 8i) which adds a little extra twist to the issue.
February 6, 2008
Trouble-shooting
How do you trouble-shoot ? Sometimes the best approach depends where you are starting from and how much time you have to act. Here’s a little sequences of events that cropped up some time ago while I was on a customer visit the day after an upgrade from 9i to 10g.
February 4, 2008
dbms_stats surprise
It’s been several years since I started telling people that one of the solutions to dealing with optimizer problems was to adjust (or create) stored statistics using the dbms_stats procedure set_column_stats, set_index_stats, and set_table_stats.
Imagine my surprise to discover an old (2003) Metalink note confirming my claim that it is perfectly reasonable to take this approach. If you have an account, you will find a demonstration script in note 157276.1.
There’s also a note (dated Nov 2002) which contains a tidy looking script for reporting object statistics at the object, partition, and subpartition level. I haven’t tested it yet, but you might find it useful. The note number is 31412.1
