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.
Regards
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 11.2.0.2 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%.
--
Thanks,
Stalin
P.S. we are 11.2.0.2 EE, 7Node RAC, RHEL 5.x
Other related posts:
