| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle CBO question
optimizer only considers a certain amount of permutations of plans.
OPTIMIZER_MAX_PERMUTATIONS and OPTIMIZER_FEATURES_ENABLE Starting with Oracle Database 10g, the OPTIMIZER_MAX_PERMUTATIONS initialization parameter has been made obsolete. If you are upgrading from Oracle9i and have OPTIMIZER_FEATURES_ENABLE set to 8.1.7 or lower and OPTIMIZER_MAX_PERMUTATIONS explicitly set to 2000 in the parameter file, then the release 8.1.7 default of 80000 will be used when you start up the release 10.1 database. Setting OPTIMIZER_FEATURES_ENABLE to 9.0.0 or higher will set the default to 2000.
given enough tables, you will go over this limit at which point the optimizer chooses the lowest cost plan it evaluated.
so yes, there is a limit, if you raise the max_permutations number, you may spend a lot longer parsing with little likelihood of finding a better join order.
There is intelligence to the order of evaluation I assume, so the options not considered I suspect are less likely to be better. not sure about that though, but I am sure the CBO experts around here will give you a more definitive answer.
corrections welcome.
Job
Joel.Patterson_at_crowley.com wrote: v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} Thanks for the replies, I believe I have the proper response.
A research project was not the intention. If the premise were true, I would imagine someone on the list would know and could answer immediately. The responses indicate for the most part that it is indeed untrue, and that the developer should verify his source or do some due diligence before requesting the same from the DBA.
(although I suspect he thought I knew the answer off the top of my head as well…). From the sounds of his questions though, something else is probably going wrong with his queries.
Sincerely,
Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546
From: Arul Ramachandran [mailto:contactarul_at_gmail.com]
Sent: Thursday, June 07, 2007 9:53 AM
To: Patterson, Joel
Cc: oracle-l_at_freelists.org
Subject: Re: Oracle CBO question
I'd like to know where the consultant developer got this information :-)
This can't be true, I've been working on CRM application where most queries are at the very least 25 way joins. No special CBO settings done for them.
Arul
On 6/7/07, Joel.Patterson_at_crowley.com <Joel.Patterson_at_crowley.com > 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?
--
Arul
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 07 2007 - 11:00:19 CDT
 |
 |