Re: Oracle 10g = bloatware?
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.comReceived on Fri Apr 13 2012 - 07:52:35 CDT