Free Oracle Tips   HTML Text AOL

Free Oracle App Server Tips   HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Oracle Tips by Burleson

Chapter 5 Oracle Tablespace Design

;

We now have a table named test_frag in a 2-KB tablespace. The next step is to populate 4,000 rows, with only a single spec in the VARCHAR2 column:

declare
myint integer := 1;
begin
loop
 
  insert into test_frag
  values
  (
     test_frag_seq.nextval,
    ' '
   );
 
  myint := myint+1;
 
  if myint > 4000 then exit; end if;
 
end loop;
end;
/

Now that we have the rows inserted, let’s take a look at how many rows are stored on the data block in DBA_TABLES:

Table                    % Free   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
-------------------- ---------- ---------- ----------- ----------
TEST_FRAG                    10       4000           9          0

In DBA_SEGMENTS, we see that the table is in a single extent. We also see that we used 32 data block, at 2 KB per block, to store 4,000 rows. This works out to 500 data rows per block.

Table             Tablespace      Buffer                       
name              Name            Pool          Bytes   Blocks  Extents
----------------- --------------- ---------- -------- -------- -----   
TEST_FRAG         ASM_TEST        DEFAULT      65,536       32     1

Now let’s make a mess and expand a large VARCHAR2 column from one byte to 2,000 bytes. After the update, we see in DBA_SEGMENTS that the table is much larger:

Table         Tablespace      Buffer                                 
name          Name            Pool              Bytes   Blocks Extents 
------------- --------------- ---------- ------------ -------- --------
TEST_FRAG     ASM_TEST        DEFAULT       9,437,184    4,608     24 

Now our table is on 4,608 blocks, and the table has taken 24 extents. When we examine DBA_TABLES, we see that the table now has an average row length of 1,378, and every single row has chained!

Table                    % Free   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
-------------------- ---------- ---------- ----------- ----------
TEST_FRAG                    10       4000        1378       4000

Row chaining is a serious problem for the database administrator (DBA), and it appears that Automatic Space Management is not appropriate for tables where you need to reserve space for large row expansions with PCTFREE.