Yaping's Weblog

August 30, 2008

when freed blocks be reused

Filed under: Oracle — Yaping @ 4:12 am
Tags:

Table and index have different when will the freed blocks be reused within one transaction/uncommit.

Table blocks allocated in freelist is followed by following order:
1.         allocate from TFL(if current transaction need);
2.         allocate from PFL(if freelists is 1, then PFL and MFL are same);
3.         if PFL empty, then move blocks from MFL to PFL;
4.         if MFL empty, then move from TFL(if TFL usable, commited);
5.         if MFL empty(if TFL unusable), then advance HWM;
6.         if extent has no free space, then allocate extent;
7.         if no tablespace, then tablespace allocate;
8.         if tablespace fail to allocate, then error

I create one table, every block can only contain one row, and create index on this table, one leaf block contains one key.

SQL> create table t1(id number, a char(2000),b char(2000),c char(2000));
Table created.

SQL> create index idx_t1_id on t1(rpad(’0′,1400,’0′)) tablespace TEST_2K;
Index created.

SQL> begin
for i in 1..10 loop
insert into t1 values (i,’a’,’b’,’c’);
end loop;
commit;
end;
/  2    3    4    5    6    7
PL/SQL procedure successfully completed.

Load data into table t1. As expect, this table will consume 10 blocks to contain 10 rows, and 10 leaf blocks,

SQL> analyze table t1 compute statistics for table for all indexes;
Table analyzed.

SQL> select table_name PCT_USED,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN from user_tables;
PCT_USED                         NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
—————————— ———- ———- ———— ———–
T1                                     10         11            0        6015

SQL> select BLEVEL,LEAF_BLOCKS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,NUM_ROWS from user_indexes;
    BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY   NUM_ROWS
———- ———– ———————– ———————– ———-
         4          10                      10                      10         10

SQL> analyze index idx_t1_id validate structure;
Index analyzed.

SQL> select HEIGHT,BLOCKS,LF_BLKS,LF_BLK_LEN,DEL_LF_ROWS,PCT_USED,USED_SPACE from index_stats;
    HEIGHT     BLOCKS    LF_BLKS LF_BLK_LEN DEL_LF_ROWS   PCT_USED USED_SPACE
———- ———- ———- ———- ———– ———- ———-
         5         32         10       1852           0         69      26847

Then run a sql block, delete and insert rows within one transaction.

SQL> begin
for i in 1..20 loop
delete from t1 where id=i;
insert into t1 values (i,’a’,’b’,’c’);
end loop;
commit;
end;
/  2    3    4    5    6    7    8
PL/SQL procedure successfully completed.

SQL> analyze table t1 compute statistics for table for all indexes;
Table analyzed.

SQL> select table_name PCT_USED,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN from user_tables;
PCT_USED                         NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
—————————— ———- ———- ———— ———–
T1                                     20         20            0        6015

SQL> select BLEVEL,LEAF_BLOCKS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,NUM_ROWS from user_indexes;
    BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY   NUM_ROWS
———- ———– ———————– ———————– ———-
         5          30                      30                      20         20

SQL> analyze index idx_t1_id validate structure;
Index analyzed.

SQL> select HEIGHT,BLOCKS,LF_BLKS,LF_BLK_LEN,DEL_LF_ROWS,PCT_USED,USED_SPACE from index_stats;
    HEIGHT     BLOCKS    LF_BLKS LF_BLK_LEN DEL_LF_ROWS   PCT_USED USED_SPACE
———- ———- ———- ———- ———– ———- ———-
         6         64         30       1852          10         75      83387

The result shows,
1.      the deleted blocks in table be reused;
2.      the leaf block didn’t be reused.

Notes:
Analyze … compute and analyze … structure commands count empty blocks, but dbms_stats doesn’t count it. In some situation, it may cause the SQL statements to perform through fast full index scan, even though it’s not optimization plan.

SQL> exec dbms_stats.gather_table_stats(OWNNAME=>’TEST’,TABNAME=>’T1′,CASCADE=>true);
PL/SQL procedure successfully completed.

SQL> select BLEVEL,LEAF_BLOCKS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,NUM_ROWS from user_indexes;
    BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY   NUM_ROWS
———- ———– ———————– ———————– ———-
         5          20                      20                      20         20

Now I rerun the sql block, but this time I commit after delete every rows.

SQL> drop table t1;
Table dropped.

SQL> create table t1(id number, a char(2000),b char(2000),c char(2000));
Table created.

SQL>create index idx_t1_id on t1(rpad(’0′,1400,’0′)) tablespace TEST_2K;
Index created.

SQL> begin
for i in 1..10 loop
insert into t1 values (i,’a’,’b’,’c’);
end loop;
commit;
end;
/  2    3    4    5    6    7
PL/SQL procedure successfully completed.

SQL> begin
for i in 1..20 loop
delete from t1 where id=i;
insert into t1 values (i,’a’,’b’,’c’);
commit;
end loop;
end;
/  2    3    4    5    6    7    8
PL/SQL procedure successfully completed.

SQL> analyze table t1 compute statistics for table for all indexes;
Table analyzed.

SQL> select table_name PCT_USED,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN from user_tables;
PCT_USED                         NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
—————————— ———- ———- ———— ———–
T1                                     20         20            0        6015

SQL> select BLEVEL,LEAF_BLOCKS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,NUM_ROWS from user_indexes;
    BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY   NUM_ROWS
