pro*c daemons problems with cursor

From: Mathieu Legare <Mathieu_Legare_at_uqtr.uquebec.ca>
Date: Mon, 20 Mar 2000 09:42:09 -0500
Message-ID: <38D638C1.2072C52D_at_uqtr.uquebec.ca>



Hi

I am having a problem with a pro*c daemon which is not responsding to anything after a while. Here is my environment and some more details :

ProC*C/C++ 8.0.5.0.0
Oracle 8.0.5.1
AIX 4.2.1.0
C for AIX Compiler

basiclly, my program is a UNIX daemon which is doing this :

opening a socket;
connect to the oracle db;

while not stopped do
{

   accepting data;
   extract query from data;
   submit query to the database
   return result to the client;
}

close socket;
disconnet from the oracle db;

The program have approx 7700 LOC, not much, but it have to be online 99,99% of the time (always).

This program is opening cursors, doing select, update, delete... is is only doing some very basic requests and the database is very small... 8-10 tables and no more than 10 thousands of records/per table.

We have noticed the program was not responding when the following request
(from our dba) was getting to 400.

 select count(*)

        into :n_cur
        from v$session s, v$open_cursor o, v$sqlarea a
        where
         s.saddr=o.saddr and
         s.sid=o.sid and
         o.address=a.address and
         o.hash_value=a.hash_value and
         s.schemaname='GEST_CPTE';

The "open_cursors" parameter is set to 400. The problem is that I almost do not use cursor in this program most of the time and when I use
some, I close them but the number of open cursor keep increasing! Every time the program serve a request, the number of cursor is going up and after a while the program freeze.

We have set a parameter named "close_cached_open_cursors" to true without any succes. I am trapping every error oracle can return without luck. Any hint or help will be very appreciated.

Thanks and have a nice day,

-- 
Mathieu Legare
Analyste en informatique (systeme/webmestre)
Service de l'informatique
Universite du Quebec a Trois-Rivieres
Email : Mathieu_Legare_at_uqtr.uquebec.ca
Tel : 376-5011 x2439
--
Received on Mon Mar 20 2000 - 15:42:09 CET

Original text of this message