Man, I don't get it. "facts is facts", the evidence is right in front of our noses. "Opinions, rumors" -- they don't count when you have "facts". To all of the people described by "Some people think different about this myth." -- they must prove it (if it is true, they should be able to do that right??)

As for:

But I have found next paragraph in book "Oracle Performance Tuning" page 429
from Mark Gurry and Peter Corrigan. ....

Well -- those guys are "wrong". You showed it to youself. I can show it to you. They made a mistake, they were incorrect. They did the test wrong in some way (did they give you the test to reproduce??? if not, question the test. In my book -- if I say something, i give you the code to try and see for yourself every time).


Here, here is a simple test:

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t1 storage ( initial 528k ) tablespace dict_managed
2 as
3 select * from all_objects where rownum <= 4000;

Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t2 storage ( initial 1k next 1k pctincrease 0 ) tablespace dict_managed
2 as
3 select * from all_objects where rownum <= 4000;

Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> select segment_name, extent_id, blocks
2 from user_extents
3 where segment_name like 'T_'
4 order by 1, 2;

SEGMENT_NAME EXTENT_ID BLOCKS
------------------------------ ---------- ----------
T1 0 70
T2 0 2
T2 1 1
T2 2 1
T2 3 1
T2 4 1
T2 5 1
T2 6 1
T2 7 1
T2 8 1
T2 9 1
T2 10 1
T2 11 1
T2 12 1
T2 13 1
T2 14 1
T2 15 1
T2 16 1
T2 17 1
T2 18 1
T2 19 1
T2 20 1
T2 21 1
T2 22 1
T2 23 1
T2 24 1
T2 25 1
T2 26 1
T2 27 1
T2 28 1
T2 29 1
T2 30 1
T2 31 1
T2 32 1
T2 33 1
T2 34 1
T2 35 1
T2 36 1
T2 37 1
T2 38 1
T2 39 1
T2 40 1
T2 41 1
T2 42 1
T2 43 1
T2 44 1
T2 45 1
T2 46 1
T2 47 1
T2 48 1
T2 49 1
T2 50 1
T2 51 1
T2 52 1
T2 53 1
T2 54 1
T2 55 1
T2 56 1

58 rows selected.

alter tablespace dict_managed offline;

alter tablespace dict_managed online;

alter session set sql_trace=true;
set termout off

select * from t1;
select * from t1;
select * from t1;
select * from t1;
select * from t1;

select * from t2;
select * from t2;
select * from t2;
select * from t2;
select * from t2;
set termout on

So, the results are:

select *
from
t1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.01 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 1340 0.32 0.33 58 1615 60 20000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1350 0.32 0.34 58 1615 60 20000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48

Rows Row Source Operation
------- ---------------------------------------------------
4000 TABLE ACCESS FULL T1

********************************************************************************

select *
from
t2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 1340 0.34 0.37 58 1640 135 20000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1350 0.34 0.37 58 1640 135 20000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48

Rows Row Source Operation
------- ---------------------------------------------------
4000 TABLE ACCESS FULL T2


Hmm, makes me suspect of their results -- how about you? (btw: if you run this over and over, sometimes T2 beats T1 and sometimes not -- meaning that any differnce you see above is "noise", ignore it -- those results are the SAME)


Your goal is to NOT worry about tables with less then say about 1,000 or so extents. It is NOT worth losing sleep over.


It is a BAD practice to store your sizable objects in a single extent (and no, i am not recommening you use 57 extents like I did above for such a trivial table -- 4,000 rows is TINY, thats too small to even test with in my opinion!)

(and as I said above, extents are LOGICALLY contigous and physically ALL OVER the place. The odds of the blocks being right next to eachother is about ZERO in the real world -- doesn't matter anyway since we do have these "multi-user" systems and anyone who even dreams the head will stay where they left it is wrong)

As for that paper, they make a compelling argument however, it predates locally managed tablespaces with the ability to FORCE an extent size. I would use the extent size that makes you happy. 30 extents/year sounds great to me. Should give you a good 30 or 40 years before you would be tempted to reorg it.