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 Example: After Shrunk Space... SQL> select table_name,blocks,num_rows,avg_row_len,avg_space from dba_tables where table_name='T1' and owner='SURACHART'; TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN AVG_SPACE ------------------------------ ---------- ---------- ----------- ---------- T1 622 50000 35 4682 SQL> ALTER TABLE T1 SHRINK SPACE; Table altered. SQL> select table_name,blocks,num_rows,avg_row_len,avg_space from dba_tables where table_name='T1' and owner='SURACHART'; TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN AVG_SPACE ------------------------------ ---------- ---------- ----------- ---------- T1 622 50000 35 4682 SQL> execute dbms_stats.gather_table_stats(user,'t1', estimate_percent=>100); PL/SQL procedure successfully completed. SQL> select table_name,blocks,num_rows,avg_row_len,avg_space from dba_tables where table_name='T1' and owner='SURACHART'; TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN AVG_SPACE ------------------------------ ---------- ---------- ----------- ---------- T1 255 50000 32 4682 SQL> analyze table T1 compute statistics; Table analyzed. SQL> select table_name,blocks,num_rows,avg_row_len,avg_space from dba_tables where table_name='T1' and owner='SURACHART'; TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN AVG_SPACE ------------------------------ ---------- ---------- ----------- ---------- T1 255 50000 35 833 SQL> execute dbms_stats.gather_table_stats(user,'t1', estimate_percent=>100); PL/SQL procedure successfully completed. SQL> select table_name,blocks,num_rows,avg_row_len,avg_space from dba_tables where table_name='T1' and owner='SURACHART'; TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN AVG_SPACE ------------------------------ ---------- ---------- ----------- ---------- T1 255 50000 32 833 SQL> analyze table T1 compute statistics; Table analyzed. SQL> select table_name,blocks,num_rows,avg_row_len,avg_space from dba_tables where table_name='T1' and owner='SURACHART'; TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN AVG_SPACE ------------------------------ ---------- ---------- ----------- ---------- T1 255 50000 35 833 But what wrong with my "AVG_ROW_LEN" column Nice for this Practice Thank You Surachart Opun http://surachartopun.com On Tue, Oct 27, 2009 at 1:46 PM, Gerwin Hendriksen < gerwin.hendriksen@xxxxxxxxx> wrote: > Hi Chen, > > I tried your test case and I found indeed the same thing... See below: > > [oracle@ghperfsuite ~]$ sqlplus /nolog > > SQL*Plus: Release 11.1.0.7.0 - Production on Mon Mar 30 10:25:34 2009 > > Copyright (c) 1982, 2008, Oracle. All rights reserved. > > SQL> connect gerwin > Enter password: > Connected. > SQL> CREATE TABLE T1 > 2 (N NUMBER, > 3 NAME VARCHAR2(30) > 4 ); > > Table created. > > SQL> insert into T1 select rownum,'ABCDEFGHIJKLMNOPQRSTUVWXYZ' from > dual connect by level <= 100000; > > 100000 rows created. > > SQL> delete from t1 where mod(n,2)=0; > > 50000 rows deleted. > > SQL> commit; > > Commit complete. > > SQL> execute dbms_stats.gather_table_stats(user,'t1', > estimate_percent=>100); > > PL/SQL procedure successfully completed. > > SQL> select table_name,blocks,num_rows,avg_row_len,avg_space from > dba_tables where table_name='T1' and owner='GERWIN'; > > TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN AVG_SPACE > ------------------------------ ---------- ---------- ----------- ---------- > T1 622 50000 31 0 > > > But when do the gather statistics with the analyze command, you will > find your missing free space (AVG_SPACE). See below: > > > SQL> analyze table t1 compute statistics; > > Table analyzed. > > SQL> select table_name,blocks,num_rows,avg_row_len,avg_space from > dba_tables where table_name='T1' and owner='GERWIN'; > > TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN AVG_SPACE > ------------------------------ ---------- ---------- ----------- ---------- > T1 622 50000 35 4597 > > I remember from the old days that there was something with dbms_stats, > not filling all the statistics. I think AVG_SPACE is one of them. > > Regards, > > Gerwin Hendriksen > > 2009/10/27 Chen Shapira <cshapi@xxxxxxxxx>: > > Hi Oracle-L, > > > > I understood from the documentation that avg_space field in dba_tables > > should contain "Average amount of free space, in bytes, in a data > > block allocated to the table". > > > > So, I figured that if I create a table and insert a bunch of rows in > > there, and then I'll delete 50% of the rows and immediately analyze > > the table - I should see avg_space of more or less 4k. makes sense? > > > > Here's what I did (on 11.1.0.7): > > SQL> CREATE TABLE T1 > > 2 (N NUMBER, > > 3 NAME VARCHAR2(30) > > 4 ); > > > > Table created. > > > > SQL> > > SQL> insert into T1 select rownum, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' from dual > connec > > t by level <= 100000; > > > > 100000 rows created. > > > > SQL> > > SQL> commit; > > > > Commit complete. > > > > SQL> > > SQL> delete from t1 where mod(n,2)=0; > > > > 50000 rows deleted. > > > > SQL> > > SQL> commit; > > > > Commit complete. > > > > SQL> > > SQL> execute dbms_stats.gather_table_stats(user,'t1', > estimate_percent=>100) > > > > PL/SQL procedure successfully completed. > > > > SQL> > > SQL> select table_name,blocks,num_rows,avg_row_len,avg_space from > dba_tables whe > > re table_name='T1' and owner='SYS'; > > > > TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN > AVG_SPACE > > ------------------------------ ---------- ---------- ----------- > ---------- > > T1 511 50000 31 > 0 > > > > > > Where's my free space? Am I getting the definition of avg_space wrong > > or is it a problem with my understanding of how row delete will work? > > > > Thanks, > > Chen Shapira > > -- > > //www.freelists.org/webpage/oracle-l > > > > > > > -- > //www.freelists.org/webpage/oracle-l > > >