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