Reorg tables and indexes in Oracle EBS Applications – Best Practices

Usually in OLTP environment like EBS Applications, tables are often get fragmented due to multiple DML activities that happens.Fragmented tables cause queries on those tables to slow down. It is very important to de-fragment this table and to reclaim the fragmented space from these objects.

For EBS we have also seen  that usually gathered statistics, indexing and proper SQL tuning is plenty to improve and maintain acceptable performance but sometime it is required to reorg the table.

One primary cause of fragmentation is that when you run delete command on the tables it delete the rows but doesn’t frees up the memory and also do not changes the high water mark.

We have also seen that this requirement for doing reorg is more required in Demantra applications and since Demantra is both OLTP and data warehouse the applications we  must tune accordingly so that query run time can be optimum.

Although this article focus on the EBS/Demantra application tables but it is true for all oracle databases.

WHAT CAUSES FRAGMENTATION

As DML activity happens in the database, it is possible for there to be discontinuous chunks, or fragments of unused space within the tablespace and fragmentation within the table rows.

When you insert  or update row in table

As rows are added to tables, the table expands into unused space within the space. It will naturally fragment as discontiguous data blocks are fetched to receive new rows. Updating table records may also cause row chaining if the updated row can’t fit into same data block.

When you delete rows from table

At deletion, a table may coalesce extents, releasing unused space back into the tablespace. A lot of deletes leaves high-water mark behind at a high value. It will cause slower full-table-scan performance since Oracle must read to the high water mark.

WHY FRAGMENTATION IS BAD FOR DATABASE

Fragmentation can make a database run inefficiently.

a) Negative Performance impact – SQL  statements that performs full-scan and large index range scans may run more slowly in a fragmented table. 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.

b) Wasted Disk Space – It means you have space in your disk which your database can not use.

REORG PROCESS

The main goal of table reorganization is to reduce IO when accessing the big database tables.

1. Reorders the table data according to the primary key index.
2. Column reordering to push columns that have no data, nulls, to the end of the table row

The column reordering can be very useful for tables that have 300+ columns many of the columns are null. When the null columns are pushed to the end of the row, the read operation becomes streamlined thus increasing performance.

We usually follow below process for counter table fragmentation. We have also mentioned some good scripts related to data fragmentation at that end of this article.

STEP 1) GATHER STATISTICS

First you need to check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to us. This means we need to have updated stats in the dba_tables for the tables.

To understand how we collect latest statistics in EBS, please see this earlier article Gather Statistics in R12 (and 11i)

STEP 2) CHECK FOR FRAGMENTATION

Execute Script 1 provided below to find the fragmented tables

It is important that you execute step 1 for gathering statistics first before you run this script or else result will be inaccurate.

This script will show you tables which are more fragmented. You can identify tables which are frequently used in your problematic long running queries and target those for reorg process.

Please note that it is not always a good idea to reorganize a partitioned table. Partitioning of data is considered an efficient data organization mechanism which boosts query performance.

STEP 3) REORG THE IDENTIFIED FRAGMENTED TABLES

We have multiple options to reorganize fragmented tables:

 METHOD 1. Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-
 METHOD 2. Export and import the table
 METHOD 3. Shrink command . (applicable for tables which are tablespace with auto segment space management)

Method 1 is most popular and is described below:

METHOD 1. Alter table move

A) Check Table size and Fragmentation in table

It is good idea to check and record what is the current size and fragmentation in table using script 1 provided below

B) Collect indexes details

Execute below command to find the indexes details

select index_name,status from dba_indexes where table_name like '&table_name';

C) Move table in to same or new tablespace

For moving into same tablespace execute below:

alter table <table_name> move;

For moving into another tablespace, first find Current size of you table from dba_segments and check if any other tablespace has free space available

alter table <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace_name>;

After that move back the table to original tablespace

alter table table_name move tablespace old_tablespace_name;

D) Rebuild all indexes

We need to rebuild all the indexes as move command will make all the index unusable. Run the alter index command one by one for each index.

select status,index_name from dba_indexes where table_name = '&table_name';
alter index <INDEX_NAME> rebuild online; 
select status,index_name from dba_indexes where table_name = '&table_name';

E) Gather table stats

For EBS application’s datbase we use FND_STATS package

exec fnd_stats.gather_table_stats('&owner_name','&table_name');

For normal oracle database, we use DBMS_STATS

exec dbms_stats.gather_table_stats('&owner_name','&table_name');

F) Check Table size and Fragmentation in table

Now again check table size using script 1.

In our case we were able to reduce the table size from 4 GB to 0.15 GB as the table was highly fragmented.

 It is also good idea to see if there are any new invalid objects in database and run utlrp.sql to compile objects.

IMPORTANT SCRIPTS

Some good scripts related to re-org:

Script 1: To locate highly fragmented tables

select 
 table_name,round(((blocks*8)/1024/1024),2) "size (gb)" , 
 round(((num_rows*avg_row_len/1024))/1024/1024,2) "actual_data (gb)",
 round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2) "wasted_space (gb)",
 round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) "reclaimable space %",
 partitioned
from 
 dba_tables
where 
 (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;

Script 2: To find how are data blocks used for a specific table

set serveroutput on

declare
 v_unformatted_blocks number;
 v_unformatted_bytes number;
 v_fs1_blocks number;
 v_fs1_bytes number;
 v_fs2_blocks number;
 v_fs2_bytes number;
 v_fs3_blocks number;
 v_fs3_bytes number;
 v_fs4_blocks number;
 v_fs4_bytes number;
 v_full_blocks number;
 v_full_bytes number;
 begin
 dbms_space.space_usage (
 'APPLSYS',
 'FND_CONCURRENT_REQUESTS',
 'TABLE',
 v_unformatted_blocks,
 v_unformatted_bytes,
 v_fs1_blocks,
 v_fs1_bytes,
 v_fs2_blocks,
 v_fs2_bytes,
 v_fs3_blocks,
 v_fs3_bytes,
 v_fs4_blocks,
 v_fs4_bytes,
 v_full_blocks,
 v_full_bytes);
 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
 dbms_output.put_line('Blocks with 00-25% free space = '||v_fs1_blocks);
 dbms_output.put_line('Blocks with 26-50% free space = '||v_fs2_blocks);
 dbms_output.put_line('Blocks with 51-75% free space = '||v_fs3_blocks);
 dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
 dbms_output.put_line('Full Blocks = '||v_full_blocks);

end;
 /

This will give output like below:

Unformatted Blocks = 64
 Blocks with 00-25% free space = 0
 Blocks with 26-50% free space = 516
 Blocks with 51-75% free space = 282
 Blocks with 76-100% free space = 282
 Full Blocks = 10993
 PL/SQL procedure successfully completed.

REFERENCES / READ MORE

Demantra Table Reorganization, Fragmentation, Null Columns, Primary Key, Editioning, Cluster Factor, PCT Fee, Freelist, Initrans, Automatic Segment Management (ASM), Blocksize…. (Doc ID 1990353.1)
SEGMENT SHRINK and Details. (Doc ID 242090.1)
How to Deallocate Unused Space from a Table, Index or Cluster. (Doc ID 115586.1)
How to Determine Real Space used by a Table (Below the High Water Mark) (Doc ID 77635.1)
Reclaiming Unused Space in an E-Business Suite Instance Tablespace (Doc ID 303709.1)
How to Re-Organize a Table Online (Doc ID 177407.1)
Reorg Failiure : Demantra Reorg Failing On SALES_DATA (Doc ID 2209718.1)