Re: Oracle Performance Problems

In article <46ggi5$amj_at_concorde.ctp.com>, msahy_at_atlantic.ny.ctp.com (Michel Sahyoun) wrote:

> I am having a puzzling problem with Oracle 7.2.2 on HP/UX 10.0 running on
> an HP 9000 K200 series with dual processors.
>
> we're running large batch loads using C and PRO*C that perform some complex
> logic on the data before inserting it into multiple tables in Oracle.
> I am using block devices, and my data is somewhat striped but not as much
> as I would like to beacuse of the number of disks I have available.
>
> When I'm running the load, the loader program seems to stop for a number of
> seconds (5-10) every 10-20 seconds. The disk access remains high but CPU usage
> drops substantially. Then after a few seconds everything goes back to normal.
>
> I can't seem to see a pattern in the timing of the "hickup": it doesn't seem
> to occur at regular intervals at my DBA is at a loss on what to do. He has
> tried every tuning paramter in the book, to no avail.
>
> The "hickups" are affecting our performance greatly since our batch loads
> are very large and we would like to save every minute possible.
>
> Has anyone seen this? Does anyone have any advice?
>
> Regards,
>
> Michel

Check your oracle alert file, if you see something like:

Thread 1 cannot allocate new log, sequence 82646 Checkpoint not complete

This means your log files are to small or you need to add some more. If Oracle can not allocate a new log because they are all in the process of being checkpointed (flushed to disk) all stops.

-- 
Dave Erwin
Lockheed Martin Missiles & Space
Email: derwin_at_lmsc.lockheed.com

The opinions expressed are those of the author and not necessarily those of
the Lockheed Martin Corporation.