Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN madness!

Re: EXPLAIN PLAN madness!

From: Ed Stevens <Ed_Stevens_at_nospam.noway.nohow>
Date: Tue, 20 Nov 2001 21:01:54 GMT
Message-ID: <3bfabdd4.24772490@ausnews.austin.ibm.com>


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

>Hi,
>

<snip>
>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.

Eureka!

I set up a series of tests, working 8 possible combinations of those parms, using these values:

HASH_JOIN_ENABLED = true or false
OPTIMIZER_INDEX_CACHING = 0 or 100
OPTIMIZER_INDEX_COST_ADJ = 10 or 100.

I then analyzed the results with TKPROF. In the end, HASH_JOIN_ENABLED made no difference, but the combination of OPTIMIZER_INDEX_CACHING = 100 and OPTIMIZER_INDEX_COST_ADJ = 10 made a huge difference. The plan went from a bunch of hash joins and 4 full table scans to a bunch of nested loops, 1 full table scan and 3 access by index rowid.

After reading up on OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ I'm astounded at what I found. Defaults are

OPTIMIZER_INDEX_CACHING=0
OPTIMIZER_INDEX_COST_ADJ=100 As I understand it, this is telling the optimizer that 1) don't ever expect to find an index block in the cache, and 2) it's just as expensive to read an index as a full table scan.

Thanks for the lead. Do to up-time requirements, I won't be able to put this into production until this weekend. "Film at eleven."

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)
Received on Tue Nov 20 2001 - 15:01:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US