dbms_space.free_space[11]

free block vs performance  june 12, 2003
reviewer:  raghu  from milwaukee, wi usa

hi tom

what is the impact of the large high water mark or free blocks or unused block 
on the table performacne (insert/update/select). i have a case in my environment 
where a batch takes 9 minutes in one instance (edev) amd ot takes 2 hrs in 
another (eprod). the outputs from yr procedure and from all_tables is given 
below for both the instance.

edev

free blocks  4
total blocks  1984
total bytes  16252928
unused blocks  1
unused bytes  8192
last used ext fileid 112
last used ext blockid  5193
last used block  15



eprod

free blocks  20
total blocks  2352
total bytes  19267584
unused blocks  11
unused bytes  90112
last used ext fileid 346
last used ext blockid  3065
last used block  5


blocks    empty_blocks    avg_space    num_freelist_blocks
2340    6             4462            2103

select blocks, empty_blocks,
           avg_space, num_freelist_blocks
      from user_tables
     where table_name = 'backlog_item'

blocks empty_blocks avg_space num_freelist_blocks
3956   11            4570     2004

2. if my process does not prevent me to issue a truncate statement, but my batch 
involves lots of deletes and insert, then how can i prevent large values for 
high water mark?. do we have any utility/script that we can run to  reduce the 
high water mark of a table.
  

1) both of those tables are so small, if i had a "batch" that took 9 minutes to 
process 20meg of data, i'd be concerned about that in itself.  that is probably 
8 minutes and 30 seconds longer then it should be.

use tkprof and sql_trace to find your low hanging fruit.  

2) you have a very very small table, i think you are looking in the wrong place 
here.  you have most likely a bad algorithm.  you need to diagnose where the 
problem is, then fix it.  not "fix something" and see if it was the right thing.