LOB space, with numbers
Date: Sun, 13 May 2012 01:58:57 +0000 (UTC)
Message-ID: <pan.2012.05.13.01.58.57_at_gmail.com>
In the last thread, I claimed that Oracle space management within the LOB segments leaves a lot to be desired. I didn't have the numbers and this thread is started to correct that mistake. Now, the numbers are here. I loaded a bunch of binary files, 614 of them, into the database. Space consumption was 2.3GB,
SQL> select bytes/1048576 from dba_segments 2 where owner='SCOTT' and
3 segment_type='LOBSEGMENT' and 4 segment_name='MUSIC';
BYTES/1048576
2368
After that, I deleted 47 files:
SQL> delete from scott.bin_files
2 where filename like '/home/mgogala/mp3/misc/The%';
47 rows deleted.
Those files were loaded in a separate table, defined exactly like the original, to measure the space they consume.
SQL> select bytes/1048576 from dba_segments 2 where owner='SCOTT' and
3 segment_type='LOBSEGMENT' and 4 segment_name='MUSIC1';
BYTES/1048576
168
The space consumption of the files is 168 MB.
What will happen when we reload those files? The files that were deleted are exactly the same so, as the theory goes, those files should fit nicely into the same holes that were made by their removal from the database. Of course, the order of the insert is not the same so we can expect some space wastage, but not too much. The files being loaded range in size from 2MB to 6.5MB:
SQL> select max(length(content)),avg(length(content)),min(length(content)) 2 from bin_files_aux;
MAX(LENGTH(CONTENT)) AVG(LENGTH(CONTENT)) MIN(LENGTH(CONTENT))
-------------------- -------------------- --------------------
6212454 3620058.74 2030876
Elapsed: 00:00:00.01
SQL> select count(*) from bin_files_aux;
COUNT(*)
47
Elapsed: 00:00:00.00
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. This is far too much. I would say that there is definitely a bug with the space management within LOB segments. This was for BASICFILE storage, which means that it is likely to encounter the same issue with version 10G. Now, let's try SECUREFILE storage. The bin_files DDL now looks like this:
CREATE TABLE "SCOTT"."BIN_FILES"
( "FILENO" NUMBER(4,0), "FILENAME" VARCHAR2(256), "CONTENT" BLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TEST_TBS"
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)) ; Off the bat, the load into the "SESCUREFILE" version is noticeably faster:
[mgogala_at_medo tmp]$ time ./test_lob
614 files loaded.
real 7m33.060s user 0m5.751s sys 0m4.416s
The best time with "BASICFILE" was 8 min 53 sec and with SECUREFILE LOB, files were loaded in 7 min 33 seconds. Second, the LOB itself is larger than the BASICFILE version:
SQL> select round(bytes/1048576,2) as MB from user_segments 2 where segment_type='LOBSEGMENT' and 3 segment_name='MUSIC';
MB
2497.13
BASICFILE version consumed 2368MB of disk space while the SECUREFILE consumed almost 2500MB. So, let's repeat the exercise:
SQL> delete from scott.bin_files
2 where filename like '/home/mgogala/mp3/misc/The%';
47 rows deleted.
Elapsed: 00:00:00.06
SQL> commit;
Commit complete.
Elapsed: 00:00:00.03
SQL> insert into bin_files
2 select * from bin_files_aux;
47 rows created.
Elapsed: 00:00:21.72
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> select round(bytes/1048576,2) as MB from user_segments
2 where segment_type='LOBSEGMENT' and
3 segment_name='MUSIC';
MB
2497.13
Elapsed: 00:00:00.02
SQL>
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:
SQL> insert into bin_files
2 select * from bin_files_aux;
47 rows created.
Elapsed: 00:00:31.75
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> select round(bytes/1048576,2) as MB from user_segments
2 where segment_type='LOBSEGMENT' and
3 segment_name='MUSIC';
MB
2625.13
Elapsed: 00:00:00.04
Remember, BIN_FILES_AUX contains 47 MP3 files and the LOB segment size 168MB. The segment grew for exactly 128MB, just as the BASICFILE version. My conclusion is that there definitely is a space management problem with the BASICFILE version. I am not sure about the SECUREFILE version but the initial allocation is noticeably larger. There is a definite speed improvement, too. Also, loading speed was consistent with the SECUREFILE LOB column, with 3 attempts, finishing in 7:33, 7:32 and 7:36 minutes. With BASICFILE, the times were ranging between 8:53 and 12:10 minutes. Of course, with SECUREFILE, defragmentation doesn't work:
SQL> alter table bin_files_aux modify lob(content) (shrink space);
Table altered.
Elapsed: 00:00:00.16
SQL> alter table bin_files modify lob(content) (shrink space);
alter table bin_files modify lob(content) (shrink space)
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type
The "AUX" version stores the files beginning with "The" and the LOB column is BASICFILE. I will try tomorrow with the manual space management in the tablespace. I will not try compression because the files being loaded are binary mp3 files and compression wouldn't be very efficient.
-- http://mgogala.byethost5.comReceived on Sat May 12 2012 - 20:58:57 CDT