Re: CBO and hash_area

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 7 Feb 2003 08:24:15 -0000
Message-ID: <b1vrd9$ast$1$8300dec7@news.demon.co.uk>

I believe it's supposed to.
There is a factor involved in the costing which includes the fact that the hash table will have to be partitioned and dumped to disc (I also think the performance tuning guide is incorrect - or at least too simplistic - about how this factor is used).

It might be quite informative to repeat the experiment with a 10053 trace to see what the optimizer thinks the hash join partition sizing looks like - and even a 10104 trace with 10046,level 8 trace to see the exact timing of the steps.

What do the two plans look like ?

Is it possible that most of the difference in time actually appeared at a later point in the query ?

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


ctcgag_at_hotmail.com wrote in message
<20030206223938.679$Cd_at_newsreader.com>...


>I ran a fairly complex self-join query on a ~10e6 table on

>our 9.2.0.1.0 instance, SunOS 5.8.

>

>It originally executed using a hash join, taking over 2 hours.

>

>One of the things I tried was changing hash_area_size from default

(128K)


>to 24M, and the same query took under a minute.

>

>The execution plans were identical, including the estimated cost.

>

>The statistics are up to date, and the cardinality estimates made by

the


>CBO are fairly close to the actual number rows at each point, as far

>as I can tell.

>

>So, why doesn't the CBO realize that you can't cram a 20M hash table

into


>100K, and then account for it by adding the massive multi-pass

overhead


>into the estimated cost?

>

>Xho

>

>--

>-------------------- http://NewsReader.Com/ --------------------

>Usenet Newsgroup Service              New Rate! $9.95/Month 50GB

Received on Fri Feb 07 2003 - 02:24:15 CST

Re: CBO and hash_area_size