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

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

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