Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Table keeps growing inspite of purging

Re: Table keeps growing inspite of purging

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/11/27
Message-ID: <347dd90c.25161890@inet16>#1/1

On Wed, 26 Nov 1997 10:44:42 -0800, "Sasha Ostojic" <sasha_at_UltimateTech.com> wrote:

>I have Oracle 7.3.2.
>
>Julio wrote in message <347CEB67.7C2_at_si.unirioja.es>...
>Sasha Ostojic wrote:
>>
>> What if I set PCTUSED 99 and PCTFREE 0? Would that ensure maximum re-use
 of
>> freed space? I tried this, and it did not seem to degrade performance
 much.
>>
>> .sasha
>
>Wich is your ORACLE version? In 7.1.6 Oracle doesn't reuse space. I
>remember now the number of problems with the Installation of Designer 2K
>(we have to install and uninstall it a lot of times) and the space was
>not freed..

Sasha,

can you send more information. I ran a test in 7.1.6, 7.2.3, 7.3.3. All the results were identical. (btw: all versions of the database release space, all of them. None of them use a 'flag' delete or anything like that. That would be dbase).

In each database I created a table with 5,000 ~255 character rows. I then analyzed the table and reported the free space/used space/allocated space. I then repeatably deleted 500 rows, inserted 500 rows. I ran each test 2 times in the database. Once analyzing after each insert/delete set and once analyzing just at the beginning and the end.

the test scripts are:

spool space_test  

drop table test_space;
drop sequence test_seq;  

create table test_space( x int, y char(255) ) storage ( initial 100k next 100k pctincrease 0 ) pctused 60 pctfree 5;  

create index test_space_idx on test_space(x);  

create sequence test_seq;  

begin

    for i in 1 .. 5000 loop

        insert into test_space values ( test_seq.nextval, rpad('x',255,'x'));
        commit;

    end loop;
end;
/  

@test2
(above line repeated lots)
@test2
spool off

------------------ eof ------------------------------

------------------ test2.sql -------------------------

begin

    for i in 1 .. 500 loop

        insert into test_space values ( test_seq.nextval, rpad('x',255,'x'));
        commit;

    end loop;
end;
/  

analyze table test_space compute statistics;  

select 'before', num_rows, blocks, empty_blocks, avg_space, avg_row_len from user_tables where table_name = 'TEST_SPACE';  

column minval new_value minval  

select min(x)+500 minval from test_space;  

delete from test_space where x < &MINVAL;  

analyze table test_space compute statistics;  

select 'after', num_rows, blocks, empty_blocks, avg_space, avg_row_len from user_tables where table_name = 'TEST_SPACE';

------------------------ eof ----------------------------


After running this, in every version, I always saw:

'BEFOR   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN
before       5500        919           30        360         265
after        5000        919           30        504         265
before       5500        919           30        360         265
..... above pair of lines repeated over and over and over...
before       5500        919           30        354         266
after        5000        919           30        499         266
 

the table hit a steady state right away. There must be something else going on with your table. Could you tell us

I'd like to replicate your results if possible....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Nov 27 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US