Thanks for the tips.
The hash join looks like the best bet. Someday we'll upgrade to 8.1.7.
Someday...
Here is the plan and stats using /*+ USE_HASH(c p) */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=104706 Card=601007 B
ytes=100969176)
1 0 HASH JOIN (Cost=104706 Card=601007 Bytes=100969176)
2 1 TABLE ACCESS (FULL) OF 'CNR' (Cost=2203 Card=601007 Byte
s=38464448)
3 1 TABLE ACCESS (FULL) OF 'PUB' (Cost=844 Card=531324 Bytes
=55257696)
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
58649 consistent gets
91957 physical reads
0 redo size
45285104 bytes sent via SQL*Net to client
441398 bytes received via SQL*Net from client
40070 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
601007 rows processed
-----Original Message-----
From: John Kanagaraj [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 07, 2002 4:44 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Horrendous Execution Plan from CBO
Mark,
> Do any of you SQL tuning gurus have any suggestions? I've
> listed all the
> gory details below.
I am not a SQL tuning guru, but it looks like this is an ideal
example where
Hash joins would be of immense help. You could set a largish value
for
HASH_AREA_SIZE (defaults to twice SORT_AREA_SIZE) to reduce visits
to TEMP
to write out hash tables... It would be nice if you could post the
explain/costs for all three methods at the end of your tests.
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
The manuals for Oracle are here: http://tahiti.oracle.com
The manual for Life is here: http://www.gospelcom.net
** The opinions and statements above are entirely my own and not
those of my
employer or clients **
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing
Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
--
Author: Stahlke, Mark
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
