Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for the ‘Oracle9i’ Category

Get High Water Mark (HWM) stats for a table – Oracle9i

Posted by Hendry chinnapparaj on December 15, 2010

What is High Water Mark(HWM)?

HWM is boundry between used block and unused block. Blocks below HWM is used blocks and above HWM is unused blocks. Used blocks might or might not have the data. In normal operations (Insert/update), the HWM is mostly bump up and never go down. Oracle tracks the HWM for the segment in segment header.

The Following script can be run as a privileged user to get the stats

set serveroutput on
DECLARE
alc_bks NUMBER;
alc_bts NUMBER;
unsd_bks NUMBER;
unsd_bts NUMBER;
luefi NUMBER;
luebi NUMBER;
lub NUMBER;
BEGIN
FOR c1_row IN (SELECT table_name
FROM dba_tables where table_name=’OLS’) LOOP
DBMS_SPACE.UNUSED_SPACE (
segment_owner => ‘OLSUSER’,
segment_name => ‘OLS’ ,
segment_type => ‘TABLE’ ,
total_blocks => alc_bks ,
total_bytes => alc_bts ,
unused_blocks => unsd_bks ,
unused_bytes => unsd_bts ,
last_used_extent_file_id => luefi ,
last_used_extent_block_id => luebi ,
last_used_block => lub);
DBMS_OUTPUT.PUT_LINE( ‘Data for ‘|| c1_row.table_name);
DBMS_OUTPUT.PUT_LINE( RPAD(‘*’,LENGTH(c1_row.table_name) + 10,’*’));
DBMS_OUTPUT.PUT_LINE( ‘Total Blocks……………..’|| alc_bks );
DBMS_OUTPUT.PUT_LINE( ‘Total Bytes………………’||alc_bts );
DBMS_OUTPUT.PUT_LINE( ‘Unused Blocks…………….’|| unsd_bks );
DBMS_OUTPUT.PUT_LINE( ‘Unused Bytes……………..’||unsd_bts );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Ext File Id……..’|| luefi );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Ext Block Id…….’|| luebi );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Block…………..’||lub );
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

steps if you’re rebuilding

1) Run the script above, collect the pre-build stats

2) Rebuild the table

3) Run the script again, collect the post-build stats

4) space reclaimed after the rebuild

For Example

HWM before Rebuild

 

Data for TABLE X
****************************
Total Blocks……………..5248
Total Bytes………………42991616
Unused Blocks…………….64
Unused Bytes……………..524288
Last Used Ext File Id……..13
Last Used Ext Block Id…….428681
Last Used Block…………..64
PL/SQL procedure successfully completed.

HWM = Total Blocks – Unused Blocks
HWM=5248-64=5184
LN10 – after

Data for TABLE X
****************************
Total Blocks……………..1536
Total Bytes………………12582912
Unused Blocks…………….114
Unused Bytes……………..933888
Last Used Ext File Id……..13
Last Used Ext Block Id…….149257
Last Used Block…………..14
PL/SQL procedure successfully completed.

HWM = Total Blocks – Unused Blocks

HWM=1536-114=1422

When ever optimizer takes full table scan, it scan all the blocks below HWM. This would degrade the performance and slowdown the Full table scan process. To avoid this, we need to shrink the table to reset the HWM.

Oracle does not release the free space under HWM for use by other objects for a simple reason: because that space is reserved for new inserts and to accommodate the growth of existing rows. The additional benefit of resetting HWM is a more efficient utilization of space because the space freed is available for other objects.

Resetting the HWM?

Option 1
Alter table OLS move tablespace

This option requires rebuilding the indexes. since the index will be invalid/unusable after running the above command. The downside is, rebuilding the index is additional overhead when we use this option. Also users can not use the application or reports while rebuilding the index.

Option 2

1. Export the data
2. truncate the table
3. import the table
4. Analyze the table

Option 3
1. copy the table data
2. truncate the original table
3. insert back.

Option 4
Use DBMS_REDEFINITION package to copy the table.

Advertisements

Posted in Oracle9i | Leave a Comment »