Re: EXPLAIN PLAN madness!

Test database is loaded from production regularly, so key distribution should be the same, even when total data volume diverges through use.

• Ed

On 15 Nov 2001 06:12:24 -0800, fmas_at_hubwoo.com (fredericm) wrote:

>Hi,
>
>Think volume of the 2 environments are in cause or data key
>distribution in the tables.
>If DB1 table have 40% of same key value table scan may be performed or
>hash join
>If DB2 Get only 10% of same key values nested loop can be performed
>
>
>in fact, if an index on 2 keys is not selective on the first key,
>optimizer can choose Full scan or hash join until the table grow or
>the selectivity is better.
>
>Try with a hint select /*+FIRST_ROWS*/ .... in the query
>
>Or
>
>Try this instance parameters:
>
>HASH_JOIN_ENABLED=FALSE; -- to avoid hash join exept if you get hash
>index, else hash join plan must construct hash join table before
>perform and can take several hours before go....
>OPTIMIZER_INDEX_CACHING=100;
>OPTIMIZER_INDEX_COST_ADJ=10; --to favour the nested loop plan, this
>say scan an index is only 10%of the cost to scan a table
>
>
>
>You can try this parameters with an alter session.

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)