Re: Why Statistics doesn't match reponse time?
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- Date: Fri, 09 Sep 2005 02:34:51 GMT
On Thu, 08 Sep 2005 17:54:18 -0700, DA Morgan wrote:
>
> I have a question too ... why are you using 9.2.0.3? There have been
> four patches since then that have substantially improved the optimizer.
Patches would not help him here. His hint causes the optimizer to do the
index full scan, instead of the full table scan. Essentially, he's reading
each key in the index and then going to the table, for every row in the
table, thus performing the full table scan the hardest way possible.
Of course, that will slightly increase the number of needed I/O requests
so he has 137605 consistent gets with the hint and only 15462 without it.
The query with the hint has 10 times as many gets as the one without it.
That might cause a slight difference in the execution times..... To solve
it, he might use hash join, star schema (is his database DW?) or the good,
old merge join. Add parallel query and stir counter-clockwise.
Reformulating query and group by condition would be the first step.
As for the Oracle 9.2.0.6, the latest version, the optimizer was improved,
some instance crashes added, so that they cancel each other. Personally, I
find 9.2.0.4 the most stable garden variety of 9.2. Up to now, version 9.2
has been, as far as stability and code quality is concerned, an
unmitigated disaster, handled an order of magnitude worse then Katrina.
There have been no 9.2 patchsets released this year. I have a hunch that
Oracle Corp. would like us to switch to 10g ASAP as, according to many of
my acquaintances, 10g has been much less buggy and much more stable. I'm
getting the hint, but the final decision is with my management and they
want a stable 9.2 first. Oracle Corp. will have to fix that Oracle*Titanic
first.
If you end up being your normal, cheerful and condescending self, and
start inquiring about my experience with Oracle RDBMS, I only started last
week, so I dunno much about Oracle RDBMS.
--
http://www.mgogala.com
.
- Follow-Ups:
- References:
- Why Statistics doesn't match reponse time?
- From: wangbinlxx
- Re: Why Statistics doesn't match reponse time?
- From: DA Morgan
- Why Statistics doesn't match reponse time?
- Prev by Date: Re: How to capture information to analyze high load peak?
- Next by Date: Re: FAQ - Etiquette - and how to get an answer
- Previous by thread: Re: Why Statistics doesn't match reponse time?
- Next by thread: Re: Why Statistics doesn't match reponse time?
- Index(es):
Relevant Pages
|
|
