RE: Why the optimizer is not choosing the best plan?

Hi=20

>I have the values like below, but still it is going for NL_JOIN.
>
>SQL> show parameter hash
>
>NAME                                 TYPE    VALUE
>------------------------------------ ------- =
------------------------------
>hash_area_size                       integer 2000000
>hash_join_enabled                    boolean TRUE
>hash_multiblock_io_count             integer 0
>SQL> show parameter join
>
>NAME                                 TYPE    VALUE
>------------------------------------ ------- =
------------------------------
>always_anti_join                     string  HASH
>always_semi_join                     string  HASH
>hash_join_enabled                    boolean TRUE

The CBO estimations depend on many parameters, not only the hash-join =
related...=20
In 8i (since you have the ALWAYS_*_JOIN parameters, I guess you are =
working with 8i...) the most important that should be set for an optimal =
CBO configuration are the following:
- BITMAP_MERGE_AREA_SIZE
- DB_FILE_MULTIBLOCK_READ_COUNT
- HASH_AREA_SIZE
- OPTIMIZER_MODE
- OPTIMIZER_FEATURES_ENABLE
- OPTIMIZER_MAX_PERMUTATIONS
- OPTIMIZER_INDEX_COST_ADJ
- OPTIMIZER_INDEX_CACHING
- SORT_AREA_SIZE

Unfortunately, it's not possible to advice you on how to set them. In =
fact each system as its own behavior...

In addition, as already mentioned by Cary, object statistics could lead =
to some problems as well. Do you use histograms? Bind variables? Do you =
gather the statistics for all columns? Etc....

Therefore, you should try to understand what's going up (e.g. with event =
10053). Then you should be able to tweak the configuration or collect =
"better" statistics.


Chris =20




--
http://www.freelists.org/webpage/oracle-l

Other related posts: