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

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Wed, 20 Oct 2004 23:17:19 +0200
Message-ID: <2CF83791A616BB4DA203FFD13007824A018D0E6A@MSXVS02.trivadis.com>

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

Received on Wed Oct 20 2004 - 16:12:31 CDT