Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle iFS (now: LOB performance)

Re: Oracle iFS (now: LOB performance)

From: Tanel Põder <>
Date: Fri, 2 Apr 2004 13:45:09 +0300
Message-ID: <0a4b01c4189f$91dad180$6df923d5@porgand>


> I welcome any discussion from the larger list regarding one or more of the
> following:

> 1. Using BLOBs as opposed to BFILE. (Although I'm not sure whether BFILE
> would work with the API I mention below)

Here are some of my thoughts and experiences:

Internal LOBs (CLOB, BLOB) should be accessible exactly with the same interface than external LOBs (BFILEs).
So you can use the same dbms_lob interface or OCI LOB procedures for accessing all of them, however BFILEs are read only. One benefit of bfiles is that you can index them with intermedia (or whatever it's called now).

In one case when I have considered bfiles, I actually went the way that I manually stored file locations in a custom table and had application servers to access the files directly from NFS file system, in order to put less load on Oracle - thus saving also on licence costs...

> 2. Tuning RETENTION, CACHE, CHUNK SIZE for a particular system's needs.

Long retention and large chunk size can consume huge amount of disk space if the LOB contents are updated frequently, since when updating a lob chunk, the whole new image of the chunk is copied to new location, in order to provide read consistency and transaction recovery. Especially when temporary lobs or other mechanisms are not used for bundling updates in a chunk, then the lob segment can explode quite easily.

On the other hand, larger chunk size may be better for performance in case of large LOBs, allowing to do larger multiblock reads and allowing a lob inode entry in lob index or table row to address more lob data.

The best solution would be not to update lob chunks frequently if possible (bundle updates to single chunk), otherwise use smaller chunks (minimum chunk size is one db/tablespace block) or lower your retention.

Regards to caching/nocaching I have been dealing with a huge challenge in one project - when you'd cache your LOBs, they'd be are always logged - with incoming data feeds hundreds of megabytes per second the logging overhead gets too large - "luckily" we can tolerate small data loss in case of disaster - we are planning to use NOCACHE NOLOGGING LOBs, storage snapshots, and incremental RMAN backups.

In case we'd lose one of the datafiles containing a partitioned LOB segment, we'd restore it from backup/snapshot, apply any incremental backups for this specific datafile and do a normal recovery using redo/archivelogs, which would leave us to the situation where all table rows and LOB indexes in the table would be fine (since they are always logged), but the corresponding LOB chunks in lobsegment would be marked invalid, thus inaccessible. An error handling mechanism has to be used, that when you hit the ORA-26040 error (Data block was loaded using the NOLOGGING option), then you just have to update the corresponding lob item to null. That way you lose the lob item, but get rid of the corruption and the space will be reused after the RETENTION period.

That way we could avoid excessive overhead due logging, but mainly thanks to small data loss allowance in case of media failure.

> 3. Coming up with test scenarios for stress testing LOB behaviour.

The main difference from standard stress testing I see, is that LOBs allow several different combinations for their usage, e.g. inline lobs (enable storage in row), inline lobs which don't fit inline, out-of-line lobs (disable storage in row), cache, nocache, cache reads, logging, nologging, different chunk sizes, also whether you have async IO available for nocache LOBs and so on. So the test results will be affected by much more parameters than with regular row manipulation...

> 4. Anticipating the migration/maintenance concerns that occur with a
> Terabyte sized database. A large percent of the data will be within a
> LOB segment.

Partitioning is definitely recommended, although there is the issue, that every LOB segment partition uses it's own space for old chunk images & retention (unlike with regular rollback segments, which can serve the whole database).

Freelist managed segments should be used with LOBs, since ASSM & LOBs used together can still hit several serious bugs.

> 5. Strategies for archiving old documents.

Luckily deleting out of line LOBs is not that resource hungry than deletes on regular data. A delete on LOB column (or update to null) doesn't have to copy the lob content itself anywhere, like with regular rows and rollback segments, thus undo & redo generation is greatly reduced.

However, some undo & redo is generated anyway, because of the updates to table row itself, to LOB index and possibly to LOB free space bitmap.


> Regarding your specific questions below:
> 1. About 400 database connections. These connections are managed by a
> Websphere application server via pooling. I'm not sure of the total number
> of end users logged into the application server at any one time, but I can
> find out.
> 3. Redo: About 10 MB every 2 minutes.
> Also
> Oracle version:
> Current OS & Hardware:
> OS: Solaris 2.6
> RAM: 7 GB
> CPU: 6 cpu @ 400 mhz
> Model: E4500
> Ben
> -----Original Message-----
> From:

> Behalf Of Tanel Põder
> Sent: Thursday, April 01, 2004 10:34 AM
> To:
> Subject: Re: Oracle iFS

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Apr 02 2004 - 04:42:22 CST

Original text of this message