Re: CBO cost and avg_row_len

On Thu, 10 Jul 2003 16:30:15 +0100, Niall Litchfield <n-litchfield_at_audit- commission.gov.uk> wrote:

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

Just a wild stab in the dark here... but if BLOCKS=400000 and avg_row_len=50 and num_rows=10, you can be pretty certain that most of those blocks under the high water mark are full of nothing but fresh air. Packed optimally, the table should only consume 500 bytes, which should comfortably fit into one block. The statistics are therefore screaming at you, "Inflated High Water Mark Syndrome!!" (ie, huge inserts, followed by massive deletes).

If you asked me then to do a select * from table, I would ordinarily expect to do a full table scan, and never mind visiting the index. But with statistics like those, I suspect I'd rather access the table via the index. Indexes give you rowid access to the table data, and you don't have to wander through 399999 empty blocks to retrieve all 10 rows.

So, yes: knowing your table is fluffier than a very fluffy soufflé is actually important to the optimizer, and avg_row_len is needed to tell it that.

~QM

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

-- 
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/