Re: avg_space field in DBA_TABLES
Date: Tue, 27 Oct 2009 17:08:41 +0700
Message-ID: <1dacf81e0910270308y19c56ce3i3f8ccd3627ab8d5_at_mail.gmail.com>
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_at_gmail.com> wrote:
> Hi Chen,
>
> I tried your test case and I found indeed the same thing... See below:
>
> [oracle_at_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_at_gmail.com>:
> > 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
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 27 2009 - 05:08:41 CDT