RE: dbms_space.space_usage and effectiveness of purge operations

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "stalinsk@xxxxxxxxx" <stalinsk@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 5 Nov 2013 18:23:27 +0000

Before you try to work out what has happened you need to check the setting for 
PCTFREE for this index. I suspect it may be 25 - or close (I'm basing that 
guess on the observation that FS2 has increased.)

FS2 are blocks which have 25% to 50% free space, which means they are 50% to 
75% utilised, which means they are not FREE, their (logical) position in the 
index is fixed and they cannot be moved. When (a newer version of) Oracle does 
a coalesce it reads several (logically) consecutive leaf blocks and spreads the 
data out evenly between then, honouring the PCTFREE value.

If your PCTFREE was 25, and you had some blocks with (say) 40% free space, and 
some with (say) 15% free space then the first batch would be FS2 and the second 
batch would be FULL - after a coalease most of them would have moved to 
approximately 25% free space - and if a lot of them went to 25.1% free space 
that would mean that many of the FULL blocks would end up switching to FS2 and 
perhaps very few of the current FS2 blocks would go the other way.

The granularity of the FS0 - FS4 is too crude to give you any clear idea of the 
state of your index, but it's possible to hypothesise what it probably looks 
like based on the distribution of item codes - but if you're deleting one day 
of history every day then you probably don't need to do a coalesce.

Unfortunately, now that you've done one coalesce, it might be a couple of 
months before your index gets back to steady state so that you could examine it 
in detail.  (This doesn't mean you've introduced a performance problem, by the 
way, just a patch of uncertainty).  There are a couple of scripts on my blog 
which scans and analyses every leaf block in an index to report the finer 
grained statistics of use, and even draw a picture of the leaf level of the 
index. They are I/O and CPU intensive, though, when applied to large indexes.


Jonathan Lewis

From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf 
of Stalin [stalinsk@xxxxxxxxx]
Sent: 05 November 2013 17:34
To: oracle-l
Subject: dbms_space.space_usage and effectiveness of purge operations


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.



Other related posts: