CBO and cartesian product

From: Dilip <dilip7772002_at_indiatimes.com>
Date: Sat, 11 Oct 2003 04:49:26 -0800
Message-ID: <F001.005D2D56.20031011044926@fatcity.com>

Hi List,

DB version - 8174. (Oracle Apps 11.5.4).

One of the customized report started running very slowly. One query was taking more than 3 GB of TEMP tablespace for 'HASH' type segment and erroring out after 2 hours for a lack of space in TEMP.

Tkprof showed that it is doing Cartesian Sort Merge Join. After running the same report under RBO, it is using Nested loops and report is completing in just 5 minutes. The stats were collected last week-end. I couldn't find any reason for this CBO's behavior ? Has anybody experienced this before ?

Thanks,
~Dilip      

Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com

 Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com

Bid for for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now !

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Dilip
  INET: dilip7772002_at_indiatimes.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 Sat Oct 11 2003 - 07:49:26 CDT