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 -> EXPLAIN PLAN madness!

EXPLAIN PLAN madness!

From: Ed Stevens <Ed_Stevens_at_nospam.noway.nohow>
Date: Wed, 14 Nov 2001 22:07:52 GMT
Message-ID: <3bf2ea7a.32307966@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.)
Received on Wed Nov 14 2001 - 16:07:52 CST

Original text of this message

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