High Water Mark
High Water Mark management in Oracle Database:
Description:
⦁ Each table is made up of extents and each extent is made up of oracle blocks – a common block size is 8k. So you have a table with 10 extents (80K).
⦁ When any table creates the high water mark will be in the ‘starting’ position. The high water mark keep moving forward as data get saved into the database. After inserting some records High Water Mark will be moved to forward.
⦁ When you delete the rows from table, the blocks below HWM may becomes empty but high water mark will stay as it is. Suppose you load the table with 1 million rows .Now you will have suppose the high water mark as 1 GB. Now if you delete all the 1 millions rows, then even the high water mark will be remain same as 1 GB. The only way to reduce the HWM is to rebuild the able or use truncate table. Oracle Truncate table reduce the High water mark.
Why do we need HWM ?
⦁ The high water mark (HWM) has a function that comes into play with tables that have heavy insert, update and delete activity. Every time data is changed in a table, the HWM moves to a setting in each table that shows how much free space is left in terms of blocks used and free in the segment object.
⦁ To resize any datafile to reclaim some space on the datafile. Need to check for the High water mark usage and based on that Water mark we may have to resize the respective datafile to get the space reclaimed.
⦁ We had an critical space issue on the datawarehouse environment to reclaim the space identified the datafiles using below query and resized the respective datafiles where we can get some space through this process.
Step1:Check the current data file size.
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +————————————————————————+
PROMPT | Report : Data File Report (all physical files) |
PROMPT | Instance : ¤t_instance |
PROMPT +————————————————————————+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN tablespace FORMAT a35 HEADING ‘Tablespace Name / File Class’
COLUMN filename FORMAT a40 HEADING ‘Filename’
COLUMN filesize FORMAT 9999999999999 HEADING ‘File Size MB’
COLUMN autoextensible FORMAT a4 HEADING ‘Auto’
COLUMN increment_by FORMAT 999999999999 HEADING ‘Next in MB’
COLUMN maxbytes FORMAT 999999999999 HEADING ‘Max Size MB’
BREAK ON report
COMPUTE sum OF filesize ON report
COMPUTE sum OF maxbytes ON report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes/1024/1024 filesize
, d.autoextensible autoextensible
, (d.increment_by * e.value)/1024/1024 increment_by
, d.maxbytes/1024/1024 maxbytes
FROM
sys.dba_data_files d
, v$datafile v
, (SELECT value
FROM v$parameter
WHERE name = ‘db_block_size’) e
WHERE
(d.file_name = v.name)
UNION
SELECT
d.tablespace_name || ‘ TEMP‘ tablespace
, d.file_name filename
, d.bytes/1024/1024 filesize
, d.autoextensible autoextensible
, (d.increment_by * e.value)/1024/1024 increment_by
, d.maxbytes/1024/1024 maxbytes
FROM
sys.dba_temp_files d
, (SELECT value
FROM v$parameter
WHERE name = ‘db_block_size’) e
UNION
SELECT
‘[ ONLINE REDO LOG ]’
, a.member
, b.bytes/1024/1024
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$logfile a
, v$log b
WHERE
a.group# = b.group#
UNION
SELECT
‘[ STANDBY REDO LOG ]’
, a.member
, b.bytes/1024/1024
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$logfile a
, v$standby_log b
WHERE
a.group# = b.group#
UNION
SELECT
‘[ CONTROL FILE ]’
, a.name
, TO_NUMBER(null)
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$controlfile a
ORDER BY 1,2
/
Step 2:Will suggest a new size based on HWM.
set lines 180 pages 200
select ‘alter database datafile’||’ ”’||file_name||””||’ resize ‘||round(highwater+100)||’ ‘||’m’||’;’ from ( select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name=’db_block_size’) d
where a.file_id= b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name);
Step 3:Find High Water Mark in particular Table.
SQL> SET LINESIZE 300
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper(‘&&Table_Name’),’ALL’,a.table_name,Upper(‘&&Table_Name’))
AND a.owner = Upper(‘&&Table_Owner’)
AND a.partitioned=’NO’
AND a.logging=’YES’
order by table_name;
op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line(‘TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK’);
Dbms_Output.Put_Line(‘—————————— ————— ————— —————‘);
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,’TABLE’,op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,’ ‘) ||
LPad(op3,15,’ ‘) ||
LPad(op1,15,’ ‘) ||
LPad(Trunc(op1-op3-1),15,’ ‘));
END LOOP;
END;
/
Run the Script to given below the output:
SQL> truncate table emp;
Table truncated.
Again the run the script will change the high water mark:
HOW TO RESET HIGH WATER MARK IN BELOW POSSIBLE WAYS:
⦁ Export /Import the table
⦁ Alter tablespace move
⦁ Truncate and insert table
⦁ Analyze the table