Wednesday, July 08, 2009

HWM (high watermark) of a tablespace ?

How many times have you thought of calculating HWM (high watermark) of a tablespace as a DBA ? Have you ever found out that a tablespace is overallocated in a database, as compared to the data being used in it ? In such a case, you'd want to reduce the wasted space & resize the tablespace to a smaller size, although greater than the current usage.
HWM is at the maximum blocks ever occupied by a SEGMENT (unless we perform a truncate on it). Segments span over datafiles however remain within a tablespace. All segments are physically allocated by way of extents, which are contiguous blocks of data. Now, when we talk about contiguous blocks, 2 datafiles (even from same tablespace) are highly unlikely to have contiguguos data blocks. Hence, theoretically, it's possible to calculate high watermark within a datafile, not a tablespace directly, as the latter is only a logical unit. Although, we can resize a tablespace, however the resize value needs to be based on exact calculations, or better, based on the above SEGMENT theory, we should resize individual datafiles within a tablespace more accurately.
For the sake of simplicity & ease of understanding, we may still see the datafile usage as an overall tablespace usage :

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size';

--Tablespace-wise Used / unused blocks for HWM calculation

select tablespace_name, file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
order by 1;


You may save this above data output as a snapshot of starting point. Now, let's create a script to resize individual datafiles (you may exclude some important ones, like SYSTEM, UNDO, TEMP). I've used 2 types of scripts - one is exact resizing to the HWM & other one is with some future space allocation planning built-in & still reducing wasted space :

--Exact resizing

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

-- Future Space planned resizing - change the 0.1, which is 10% estimate, to any other value less than 1, to alter how much extra you wish to still keep in each datafile / tablespace

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*8192)/1024/1024 + 0.1*(nvl(hwm,1)*8192)/1024/1024) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) > 0
/


Dear reader, I've been able to make use of this procedure successfully. Please let me know your results & learnings.

5 comments:

  1. 人類最大的悲劇不是死亡,而是沒有掌握有意義的人生..............................

    ReplyDelete
  2. hello... hapi blogging... have a nice day! just visiting here....

    ReplyDelete
  3. hi.. just dropping by here... have a nice day! http://kantahanan.blogspot.com/

    ReplyDelete
  4. Hi... Looking ways to market your blog? try this: http://bit.ly/instantvisitors

    ReplyDelete