RE: Which plan is better - what COST really means ?
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 14 Jan 2005 04:58:26 +0000 (GMT)
Dunno about the "one execution as an input to the next plan" but there is the
SQL tuning sets,
which is the basically the:
"Mr CBO, I don't care how long as it takes, go and know yourself out finding a
the best plan - get
some more stats if you have to, do some dynamic sampling if needed, but make
sure the information
that comes out gives me a damn close to optimal plan"
option...It would be really really cool if you use this facility on a standby
database (then
again, maybe you can? I've never really looked into it).
Cheers
Connor
--- Cary Millsap <cary.millsap@xxxxxxxxxx> wrote:
> 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@xxxxxxxxxxxxx =
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Jaffar_DBA
> Sent: Thursday, January 13, 2005 3:01 AM
> To: premj@xxxxxxxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxx> =
> 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.
> > --
> > //www.freelists.org/webpage/oracle-l
> >=20
>
>
> --=20
> Best Regards,
> Jaffar, OCP DBA
> Banque Saudi Fransi
> Saudi Arabia
> --
> //www.freelists.org/webpage/oracle-l
>
> --
> //www.freelists.org/webpage/oracle-l
>
=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
------------------------------------------------------------
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
