Re: Musings on tuning and the optimizer
Thanks chief. I'll keep an eye out for any outer joins misbehaving. Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den - Original Message - _PUSH_JOIN_PREDICATE is set to TRUE or the accessing query contains the PUSH_PRED hint. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: Musings on tuning and the optimizer
- Original Message - Have run across some interesting things while reading up on the optimizer. Same here! :-) Always thought that the RBO joins your tables in the order found in the FROM clause? Think again. Actually, I found one in Metalink that says with RBO, it's the reverse order of the FROM clause. Which I was aware of. But it also says: if there are NO STATS whatsoever and you use a hint that forces the CBO to be used or it is the default, the order of tables is left to right. Like what you get when using the hint ORDERED. Also, a few other interesting rules: - FIRST_ROWS tends to favour NESTED LOOPS. - ALL_ROWS tends to favour HASH and table scans. old hat in the above EXCEPT (!) if the CBO finds a table with no stats in a join. Then it's most likely hash, for both settings above. And optimizer_mode is CHOOSE and there is a mix of tables in the join with/without stats, then ALL_ROWS is the result. Speaking of the ORDERED hint, it can greatly reduce parse times when joining many tables. Obvious when you think about it. That, it certainly does! My experience too. It never hurts to help the optimizer do its job. All kinds of interesting stuff when you Read The Fine Manual. ;) And a few others. I found out the problem I reported a while ago with CBO suddenly going South on hash scan joins and completely ignoring nested loops or indexes is actually an introduced problem due to a change in CBO rules after 8.0.4. It first affected SAP users. It used to be fixed by a couple of events which due to their usefulness, became the two new optimizer cost adjust parameters in later versions of 8.0 and some of the 8.1. groan... roll on 9i, I've had enough of this optimizer! Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: Musings on tuning and the optimizer
On Monday 28 May 2001 04:45, Nuno Souto wrote: Always thought that the RBO joins your tables in the order found in the FROM clause? Think again. Actually, I found one in Metalink that says with RBO, it's the reverse order of the FROM clause. Which I was aware of. But it also says: if there are NO STATS whatsoever and you use a hint that forces the CBO to be used or it is the default, the order of tables is left to right. Like what you get when using the hint ORDERED. The RBO may consider them in reverse order of how they are listed in the FROm clause, but the docs are quite clear that this is not necessarily the order they will be joined in. And a few others. I found out the problem I reported a while ago with CBO suddenly going South on hash scan joins and completely ignoring nested loops or indexes is actually an introduced problem due to a change in CBO rules after 8.0.4. It first affected SAP users. It used to be fixed by a couple of events which due to their usefulness, became the two new optimizer cost adjust parameters in later versions of 8.0 and some of the 8.1. I think a lot of us have been bitten by that. It has caused a lot of extra work for me when upgrading. 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).
Re: Musings on tuning and the optimizer
On Monday 28 May 2001 04:45, Nuno Souto wrote: And a few others. I found out the problem I reported a while ago with CBO suddenly going South on hash scan joins and completely ignoring nested loops or indexes is actually an introduced problem due to a change in CBO rules after 8.0.4. It first affected SAP users. It used to be fixed by a couple of events which due to their usefulness, became the two new optimizer cost adjust parameters in later versions of 8.0 and some of the 8.1. groan... roll on 9i, I've had enough of this optimizer! Nuno, here's an interesting bit in the tuning manual that may take care of the hash join problem. For a view with multiple base tables on the right side of an outer join, the optimizer can push the join predicate into the view (see Pushing the Predicate into the View) if the initialization parameter _PUSH_JOIN_PREDICATE is set to TRUE or the accessing query contains the PUSH_PRED hint. Pushing a join predicate is a cost-based transformation that can enable more efficient access path and join methods, such as transforming hash joins into nested loops joins, and full table scans to index scans. I haven't had a chance to try this, as I don't currently have access to a database with real data in it, but this could be the way to prevent those hash join problems when migrating to 8i from 7.x. 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).
Musings on tuning and the optimizer
Have run across some interesting things while reading up on the optimizer. First of all, there is a very interesting paper on MetaLink titled Cost Based Optimizer - Common Misconceptions and Issues. This is note 35934.1. Among other things, it makes it clear under what conditions the CBO will be used. In a nutshell, any hint other than RULE will invoke the CBO. Are you using hints in your PL/SQL? No? Better read this paper. :) This interesting line was found in the 'Designing and Tuning for Performance' Manual. Common subexpression elimination is enabled with initialization parameter OPTIMIZER_FEATURES_ENABLE or by setting the _ELIMINATE_COMMON_SUBEXPR parameter to TRUE. Notice the sanctioned use of a hidden parameter. Always thought that the RBO joins your tables in the order found in the FROM clause? Think again. A partial quote from the same manual Usually, the optimizer does not consider the order in which tables appear in the FROM clause when choosing an execution plan. The optimizer makes this choice by applying the following rules in order: This from Chapter 4, 'The Optimizer How about CBO ignoring hints? It will ignore the ORDERED hint on outer joins if it violates certain conditions. Speaking of the ORDERED hint, it can greatly reduce parse times when joining many tables. Obvious when you think about it. All kinds of interesting stuff when you Read The Fine Manual. ;) 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).
RE: Musings on tuning and the optimizer
Isn't elimination of common subexpressions the default in 8.1.7? Also, the optimizer is sometimes overzealous and eliminates essential expressions. My source for this is Peoplesoft which suggests setting optimizer_features_enable = 8.1.6 When using their products against an 8.1.7 database. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Sunday, May 27, 2001 12:33 PM To: Multiple recipients of list ORACLE-L Have run across some interesting things while reading up on the optimizer. First of all, there is a very interesting paper on MetaLink titled Cost Based Optimizer - Common Misconceptions and Issues. This is note 35934.1. Among other things, it makes it clear under what conditions the CBO will be used. In a nutshell, any hint other than RULE will invoke the CBO. Are you using hints in your PL/SQL? No? Better read this paper. :) This interesting line was found in the 'Designing and Tuning for Performance' Manual. Common subexpression elimination is enabled with initialization parameter OPTIMIZER_FEATURES_ENABLE or by setting the _ELIMINATE_COMMON_SUBEXPR parameter to TRUE. Notice the sanctioned use of a hidden parameter. Always thought that the RBO joins your tables in the order found in the FROM clause? Think again. A partial quote from the same manual Usually, the optimizer does not consider the order in which tables appear in the FROM clause when choosing an execution plan. The optimizer makes this choice by applying the following rules in order: This from Chapter 4, 'The Optimizer How about CBO ignoring hints? It will ignore the ORDERED hint on outer joins if it violates certain conditions. Speaking of the ORDERED hint, it can greatly reduce parse times when joining many tables. Obvious when you think about it. All kinds of interesting stuff when you Read The Fine Manual. ;) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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: Musings on tuning and the optimizer
Ian, There are some bugs in 8.1.7.0 to do with common subquery elimination (1578644 and 1651014 and maybe others). These bugs may be fixed in 8.1.7.1.2 and / or 8.1.7.1.3 (I am currently trying to get clarification from Oracle on this). Also see Metalink note 137430.1. Regards, Bruce -Original Message- Sent: Monday, 28 May 2001 3:35 To: Multiple recipients of list ORACLE-L Isn't elimination of common subexpressions the default in 8.1.7? Also, the optimizer is sometimes overzealous and eliminates essential expressions. My source for this is Peoplesoft which suggests setting optimizer_features_enable = 8.1.6 When using their products against an 8.1.7 database. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Sunday, May 27, 2001 12:33 PM To: Multiple recipients of list ORACLE-L Have run across some interesting things while reading up on the optimizer. First of all, there is a very interesting paper on MetaLink titled Cost Based Optimizer - Common Misconceptions and Issues. This is note 35934.1. Among other things, it makes it clear under what conditions the CBO will be used. In a nutshell, any hint other than RULE will invoke the CBO. Are you using hints in your PL/SQL? No? Better read this paper. :) This interesting line was found in the 'Designing and Tuning for Performance' Manual. Common subexpression elimination is enabled with initialization parameter OPTIMIZER_FEATURES_ENABLE or by setting the _ELIMINATE_COMMON_SUBEXPR parameter to TRUE. Notice the sanctioned use of a hidden parameter. Always thought that the RBO joins your tables in the order found in the FROM clause? Think again. A partial quote from the same manual Usually, the optimizer does not consider the order in which tables appear in the FROM clause when choosing an execution plan. The optimizer makes this choice by applying the following rules in order: This from Chapter 4, 'The Optimizer How about CBO ignoring hints? It will ignore the ORDERED hint on outer joins if it violates certain conditions. Speaking of the ORDERED hint, it can greatly reduce parse times when joining many tables. Obvious when you think about it. All kinds of interesting stuff when you Read The Fine Manual. ;) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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: Reardon, Bruce (CALBBAY) 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).
