Re: avg_space field in DBA_TABLES
- From: Yong Huang <yong321@xxxxxxxxx>
- To: cshapi@xxxxxxxxx
- Date: Wed, 28 Oct 2009 18:38:21 -0700 (PDT)
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 <surachart@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
