Re: Progressively slower insert of CLOBs

dfult_at_econs.umass.edu (Doug) wrote in message news:<358a7114.0303121352.3512a514_at_posting.google.com>...
> I am migrating a database to Oracle 8.1.7. First, I have created the
> tables in Oracle without any indexes (including no primary key).
> Then, I am using an application that uses JDBC to pull records out of
> the old database and then insert them (one at a time) into Oracle.
> This works like a charm except in the case of one somewhat large table
> (220,000 records) that contains a CLOB -- other large tables without a
> CLOB are fine. The table only has an id and the CLOB. The migration
> application starts out inserting at a 50 records/second pace; it gets
> progressively slower such that by less than halfway through it takes a
> couple seconds per record. I tried restarting the server with
> _disable_logging=true to no avail. The Oracle server box
> (sun-solaris) looks fine in terms of memory (I have 2GB), but CPU is
> flatlined near 100%, mostly to user. I feel confident that Oracle is
> the bottleneck, because once the table had 90,000+ records, I stopped
> the migration and modified my migration program to simply try to
> insert a few hardcoded rows and got the same poor performance. I did
> this after restarting with _disable_logging=true. For good measure, I
> also did an alter table ... NOLOGGING, though presumable redundant.
>
> Here are various settings from my init<DB>.ora:
>
> db_block_buffers = 175766
> shared_pool_size = 479959040
> large_pool_size = 15728640
> log_buffer = 1638400
> _disable_logging=true
> java_pool_size = 0
> log_checkpoint_interval = 10000
> log_checkpoint_timeout = 1800
> processes = 50
> db_block_size = 8192
> sort_area_size = 20971520
> sort_area_retained_size = 524288
>
> TIA,
> Doug

Three possible culprits here:
1 Improper sizing of the online redo log files, causing Oracle to switch logfiles like hell. Can be checked by selecting from v$log_history and checking for 'checkpoint not complete' or 'cannot allocate log' messages in the alert
2 Improper SGA configuration is apparent from the above init.ora parameters.
The SGA is *WAY* too big. You must be faulting continually. According to Oracle NT gets 50 percent of the physical RAM by default, in your case 1G. Your SGA is bigger than the available 1G, so you are faulting on a permanent basis
3 Excessive block chaining in the affected table: rows won't fit in 1 database block, so they are chained.

You could try setting event 10046 to check out for what your process exactly is waiting. Other than that you need to decrease the SGA and to review the size of your online redologs.

BTW: Disable logging is *NOT* the solution, it should be considered as avoiding to address the *REAL* problems you are having.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Thu Mar 13 2003 - 11:16:18 CET