block usage vs. extent allocation

From: Ed Stevens <nospam_at_noway.nohow>
Date: Thu, 25 Mar 2004 15:36:16 -0600
Message-ID: <83j660lc109isd0ri0vdamsacsdvlb9507@4ax.com>

After getting into another "animated discussion" with my partner regarding extent allocation vs tablespace file extension, I put together a test/demo. In building it up, I noticed a side issue that I thought rather strange, and can't explain.

First, I create a tablespace and a table:

CREATE TABLESPACE EDS_TEST_TS
    DATAFILE '<snip>' SIZE 1M AUTOEXTEND ON NEXT 1m MAXSIZE UNLIMITED     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K     LOGGING
    ONLINE CREATE TABLE EDS_TEST_TABLE
(

    COL_1       CHAR(100) NOT NULL,
    COL_2       CHAR(100) NOT NULL,
    COL_3       CHAR(100) NOT NULL,
    COL_4       CHAR(100) NOT NULL

)
TABLESPACE EDS_TEST_TS
LOGGING
PCTFREE 0
PCTUSED 1
INITRANS 1
MAXTRANS 255
STORAGE(FREELISTS 1

        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT)

PARALLEL(DEGREE 4 INSTANCES 1)
NOCACHE
/

ANALYZE TABLE EDS_TEST_TABLE COMPUTE STATISTICS
/

select t.table_name, 
       t.num_rows as "rows",
       t.blocks,
       s.extents
from dba_tables t,
     dba_segments s

 where t.owner='PUR003'
   and t.table_name = s.segment_name
/

At this point I see the table has zero rows, zero blocks, and one extent. All is well and good.

Then I start adding rows 2 at a time, following each addition with the above ANALYZE and SELECT commands. Again, as expected, about every 8 rows the number of blocks increases by one .... until I get to 44 rows and 5 blocks. At that point, when I insert two more rows, going to 46 rows, the block count jumps from 5 to 10. Then the block count stays at 10 until I insert rows 91 and 92, at which time it jumps to 15 (still only one extent). What I expected was a smooth progression of block count in relation to row count.

So, what have I missed in my understanding of block and extent usage and allocation? Received on Thu Mar 25 2004 - 15:36:16 CST