RE: long runtimes cured by cutting hash_area_size by 90% - ???

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 14 Apr 2003 06:49:14 -0800
Message-ID: <F001.005805E8.20030414064914@fatcity.com>

Probably when the hash area size is big, this encourages the optimizer to do a hash join or may be many but on the other hand when it's small the optimizer chooses to avoid an expensive hash join by probably a nested-loop.

You need to get the execution plan twice: one with the big hash area size and the other with the small one.

Regards,

Waleed

-----Original Message-----
Sent: Friday, April 11, 2003 5:44 PM
To: Multiple recipients of list ORACLE-L

Attached are 3 PLANs output by tkprof after SQL_TRACE. This is Oracle 8.1.7.4 on Sun Solaris, NO parallel query.

Attached also are

• The Query
• The V$PARAMETER values We changed, at runtime: hash_multiblock_io_count is always UNSET, i.e. ZERO hash_area_size (see below)

Queries are generated by a tool, Cognos.

Note: table RI_XLR_T has 1.8M rows, others are ALL under 60k.

tkprof PLANS -
13 secs., fastest w/ 1Meg HASH_AREA_SIZE. (Similar times seen for 2Meg, 500k)

6 mins. 35 secs., slowest w/ 100Meg HASH_AREA_SIZE. There's a killer HASH JOIN at the innermost point of the PLAN .. (Similar times seen for 8Meg, 10Meg, 20Meg)

 Hash_Area_Size runtime (faster when smaller):
> 10,000,000 6:16 ( 6+ mins )
> 1,000,000 0:15 ( 16 secs)

This does NOT use parallel query.
It is repeatable.
Table Rowcounts:

xlr:	1.8M
xcp:	61k
mcp:	44k
xru:	30k
xrx:	30k

others: 22k or less

In advance thanks if you can shed light on the mysteries of CBO, etc. etc.

• Chris Barr

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Apr 14 2003 - 09:49:14 CDT