SECUREFILES disaster
Date: Sun, 22 Apr 2012 19:20:39 +0000 (UTC)
Message-ID: <pan.2012.04.22.19.20.39_at_gmail.com>
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.
-- http://mgogala.byethost5.comReceived on Sun Apr 22 2012 - 14:20:39 CDT