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 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)
select index_name,status from dba_indexes where table_name like '&table_name';
alter table <table_name> move;
alter table <table_name> enable row movement; alter table <table_name> move tablespace <new_tablespace_name>;
alter table table_name move tablespace old_tablespace_name;
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';
exec fnd_stats.gather_table_stats('&owner_name','&table_name');
exec dbms_stats.gather_table_stats('&owner_name','&table_name');
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)
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
- Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021
Clear Cut Info as always, Thx Brijesh 🙂
Thank a lot, i found it very helpful.
Very Well explained as always!
Thank you Brijesh
Thank you sir
Very well done, thanks.
Hello,
Can we see and other methods -2 and 3 ?
Thank you for well explained and easy to understand the concepts.