Possible Join Paths:

In Oracle the number of possible paths a join can take is based on the number of tables participating in the join. The raw number of possible combinations is determined using the n! of the number of tables. For those a bit rusty with what a factorial is, basically you take each integer up to "n" times each other, for example, 4!=(1*2*3*4) which is equal to 24. Now this doesn't seem too bad, until you do 8 and above tables. The value for 8! is 40,320 possible join paths. Place the most limiting tables first in the FROM clause.

Maximum Allowed Paths: 

In Oracle8i the parameter for setting the maximum allowed number of paths for the optimizer to consider is optimizer_max_permutations and it is set to 80,000. In 9i the parameter remains the same but it has been reduced to 2000, under the supposition that if you don't find the proper path in the first 2000 you probably won't find it. Reduce the setting of optimizer_max_permutations by at least one.

Join Order:

In experiential testing the join orders are evaluated left-to-right in the list of tables, therefore if you are doing a 9 table join in 8i and the important (most limiting) table is at the end of the list, it won't be considered. Likewise in 9i if it is placed 7th or greater in the list. What this leads us to is the first tip for join order evaluation: Place the most limiting tables for the join first in the FROM clause.

Prioritizing the Evaluation of Join:

It has been found that by changing the default value of the optimizer_max_permutations setting to a value less than the original setting that join orders are evaluated first. For example just reducing the setting by one in 8i to 79,999 improves the consideration of join orders. In 9i setting it to 1,999 has similar results. So this leads to our second tip: Set the optimizer_max_permutations parameter to slightly less than the default value to improve join order consideration.

Better Determination of Join Order:

In 8i consider resetting the _new_initial_join_orders undocumented parameter to true or set event 10131 to 1 or greater. In Oracle8i the undocumented parameter _new_initial_join_orders was set to false by default, by setting it to true a better determination of join orders is implemented in some cases. Under 9i and 10g this parameter has been defaulted to true. However, resetting any undocumented parameter should not be undertaken lightly, however Oracle itself recommends setting this parameter to true as a result of bug 1002975 in 8.1.6 and 8.1.7. So our final tip is to set the_new_initial_join_orders undocumented parameter to TRUE in 8i. Note that setting event 10131 to level 1 or higher will have the same result if you are squeamish about using undocumented parameters and utilizing the tip to reset optimizer_max_permutations may achieve the same result.  

Determining the path: 

Another parameter of interest is the optimizer_search_limit. The optimizer_search_limit parameter defaults to 5. If the number of tables returning more than a single row in the query is less than optimizer_search_limit then the full factorial number of possible joins will be used in determining the path. If the number of tables returning more than a single row is greater than the optimizer_search_limit then Cartesian products are eliminated from the possible joins considered. Therefore the maximum number of joins considered for a given query with less than optimizer_search_limit+1 of involved tables can be expressed by either the value of optimizer_max_permutations or the optimizer_search_limit_factorial, whichever is larger.

If the number of non-single row tables in a query is greater than optimizer_search_limit, then the maximum number of permutations to consider is the larger of: (optimizer_max_permutations or optimizer_search_limit factorial) divided by (number of possible start tables + 1)

STAR Join:

In DSS and DWH environments the optimizer_search_limit can be used to alter the threshold for use of STAR optimization paths. Usually you don't touch the setting of the optimizer_search_limit. However, the setting of 5 for optimizer_search_limit is the default setting and it can be set to any integer value. It is unlikely that resetting this value will have much effect unless you have a significant number of large joins such as in a DSS or data warehouse. If the STAR join is enabled, the optimizer_search_limit sets the threshold above which the STAR algorithm will be considered for tables joins. 

In 10g both the optimizer_max_permutations and optimizer_search_limit are being deprecated and made into undocumented settings defaulting to 2000 and 5 respectively.

 More Database Articles
   Database Security: Step by step guideline
   Important Concerns related to Oracle Compression!!
   DBA Tips for Shrinking Oracle Datafiles!!
   Oracle – Availing The Assistance Of Wget For Patch Download
   Efficient Tips for Oracle RAC Listener!!