If a table is full and insert a new row, oracle will bump up the HWM, format the blocks in memory, and insert the data into the formatted block. During the whole process, no disk IO is required. Oracle doesn’t need to read the block above the HWM from disk as it knows the block above HWM is logically empty. It’s a waste of resource to read block from disk if the block content is already known (empty in this case) to oracle.
And we can also pre-allocate some extent before insert rows into a full table, the blocks in the extent will be put below HWM. But oracle has to go to disk for the block when insert a new row as oracle doesn’t know whether the block is empty or not (if oracle is sure it is empty, oracle could just create the block in memory without touch the disk, and flush the block back to disk when necessary). So in this case, disk IO is required.
1: test 1 (without pre allocate space)
SQL> create table t (a char(200)) pctfree 99 pctused 1;
Table created.
SQL> insert into t select rpad(‘x’,100,’x’) from dual connect by rownum<1000;
999 rows created.
SQL>
Checking the 10046 raw trace file, there is no disk IO wait.
2: test 2 (pre-allocate)
SQL> alter table t allocate extent (size 400M instance 1);
Table altered.
SQL> alter system flush buffer_cache; — The block could be cached in SGA
System altered.
SQL> alter session set events ‘10046 trace name context forever, level 8’ ;
Session altered.
SQL> insert into t values(rpad(‘x’,100,’x’));
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL>
TEST$> grep “db file” TEST_ora_21015.trc
WAIT #1: nam='db file sequential read' ela= 2947 file#=1 block#=141527 blocks=1 obj#=489208 tim=8943453664111
WAIT #1: nam='db file sequential read' ela= 349 file#=201 block#=12133 blocks=1 obj#=489208 tim=8943453664744
WAIT #2: nam='db file sequential read' ela= 1032 file#=201 block#=12134 blocks=1 obj#=489208 tim=8943454366833
We could see there are disk IO in this case. Sometimes we pre-allocate extent to alleviate the HWM enqeue contention issue (another option to alleviate the HWM enqeue contention is to increase _bump_highwater_mark_count ), but this will increase the disk IO request. If the IO is the bottleneck, pre-allocate might not be a good choice.
Mysqloracle9's Blog 