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>

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.org

Received on Thu Dec 11 2008 - 19:23:26 CST