dbms_space.space_usage and effectiveness of purge operations
- From: Stalin <stalinsk@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 5 Nov 2013 09:34:37 -0800
All,
I'm Trying to analyze the need to coalesce indexes after purge operations.
Since our purge operations deletes rows from tables that are older than 6
months, we expect to see clean-up of leaf blocks evenly, thereby making the
blocks candidate for new inserts.
As an example, i used dbms_space.space_usage package to study the output.
This is run on a fk index on order_line_item_id sequence based, where purge
operation are performed everyday to delete rows beyond 6 months.
Note: FSn are all blocks.
Before Coalesce
UNF = 1224, FS4 = 0, FS3=0, FS2=9525, FS1=0, FULL=149334
With Coalesce run on the same index.
UNF = 1208, FS4 = 0, FS3=0, FS2=73548, FS1=0, FULL=85327
Questions :
1. Why does after coalesce FS2 free blocks increased? From the nature of
the deletes we do, i would have expected blocks to join the freelist as and
when they get empty for future inserts. That way, coalesce can be
completely avoided.
2. Looking at post coalesce numbers, does this mean we have 73548 blocks
available in freelist for future inserts.
3. Does this mean we ought to coalesce these type of indexes cause almost
50% of the blocks were released to FS2 bucket.
Note: The index is housed in ASSM tablespace with auto allocate.
--
Thanks,
Stalin
Other related posts:
