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.
On Sun, 22 Apr 2012 19:20:39 +0000, Mladen Gogala wrote:
> e 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.
BTW, space management, if that can be called that way, uses only high water mark after inserts. That's all that I was able to confirm.
On Sunday, April 22, 2012 3:24:27 PM UTC-4, Mladen Gogala wrote:
> On Sun, 22 Apr 2012 19:20:39 +0000, Mladen Gogala wrote:
> > e 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.
> BTW, space management, if that can be called that way, uses only high > water mark after inserts. That's all that I was able to confirm.
The LOB data type has never been known for its space efficiency. In the environemts where we have used LOB datatypes update to the LOB data has been rare so we have not suffered heavy excessive space usage though I have seen some. With traditional LOB data types now referred to as basic files by Oracle the chunk size was very important in relation to space usage since the chunk size was the smallest unit of space allocated to a LOB operation.
Concurrent update to the LOB data has been pretty much non-existent in my experience.
If you have an environment where the LOB column itself is not just updated but is subject to concurrent update then it may be worth looking into what the LOB data consists of. You may find that a better design than using a single LOB column for the data might be using multiple LOB columns. The multiple LOB columns might take the form of separate LOB columns each holding a portion of the existing data that serves a specific purpose or involve some form of versioning so that multiple rows exist where new versions of the data result in new rows in the table. Potentially old versions or old enough versions could be purged freeing entire LOB's allowing better reuse of LOB space.
But then changing the design after the fact is rarely an easy sell even when the proposal makes sense.
On Mon, 23 Apr 2012 06:33:20 -0700, Mark D Powell wrote:
> ust some general comments
> The LOB data type has never been known for its space efficiency.
This is an understatement. That was OK while LOB columns were an exception but now LOB columns are found in each and every database. CTXSYS indexes are free, text searches are more and more common, but the space issues are just horrific. I hoped that "completely re-written LOB storage engine", to put it into Oracle's own words, might help, but no such luck.
Mark D Powell wrote,on my timestamp of 23/04/2012 11:33 PM:
> The LOB data type has never been known for its space efficiency.
Aye! Indeed!...
> of separate LOB columns each holding a portion of the existing
> data that serves aspecific purpose or involve some form of
> versioning so that multiple rowsexist where new versions of
> the data result in new rows in the table.
> Potentially old versions or old enough versions could be purged
> freeingentire LOB's allowing better reuse of LOB space.
I share this experience as well. A while ago I was
involved in a db design that required updates to a LOB.
We ended up simply inserting a new version and deleting the
old one: it was much faster than waiting for Oracle's
"efficient" handling of LOB updates...
On Monday, April 23, 2012 11:16:33 PM UTC-4, Mladen Gogala wrote:
> On Mon, 23 Apr 2012 06:33:20 -0700, Mark D Powell wrote:
> > ust some general comments
> > The LOB data type has never been known for its space efficiency.
> This is an understatement. That was OK while LOB columns were an > exception but now LOB columns are found in each and every database. CTXSYS > indexes are free, text searches are more and more common, but the space > issues are just horrific. I hoped that "completely re-written LOB storage > engine", to put it into Oracle's own words, might help, but no such luck.
news:jn65ch$3qa$1@dont-email.me...
|
| I share this experience as well. A while ago I was
| involved in a db design that required updates to a LOB.
| We ended up simply inserting a new version and deleting the
| old one: it was much faster than waiting for Oracle's
| "efficient" handling of LOB updates...
That's odd, because if you haven't written code to do page-based updates to LOBs, Oracle deletes the old one and inserts a new one when you do an update.
"Mladen Gogala" <gogala.mla...@gmail.com> wrote in message
news:pan.2012.04.22.19.20.39@gmail.com...
| effectively transform row level locking into table level locking, with
| predictably disastrous effects on concurrency.
More like extent-level locking - and then only with the concurrency impact of buffer busy waits, not table locks.
I've seen some undesirable effects with LOBs that can't be worked around, but nothing catastrophic (yet).
On Fri, 27 Apr 2012 20:57:51 +0100, Jonathan Lewis wrote:
> More like extent-level locking - and then only with the concurrency
> impact of buffer busy waits, not table locks.
> I've seen some undesirable effects with LOBs that can't be worked
> around, but nothing catastrophic (yet).
Jonathan Lewis wrote,on my timestamp of 28/04/2012 5:54 AM:
> | I share this experience as well. A while ago I was
> | involved in a db design that required updates to a LOB.
> | We ended up simply inserting a new version and deleting the
> | old one: it was much faster than waiting for Oracle's
> | "efficient" handling of LOB updates...
> That's odd, because if you haven't written code to do page-based updates to
> LOBs, Oracle deletes the old one and inserts a new one when you do an
> update.
Like I said - a while ago, or more specifically: 9ir2.
It also had the habit (still there, I believe?) of using the LOB tablespace as the UNDO tablespace for the operation. Which made sense, since copying the original to a separate UNDO would have been a big hit on performance. But the clincher with us was with all the indexes on the row and which UNDO they used. It was much faster to just delete the row and insert a new one than wait for Oracle to figure out how to create a "hole" or fill one in the LOB tablespace and work out how/where to write to the system UNDO.
"Mladen Gogala" <gogala.mla...@gmail.com> wrote in message
news:pan.2012.04.28.00.01.51@gmail.com...
| On Fri, 27 Apr 2012 20:57:51 +0100, Jonathan Lewis wrote:
|
| > More like extent-level locking - and then only with the concurrency
| > impact of buffer busy waits, not table locks.
| > I've seen some undesirable effects with LOBs that can't be worked
| > around, but nothing catastrophic (yet).
|
| How about abnormal growth and space consumption?
|
| -- | http://mgogala.byethost5.com
I have seen that, but not for quite a long time, and it was with ASSM tablespaces.
But I've also seen that with bitmap indexes, btree indexes, and simple heap tables in various versions and under various circumstances in the complete absence of LOBs. Apart from things that you could definitely call bugs, these phenomena also appear when some Oracle developer has missed a possible boundary condition in how an application may use their feature so my typical approach is to figure out where the collision is between the types of things that Oracle is probably doing and the nature of the application activity.
On Sat, 28 Apr 2012 10:01:37 +0100, Jonathan Lewis wrote:
> I have seen that, but not for quite a long time, and it was with ASSM
> tablespaces.
> But I've also seen that with bitmap indexes, btree indexes, and simple
> heap tables in various versions and under various circumstances in the
> complete absence of LOBs. Apart from things that you could definitely
> call bugs, these phenomena also appear when some Oracle developer has
> missed a possible boundary condition in how an application may use their
> feature so my typical approach is to figure out where the collision is
> between the types of things that Oracle is probably doing and the nature
> of the application activity.
Jonathan, I am not aware of any free space regulating mechanisms within a LOB segment. Within the table segment, there are free lists or the lists of blocks with 25%,50% and 75% of used space, index segments have their own mechanism, but no such things for LOB segments.
Mathias Hoys has recently posted a thread about less then 7000 rows consuming 900MB. It was an Apex table and the culprit was LOB. I had a close encounter with LOGMNR_SPILL$ table, which had its LOB column SPILL_DATA grow over 32GB. The version was 10.2.0.5, 64bit. The database itself is a logical standby used for reporting purposes, LogMiner cannot be avoided. I had to run "SHRINK SPACE COMPACT CASCADE" on that table.
The table is owned by the user SYSTEM and is located in SYSAUX tablespace.
I have been firefighting LOB segments explosion for the last 5 years.
"Mladen Gogala" <gogala.mla...@gmail.com> wrote in message
news:pan.2012.04.28.13.36.17@gmail.com...
|
| Jonathan, I am not aware of any free space regulating mechanisms within a
| LOB segment. Within the table segment, there are free lists or the lists
| of blocks with 25%,50% and 75% of used space, index segments have their
| own mechanism, but no such things for LOB segments.
I think this is a refernce back to my comment about extent-level contention - and you're right, i was thinking of bitmap space allocation when I wrote it, but the LOB space allocation is managed by the lobindex, of course.
| Mathias Hoys has recently posted a thread about less then 7000 rows
| consuming 900MB. It was an Apex table and the culprit was LOB.
I've added a note to that thread - the final 200MN size for 7,000 rows is potentially perfectly reasonable.
The 900MB extreme after a 4 year life cycle could also be explained with a fairly small (relatively speaking) variation in usage.
| I had a
| close encounter with LOGMNR_SPILL$ table, which had its LOB column
| SPILL_DATA grow over 32GB. The version was 10.2.0.5, 64bit. The database
| itself is a logical standby used for reporting purposes, LogMiner cannot
| be avoided. I had to run "SHRINK SPACE COMPACT CASCADE" on that table.
| The table is owned by the user SYSTEM and is located in SYSAUX tablespace.
| I have been firefighting LOB segments explosion for the last 5 years.
|
If that's spill data in the standby isn't is populated when the incoming logical change records can't be applied fast enough to keep up with the source - and if that's the case, why would any extreme size be a surprise ? 32GB at 16KB per chunk is only 2M chunks; that might only be a few hours of overload to grow, and just like any HWM it just doesn't shrink.
On Sun, 29 Apr 2012 11:03:30 +0100, Jonathan Lewis wrote:
> If that's spill data in the standby isn't is populated when the incoming
> logical change records can't be applied fast enough to keep up with the
> source - and if that's the case, why would any extreme size be a
> surprise ?
> 32GB at 16KB per chunk is only 2M chunks; that might only be a few hours
> of overload to grow, and just like any HWM it just doesn't shrink.
Jonathan, when the data from the spill table gets finally applied the rows are deleted and there are "holes" in the extent. Those holes rarely get plugged, LOB segments tend to exhibit enormous growth rates, far faster than any other type of segments.
SECUREFILES implementation doesn't do me any good, it does prevent me from using shrink space command.
"Mladen Gogala" <gogala.mla...@gmail.com> wrote in message
news:pan.2012.04.29.17.10.05@gmail.com...
|
| Jonathan, when the data from the spill table gets finally applied the
| rows are deleted and there are "holes" in the extent. Those holes rarely
| get plugged, LOB segments tend to exhibit enormous growth rates, far
| faster than any other type of segments.
| SECUREFILES implementation doesn't do me any good, it does prevent me
| from using shrink space command.
|
I'm prepared to believe that you've found a space management problem with LOBs, but so far all you've really said is:
"Once the HWM goes up it doesn't drop automatically, and since LOBs are Large OBjects, the HWM can go very high very quickly."
"Jonathan Lewis" <jonat...@jlcomp.demon.co.uk> wrote in message
news:j96dnTqpuvF9jADSnZ2dnUVZ8n-dnZ2d@bt.com...
| "Mladen Gogala" <gogala.mla...@gmail.com> wrote in message
| news:pan.2012.04.28.13.36.17@gmail.com...
||
|| Jonathan, I am not aware of any free space regulating mechanisms within a
|| LOB segment. Within the table segment, there are free lists or the lists
|| of blocks with 25%,50% and 75% of used space, index segments have their
|| own mechanism, but no such things for LOB segments.
|
| I think this is a refernce back to my comment about extent-level
| contention - and you're right, i was thinking of bitmap space allocation
| when I wrote it, but the LOB space allocation is managed by the lobindex,
| of course.
|
Except I've realised that I wasn't right out of the ballpark in my original comments.
Although the LOBINDEX handles space management for space that is below the HWM, Oracle still has to have a mechanism to identify where the HWM is and which blocks have been prepared for use - which is why you have various bitmap blocks at the segment level when using tablespaces with freelist management, and why you still see the normal ASSM bitmap blocks even for LOB segments.
