Re: block usage vs. extent allocation

On Fri, 26 Mar 2004 08:57:38 +1100, "Howard J. Rogers" <hjr_at_dizwell.com> wrote:

>
>"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
>

I see. Thank you for the explanation. Received on Fri Mar 26 2004 - 07:52:33 CST