Re: Wasted space inside LOB and strange problems releasing it.

Comments embedded.
On Aug 15, 10:41 am, andreik <spamme.andr..._at_gmail.com> wrote:
> On Aug 15, 5:33 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> >Apparently English is not your primary language, and I don't have a
> >problem with that. What I do take issue with is your 'picking and
> >choosing' the parts you like from the Metalink document rather than
> >reading it in its entirety and using ALL of the provided information
> >and techniques before posting your question here.
>
> Thank you for you comment. English really is not my native language,
> so sorry for the mistakes.
> I actually spent a whole day experimenting and reading that article
> over and over before posting here and I honestly considered the set of
> information I have provides to be sufficient. But ok, let's go deeper
> with this...
>
> > What it also says is this:
>
> > "This does not mean this space can be released to the tablespace, it
> > could be under the HWM. To find the freeable space, use the
> > UNUSED_SPACE procedure ..." and it provides a script to provide this
> > information.
>
> > You need to run the procedure listed in the note to determine IF you
> > have any 'wasted space' you can free, something you haven't done. My
> > educated guess is you don't.
>
> Most certainly I have tried that procedure also. It wasn't much of a
> help.

Define 'wasn't much of a help'. It either proved you have releasable space or you don't. I would find that quite helpful.

> Also notice these words from metalink article:
> [Determine the unused space within the LOB segment, above the HWM.
> using the UNUSED_SPACE procedure.]
> And as you know, we are not interested in the "above HWM" but need to
> reclaim the "deleted" blocks which must be "below HWM", am I right?

You can't reclaim any space below the HWM but you shouldn't need to. Your understanding of HOW Oracle reuses that space is lacking. PCTFREE and PCTUSED affect how that space is reused. And since your LOB segments are sized exactly the same as your CHUNK you have nothing to reclaim, as Oracle processes your LOB in CHUNK size pieces, and won't subdivide that CHUNK to conserve space. Remember, too, that the CHUNK is the minimum amount Oracle will process during inserts and updates to a LOB, meaning if you modify one byte in your 4k LOB Oracle reads and writes all 4k since that is also your CHUNK size. Were your CHUNK size set to 1K instead of 4K you might see less 'wasted' space in your LOBs.

> Another quote:
> [The deleted space can be converted into free by rebuilding the
> freelist or by rebuilding the LOB segment itself, but this is not
> always possible.]

That free space mentioned in the Metalink note is free space ABOVE the HWM.
>
> > ps.
>
> > > I must also notice that the LOB was in a non-ASSM tablespace before
> > > and I have moved it into an ASSM tablespace so to have the "alter
> > > table ... modify lob ... shrink space" feature, which is not available
> > > in a non-ASSM tablespace. I was also hoping that in the process of
> > > moving the LOB will get rebuilt but it wasn't...
>
> > Why should it? The number of freepools hasn't changed, the CHUNK size
> > is the same as the LOB size so there is nothing to modify or rebuild.
>
> another quote from the same article:
> [However, the deleted space can be turned into free space and, when
> this happens, the procedure in 2.2 would show this free space. To turn
> the deleted space into free, you have to rebuild the freepools. The
> command used to do this is:
> alter table <table name> modify lob(<lob column name>) (freepools
> <number of free pools>);]
>
> So that is why I was rebuilding the freepools.

But that's a useless task given how you've configured your LOB, with a CHUNK size equal to the size of the LOB segment.

>
> So to sum up, here is my general understanding of the question:
> 1. when there are some blocks which are in the "deleted" state, they
> could be turned into "free" blocks by rebuilding the freepools.

Not in your case, and not in all cases. As I have said several times having your CHUNK sized the same as the LOB segment prevents this.

> 2. none of the DBMS_SPACE package procedures can show you the blocks
> of the "deleted" state

Why should they? Deleted space in block is managed by PCTFREE and PCTUSED. 'Wasted' space in a LOB isn't deleted space, it's unused but allocated.

> 3. after you rebuild the freelpools you can see blocks being freed.
> and you can then shrink the LOB segment using the alter table ..
> shrink space command.
>

Provided all other criteria set forth in Metalink Note 386341.1 has been met and the procedures outlined therein have been followed.

> My problem is:
> 1. after I run the freepools rebuild, I don't get any of the "deleted"
> blocks converted into "free" ones.

Because your CHUNK size is equal to your LOB segment size.

>
> So why could such thing happen, when it is clearly seen that almost
> half of the LOB segment disk space is not being used by real data?...

Because your CHUNK size is equal to your LOB segment size.

David Fitzjarrell Received on Wed Aug 15 2007 - 11:10:09 CDT