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 -> Meaning of POSITION column in PLAN_TABLE

Meaning of POSITION column in PLAN_TABLE

From: Clive Backham <clive_at_capita.nildram.co.uk>
Date: Thu, 25 Oct 2001 16:27:36 GMT
Message-ID: <3bd839f4.2097305616@supernews.nildram.co.uk>


I have a performance problem that has me baffled.

A fairly complex, but by no means crazy, SQL statement executes in virtually no time at all (less than 1 sec) on my test system, yet takes about a minute on the production system. The size of the production system database is about 10 times that of the test system. I have checked that all the necessary indexes are present. The production system runs on a very powerful NT box, while the test system runs on a very much smaller NT system.

I thought that perhaps the different sizes of the databases might be causing the optimizer to choose a different execution path, so I did an EXPLAIN PLAN on both systems. The plans were identical, using exactly the same operations and indexes. However, the value of the POSITION column for the root of the plan (ie. the select statement) was about 350 for the test system and 630,000 for the production system. The Oracle reference manual suggests that this figure represents the cost of the statement, so it seems like this may have some bearing on the problem.

My question is, what exactly does this value represent? Any common reasons why it would be so vastly different in otherwise identical plans? I'm presuming that the differing sizes of the databases must be at the root of the problem. Received on Thu Oct 25 2001 - 11:27:36 CDT

Original text of this message

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