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.
Regards
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
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:
