Re: avg_space field in DBA_TABLES
- From: Gerwin Hendriksen <gerwin.hendriksen@xxxxxxxxx>
- To: cshapi@xxxxxxxxx
- Date: Tue, 27 Oct 2009 07:46:14 +0100
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
Other related posts:
