Re: Oracle iFS (now: LOB performance)
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 2 Apr 2004 13:45:09 +0300
Hi!
> 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
single
> 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.
Tanel.
>
> 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.
> 2. CACHE: NOCACHE
> 3. Redo: About 10 MB every 2 minutes.
>
> Also
> Oracle version: 9.2.0.4
> Current OS & Hardware:
> OS: Solaris 2.6
> RAM: 7 GB
> CPU: 6 cpu @ 400 mhz
> Model: E4500
>
> Ben
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
> Behalf Of Tanel Põder
> Sent: Thursday, April 01, 2004 10:34 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Oracle iFS
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
