Re: Slow Inserts into BIG table in 7.2.3

utrankar_at_nyc.pipeline.com (Sameer Utrankar) wrote:
>Output of one of the slowest Insert in my pl*sql batch is attached.
>Table contains 2 million rows already. Insert into it takes a long
>time. 42464 rows inserted in 3392 secs. Query uses index alright (no
>full table scans). When this stmt runs, HP-UX Glance Plus shows 100%
>CPU bottleneck probability. (I always consider high CPU usage as
>inefficinet - indication of full table scans usually - although not so
>in this case). Any ideas on how to improve this one ?
>
>TKPROF: Release 7.2.3.0.0 - Production on Fri Mar 8 09:44:24 1996
>
> <SNIP>

I had severe problems with large inserts and other large transactions under 7.2.2.3 and 7.2.3. Our environment is a dual processor K200 running HP/UX 10.01. I worked with Oracle for 6 weeks on an open TAR to fix the problem. It was finally resolved by applying the patch for bug #338507 to Oracle 7.2.3. The underlying cause was a bug in the SMON process' code for coalescing free space. When we set the proper event to disable SMON's coalescing, performance of the system increased very dramatically.

A quick way to check if might be your problem, is to look at the CPU usage of the SMON process. Under normal conditions, we find the SMON process uses about 10 seconds of CPU time per day. When hit by the bug, the utilization was often over 60 minutes per day. SMON wakes up periodically to perform certain cleanup. On our system this is every 5 minutes. During certain periods of high user activity, particularly during large transactions, SMON seems to run out of control. On our system, SMON would take over one processor and run at
> 98% CPU utilization for 5 to 10 minutes. During this time, SMON
was found to issue ST locks that block some (but not all) of the Oracle transactions in progress. Eventually everything ran to completion, often taking 5 to 10 times the expected elapsed time.

We have been running with the patch for about 3 weeks with no obvious adverse side affects. Massive tuning efforts on the system did not help other than to delay the onset of our problem. Initally SMON would go crazy about 5,000 records into a large transaction. Increasing the stored pool size and other adjustments defered the problem to about 20,000 records into a transaction. I found I could also trigger the problem by issuing an ALTER SYSTEM FLUSH SHARED_POOL command. The next few times time SMON woke up, after the flush, it took over a processor for five to ten minutes each time. I hope this is useful.

• Andy Zitelli, Silicon Systems, Inc.