Re: Fragmentation in Oracle
Date: 1997/11/09
Message-ID: <346e1776.132097085_at_newshost>
On Fri, 7 Nov 1997 22:14:30 -0000, "Dave S." <dsingh_at_tgtsolutions.com> wrote:
>Hi Tom,
>
>Though I did not try your method, I was able to prove what I said before.
>Please try this on your site and see what happens.
>
Well, what you said before was
>>>
>>>Also beware, Index space IS NEVER REUSED. If you add records, then delete
>>>them, the index space is not release nor reused.
>>>
and your test doesn't prove that (although my test does effectively disprove it since it shows that there does exist a case where index space is reused).
So, digging deeper. I ran a slightly modified set of tests (3 different cases) involving 27,000+ rows.
Test1 - create a table with 27,000+ rows. Each row contains a unique number from a sequence (numbers are never reused in test1 or test3). We measure the amount of space consumed by the index and then proceed to issue:
for i 1 .. 27000/2 loop
delete where rownum = 1;
commit;
insert a brand new X value (never NULL, never a value that existed)
commit;
end loop
and then measure the space again, we do this over and over and over. Synopsis for this test -- index never grew after the initial index was built. 27,000 rows consumed a constant amount of space.
Test2 - very similar to yours. create a table with 27,000 rows. Each row contains a unique number from a sequence. We measure the amount of space consumed by the index. We then issue
delete from table
insert the same rows back into the table
commit
and then measure the space again, we do this over and over and over. Synopsis for this test -- index grew by a factor of exactly 2 between the initial build and the first delete/insert. Index stayed exatly 2x the size of the original for every iteration. It is interesting to note that if we commit between the delete and the insert -- the index space *never* grows.
Test3 - a new test. create that table again. Measure space. We then issue:
for each row in the table
if the rownum is odd then
delete it
insert a new one
end if
end loop
and then measure the space again, we do this over and over... Synopsis for this test -- index grew over time until it eventually hit a steady state at about 3x its original size..
so, why the different results when the 3 tables had pretty much the same data? Its because of a couple of things. Note: for the following tables the columns have the meanings:
Index Stats Table: BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS
User_Extents Summed up for the Index: EBYTES (extent Bytes) EBLOCKS (extent Blocks)
test1 was a perfect 'best case'. the way I issued deletes almost guaranteed I would delete consecutive numbers. I deleted rows that were inserted together by always deleteing "row 1". so, since I was always inserting an increasing number, and always deleteing numbers close to each other -- I caused complete index blocks to empty out (eg: i would delete 1-500, that was block 1 in the index, block 1 is now completely empty, block 1 can be reused as it is no longer linked in the index). the index in this test case sort of swept from left to right. I deleted 'old' numbers and inserted new. The index never grew. Here are the actual numbers from test1:
BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS EBYTES EBLOCKS -------- -------- -------- -------- -------- ----------- -------- --------
263 27,434 258 257 3 0 538,624 263
264 27,436 259 258 3 2 540,672 264
264 27,436 259 258 3 2 540,672 264
264 27,436 259 258 3 2 540,672 264
264 27,436 259 258 3 2 540,672 264
264 27,436 259 258 3 2 540,672 264
264 27,436 259 258 3 2 540,672 264
264 27,436 259 258 3 2 540,672 264
264 27,436 259 258 3 2 540,672 264
...... repeated forever.....
test2 (your test) was a very atypical test. It asked us to (in one transaction) delete every row and reinsert them. this caused the index to double in allocated space simply because the deleted index blocks are not used in the same transaction (an index block that is emptied by a transaction will not be reused by that transcation). We issued:
delete from T
insert all rows into T
commit
If you would have simply thrown a commit between the delete and insert, the results in your test would have been *very* different. (it would have looked just like test1 -- i tried it and it does). So the reason you index went into 9 extents from 2 was because it effectively doubled (i don't know what you used for initial/next extents when building the index in the first place but I would guess that an index on a 27,000 row table that used 2 extents initially was sized with non-default values -- so the extra 7 extents probably were a little more then double the original size -- don't know what your pctincrease was so the 7'th extent might have been really big and not used).
so, if you are going to delete all rows and want to reuse the index space you just freed up, commit. Here is the output from test2 (i ran test2 on an 8k blocksize so the numbers are different then above which was a 2k blocksize -- test3 was on a 2k blocksize again):
BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS EBYTES EBLOCKS -------- -------- -------- -------- -------- ----------- -------- --------
74 32,596 72 71 1 0 606,208 74
147 65,192 145 144 1 32,596 1,204,224 147
147 65,712 145 144 1 33,116 1,204,224 147
147 65,712 145 144 1 33,116 1,204,224 147
147 65,712 145 144 1 33,116 1,204,224 147
147 65,712 145 144 1 33,116 1,204,224 147
147 65,712 145 144 1 33,116 1,204,224 147
147 65,712 145 144 1 33,116 1,204,224 147
147 65,712 145 144 1 33,116 1,204,224 147
147 65,712 145 144 1 33,116 1,204,224 147
147 65,712 145 144 1 33,116 1,204,224 147
147 65,712 145 144 1 33,116 1,204,224 147
.... repeated forever.....
btw: when running test2 with a commit between the delete and insert:
BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS EBYTES EBLOCKS -------- -------- -------- -------- -------- ----------- -------- --------
74 32,596 72 71 1 0 606,208 74
74 32,596 72 71 1 0 606,208 74
74 32,596 72 71 1 0 606,208 74
.... repeated forever.....
test3, whereas test1 was a 'best case', test3 was the worst case. Here, because of the way I did deletes, we left lots and lots of holes in our index. We deleted 'every other row' in the table by using a loop:
insert into rowid_table select rowid from our_table;
for x in ( select therowid, rownum from rowid_table ) loop
if ( mod( rownum, 2 ) = 0 ) then
delete from our_table where rowid = therowid;
insert a new value
commit;
end if;
end loop
delete from rowid_table
so, we built a table with all the rowids in our table, looped over all of the rowids, deleteing every other row (eg: we deleted 2, 4, 6, 8 and then 3, 7, 11, 15, and so on). Our index grew and grew over time. This was because as we added new values to the table using our sequence, they could not go into an existing block (since those blocks had small numbers on them and the new numbers where 'big') so new blocks were added to the right of the index. Eventually though, after enough interations, the 'first' block in the index had nothing left on it and was reused by new values. By randomly deleting the data, leaving these holes in index blocks and never going back to those blocks (the increasing sequence did that for us), we had the worst case. If we didn't use an index by a 'random number' or last names or something, we wouldn't have seen such a bad case.
here are the numbers from test3:
BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS EBYTES EBLOCKS -------- -------- -------- -------- -------- ----------- -------- --------
263 27,463 258 257 3 0 538,624 263
383 27,721 376 375 4 258 784,384 383
486 27,809 478 477 5 346 995,328 486
582 27,920 574 573 5 457 ######## 582
598 27,932 589 588 6 469 ######## 598
699 28,032 688 687 7 569 ######## 699
746 28,067 735 734 7 604 ######## 746
746 28,042 734 733 8 579 ######## 746
747 28,027 734 733 9 564 ######## 747
846 28,124 832 831 10 661 ######## 846
849 28,136 835 834 10 673 ######## 849
849 28,115 834 833 11 652 ######## 849
849 28,100 833 832 12 637 ######## 849
849 28,093 832 831 13 630 ######## 849
849 28,111 832 831 13 648 ######## 849
849 28,110 831 830 13 647 ######## 849
849 28,108 831 830 14 645 ######## 849
test3 is why you sometimes rebuild indexes. test1 shows you in some cases you don't have to. test2 is very atypical (if you do that alot, truncate instead -- it'll waste no space).
All test cases can be made available (the scripts). Just email me for them. They are very similar to what I already posted but adding three sets of scripts to this already very long post would just waste space.
>create table sv_dave as select * from <<<any large table>>>
>create index sv_dave on sv_dave (<<any unique field>>>)
><<< check the number of extents>>>
>delete from sv_dave;
>insert into sv_dave as select * from <<<the same large table>>>
>commit;
><<<< check the number of extents on the index>>>>
>>>> do the insert / delete /commit repeatedly a few times
><<<< check the number of extents on the index >>>> It should have grown.
>
>The table I used was 27000 records and the index field was defined as
>number(7).
>
>In my example the first time I create the index it had 2 extents. After I
>deleted / inserted / committed a few times it had grown to 9 extents though
>the number of records in the table was still 27000 records.
>
>The only explanation I would have that your test did not work was because
>the size of the table / index was relatively small.
>
>By the way, do you work for Oracle? Your address is *.us.oracle.com?
>
Yes I do.
>Thomas Kyte wrote in message <346331ed.7800857_at_newshost>...
>>On Thu, 6 Nov 1997 20:11:28 -0000, "Dave S." <dsingh_at_tgtsolutions.com>
wrote:
>>
>>>Try using an 80% (or higher) PCTUSED setting on the table. Please see
>>>below.
>>>
>>>Also beware, Index space IS NEVER REUSED. If you add records, then delete
>>>them, the index space is not release nor reused.
>>>
>>
>>
>>Thats not true. Index space is reused. (like a table, space is not
released to
>>be used by other objects, but it is reused within the index).
>>
>>To show this, I created a table. I indexed a column in that table. Put
1,000
>>rows in there. I repeatedly delete and add records to this table. After I
>>delete 500 records and add 500 new records (using values never before seen
in
>>the table), I analyze the index and dump the results. I do this over and
over
>>and over. If Oracle never reused index space, we would expect the index to
grow
>>infinitely large over time. In fact, the index never really grows -- it
reuses
>>the space as it is freed up. the following table is the output of the
>
>.<<<<< Data was removed to save space>>>>>
>>-----------------------------------------------------
>>
>>[snip]
>>
>>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.
>
>
>
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 Sun Nov 09 1997 - 00:00:00 CET