Re: avg_space field in DBA_TABLES
Date: Tue, 27 Oct 2009 07:46:14 +0100
Message-ID: <19f73e520910262346m580df80awff90cc1f3cfd9ffd_at_mail.gmail.com>
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-lReceived on Tue Oct 27 2009 - 01:46:14 CDT