RE: Oracle CBO question

  • From: Jože Senegačnik <Joze.Senegacnik@xxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 8 Jun 2007 21:00:45 +0200

According to Mohamed Zait's presentation last year in Zurich at Trivadis CBO 
Days the CBO does join search space optimizations like:

-          When number of tables <=5 it performs exhaustive search 

-          When number of tables  > 5 the it performs heuristic search. The 
search is repeated using various initial join order heuristics. An example of 
initial join order heuristic is to order tables on their number of rows.

-          Number of join permutations is limited to 2000 for each query block 
(each select statement within a statement) and the plan prefix of previous join 
permutations is reused and note performed once again

-          1-Row tables are identified and fixed as join prefix (Equality 
predicate on unique column means 1-Row table)

-          Current partial plan is discarded as soon as it becomes more 
expensive than the best plan so far

 

Regards, Joze

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jared Still
Sent: 7. junij 2007 19:58
To: Joel.Patterson@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Oracle CBO question

 

On 6/7/07, Joel.Patterson@xxxxxxxxxxx <Joel.Patterson@xxxxxxxxxxx> wrote:

        I have received a question from a consultant developer - below.   Can 
anyone answer?

        I was once told that the CBO can only handle a certain number of tables 
in a query (16 iirc).  Do you know what the CBO does when there are more?  Try 
to optimize based on knowledge of the first 16 tables in the Where clause? 


Perhaps what he heard was that the number of join orders considered 
by the optimzer could not accomodate checking all possible join orders.

Though I'm not sure why that would matter, as it seems very unlikely
you would ever want to consider all possible join orders.

With 16 tables all possible join orders is !16 which is 20,922,789,888,000 
possibilities.

Having seen what happens when the 8i optimizer tries to consider all 80k
possibilities when considering join orders I would say it might be a problem. 
(setting optimizer_max_permutations = 2000 fixed that app. thankfully the 
default on newer incarnations of Oracle)

Why the number 16 was chosen I am not sure.
Perhaps someone speculated there was a 4 bit limitation? 

With the 8i default of 80k permutations the number of possibile join orders
runs out somewhere between 8! and 9!.

With OMP = 2k it runs out somewhere between 6! and 7!.

-- 
Jared Still 
Certifiable Oracle DBA and Part Time Perl Evangelist

Other related posts: