Re: CBO cost and avg_row_len

ydias_at_hotmail.com (dias) wrote in message news:<55a68b47.0307100208.30ffd38_at_posting.google.com>...
> Thanks Sybrand,
>
> But why DBMS_STATS.GATHER_TABLE_STATS generates 3 statistics :
> num_rows, blocks and avg_row_len ?
>
> Dias
>
> Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<4qkogvoob381bka3g96sdg768718rsbhpq_at_4ax.com>...
> > On 9 Jul 2003 08:34:56 -0700, ydias_at_hotmail.com (dias) wrote:
> >
> > >Hi,
> > >
> > >Can someone explain when the CBO takes into account the value of
> > >avg_row_len to estimate an access plan cost ?
> > >
> > >I suppose that the CBO uses this value to estimate the cost for a
> > >table scan, in combination with the number of blocks and the value of
> > >db_file_multiblock_read_count ?
> > >
> > >Thanks
> >
> > It doesn't. It would also be unnecessary, as a block is a block is a
> > block and a table scan is a table scan is a table scan. Oracle will
> > always read a complete block. The number of blocks in use is taken
> > into account.
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address

Dias, Sybrand answered your statement on scanning. but in regards to your first question on when the avg_row_len statistic is used I would like to add a couple of comments.

First, I am not sure when avg_row_len is used, but I believe that all statistics gathered by dbms_stats, unlike some of those gathered by the analyze commad (such as chained rows), are used by the CBO. The use may be indirect in that some statistics may be used in calculating other statistics that are directly used. For some reason I think the use of avg_row_len is in estimating logical IO, but I would not bank on that.

For the average DBA it is probably much more important to know which statistics are important to the optimizer and to be able to recognize when they need recalculation than to know exactly how the CBO uses them.

HTH -- Mark D Powell -- Received on Thu Jul 10 2003 - 08:41:55 CDT