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.
- Chris Barr
> 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: C. Barr
INET: cbarr_at_hotpop.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 - 14:08:58 CDT
