RE: Which plan is better - what COST really means ?

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 13 Jan 2005 08:25:03 -0600
Message-ID: <001201c4f97b$b1be0310$6400a8c0@CVMLAP02>

People seem to cloud the concepts of the pre-run estimated runtime of a query (officially its "estimated cost") and the post-run operationally measured runtime of a query. The problem with a CBO is that before a = query
runs, it can only estimate a cost using a model. The model is not = perfect
(although it's usually more perfect than the input--db and schema statistics--than DBAs feed the model).

Now, AFTER a query runs, it's easy to see whether the estimated cost was = any
good or not. But who wants the CBO to run a query six ways before = choosing a
plan?!

If I recall correctly, 10g has some operational memory built into the = CBO
that allows the CBO to use operationally measured response times for a = given
query as an input into computing the next plan.

Mr. Sharman, care to comment? (And by the way, will I be seeing you in = M=E5l=F8v
next week?)

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:

- Performance Diagnosis 101: 2/23 Houston, 3/16 Salt Lake City
- SQL Optimization 101: 2/7 Dallas
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jaffar_DBA
Sent: Thursday, January 13, 2005 3:01 AM To: premj_at_rediffmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Which plan is better - what COST really means ?

I definately agree with about what toms is saying. I had expereince where higher COST queries much father than lower COST queries in the CBO. Taking consideration of response time would be more better than COST of the query.

On 13 Jan 2005 08:57:11 -0000, Prem Khanna J <premj_at_rediffmail.com> = wrote:
> Friends,
> when i have got two plans , how do i decide
> which one is the best ?

>=20

> just read in asktom , that COST of a plan ,
> does not have any correspondence with response
> time of a query.

>=20

> i.e.,a query with higher COST than another
> can be faster.

>=20

> if so , what in the execution plan does help
> me to decide about a good (faster) plan ?

>=20

> unless i execute it , can't i know it ?
> is that the only way !!!

>=20

> Can someone throw light on this ?
> The environment is oracle 9.2.0.4/AIX5.2L.

>=20

> Thanks in advance.

>=20

> Regards,
> Prem.
> --
> http://www.freelists.org/webpage/oracle-l
>=20

--=20
Best Regards,
Jaffar, OCP DBA
Banque Saudi Fransi
Saudi Arabia

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 13 2005 - 08:23:50 CST