RE: Which plan is better - what COST really means ?
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <hamcdc@xxxxxxxxxxx>
- Date: Fri, 14 Jan 2005 08:25:22 +0100
Hi
>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=20
>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"
SQL tuning sets are, AFAIK, only used as input to the SQL tuning =
advisor. What you are describing is what the SQL tuning advisor do to =
tune a SQL statement (this part of the advisor is called automatic SQL =
tuning).
The idea of automatic SQL tuning it that the CBO takes time to analyze =
the statement and the "environment", i.e. to find out if:
- statistics are missing or stale
- indexes are missing
- SQL statement can eventually be rewritten to improve performance
- CBO does wrong assumption during some cost estimation
The most important part, IMHO, is the last one. In fact the CBO tries to =
partially execute the whole or just a part of the SQL statement to find =
out where it is wrong. In the case it founds that it has problem with =
some particular estimation a so called SQL profile is created to avoid =
this problem and therefore to let the CBO do a better job.
Chris
--
//www.freelists.org/webpage/oracle-l
Other related posts:
