Re: dbms_space.space_usage and effectiveness of purge operations

  • From: Stalin <stalinsk@xxxxxxxxx>
  • To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 5 Nov 2013 13:32:36 -0800

Oops, didn't hit reply all.

+ oracle-l

On Tue, Nov 5, 2013 at 1:28 PM, Stalin <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%.
> --
> Thanks,
> Stalin
> P.S. we are EE, 7Node RAC, RHEL 5.x

Other related posts: