Re: block usage vs. extent allocation

"Ed Stevens" <nospam_at_noway.nohow> wrote in message news:83j660lc109isd0ri0vdamsacsdvlb9507_at_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?

BLOCKS is the number of blocks under the High Water Mark. The High Water Mark is actually some bytes stored in the segment header block. Updating those bytes to increment the HWM by one block at a time would make the segment header block even more of a hot block than it is already going to be. Therefore, since time immemorial, Oracle has always incremented the HWM in 5-block increments. It cuts the number of visits to the segment header block for the purposes of updating the HWM to 20% of what it otherwise would be.

Regards
HJR Received on Thu Mar 25 2004 - 15:57:38 CST