Oracle table fragmentation causing performance issue

Issue Description:

Application team reported, one of the jobs is running very slow in production. Due to this delay another job also accessing the same objects and it’s causing blocking locks on database.

1.    Almost one year, we deployed the new enhancement in prod database. Suddenly we faced this performance issue.

2.    Job A is running every  hour and it’s copying the data  from few tables and fetching into another DB via DB link and deleting the records in source database (whatever data copied from source database to target database)

3.     We locked the better statistics for these tables based on performance testing.

Impact:

1.    Job A deleting records on tables for every hour, Due to the large number of deletion on tables causing the fragmentation on tables. Obviously if tables are fragmented, corresponding indexes also fragmented.

2.    If tables statistics were locked, so I couldn’t find the exact details about these tables also I couldn’t gather the current stats for these tables.

How to check if tables are fragmented?

select t.owner,

t.table_name,

t.avg_row_len,

t.last_analyzed,

s.bytes/1024/1024 as SEGMENT_SIZE_MB

from

dba_tables t,

dba_segments s

where t.table_name=s.segment_name

and

t.owner=s.owner

and s.segment_type='TABLE'

and owner='&owner';

 

These below table’s stats were locked. So I manually count the records from below tables and put on Original columns.

OWNER     TABLE_NAME    AVG_ROW_LEN    SEGMENT SIZE  (MB)    Original Rows    Original Space Size (MB)
TEST           Table1             302                          8.000                         0                               0.000
TEST           Table2            120                          259.000                     4369                          0.650
TEST           Table3            104                          145.000                     442                            0.057
TEST           Table4           148                          0.125                         0                                0.000
TEST           Table5           147                          0.125                         0                                0.000
TEST           Table6           0                              0.125                         0                                0.000
TEST           Table7           0                              0.125                         0                                0.000
TEST           Table8           154                          20.000                       4509                          0.861
TEST           Table9           143                          130.000                      25058                       4.442
TEST           Table10          152                          59.000                        0                               0.000
TEST           Table11          158                            0.125                        0                               0.000
TEST           Table12           0                               0.125                        0                               0.000
                                                                   ~622MB                                                      ~6MB

These tables having only need ~ 6 MB, but these occupied ~622 MB.

How to calculate the actual space requirement?

Actual Space = (Num of rows in a table) * (Avg_row_len) + ((Num of rows in a table) * (Avg_row_len)* 0.3)

Explanation:                                     

(Num of rows in a table) * (Avg_row_len) --- gives a actual space required for a table

Oracle thumb rule says (actual space required for a table + 30 % space) will calculate the original space requirement for a table.

Note: whenever we creating the segment oracle initially allocated, 0.125 MB space allocated to each segment.

Temporary Solution:

We have a several method to fix the fragmentation.( reset the HWM)

1.    Export/import method

2.    Online redefinition method

3.    CTA’s method

4.    Move the table segment

I suggested the below method.

1)    Hold the jobs

2)    Take the listed tables backup using exp/expdp utility

3)    Truncate the tables

4)    Imported the tables using backup

5)    Release the Job

Permanent Solution:

1.    Tables should be change as daily partition tables.

2.    Instead of deleting the records from tables for every hour, every day that job will drop the daily partition. It will help to avoid the fragmentation.