MTSCost based Optimzer
Title: MTSCost based Optimzer Hello folks! I am working in SUN solaris 5.6 with Oracle 8i. I want to implement MTS on the Oracle server. Next we are running with Rule based optimizer and we want to change to costbased optimizer. I want to know what are the advantages we get on this and what are the steps to implement this? Any help is grateful... Rgd Venkat DBA.
Re: MTSCost based Optimzer
turning on cost-based is easy: set the optimizer_mode parameter to choose in your init.ora, stop and start your database and then analyze all your tables. HOWEVER (and this is a biggie) this will not automatically make all your queries run faster. You will need to examine each and every query to see if it needs to be rewritten to take advantage of CBO. In fact, sometimes there are queries that run faster in rule-based, and I have had to include hints in queries to force the optimizer to run rule. From: Venkat_Kalepalli [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: MTSCost based Optimzer Date: Tue, 24 Apr 2001 02:25:25 -0800 Hello folks! I am working in SUN solaris 5.6 with Oracle 8i. I want to implement MTS on the Oracle server. Next we are running with Rule based optimizer and we want to change to costbased optimizer. I want to know what are the advantages we get on this and what are the steps to implement this? Any help is grateful... Rgd Venkat DBA. _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: MTSCost based Optimzer
definitely all or nothing -- if you have analyzed the large table and join it to an unanalyzed small table, you are going to get rule based From: Tim Sawmiller [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: MTSCost based Optimzer Date: Tue, 24 Apr 2001 07:51:18 -0800 I wouldn't... analyze all or nothing... [EMAIL PROTECTED] 04/24/01 09:46AM How would you decide that out of total big and small tables (say 1500) these are the tables need to be analysed and these are not, as in our case Transaction types are both OLTP and DSS ?? -Original Message- Carmichael Sent: Tuesday, April 24, 2001 6:46 PM To: Multiple recipients of list ORACLE-L turning on cost-based is easy: set the optimizer_mode parameter to choose in your init.ora, stop and start your database and then analyze all your tables. HOWEVER (and this is a biggie) this will not automatically make all your queries run faster. You will need to examine each and every query to see if it needs to be rewritten to take advantage of CBO. In fact, sometimes there are queries that run faster in rule-based, and I have had to include hints in queries to force the optimizer to run rule. From: Venkat_Kalepalli [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: MTSCost based Optimzer Date: Tue, 24 Apr 2001 02:25:25 -0800 Hello folks! I am working in SUN solaris 5.6 with Oracle 8i. I want to implement MTS on the Oracle server. Next we are running with Rule based optimizer and we want to change to costbased optimizer. I want to know what are the advantages we get on this and what are the steps to implement this? Any help is grateful... Rgd Venkat DBA. _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Deepender Kr Gupta 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: Tim Sawmiller 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: MTSCost based Optimzer
If you have the time and horsepower, analyze the database. If not, wait until somebody complains and run a trace/tkprof against their SQL. You may want to consider using histograms on selected tables. There was a discussion on using histograms earlier this month. Steve Adams wrote a script to help in identifying possible candidates for histogram creation. It's on his web site at: http://www.ixora.com.au/scripts/query_opt.htm#consider_histogram Be judicious with its use. In general, I've found it useful to consider running an analysis of large tables used frequently in the select and where clauses of SQL statements. Regards, David A. Barbour Oracle DBA Deepender Kr Gupta wrote: How would you decide that out of total big and small tables (say 1500) these are the tables need to be analysed and these are not, as in our case Transaction types are both OLTP and DSS ?? -Original Message- Carmichael Sent: Tuesday, April 24, 2001 6:46 PM To: Multiple recipients of list ORACLE-L turning on cost-based is easy: set the optimizer_mode parameter to choose in your init.ora, stop and start your database and then analyze all your tables. HOWEVER (and this is a biggie) this will not automatically make all your queries run faster. You will need to examine each and every query to see if it needs to be rewritten to take advantage of CBO. In fact, sometimes there are queries that run faster in rule-based, and I have had to include hints in queries to force the optimizer to run rule. From: Venkat_Kalepalli [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: MTSCost based Optimzer Date: Tue, 24 Apr 2001 02:25:25 -0800 Hello folks! I am working in SUN solaris 5.6 with Oracle 8i. I want to implement MTS on the Oracle server. Next we are running with Rule based optimizer and we want to change to costbased optimizer. I want to know what are the advantages we get on this and what are the steps to implement this? Any help is grateful... Rgd Venkat DBA. _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Deepender Kr Gupta 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: David A. Barbour 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: MTSCost based Optimzer
Well, I was on 7.3.4 at the time.. so stored outlines were not an option. I haven't used them since, as the 8i databases I have are all new and we coded for cost-based to begin with. Rachel From: Mark Leith [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: MTSCost based Optimzer Date: Tue, 24 Apr 2001 08:21:57 -0800 Rachel I recently heard about something called a Stored Outline that supposedly can be used to lock the execution path that a query runs with. One of the uses of this was apparently to aid people moving from rule to cost based optimisation, where the rule execution path may be the better option to go down. Did/have you looked at these as an alternative to the hint? I am curious to see if anybody has used this option and what feedback they may have? Cheers Mark -Original Message- Carmichael Sent: Tuesday, April 24, 2001 02:16 To: Multiple recipients of list ORACLE-L turning on cost-based is easy: set the optimizer_mode parameter to choose in your init.ora, stop and start your database and then analyze all your tables. HOWEVER (and this is a biggie) this will not automatically make all your queries run faster. You will need to examine each and every query to see if it needs to be rewritten to take advantage of CBO. In fact, sometimes there are queries that run faster in rule-based, and I have had to include hints in queries to force the optimizer to run rule. From: Venkat_Kalepalli [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: MTSCost based Optimzer Date: Tue, 24 Apr 2001 02:25:25 -0800 Hello folks! I am working in SUN solaris 5.6 with Oracle 8i. I want to implement MTS on the Oracle server. Next we are running with Rule based optimizer and we want to change to costbased optimizer. I want to know what are the advantages we get on this and what are the steps to implement this? Any help is grateful... Rgd Venkat DBA. _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Mark Leith 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: MTSCost based Optimzer
Hi Rachel, It was my impresssion that joining an analyzed and unanalyzed tables will used CBO, but the choice of access path will be badly skewed since the assumptions Oracle makes about the unanalyzed table will probably be nowhere near reality. Obviously this can result in a terrible access path. Jay Miller -Original Message- Sent: Tuesday, April 24, 2001 12:45 PM To: Multiple recipients of list ORACLE-L definitely all or nothing -- if you have analyzed the large table and join it to an unanalyzed small table, you are going to get rule based From: Tim Sawmiller [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: MTSCost based Optimzer Date: Tue, 24 Apr 2001 07:51:18 -0800 I wouldn't... analyze all or nothing... [EMAIL PROTECTED] 04/24/01 09:46AM How would you decide that out of total big and small tables (say 1500) these are the tables need to be analysed and these are not, as in our case Transaction types are both OLTP and DSS ?? -Original Message- Carmichael Sent: Tuesday, April 24, 2001 6:46 PM To: Multiple recipients of list ORACLE-L turning on cost-based is easy: set the optimizer_mode parameter to choose in your init.ora, stop and start your database and then analyze all your tables. HOWEVER (and this is a biggie) this will not automatically make all your queries run faster. You will need to examine each and every query to see if it needs to be rewritten to take advantage of CBO. In fact, sometimes there are queries that run faster in rule-based, and I have had to include hints in queries to force the optimizer to run rule. From: Venkat_Kalepalli [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: MTSCost based Optimzer Date: Tue, 24 Apr 2001 02:25:25 -0800 Hello folks! I am working in SUN solaris 5.6 with Oracle 8i. I want to implement MTS on the Oracle server. Next we are running with Rule based optimizer and we want to change to costbased optimizer. I want to know what are the advantages we get on this and what are the steps to implement this? Any help is grateful... Rgd Venkat DBA. _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Deepender Kr Gupta 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: Tim Sawmiller 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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
RE: MTSCost based Optimzer
In this case it will use cost based optimizer anyway (one table with statistics enough), but exection plan can be very bad. Alex Hillman -Original Message- Sent: Tuesday, April 24, 2001 12:45 PM To: Multiple recipients of list ORACLE-L definitely all or nothing -- if you have analyzed the large table and join it to an unanalyzed small table, you are going to get rule based From: Tim Sawmiller [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: MTSCost based Optimzer Date: Tue, 24 Apr 2001 07:51:18 -0800 I wouldn't... analyze all or nothing... [EMAIL PROTECTED] 04/24/01 09:46AM How would you decide that out of total big and small tables (say 1500) these are the tables need to be analysed and these are not, as in our case Transaction types are both OLTP and DSS ?? -Original Message- Carmichael Sent: Tuesday, April 24, 2001 6:46 PM To: Multiple recipients of list ORACLE-L turning on cost-based is easy: set the optimizer_mode parameter to choose in your init.ora, stop and start your database and then analyze all your tables. HOWEVER (and this is a biggie) this will not automatically make all your queries run faster. You will need to examine each and every query to see if it needs to be rewritten to take advantage of CBO. In fact, sometimes there are queries that run faster in rule-based, and I have had to include hints in queries to force the optimizer to run rule. From: Venkat_Kalepalli [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: MTSCost based Optimzer Date: Tue, 24 Apr 2001 02:25:25 -0800 Hello folks! I am working in SUN solaris 5.6 with Oracle 8i. I want to implement MTS on the Oracle server. Next we are running with Rule based optimizer and we want to change to costbased optimizer. I want to know what are the advantages we get on this and what are the steps to implement this? Any help is grateful... Rgd Venkat DBA. _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Deepender Kr Gupta 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: Tim Sawmiller 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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
Re: MTSCost based Optimzer
On Tuesday 24 April 2001 06:15, Rachel Carmichael wrote: HOWEVER (and this is a biggie) this will not automatically make all your queries run faster. LOL! Most of us here know this, but for the benefit of you that don't, here goes. If your system is running fine on rule-based optimization, be *very* cautious about changing to cost-based. Sure, cost-based has lots of bells and whistles, and allows some fine grained control through hints, and is required for certain operations. But it can bite you when you aren't looking. One gotcha that comes to mind is that you will suddenly get hash joins performed on SQL where nested loops works just fine, and your app's will suddenly go very slowly. Changing to cost-based requires some planning, testing and familiarization. Don't just turn it on until you know what you are doing. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
