Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> When 10053 trace gives same cost for 2 join ops which join is chosen ?

When 10053 trace gives same cost for 2 join ops which join is chosen ?

From: Spendius <spendius_at_muchomail.com>
Date: 16 Aug 2004 09:12:06 -0700
Message-ID: <aba30b75.0408160812.78c14c22@posting.google.com>


Hi,
When the optimizer ends up with the same cost for 2 different join operations in a query, which one does it choose ?

In one of these trace files I noticed that for a given query the calculation gave CST=5 for the NL and HA paragraphs (and 6 for the sort-merge), but Oracle would keep using the NL join and it resulted *every time this query was executed* in an execution time of more than 1 second => I hinted the SELECT with USE_HASH (as I could see the cost was the same as NL) and from now on this query's been performing very well (duration of a few 100ths of second, never more). In the EXPLAIN PLAN I could check this change from NL to HA was the only one occuring. So keeping the NL join op against the HA one was a bad choice.

Thanks.
Spendius Received on Mon Aug 16 2004 - 11:12:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US