Re: avg_space field in DBA_TABLES

Note:237293.1 says this clearly. DBMS_STATS only collects stats used by CBO. Stats not used by CBO such as "Space Usage information" (EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT) and "Information on freelist blocks" (AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS) have to be collected by ANALYZE.

As to AVG_ROW_LEN between DBMS_STATS and ANALYZE, whether the value was already there matters (see Note:237537.1). Jonathan Lewis's CBO book (p.373 and pp.322-3) talks about whether AVG_ROW_LEN or sum(AVG_COL_LEN) is used by CBO, and the difference in estimating AVG_COL_LEN between ANALYZE and DBMS_STATS. In some boundary cases, the small difference *will* change CBO's decision to generate the execution plan.

Yong Huang

• Chen Shapira wrote -----

On Tue, Oct 27, 2009 at 3:08 AM, Surachart Opun <surachart_at_gmail.com> wrote:
> AVG_SPACE column filled in by ANALYZE but not dbms_stats
>
> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4347359891525
>

Thanks, Surachart. This indeed solved the issue. I thought DBMS_STATS is the same as ANALYZE... but now I know better!

> But what wrong with my "AVG_ROW_LEN" column

I wonder about that as well.
My avg row length is 31 with dbms_stats, 35 with analyze, and 29.8 when I compute it myself using vsize. Its not a big or meaningful difference, but I wonder what are they doing differently.

Chen       

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