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: [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: Ferenc Mantfeld 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: 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).
RE: HP-UX 11.0/8.1.6.2.0/Optimizer
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: [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: Ferenc Mantfeld 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).
RE: HP-UX 11.0/8.1.6.2.0/Optimizer
On Mon, 24 Jun 2002, John Kanagaraj wrote: > Contrary to common understanding, the Cost based optimizer is 'forced' in > certain situations even if the mode is RULE. Examples are: Use of hints > other than RULE, Partitioned tables and indexes, Index-organized tables, > Reverse key indexes, Function-based indexes, SAMPLE clauses in a SELECT > statement, Parallel execution and parallel DML (i.e. presence of DEGREE on > Tables/Indexes), etc. Was this the case? This was the focus of my paper at > IOUG... My mouth dropped open when I read this. News to me. This is a bit astonishing, though I'm sure well documented. Somehow I've passed over it. John, can you send a link to your paper? THanks, Sean -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: HP-UX 11.0/8.1.6.2.0/Optimizer
John: OH! D'OH! I missed your paper at IOUG. Probably shouldn't have. This particular query had an index hint in it. We did not try running the query without the hint until the tables were almost all analyzed. Can you shoot me a copy of your paper? Or point me towards a copy on the net? Thanks, Mike -Original Message- Sent: Monday, June 24, 2002 5:24 PM To: Multiple recipients of list ORACLE-L Mike, > 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? Contrary to common understanding, the Cost based optimizer is 'forced' in certain situations even if the mode is RULE. Examples are: Use of hints other than RULE, Partitioned tables and indexes, Index-organized tables, Reverse key indexes, Function-based indexes, SAMPLE clauses in a SELECT statement, Parallel execution and parallel DML (i.e. presence of DEGREE on Tables/Indexes), etc. Was this the case? This was the focus of my paper at IOUG... 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: 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).
RE: HP-UX 11.0/8.1.6.2.0/Optimizer
Mike, > 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? Contrary to common understanding, the Cost based optimizer is 'forced' in certain situations even if the mode is RULE. Examples are: Use of hints other than RULE, Partitioned tables and indexes, Index-organized tables, Reverse key indexes, Function-based indexes, SAMPLE clauses in a SELECT statement, Parallel execution and parallel DML (i.e. presence of DEGREE on Tables/Indexes), etc. Was this the case? This was the focus of my paper at IOUG... 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).
