Re: System Statistics and the CBO

Mladen,

She was asking why the CBO chose a different index with system stats than without. By comparing the traces from each case, she will find the answer. A 10053 trace is a dump of the decision tree used by the CBO to arrive at an execution plan. It is an outstanding tool in gaining a better understanding of CBO behaviour.

Patty,

If you'll also provide the output from autotrace with statistics from each case, that will provide valuable information as well. In fact, a 10046 level 12 trace for each case would be even better, but may be too large to send to the group. Feel free to send them directly to me if you like.

John Smiley
Technical Management Consultant
TUSC, Inc

On 6/17/05, Mladen Gogala <mgogala_at_allegientsystems.com> wrote:
> John Smiley wrote:
>
> >Hi Patty,
> >
> >When you gather/import system statistics, you provide the CBO with
> >more information about your system and allows it to use CPU costing as
> >well as I/O costing. System stats should be gathered while the system
> >is under the type of load you want to use the stats for. If you have
> >differing loads (say transaction processing during the day and batch
> >processing at night), you may want to have more than one set of system
> >stats to fit the load.
> >
> >
> >
> John, with all due respect, she was asking about the differences in the
> access path of a query, not how to
> generate optimizer trace. Second, optimizer trace does not show you the
> way that optimizer uses system
> statistics. The values from SYS.AUX_STATS$ are used to calculate the
> costs. Exactly how are they used
> is still not widely known and there is more then one unanswered
> question. Thanks for posting your information, though,
> I'm sure that many people on this group will find it very interesting.
> Something completely unrelated: I know that TUSC was distributing
> Oracle9i Metadata posters and there was
> a poster requesting something like that earlier today? Do you still have
> some?
>
> --
> Mladen Gogala
> Oracle DBA
> Ext. 121
>
>
>

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