Re: Please Help with Maxed CPU usage

We had this problem when using bulk collects. If you use dynamic sql bulk collect works fine. This was a bug in Oracle where PL/SQL was a bit behind in the versioning.

Martin

gdas_at_my-deja.com wrote:
>
> Can anyone provide me with any tips for the following problem?
>
> We are running oracle 8.1.5 on NT.
>
> We are developing a batch processing application coming in from 2
> servers running multiple threads. Each machine employs a connection
> pool which is shared amongst the threads. Each machine runs with a
> fixed number of threads (currently 15). The method of connection is via
> JDBC.
>
> The application threads are performing several different actions on the
> database. sometimes selects, sometimes updates and sometimes inserts.
> Additionally, several stored procedures are called. There is a central
> work queue which has the tasks that need to be done. This runs on a
> separate "dispatcher" machine. The dispatcher routes work to threads
> on the other machines. Once all work has been dispatched, it goes back
> to the top and does it again and again etc...
>
> The problem is that once we startup the application, Oracle runs fine
> for a period of time (approximately 1 hour). During this time, from
> the application side it has already gone through the work queue several
> times. After this time, the cpu usage goes to 100% on the oracle
> server and stays pinned there forever until we shutdown the database.
> Even after stopping the application, the cpu usage on oracle stays at
> 100%...
>
> My original theory was that this was being caused by some messy code in
> the application. Perhaps cursors weren't being closed. The number of
> open cursors (from v$sysstat) is pretty high (~2500). But I've gone
> through all the code and I've made sure that we are closing all of our
> resultsets and statements. Because we use a connection pool, we can't
> close the connections, however.
>
> So that's where I am and I'm stuck. I don't know what more to
> investigate. I'd appreciate any advice that anyone may offer.
>
> Thanks,
> Gavin
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Oct 28 2000 - 17:10:34 CDT