Case:
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.
Note
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)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)
