Re: Why Statistics doesn't match reponse time?

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