Giganews Newsgroups
Subject: SECUREFILES disaster
Posted by:  Mladen Gogala (gogala.mlad…
Date: Sun, 22 Apr 2012

LOB segment stored as SECUREFILES cannot be defragmented using SHRINK
SPACE utility. Also those segments are just as wasteful when it comes to
space as the 10G implementation of LOB columns.

The problem is, of course, the fact that Oracle does extremely lousy job
managing space in the LOB segments, because of concurrency. Here is the
problem: whenever a row is deleted or LOB column updated, Oracle would
theoretically have to update the structures describing the free space in
LOB segments, should they exist. Should such "free space bitmap" exist,
it would have to be updated after every DML operation on LOB column. In
order to update such structure, the updating process would have to lock
it first. Now, all LOB data for a table or partition is in the same
segment, which means that updating a LOB column would imply locking a
table-wide structure for a while, whenever a row is updated. That would
effectively transform row level locking into table level locking, with
predictably disastrous effects on concurrency. LOB columns are not
relational data, they're stored within a LOB segment like files in a
directory, but the directory operations are conducted without doing the
space accounting, which means that there will be a lot of wasted space.

Now, the question is what does SECUREFILE storage actually do for me in
terms of space? The answer is: it does nothing. LOB segments stored as
SECUREFILES will grow as fast as the ones in the version 10G and waste
equal amount of space. A friend told me that 12C will have significant
news in this area, but it's not available yet, at least not to me. The
SECUREFILE storage is actually harmful because it is not possible to
shrink space within the LOB segment using the classic 10G utility, if the
segment is stored as SECUREFILE. Of course, Oracle followed its recent
practice and published a ton of information about the SECUREFILES
storage, without actually publishing how does it work, what does it do
and what benefits will I get by storing my LOB as SECUREFILES.

With purchasing another expensive database option, licensed per CPU
thread, it is possible to compress LOB columns, if they're stored as
SECUREFILES but that option apparently isn't particularly popular.
Neither of the two companies that I've been recently working for has
bought it. As a matter of fact, I am not aware of any medium sized
company which has purchased the advanced compression. Also, that will
still not introduce any sensible space management in the LOB segment, it
will only make each LOB smaller.

So, for now, I have to judge SECUREFILES as an unqualified disaster which
wastes disk space by the ton. In the databases that I manage, storing LOB
columns as SECUREFILES is expressly forbidden.