Re: Fragmentation in Oracle
Date: 1997/11/11
Message-ID: <346f7df4.91847219_at_newshost>
Ok these will be my last as well (maybe :).
On Sun, 9 Nov 1997 19:24:10 -0000, "Dave S." <dsingh_at_tgtsolutions.com> wrote:
>Look, I don't seem to have the same amount of time as you to go over and
>over this! I'm a consultant and work for a living. You don't seem to be
>able to expect any other point of views. So fine....
>
I don't get that statement "you don't seem to be able to expect any other point of view".
Point i've been trying to make is that Index space gets reused in many cases.
I've asked for the documentation reference (so I can get it fixed if its written in there someplace) where you say that it says Index Space is never reused.
This is not a point of view issue, Its a matter of fact.
I have never said
- indexes don't ever need to be rebuilt (there are cases where they do)
- index space management is perfect and all space is reused (I even gave cases where it was reused perfectly AND not so perfectly)
>Your 2x growth mentioned below is a false, in my example the index grew from
>2 extents (8K blocks) to 11 extents. Is this 2X???
>
What were your initial extent? What was your next extent. Pctincrease? How big were the 2 extents in the beginning. I know that you must have explicitly sized your initial extent in your test (or next) since in all my examples using default storage parameters, at least 6 extents were required just to build the index on 27,000 rows in a database with 8k blocks.
So, in order to exactly duplicate your results (and show that 11 extents can equal 2 extents) I ran the following test with 27,000 rows:
- contents of index.sql ----------------------------- set serveroutput on set heading on set numformat "" define ROWS=&1
drop table test_tbl;
drop sequence test_seq;
create sequence test_seq;
create table test_tbl as
select test_seq.nextval x from all_objects;
declare
l_cnt number;
begin
loop
select count(*) into l_cnt from test_tbl;
exit when l_cnt > &ROWS;
insert into test_tbl select test_seq.nextval from all_objects;
commit;
end loop;
dbms_output.put_line( l_cnt || ' rows created in test_tbl' ); end;
/
commit;
create table tmp as select * from test_tbl;
spool test
drop index test_idx;
create index test_idx on test_tbl(x) storage (initial 450k pctincrease 0);
_at_index2
... above line repeated N times ........
- contents of index2.sql ------------------ analyze index test_idx validate structure;
set numformat 999,999
select i.lf_rows, i.lf_blks, i.br_rows, i.br_blks, i.del_lf_rows ,
sum(e.bytes) ebytes, sum(e.blocks) eblocks, count(*) exts
from user_extents e, index_stats i
where segment_name = 'TEST_IDX'
group by i.blocks, i.lf_rows, i.lf_blks, i.br_rows, i.br_blks, i.del_lf_rows;
delete from test_tbl;
insert into test_tbl select * from tmp;
commit;
So, this emulates exactly your scenario. Index starts with 2 extents, 11 are added with the first delete/insert and the index doubled. Looking at the results of the select from user_extents and index_stats I see:
LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS EBYTES EBLOCKS EXTS -------- -------- -------- -------- ----------- -------- -------- --------
27,288 60 59 1 0 532,480 65 2 54,576 121 120 1 27,288 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13 55,096 121 120 1 27,808 ######## 125 13
So in response to
>Your 2x growth mentioned below is a false, in my example the index grew from
>2 extents (8K blocks) to 11 extents. Is this 2X???
I too grew from 2 extents and added 11 more. It is 2X (actually just a little shy of 2X). You need to count blocks, not extents.
>Further, when you comment that deleting 27000 rows is large or that rollback
>segments are not large enough to handle this size is incorrect. AGAIN YOU
>ARE SUPPOSING EVERY SYSTEM IS THE SAME AND THE SAME SIZE!!! With Large
>systems, I have worked on rollback segments of 12mgs each, and rollback
>tablespaces of 2 gigs.
>
No, I said it was atypical -- not the norm. I too have worked on a couple of systems over time. I have worked with rollback segments of various sizes as well. I said atypical -- meaning not the norm. Not meaning out of the question -- Not meaning never done.
I also pointed out that if you were going to delete/insert such a large number of rows on a recurring basis (and have to rebuild the indexes anyway) you might want to look for a different way to do it. Drop the indexes and then delete/insert and rebuild them. Much faster. Or, look at truncate. Or, look at the process and see if you can't commit between the delete and the insert and avoid having to rebuld the index at all. Or, question why someone is deleting thousands of rows and reinserting them -- maybe a create table as select, index, drop and rename would be better (thats what I do when I need to rebuild a table frequently, we do it every night).
>Your comment that this would not happen if only a couple of rows are deleted
>/ inserted this would not happen is also debatable. If this process occurs
>over lets say A PERIOD OF TIME would this not happen ???
>
Again, I offer the scripts to you. You have seen the previous numbers. I ran the insert/delete process on all tests 43 times. When I put in .... above line repeated forever.... that meant after the delete/insert process ran 43 times, the above line (sizing of the index) popped out repeatably. I suppose, the 44'th it could have changed, but the tables were typically:
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
.... above line repeated forever ....
for example (where in the above table, we deleted 50% of the records and inserted as many rows with new numbers between each row). I have no reason to believe that on the 44'th iteration the results would be different.
so, over the duration of the test, we deleted 580,000+ rows and inserted 580,000+ rows in this 27,000 table (in test1 anyway - in test2 we did 1 million rows deleted/inserted with an unqualified delete and re-insertion).
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 Tue Nov 11 1997 - 00:00:00 CET