Re: CBO cost and avg_row_len

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:2687bb95.0307100541.10a6a305_at_posting.google.com...
> ydias_at_hotmail.com (dias) wrote in message
news:<55a68b47.0307100208.30ffd38_at_posting.google.com>...
> 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.

I agree, if one does a 10053 trace then AVG_ROW_LEN is one of the pieces of 'BASE STATISTICAL INFORMATION' listed strongly suggesting that it is used in someway. If the table isn't analyzed the default value of AVG_ROW_LEN is used to estimate the number of rows in the table.

> 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.

Absolutely, especially as this information doesn't appear to be published anywhere.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK