Re: LOB segment space usage

On Aug 17, 2:05 pm, andreik <spamme.andr..._at_gmail.com> wrote:
> Hello,
>
> can anyway here explain me, what is oracle storing inside the LOB
> segments besides the data which I am telling it to store?
>
> here's a test.
> 10.2.0.3 EE Solaris 5.10 sparc 64bit (have tried the same on 32bit
> Linux, result was the same)
>
> 1. create a tablespace with 2K blocksize called "smallblock"
> 2. create a table with a nclob type field and use smallblock
> tablespace to store the LOB segment:
>
> create table t1 (i int, b nclob)
> lob (b) store as t1_lob (
> chunk 2K
> disable storage in row
> tablespace smallblock
> pctversion 0);
>
> note the "disable storage in row" this makes the LOB to be out-of-line
> and pctversion 0 is supposed to mean, than I don't want to waste any
> space on concurrency.
>
> 3. use the Tom Kyte's show_space() procedure to get the initial
> picture:
>
> SCOTT_at_andrkydb> exec show_space ('T1_LOB','SCOTT','LOB');
> Unformatted Blocks ..................... 0
> FS1 Blocks (0-25) ..................... 0
> FS2 Blocks (25-50) ..................... 0
> FS3 Blocks (50-75) ..................... 0
> FS4 Blocks (75-100)..................... 0
> Full Blocks ..................... 0
> Total Blocks............................ 32
> Total Bytes............................. 65,536
> Total MBytes............................ 0
> Unused Blocks........................... 28
> Unused Bytes............................ 57,344
> Last Used Ext FileId.................... 4
> Last Used Ext BlockId................... 33
> Last Used Block......................... 4
>
> The parameter which I'm looking at is "Full Blocks". It is zero at the
> moment, which is very correct.
>
> 4. insert a row, passing 2048 bytes of data into nclob field and take
> a look at what happens:
>
> SCOTT_at_andrkydb> insert into t1 values (0, rpad('*',2048,'*'));
>
> 1 row created.
>
> SCOTT_at_andrkydb> commit;
>
> Commit complete.
>
> SCOTT_at_andrkydb> exec show_space ('T1_LOB','SCOTT','LOB');
> Unformatted Blocks ..................... 25
> FS1 Blocks (0-25) ..................... 0
> FS2 Blocks (25-50) ..................... 0
> FS3 Blocks (50-75) ..................... 0
> FS4 Blocks (75-100)..................... 0
> Full Blocks ..................... 3
> Total Blocks............................ 32
> Total Bytes............................. 65,536
> Total MBytes............................ 0
> Unused Blocks........................... 0
> Unused Bytes............................ 0
> Last Used Ext FileId.................... 4
> Last Used Ext BlockId................... 33
> Last Used Block......................... 32
>
> So why did it need to use 3 blocks (that's 6144 bytes) instead of just
> 1 to store the poor 2048 bytes?
>
> SCOTT_at_andrkydb> select (dbms_lob.getlength(b)) from t1;
>
> (DBMS_LOB.GETLENGTH(B))
> -----------------------
> 2048
>
> What causes such an overhead?
>
> In experimental way I have discovered that only ~990 bytes of data
> fits into a single block (if nclob is used):
>
> SCOTT_at_andrkydb> insert into t1 values (0, rpad('*',990,'*'));
>
> 1 row created.
>
> SCOTT_at_andrkydb> exec show_space ('T1_LOB','SCOTT','LOB');
> Unformatted Blocks ..................... 24
> FS1 Blocks (0-25) ..................... 0
> FS2 Blocks (25-50) ..................... 0
> FS3 Blocks (50-75) ..................... 0
> FS4 Blocks (75-100)..................... 0
> Full Blocks ..................... 4
> Total Blocks............................ 32
> Total Bytes............................. 65,536
> Total MBytes............................ 0
> Unused Blocks........................... 0
> Unused Bytes............................ 0
> Last Used Ext FileId.................... 4
> Last Used Ext BlockId................... 33
> Last Used Block......................... 32
>
> This kind of explains why do I have a LOB segment of size ~5GB in a
> prod database, which stores only 2,2GB of useful data.
>
> In case of CLOB, overhead is slightly smaller. The whole 1800 bytes
> fit into a 2048 bytes block. Which is still kind of strange and what
> is worse, it is nowhere mentioned in the docs.
>
> I have also tried to load a bigger volumes of data. For example I have
> loaded 30K into a LOB with 8K chunks and got 8 blocks filled, which
> makes 8 x 8192 = 65536 bytes. Which is exactly 2 times bigger number
> than it should have been.
>
> I have searched metalink and docs and forums and have found nothing.
> Here ->http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_...
> it says absolutely nothing about such an overhead. On the contrary, is
> it said there that:
> [Varying-width character data in CLOB and NCLOB datatypes is stored in
> an internal format that is compatible with UCS2 Unicode character set
> format. This ensures that there is no storage loss of character data
> in a varying-width format]
>
> note the "no storage loss" part ;)
>
> How can I be estimating my data structures in this case? Such a
> behavior makes this task very difficult if not impossible. Or do I
> need to simply always multiply by 2? (if so then I want to see it in
> the docs)
> Why would I use oracle to store my large objects, if I know I'm going
> to waste about 2 or 3 times more disk space than storing them simply
> inside a file system...?
>
> Thanks for your time!
>
> -- Andrei

I think you would need to dump data blocks to a trace file to look at the actual gory details.
The dump will be in a symbolic format (alter database dump ...)

You would store large objects as BLOBs when you want to edit them through your application.
If you don't need to edit them, or shouldn't edit them, because they can't be changed, a BFILE would suffice.

--
Sybrand Bakker
Senior Oracle DBA