Re: Oracle 10g = bloatware?

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 13 Apr 2012 12:52:35 +0000 (UTC)
Message-ID: <pan.2012.04.13.12.52.34_at_gmail.com>



On Fri, 13 Apr 2012 01:19:33 -0700, Matthias Hoys wrote:

> On Thursday, April 12, 2012 8:28:16 PM UTC+2, Mladen Gogala wrote:

>> 
>> Both 900MB and 200MB seem like an enormous allocation for 6800 records.
>> 
>> 
>> 

> I did some more research on this; the table itself is only 10MB. It's
> the "SYS_LOB0000050125C00017$$" segment which has all the air (the table
> has a BLOB column which stores uploaded images and scripts and such).
>
> select segment_name,sum(bytes)/1024/1024 MB from dba_extents where owner
> = 'FLOWS_FILES' group by segment_name order by 2 asc;
>
> WWV_FLOW_FILE_OBJ_PK SEGMENT_NAME,MB WWV_FLOW_FILE_OBJ_PK,0.3125
> WWV_FLOW_FILES_USER_IDX,0.375 SYS_C004982,0.5
> WWV_FLOW_FILES_FILE_IDX,0.5625 SYS_IL0000050125C00017$$,3
> WWV_FLOW_FILE_OBJECTS$,10 SYS_LOB0000050125C00017$$,931
>
> Matthias

Mathias, Oracle doesn't manage space within the LOB segments the same way as the space in the table segments. LOB segments tend to grow and never to reuse space. You can compact them by using CASCADE option on the table, but not if they are stored as SECUREFILE. LOB segments are akin to file system directories and when row gets deleted or modified, the space seems to linger there, forever.
One of the companies I was working for decided to keep CLOB documents in a PostgreSQL database and use Sphinx as the text indexing software. Unfortunately, Postgres has huge issues with the partitioning, optimizer and concurrency, so the decision was made to try MongoDB. Sadly, the company went bankrupt before the project was completed. The company that I am working for now is following the same path and does keep documents in MongoDB, but I am no longer in charge of the project so I can't tell you much about it. What I can tell you is that MongoDB also has space maintenance problems.
LOB columns are non-relational by their nature and are hard to maintain within the database which uses fixed size blocks for its free space maintenance. When you delete row within a table, the space within the block gets added to the free space within the block. The problem with having a LOB segment header and some kind of bitmap would be concurrency. Two transactions manipulating LOB columns would have to serialize on that header, rendering row locking useless. Oracle chose to leave it at that and not do anything for the space within the LOB segments. The right approach would be to provide a reorg tool that one could run from time to time to release space, but there is none as of yet. Hopefully, something will be available in 12c or 13f.

-- 
http://mgogala.byethost5.com
Received on Fri Apr 13 2012 - 07:52:35 CDT

Original text of this message