Re: Progressively slower insert of CLOBs

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0303130216.5c1e2af0_at_posting.google.com>...
> 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

I had a hunch that the progressively longer times might have to so with sorting (even though I had no primary key or indexes on the table) and that in fact the sort was being done on the CLOB -- since other large tables without a CLOB were not a problem; thus, I created the primary key on the id. After this, the inserts maintained the 50 records/second pace throughout all 220,000 records. Does it seem plausible that Oracle chooses a column to sort on in the absence of a primary key and that it might choose the CLOB? That's what my evidence appears to indicate.

Sybrand: thanks for pointing out the issue with the SGA; I will want to address this regardless. Interestingly, I got these settings from dbassist. I changed the sort settings afterwards, but not the db_block_buffers or db_block_size settings. Do you have a suggested size? Do I need to take into account more than db_block_buffers x db_block_size?

Cheers,
Doug Received on Thu Mar 13 2003 - 17:30:58 CET