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