Re: EXPLAIN PLAN madness!

Sybrand,

I would hope you know me better than that by now!

No puzzle or riddle intended. My question was more general that the details of the two plans:

1. Why would the same query yeild vastly different plans on two supposedly identical databases?
2. How could a plan not reference a table that was being selected on?

The details that were provided were to set up the situation and show that I had already covered the usual suspects. Indexes, data volume, etc.

• Ed

On Thu, 15 Nov 2001 00:43:57 +0100, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>
>"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message
>news:3bf2ea7a.32307966_at_ausnews.austin.ibm.com...
>> Subject: EXPLAIN PLAN madness!
>>
>> Platform: Oracle 8.0.5 SE, NT 4
>>
>> I pulled a heavy-hitting query from v$sqlarea to see if I couldn't
>> improve it a bit. First thing I do is run an explain plan in both the
>> production and test databases. This query is particularly nasty, with
>> joins on 4 different local tables and 2 references to a synonym
>> pointing to a remote database. To my surprise, the production and
>> test plans were wildly different. The one in test had two separate
>> groups of deeply nested outer loops, with full table scans of the same
>> table at the bottom of each, and a computed cost of 12722. The
>> production version had a single group of nested hash joins and a
>> single scan of each of the 4 tables, with a computed cost of 5301.
>>
>> Confirmed that index structures are identical.
>>
>> Data volume is within 5% of each other with row count differences of
>> less than 1000 on tables with total row counts of 15,000 to 25,000.
>>
>> Performed ANALYZE . . . COMPUTE on tables and all their indexes in
>> both databases.
>>
>> Re-ran the explain plans and got the same results as before. After
>> staring at them a bit something else jumped out at me . . . . the plan
>> from the production db had a full table scan on a table that wasn't
>> even mentioned in the plan from the test db. Double-checked the query
>> and that table is very much in the FROM clause, and does have at least
>> one column referenced in the SELECT column list.
>>
>> So now I have two questions:
>>
>> How do I explain these wildly different plans?
>>
>> How is it possible to generate a plan that doesn't reference a table
>> used in the query?
>>
>> --
>> Ed Stevens
>> (Opinions expressed do not necessarily represent those of my employer.)
>
>OK, is this a puzzle? A riddle?
>I will take my crystall ball to bed with me and try to think up your explain
>plan and other details which are sadly missing from your post.
>
>Regards
>
>--
>Sybrand Bakker
>Senior Oracle DBA
>
>to reply remove '-verwijderdit' from my e-mail address
>
>
>

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