Re: Help: Is cost of explain plan reliable?

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 19 Apr 2002 13:40:09 -0800
Message-ID: <3cc080a9@news.victoria.tc.ca>

Mike F (u518615722_at_spawnkill.ip-mobilphone.net) wrote:
: When we tune the sql statement, can we trust cost by the explan plan,
: or we should always set timing on and test ourselves?

Wild guess. The estimated cost is based on the table's statistics. If those are out of date then the cost estimates will be wrong. (i.e. the time it takes to run a query will not be what you expect).

Run the ANALYSE command to bring the statistics up to date. Run the analyse frequently enough to keep them up to date. I notice that the people I work with always re-analyse everything once per day. Don't know if its recommended anywhere, but that's what they do.

With new statistics, the cost estimate will be much closer to the real cost.

More importantly, Oracle will know how to correctly optimize the query so it runs as fast as possible. (Not only will the indicated cost change, but the plan itself will change.) Received on Fri Apr 19 2002 - 16:40:09 CDT