RE: dbms_space.space_usage and effectiveness of purge operations

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: Stalin <stalinsk@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 5 Nov 2013 22:20:05 +0000

I am now curious about why your index shows only FULL and FS2 - and it occurs 
to me that it's a feature of the way ASSM is using its bitmaps for indexes that 
I've not even considered.  My comments about pctfree 25 would make perfect 
sense if indexes behaved the same way as tables with ASSM - but actually it 
doesn't make sense to talk about any "level of freeness" for index blocks, the 
leaf block is either in the right place with the right content or it's empty 
and can be used elsewhere.

Where did you read the bit about the FS2 identifying blocks on the free list ? 
I clearly have some reading to do, and a little bit of testing because I'm no 
longer sure how I should interpret your original figures.


Jonathan Lewis

From: Stalin [stalinsk@xxxxxxxxx]
Sent: 05 November 2013 21:32
To: Jonathan Lewis; oracle-l
Subject: Re: dbms_space.space_usage and effectiveness of purge operations

Oops, didn't hit reply all.

+ oracle-l

On Tue, Nov 5, 2013 at 1:28 PM, Stalin 
<stalinsk@xxxxxxxxx<mailto:stalinsk@xxxxxxxxx>> wrote:
Thanks Jonathan, Mark and Martin for your inputs.

Jonathan as summed up well in his last response to Martin. Based on the way we 
do deletes, i came to the same conclusion of not coalescing the index, However 
i wanted to see, if dbms_space.space_usage package would give me clues to 
ascertain the fact without having to use validate structure to analyze the 
index which could cripple our production systems.

I was hoping FS2 blocks (25-50% free space) would give me a clue after few days 
of deletes, as i remember reading from on wards, FS2 blocks will be 
considered for inserts. I was little unclear on the increase in FS2 block 
counts after coalesce but Jonathan explanation honoring PCTFREE makes sense.

Jonathan, PCTFREE for that index is set to 10%.



P.S. we are EE, 7Node RAC, RHEL 5.x

Other related posts: