Re: LOB space, with numbers
Date: Sun, 13 May 2012 07:33:35 +0100
Message-ID: <EeedndJJT_YiyDLSnZ2dnUVZ8tSdnZ2d_at_bt.com>
"Mladen Gogala" <gogala.mladen_at_gmail.com> wrote in message news:pan.2012.05.13.01.58.57_at_gmail.com...
|
| SQL> select bytes/1048576 from dba_segments
| 2 where owner='SCOTT' and
| 3 segment_type='LOBSEGMENT' and
| 4 segment_name='MUSIC';
|
| BYTES/1048576
| -------------
| 2368
|
| So, how much space will be consumed after we reload the data?
|
| SQL> select bytes/1048576 from user_Segments
| 2 where segment_name='MUSIC';
|
| BYTES/1048576
| -------------
| 2496
|
|
| So, after re-loading 168MB of data, the size of the LOB segment is
| extended for a whopping 128MB.
But you're looking at segment size, not used block size, so there's room for error in how much further the high water mark has moved. In this case your figues is probably an UNDER-estimate.
I think you're using auto undo retention, and have the RETENTION keyword in the LOB definition (from your previous thread. This means Oracle will NOT overwrite a LOB until the amount of time is has been deleted is at least as long as your auto undo retention time for the database - so your 168MB of LOBs should have grown the lob segment by 168MB, not just 128MB.
| Now, let's try SECUREFILE storage. The bin_files DDL now looks like
this:
|
| LOB ("CONTENT") STORE AS SECUREFILE "MUSIC"(
| TABLESPACE "TEST_LOB" DISABLE STORAGE IN ROW CHUNK 32768
| CACHE NOCOMPRESS KEEP_DUPLICATES
| STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
| PCTINCREASE 0
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
|
There is no RETENTION keyword visible which I think means you have left it to default, which means the same as for BASICFILES. In this case, though, I'd guess that the space allocated already allowed enough space for the 168MB of extra data.
|
| Now this is a surprise! The segment hasn't grown at all! It is still
| larger than the BASICFILE segment after the reload but at least an
| exorbitant growth is not there. When I repeated the insert, the LOB
| segment growth was moderate, less than the size of the batch that was
| loaded:
|
| MB
| ----------
| 2625.13
|
That looks like one more extent of 128MB - again leaving us the possibility
that you first filled a previous extent and then added one more for the
overflow.
There is a small anomaly in the numbers, though, that might indicate a
timing problem, or might indicate that LOBs can add extents prematurely
when they are following a rapid growth pattern - I believe there's a
background asynchronous process that does some space management for
securefiles (but I'd have to check my notes to find out why I believe
that).
| Of course, with SECUREFILE, defragmentation doesn't work:
|
On the other hand you can specify RETENTION NONE with securefiles and not use any of your space for retaining older versions.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Oracle Core (Apress 2011) http://www.apress.com/9781430239543Received on Sun May 13 2012 - 01:33:35 CDT