Re: EXPLAIN PLAN madness!

From: Ed Stevens <Ed_Stevens_at_nospam.noway.nohow>
Date: Thu, 15 Nov 2001 17:04:33 GMT
Message-ID: <3bf3f396.61729662@ausnews.austin.ibm.com>

Upgrade to 8.1.7 is planned for Q2 2002

On closer inspection, the plan that did not reference the 4th table DID have an index range scan on the table's primary key, which is the one column from that table that was selected.

On both databases we find:

  hash_area_size = 0
  hash_join_enabled = true
  hash_multiblock_io_count = 1

these are defaulted values, there is no mention of them in the init.ora files. How about differences in some memory spec in the SGA?

• Ed Stevens

On Thu, 15 Nov 2001 09:11:53 GMT, nsouto_at_optushome.com.au.nospam (Nuno Souto) wrote:

>In a sudden spasm of awareness,
>Ed Stevens doodled thusly:
>
>>Subject: EXPLAIN PLAN madness!
>>
>>Platform: Oracle 8.0.5 SE, NT 4
>
>Hmmmm time to upgrade, mayhap?
>
>
>>
>>So now I have two questions:
>>
>>How do I explain these wildly different plans?
>
>Different init.ora parameters that favour hash joins in production.
>
>
>>
>>How is it possible to generate a plan that doesn't reference a table
>>used in the query?
>>
>
>Because it might be reading an index of that table instead of the
>table itself? Quite common on joins to lookup tables.
>
>
>Cheers
>Nuno Souto
>nsouto_at_optushome.com.au.nospam

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

Received on Thu Nov 15 2001 - 11:04:33 CST