Wasted space inside LOB and strange problems releasing it.
andreik
so my situation here:
10.2.0.3 with RAC (one node)
SunOS 5.9 sparc 64bit
There is a table with a NCLOB type field. The table and the LOB
segments reside in different tablespaces.
Block size is 4K, LOB chunk is also 4K
LOB resides in an ASSM tablespace, table in a non-ASSM tablespace.
There is a good metalink note (Note:386341.1) which talkes about how
to determine wasted space inside LOBs and how to release it. According
to it the following should be correct:
When I query the dba_segments view:
SQL> select bytes from dba_segments
where segment_name = 'SYS_LOB0000133926C00014$$'; --<== this is my
LOB's name, got it from dba_lobs
BYTES
----------
4690280448
But when I count the real bytes taken by the data:
SQL> select sum(dbms_lob.getlength(flob1)) from T1;
SUM(DBMS_LOB.GETLENGTH(VASTUS))
-------------------------------
2288847401
So as I understand (and as the metalink says) I have 4690280448 -
2288847401 = 2401433047 bytes wasted.
The good metalink article says that:
[quote]
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>);
[/quote]
I ran this command. Alert log gave notice:
Wed Aug 14 17:42:01 2007
LOB Freelists need to be dropped. This may take some time.
Now I want to see the blocks to become marked as "free". So I use the
DBMS_SPACE.SPACE_USAGE procedure like this:
SQL>
begin
DBMS_SPACE.SPACE_USAGE(
'XLKF',
'SYS_LOB0000133926C00014$$',
'LOB',
:unformatted_blocks ,
:unformatted_bytes ,
:fs1_blocks ,
:fs1_bytes ,
:fs2_blocks ,
:fs2_bytes ,
:fs3_blocks ,
:fs3_bytes ,
:fs4_blocks ,
:fs4_bytes ,
:full_blocks ,
:full_bytes,
NULL
);
end;
SQL>print
UNFORMATTED_BLOCKS
------------------
5375
UNFORMATTED_BYTES
-----------------
22016000
FS1_BLOCKS
----------
0
FS1_BYTES
----------
0
FS2_BLOCKS
----------
0
FS2_BYTES
----------
0
FS3_BLOCKS
----------
0
FS3_BYTES
----------
0
FS4_BLOCKS
----------
0
FS4_BYTES
----------
0
FULL_BLOCKS
-----------
1137624
FULL_BYTES
----------
4659707904
As you can see all the FS* values are 0. This should mean that there
are still no blocks to be released from the segment. And this explains
why nothing happens when I run the:
>alter table <table name> modify lob(<lob column name>) (shrink space [cascade]);
So my question is: why the blocks are not getting marked as "free" and
if there is another approach to the problem?
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...
fitzj...@cox.net
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 likely because they are below the High Water Mark (HWM).
> And this explains
> why nothing happens when I run the:
>
> >alter table <table name> modify lob(<lob column name>) (shrink space [cascade]);
>
No, what explains that behaviour is the fact that none of this
'wasted' space is below the tablespace HWM.
> So my question is: why the blocks are not getting marked as "free" and
> if there is another approach to the problem?
>
I've explained this above.
> 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.
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.
The Metalink document explains the issue well; it's a shame you
couldn't take all of the information it presented instead of
selectively applying only that which YOU thought was relevant.
David Fitzjarrell
andreik
>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.
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?
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.]
> 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.
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.
2. none of the DBMS_SPACE package procedures can show you the blocks
of the "deleted" state
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.
My problem is:
1. after I run the freepools rebuild, I don't get any of the "deleted"
blocks converted into "free" ones.
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?...
fitzj...@cox.net
On Aug 15, 10:41 am, andreik <spamme.andr...@gmail.com> wrote:
> On Aug 15, 5:33 pm, "fitzjarr...@cox.net" <fitzjarr...@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
andreik
> Comments embedded.
> Because your CHUNK size is equal to your LOB segment size.
can you explain this? I really don't get your point.
maybe you intended to say "chunk size is equal to the tablespace block
size" ?
because in my case the LOB _segment_ is about 4G and it consists of 4K
chunks.
You should not be so sure about the PCTUSED and PCTFREE parameters,
since I'm using the ASSM tablespace and I believe that blocks are
managed some other (was it a bitmap or smth?...) way there.
But anyway, I believe I have found something new here.
I have made some tests.
I created a tablespace called "smallblock" with blocksize of 2KB.
Then created a table with a LOB segment (chunk is also 2K)
SCOTT@andrkydb> create table t1 (i int, b nclob) lob (b) store as
t1_lob (chunk 2K disable storage in row tablespace smallblock);
SCOTT@andrkydb> select bytes from dba_segments where segment_name =
'T1_LOB';
BYTES
----------
65536
Then I wrote a simple script which inserted 1000 rows into table T1,
putting a bit less than 2K of data into the "b" field (so that each
inserted row would take exactly 1 chunk of the LOB segment)
After running the script:
SCOTT@andrkydb> select bytes from dba_segments where segment_name =
'T1_LOB';
BYTES
----------
5242880
So you see that my LOB has grown up to ~5M
But when I count the "real bytes" then I get a much smaller number:
SCOTT@andrkydb> select sum(dbms_lob.getlength(b)) from t1;
SUM(DBMS_LOB.GETLENGTH(B))
--------------------------
1952000
So.. I had 2K tablespace blocks and 2K LOB chunks. I was inserting
about 2K of data in each row and got the LOB segment about 2x bigger
than is needed by the data.
As I understand now, I will not get to shrink my LOB, because there
are actually no "deleted" or "free" blocks, but space is being taken
over by some spooky Oracle internal stuff... Now I wonder, if it could
be explained what is that space is being used for?
ps. otherwise the metalink article works. meaning, that when I delete
some stuff from the table and then run the dbms_space, I don't see any
blocks being unused. but after I rebuild the freepools, the blocks
really become marked as free and I can release them using the "alter
table ... shrink space" command. I will not be posting the logs of
that part of my tests but you can try it yourself to see that it
doesn't have to do anything with LOB chunk being equal to anything.