Re: avg_space field in DBA_TABLES

From: Yong Huang <>
Date: Wed, 28 Oct 2009 18:38:21 -0700 (PDT)
Message-ID: <>

Hi Chen,

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 <> wrote:
> AVG_SPACE column filled in by ANALYZE but not dbms_stats

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.


Received on Wed Oct 28 2009 - 20:38:21 CDT

Original text of this message