Re: Table fragmented
From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 11 Dec 2008 17:23:26 -0800
Message-ID: <1229045010.640757@bubbleator.drizzle.com>
>
> SQL> create table space_waster(
> 2 stuff varchar2(4000)
> 3 )
> 4 storage(initial 4M next 4M);
>
> Table created.
>
> SQL>
> SQL> insert into space_waster
> 2 values('This is a terrible waste of space, but, dammit, it''s MY
> waste of space!!! It''s MINE, MINE, MINE, MINE, MINE, MINE, MINE, ALL
> MINE!!!!!
> Hahahahahahahahahahahahahahahahahahahahahahahaha!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
>
> 1 row created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> exec dbms_stats.gather_table_stats(ownname=>null,
> tabname=>'SPACE_WASTER', cascade=>true);
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> select (pct_free/100)*8000*blocks/1024||'kb' "room for updates"
> 2 from user_tables
> 3 where table_name = 'SPACE_WASTER';
>
> room for updates
> ------------------------------------------
> 46.875kb
>
> SQL>
> SQL> select table_name,round((blocks*8192/1024),2)||'kb' "current
> size"
> 2 from user_tables
> 3 where table_name = 'SPACE_WASTER';
>
> TABLE_NAME current size
> ------------------------------
> ------------------------------------------
> SPACE_WASTER 480kb
>
> SQL>
> SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb'
> "occupied space"
> 2 from user_tables
> 3 where table_name = 'SPACE_WASTER';
>
> TABLE_NAME occupied space
> ------------------------------
> ------------------------------------------
> SPACE_WASTER .28kb
>
> SQL>
>
> Gee, is it wasted space because I haven't populated it yet? Or, don't
> I understand the 'problem'?
>
>
> David Fitzjarrell
Date: Thu, 11 Dec 2008 17:23:26 -0800
Message-ID: <1229045010.640757@bubbleator.drizzle.com>
ddf wrote:
> On Dec 4, 3:03 pm, "astalavista" <nob..._at_nowhere.com> wrote:
>> Table size (with fragmentation) >> >> SQL> select table_name,round((blocks*8),2)||'kb' "size" >> 2 from user_tables >> 3 where table_name = 'BIG1'; >> >> TABLE_NAME size >> ------------------------------ ------------------------------------------ >> BIG1 72952kb >> >> Actual data in table: >> >> SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" >> 2 from user_tables >> 3 where table_name = 'BIG1'; >> >> TABLE_NAME size >> ------------------------------ ------------------------------------------ >> BIG1 30604.2kb >> >> Note = 72952 - 30604 = 42348 Kb is wasted space in table >> >> The difference between two values is 60% and Pctfree 10% (default) - so, the >> table has 50% extra space which is wasted because there is no data.
>
> SQL> create table space_waster(
> 2 stuff varchar2(4000)
> 3 )
> 4 storage(initial 4M next 4M);
>
> Table created.
>
> SQL>
> SQL> insert into space_waster
> 2 values('This is a terrible waste of space, but, dammit, it''s MY
> waste of space!!! It''s MINE, MINE, MINE, MINE, MINE, MINE, MINE, ALL
> MINE!!!!!
> Hahahahahahahahahahahahahahahahahahahahahahahaha!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
>
> 1 row created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> exec dbms_stats.gather_table_stats(ownname=>null,
> tabname=>'SPACE_WASTER', cascade=>true);
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> select (pct_free/100)*8000*blocks/1024||'kb' "room for updates"
> 2 from user_tables
> 3 where table_name = 'SPACE_WASTER';
>
> room for updates
> ------------------------------------------
> 46.875kb
>
> SQL>
> SQL> select table_name,round((blocks*8192/1024),2)||'kb' "current
> size"
> 2 from user_tables
> 3 where table_name = 'SPACE_WASTER';
>
> TABLE_NAME current size
> ------------------------------
> ------------------------------------------
> SPACE_WASTER 480kb
>
> SQL>
> SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb'
> "occupied space"
> 2 from user_tables
> 3 where table_name = 'SPACE_WASTER';
>
> TABLE_NAME occupied space
> ------------------------------
> ------------------------------------------
> SPACE_WASTER .28kb
>
> SQL>
>
> Gee, is it wasted space because I haven't populated it yet? Or, don't
> I understand the 'problem'?
>
>
> David Fitzjarrell
I'm with David on this one. I don't think what was posted, in and of itself, indicates an issue.
Perhaps more familiarity on ASTA's part with respect to DBMS_SPACE http://www.psoug.org/reference/dbms_space.html would help identify the issue if one exists.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Dec 11 2008 - 19:23:26 CST