Re: Max permutations
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 9 Feb 2004 21:43:55 -0000
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@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
