Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: long runtimes cured by cutting hash_area_size by 90% - ???

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

From: C. Barr <>
Date: Mon, 14 Apr 2003 11:08:58 -0800
Message-ID: <>

Yes, I did gen 2 tkprofs for large & small hash_area_size, got 2 exec plans and sure enough there was a HASH JOIN (not in EXPLAIN PLAN output) returning an absurd 94,000,000 rows when hash_area_size was 100M, vs. 1M. The good query has 4 or 5 nested nested loops and one or 2 hash joins.

Jonathan Lewis & others (THANK YOU Jon & Wolfgang!) shed light on the inner workings of CBO & reading tkprof output. We're still looking at what to do ..

My understanding is that a smaller hash_area_size effectively "hints" against attempting a large hash join. We're testing now to see about side effects w/ the smaller value.

This query joins 9 tables. I have read that Oracle CBO "gets confused" with > 5 tables joined. And Jon says something similar about concurrent hash joins. These queries were running concurrently at one point - 14 of them - and TEMP frequently hit 100% full, at 10Gig then at 20Gig. But the 6-min. runtime was for one standalone query.

Our solution is constrained by the query being generated in real time from a tool - Cognos' Impromptu - so hints, subqueries etc. are not possible. At some point the generated query might be rewritten to use a view or new table.

Meanwhile thanks very much to Jonathan, Wolfgang and others on this list.

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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 - 14:08:58 CDT

Original text of this message