Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 6 January 2013

How to reset High water mark or Remove Fragmentation?

Fragmentation comes with when we perform update and deleted operation in a table. The space which gets freed up during these update and delete operation is not immediately re-used. This leaves behind holes in a table which results in table fragmentation.
As we know the high water mark of table actually defines the border line between used and unused space for tables. While performing full table scan, oracle will always read the data up to the high water mark (used block). HWM is an indicator of USED BLOCKS in the database. DDL statement always reset the high water mark but not the DML (update/delete). When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.
Create table DEMO as select * from PAYROLL_MAIN_FILE;
Table created.
Analyze table DEMO compute statistics;
Table analyzed.
Select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
From user_tables where table_name='DEMO';
Ever Used Never Used Total rows
---------- ---------- ----------
154 18 3680

Delete from DEMO where owner='HRMS';
1784 rows deleted.
Commit;
Analyze table DEMO compute statistics;
Table analyzed.

Select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
From User_tables where table_name='DEMO';
Ever Used Never Used Total rows
---------- ---------- ----------
154       18          1896
PL/SQL procedure successfully completed
Even though you deleted almost half of the rows, the above shows that table high water mark is up to 154 blocks, and to perform any query for full table scan, Oracle will go up to 154 blocks to search the data. Thus for better performance you need to re-organize this table.
Query to find Table size with fragmentation
SQL> select table_name,round((blocks*8),2)||'kb' "size"
From user_tables where table_name = 'PAY_PAYMENT_MASTER';
TABLE_NAME                size
------------------        -------------
PAY_PAYMENT_MASTER        14376kb
Query to find Actual data in Table
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
from user_tables where table_name = 'PAY_PAYMENT_MASTER';
TABLE_NAME               size
------------------        -------------
PAY_PAYMENT_MASTER        9248.96kb
Note: 14376 – 9248.96 = 5127.04 Kb is wasted space in table
The difference between two values is 35% as the default Pctfree 10% so, the table has 25% extra space. For that we need to reorganize the fragmented table. You can use any of the below option to reorganize fragmented tables:
  1. Alter table move + rebuild indexes
  2. export/truncate/import
  3. Create table as select
  4. dbms_redefinition
Here in this article I am applying following two methods to show how to reset high water mark and remove fragmentation. The document is tested on oracle 9i database.
Alter table move + rebuild indexes Method:
SQL> alter table PAY_PAYMENT_MASTER move;
Table altered.
SQL> alter index PAY_PAYMENT_MASTER_PK rebuild;
Index altered.
SQL> select status,index_name from user_indexes
Where table_name = 'PAY_PAYMENT_MASTER';
STATUS INDEX_NAME
-------- ------------------------------
VALID PAY_PAYMENT_MASTER_PK
SQL> exec dbms_stats.gather_table_stats('HRMS','PAY_PAYMENT_MASTER');
PL/SQL procedure successfully completed.
 
SQL> select table_name,round((blocks*8),2)||'kb' "size"
from user_tables where table_name = 'PAY_PAYMENT_MASTER';
TABLE_NAME                size
------------------        -------------
PAY_PAYMENT_MASTER        11376kb
 
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
from user_tables where table_name = 'PAY_PAYMENT_MASTER';
TABLE_NAME                size
------------------        -------------
PAY_PAYMENT_MASTER        9148.32kb
Create table as select * from Method:
SQL> create table PAY_PAYMENT_MASTER_TEMP as select * from PAY_PAYMENT_MASTER;
Table created.
SQL> drop table PAY_PAYMENT_MASTER purge;
Table dropped.
SQL> rename table PAY_PAYMENT_MASTER_TEMP to PAY_PAYMENT_MASTER;
Table renamed.
SQL> exec dbms_stats.gather_table_stats('HRMS','PAY_PAYMENT_MASTER');
PL/SQL procedure successfully completed.
 
SQL> select table_name,round((blocks*8),2)||'kb' "size"
from user_tables where table_name = 'PAY_PAYMENT_MASTER';
TABLE_NAME                size
------------------        -------------
PAY_PAYMENT_MASTER        85536kb
 
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
from user_tables where table_name = 'PAY_PAYMENT_MASTER';
TABLE_NAME                size
------------------        -------------
PAY_PAYMENT_MASTER        68986.97kb
 
SQL> select status from user_indexes where table_name = 'PAY_PAYMENT_MASTER';
no rows selected
Note: Here you need to create all indexes again.

In Oracle 10g onwards you can also use the shrink command to re-organize the data.
This command is only applicable for the tables whose tablespace defined with auto segment space management. Before using this command, you should have row movement enabled.
SQL> alter table DEMO enable row movement;
Table altered.
In first part re-arrange rows and in second part reset the HWM.
SQL> alter table DEMO shrink space compact;
Table altered.
SQL> alter table DEMO shrink space;
Table altered.
Benefit New Shrink command Method
  1. Unlike "alter table move", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.
  2. It is an online operation, so you do not need downtime to do re-organization.
  3. It does not require any extra space for the process to complete.

0 comments:

Post a Comment