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.

Share this post: on Twitter on Facebook on Google+