Re: Max permutations

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 9 Feb 2004 21:43:55 -0000
Message-ID: <003501c3ef55$d19b7270$6702a8c0@Primary>

Performance Tuning 101 says any number
less than 80,000 (e.g. 79,000), it doesn't give a magic number.

The book agrees with Metalink note 66030.1 that changing the value causes Oracle to try different tables as the leading table in the join order 'prematurely'. (Although the book says 8, and Metalink says 4 tables may be chosen).

The is also a note by Steve Adams that says the same sort of thing - I think he agrees with the 8, but I can't remember.

My problem, until Jared sent me the sample, was that my test case (which had actually more than the implied 8 tables) didn't do anything to bypass the normal join order sequencing. At present I'm working on the assumption that my test case "failed" because it was auto-generated and turned into an extremely symmetrical problem that the CBO recognised.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- From: "Wolfgang Breitling" <breitliw_at_centrexcc.com> To: <oracle-l_at_freelists.org> Sent: Monday, February 09, 2004 8:41 PM Subject: Re: Max permutations

Dave Ensor, in his presentation at UKOUG claims - reduce the setting (say to 40,320) and

Oracle uses an enhanced strategy to decide which join orders to evaluate
very significantly increases the probability of picking the correct driving table for a join of more than 8 tables
decreases parse time for joins of more than 8 tables

Gaja, Kirti, et al. also claim in their book "Performance Tuning 101" that OMP 79,999 and 79,998 have "magic" powers. That was for Oracle 8.

That last piece is from memory, so it may not be correct. The book is at home and I'm at a client's site.
Kirti, care to confirm/deny/comment.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Feb 09 2004 - 15:43:55 CST