RE: optimizer_max_permutations
This makes me think of a stat that would be nice to see...it would be nice if I could see how many permutations the database was going through for each sql statement that it parsed. I might have a database that rarely uses more than 1000 permutations and I might have one that typically uses 10,000. By determining how often the statement is parsed and how many average permutations are used I might be able to more effectively judge the impact of lowering or raising this value. -Original Message- Sent: Tuesday, August 26, 2003 11:00 AM To: Multiple recipients of list ORACLE-L Yes, it is required in one of our COTS applications. Inserts into complex views with instead-f triggers. The views are quite complex, ridiculously so, one might say. Before setting optimizer_max_permutations=1000, it took a very long time to parse those views. Jared On Tue, 2003-08-26 at 05:44, Boivin, Patrice J wrote: Has anyone worked with this one? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch11 23.htm#81357 http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1 123.htm#81357 Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [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.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] 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: [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: optimizer_max_permutations
turns out the parameter only governs how much time the optimizer can spend looking for the best execution plan... 8 means no limit, OK for small SQL queries but for complex ones parsing can take too long. So this has nothing to do with TEMP space usage after all. sort_area_size is probably the one for TEMP. Patrice. -Original Message- Sent: Tuesday, August 26, 2003 11:58 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Yes, it is required in one of our COTS applications. Inserts into complex views with instead-f triggers. The views are quite complex, ridiculously so, one might say. Before setting optimizer_max_permutations=1000, it took a very long time to parse those views. Jared On Tue, 2003-08-26 at 05:44, Boivin, Patrice J wrote: Has anyone worked with this one? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch11 23.htm#81357 http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1 123.htm#81357 Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] 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: [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: optimizer_max_permutations
Title: Message Hi! Increasing it from it's default won't probaly helpto get a better execution plan, unless you have 10-way or even more complex joins. However, having a large value of this parameter with complex joins can push your parse times (QEP calculations) very high! That's why one has to reduce it from 8 to 2000 in latest Oracle Apps versions for example. And reducing it in regular apps with not-so-complex joins won't kill either, because a 7-way join can be evaluated in 7! = 5040 permutations and Oracle uses several optimization mechanisms such QEP early elimination, join order intermediate cutoffs, putting cartesian joins last in evaluation sequence if there's moretables in join thanspecified by _optimizer_search_limit parameter, etc.. I'd say you definitely get the best plan (in CBO sense) with optimizer_max_permutations when doint 8-way joins, you probably get the best plan even with 9-way joins, and you get near-the-best plan with higher, 10-12 ones too, thanks to internal optimizations in finding theoptimal plan. Tanel. - Original Message - From: Boivin, Patrice J To: Multiple recipients of list ORACLE-L Sent: Tuesday, August 26, 2003 3:44 PM Subject: optimizer_max_permutations Has anyone worked with this one? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1123.htm#81357 Patrice.
Re: optimizer_max_permutations
One thing that the docs don't mention is that '8' (the default in 8) is also a special boundary value. Anything less than 80,000 changes some of ways the optimizer does it work, ie, its not just a reduction in permutations. Can't remember the specifics - join orders spring to mind but there is a metalink note about it. Because of this, there's a school of thought that even on 8i, adopting the (9i default) value of 2000 will improve the general optimizer performance (ie the quality of the decisions it makes). Cheers Connor --- Boivin, Patrice J [EMAIL PROTECTED] wrote: Has anyone worked with this one? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch11 23.htm#81357 http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1 123.htm#81357 Patrice. = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] 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: [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: optimizer_max_permutations
Yes, it is required in one of our COTS applications. Inserts into complex views with instead-f triggers. The views are quite complex, ridiculously so, one might say. Before setting optimizer_max_permutations=1000, it took a very long time to parse those views. Jared On Tue, 2003-08-26 at 05:44, Boivin, Patrice J wrote: Has anyone worked with this one? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch11 23.htm#81357 http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1 123.htm#81357 Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [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: optimizer_max_permutations
Just fyi - it seems that Oracle had realized this in advance and has specifically instructed the Oracle Applications 11i installations to set this to 2000. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, August 26, 2003 8:55 AM To: Multiple recipients of list ORACLE-L One thing that the docs don't mention is that '8' (the default in 8) is also a special boundary value. Anything less than 80,000 changes some of ways the optimizer does it work, ie, its not just a reduction in permutations. Can't remember the specifics - join orders spring to mind but there is a metalink note about it. Because of this, there's a school of thought that even on 8i, adopting the (9i default) value of 2000 will improve the general optimizer performance (ie the quality of the decisions it makes). Cheers Connor --- Boivin, Patrice J [EMAIL PROTECTED] wrote: Has anyone worked with this one? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch11 23.htm#81357 http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1 123.htm#81357 Patrice. = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] 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: [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.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [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: optimizer_max_permutations
Yes starting from db version 8.1.7 for 11i. For 8.1.6 there was no such requirement IIRC. I tried to be smart and ignored the recommendation once when upgrading to 8.1.7 - the result, some queries which were using a view (which name I don't remember anymore) got extremely slow. I learned a lesson. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 26, 2003 8:54 PM Just fyi - it seems that Oracle had realized this in advance and has specifically instructed the Oracle Applications 11i installations to set this to 2000. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, August 26, 2003 8:55 AM To: Multiple recipients of list ORACLE-L One thing that the docs don't mention is that '8' (the default in 8) is also a special boundary value. Anything less than 80,000 changes some of ways the optimizer does it work, ie, its not just a reduction in permutations. Can't remember the specifics - join orders spring to mind but there is a metalink note about it. Because of this, there's a school of thought that even on 8i, adopting the (9i default) value of 2000 will improve the general optimizer performance (ie the quality of the decisions it makes). Cheers Connor --- Boivin, Patrice J [EMAIL PROTECTED] wrote: Has anyone worked with this one? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch11 23.htm#81357 http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1 123.htm#81357 Patrice. = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] 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: [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.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [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.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [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).
