inconsistency of dba_segments.bytes between 2 tbs

[suhaimi]

Hi all,
I've created 2 different tablespaces:
TBS1.
1. CREATE TABLESPACE hdbx400M DATAFILE '/ora15/oradata/HDBSIL/HDBX400M001.dbf' SIZE 1993539584 REUSE DEFAULT STORAGE ( INITIAL 398704640 NEXT 398704640 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0);
2. Create a number of tables using tbs storage clause
3. select segment_name,bytes,initial_extent,next_extent from dba_segment
Results:
y1 398704640 398704640 398704640
y2 398704640 398704640 398704640
y3 398704640 398704640 398704640
y4 398704640 398704640 398704640
y5 398712832 398704640 398704640

TBS2.
1. CREATE TABLESPACE hdbxindex100M DATAFILE '/ora15/oradata/HDBSIL/HDBXIDX100M001.dbf' SIZE 1000095744 REUSE DEFAULT STORAGE ( INITIAL 100007936 NEXT 100007936 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0);
2. Create a number of indexes using tbs storage clause
3. select segment_name,bytes,initial_extent,next_extent from dba_segment
Results:
x1 100024320 100007936 100007936
........
x5 100024320 100007936 100007936

Q1. Why the bytes of index x's in TBS2 do not match initial/next extent (extra 16K) whereas bytes of table y's in in TBS1 do match with initial & extent ?
Q2. Why bytes of last table y5 has extra 8K than initial/extent ?


Rgds,
Suhaimi

 

[SantaMufasa]

Suhaimi,

I cannot explain why the extra 16K on the INITIAL extents, but I am waaay curious why you want your INITIAL and NEXT extents to be so enormous? In fact, under your circumstances, with 2GB of file space, the 5 segments you have in your tbs1 have used up all the space and cannot even create a NEXT extent for any table. So, please help me understand the benefits of such large extents.

Dave
Sandy, Utah, USA @ 06:25 GMT, 23:25 Mountain Time

 

[suhaimi]

Hi Dave,
We are running OLTP and the transactions are huge. Oracle Expert did advise us to create large INITIAL/NEXT extents for transaction tables to overcome frequent extent which may slow down the overall performance.

Rgds,

 

[suhaimi]

Hi,
Here's another example of bytes > extent:
NAME BYTES INITIAL_EXTENT NEXT_EXTENT BYTES-NEXT_EXTENT
idx1 100024320 100007936 100007936 16384
idx2 100024320 100007936 100007936 16384
idx3 100024320 100007936 100007936 16384
idx4 100024320 100007936 100007936 16384
idx5 100032512 100007936 100007936 24576
idx6 100032512 100007936 100007936 24576
idx7 100024320 100007936 100007936 16384
idx8 100024320 100007936 100007936 16384
idx9 100024320 100007936 100007936 16384
idx10 100024320 100007936 100007936 16384
idx11 100024320 100007936 100007936 16384
idx12 100032512 100007936 100007936 24576

Rgds,