———- ———– ———————– ———————– ———-
         5          20                      20                      20         20

SQL> analyze index idx_t1_id validate structure;
Index analyzed.

SQL> select HEIGHT,BLOCKS,LF_BLKS,LF_BLK_LEN,DEL_LF_ROWS,PCT_USED,USED_SPACE from index_stats;
    HEIGHT     BLOCKS    LF_BLKS LF_BLK_LEN DEL_LF_ROWS   PCT_USED USED_SPACE
———- ———- ———- ———- ———– ———- ———-
         6         64         20       1852           0         59      55107

The result shows, the leaf block was reused.
If freed blocks within one transaction/uncommit can not be reused in the index, the reason is for rollback.

Following test is more detail on tables. There are two cases, the first is insertion after deletion be committed.
The second one is deletion and insertion data within one transaction.

The following steps have order.

Case 1
============================================
session 1

SQL> create table t1(id number, a char(2000),b char(2000),c char(2000));
Table created.

SQL> begin
for i in 1..9 loop
insert into t1 values (i,’a’,’b’,’c’);
end loop;
commit;
end;
/   2    3    4    5    6    7
PL/SQL procedure successfully completed.

Then open anther session, run following sql.

 

session 2

SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner=’TEST’;
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
——————– ———- ———- ———- ———-
T1                            0          7          9          8
T1                            1          7         17          8

SQL> alter system checkpoint;
System altered.

SQL> alter system dump datafile 7 block min 9 block max 24;
System altered.

Then check trace file, there is no TFL now, first block in freelist is block 18(x12).

buffer tsn: 6 rdba: 0×01c00009 (7/9)
  nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 7
  SEG LST:: flg: USED   lhd: 0×01c00012 ltl: 0×01c00015

Then I return back to session 1, run following sql.

session 1
SQL> delete from t1 where rownum<5;
4 rows deleted.
SQL> commit;
Commit complete.

session 2
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 7 block min 9 block max 24;
System altered.

Check trace file, now there is one TFL, the start block is 13(x0d).

  nfl = 1, nfb = 1 typ = 1 nxf = 1 ccnt = 7
  SEG LST:: flg: USED   lhd: 0×01c00012 ltl: 0×01c00015
  XCT LST:: flg: USED   lhd: 0×01c0000d ltl: 0×01c0000a xid: 0×0009.028.00000285

We can also follow block 13, then find the next block.
1.         If flg is O then this block in freelist, if it is -, then this block not in freelist;
2.         fnx shows the next block in the freelist.

buffer tsn: 6 rdba: 0×01c0000d (7/13)
scn: 0×0000.004a040f seq: 0×01 flg: 0×06 tail: 0×040f0601
frmt: 0×02 chkval: 0×364b type: 0×06=trans data
Block header dump:  0×01c0000d
 Object id on Block? Y
 seg/obj: 0×17ab  csc: 0×00.4a040b  itc: 2  flg: O  typ: 1 – DATA
     fsl: 2  fnx: 0×1c0000c ver: 0×01

session 3
SQL> insert into t1 values (99999,’a’,’b’,’c’);
1 row created.
SQL> commit;
Commit complete.

session 2
I dump data block again, it shows that session 3 used one block which is in MFL, not in TFL.

  nfl = 1, nfb = 1 typ = 1 nxf = 1 ccnt = 7
  SEG LST:: flg: USED   lhd: 0×01c00013 ltl: 0×01c00015
  XCT LST:: flg: USED   lhd: 0×01c0000d ltl: 0×01c0000a xid: 0×0009.028.00000285

Case 2
============================================
session 1
SQL> drop table t1;
Table dropped.
SQL> create table t1(id number, a char(2000),b char(2000),c char(2000));
Table created.
SQL> begin
for i in 1..9 loop
insert into t1 values (i,’a’,’b’,’c’);
end loop;
commit;
end;
/  2    3    4    5    6    7
PL/SQL procedure successfully completed.
SQL> delete from t1 where rownum<5;
4 rows deleted.

session 2
SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner=’TEST’;
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
——————– ———- ———- ———- ———-
T1                            0          7          9          8
T1                            1          7         17          8
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 7 block min 9 block max 24;
System altered.

Check trace file. We can find that TFL exists, although transaction is uncommit.

  nfl = 1, nfb = 1 typ = 1 nxf = 1 ccnt = 7
  SEG LST:: flg: USED   lhd: 0×01c00012 ltl: 0×01c00015
  XCT LST:: flg: USED   lhd: 0×01c0000d ltl: 0×01c0000a xid: 0×0001.019.000001da

session 1
SQL> insert into t1 values (99999,’a’,’b’,’c’);
1 row created.
SQL> insert into t1 values (9999,’a’,’b’,’c’);
1 row created.
SQL> insert into t1 values (999,’a’,’b’,’c’);
1 row created.
SQL> commit;
Commit complete.

session 2
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 7 block min 9 block max 24;
System altered.

This time, we find the transaction use block in TFL, not in MFL.

  nfl = 1, nfb = 1 typ = 1 nxf = 1 ccnt = 7
  SEG LST:: flg: USED   lhd: 0×01c00012 ltl: 0×01c00015
  XCT LST:: flg: USED   lhd: 0×01c0000b ltl: 0×01c0000a xid: 0×0001.019.000001da

If we use ASSM tablespace, it’s very different from freelist management.

Refer to http://www.ixora.com.au/q+a/datablock.htm

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: