Re: avg_space field in DBA_TABLES

From: Gerwin Hendriksen <gerwin.hendriksen_at_gmail.com>
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-l
Received on Tue Oct 27 2009 - 01:46:14 CDT

Original text of this message