RE: HP-UX 11.0/8.1.6.2.0/Optimizer

Ferenc,

Looks like you got your way with the result. Depressing but true What happened to your web site, is it still up and if so what is the URL

Regards

John

-----Original Message-----
Sent: 25 June 2002 13:33
To: Multiple recipients of list ORACLE-L

Mike

Siebel does not support CBO either, and I have seen your exact problem.

If you have the segment level degree of parallelism on any of the tables or indexes in the query with a non serial degree of parallelism, the optimizer immediately invokes CBO for the query, regardless of what optimizer_mode is set to, and of course in the absence of statistics, a query written for CBO will stink like nothing stinketh, especially on large data set. I tore my hair out for a day with such a query at a customer's site, and like you, all I could say is 'ba-a-a-a-a-a-a'. But I will never forget it.

I'd love to chat more, but the game is about to start, and I want to see Germany hand justice to Korea, though it will be difficult because the Germans only have 11 players, and the Koreans up to now have had 14 on the field (team plus ref plus two lines men ). At least I have my priorities straight.

Hope that helps you.

Regards:
Ferenc Mantfeld
Senior Performance Engineer
Siebel Performance Engineering
Melbourne, 3000, VIC, Australia
Only Robinson Crusoe had all his work done by Friday

-----Original Message-----
Sent: Monday, 24 June 2002 4:34 PM
To: Multiple recipients of list ORACLE-L

Hi All:

Here's a strange thing. I did a reorg of a very nasty tablespace over the weekend. I broke it out into 4 new tablespaces for the large tables and the rest into a single tablespace. This database has 'optimizer_mode = rule' set in the initSID.ora file because the Cognos application can't seem to handle the CBO, so I did not compute any statistics as part of the process.

Sounds like routine maintenance, right?

Nope. It went weird. One query, which included an outer join and a sub-query went from about 2 minutes to not finishing in over two hours. All indexes and objects were back in the DB. I verified that about a dozen times, all with manglement breathing down my neck. I EXPLAINED the query till I was blue in the face. I rebuilt (again!) all the indexes. No joy.

Finally, I thought "oh heck...might as well analyze them".

Shazzam. Back to 2 minutes. Huh? But Optimizer-mode is RULE!!

How? Why? I look stupid and so does my whole DBA group. Does anybody have any insights about this behavior?

Thanks,
Mike

---
===========================================================================
Michael P. Vergara
Oracle DBA
Guidant Corporation



-- 

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


-- 

Author: Vergara, Michael (TEM)
  INET: mvergara_at_guidant.com

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: 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.com


-- 

Author: Ferenc Mantfeld
  INET: fmantfeld_at_siebel.com

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: 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.com


-- 

Author: 
  INET: John.Hallas_at_vodafone.co.uk

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: 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).