Poor performance w/ stored procedures

Hello! We've recently moved some Forms 3.0 user exits to stored procedures.

What we're experiencing is that the performance for people using a stored procedure gets worse as the day goes on. We're eventually forced to shut down the database to clear out the SQLAREA in the SGA. Once this is done, the users run fine for a while then things start to slow again.

We've boosted the SQLAREA from 9M to 18M which has helped some; this just gives us a little more time between shutdowns.

The owner of the procedure is PATSMIG with a public synonym for the procedure of PATS_M56_P. We granted execute to PUBLIC on the procedure. On our production boxes, we get the following values out of V$SQLAREA for a 24 hour period:

            VERSION    KEPT       OPEN       LOADED     TOTAL      
PROCEDURE   COUNT      VERSIONS   VERSIONS   VERSIONS   EXECUTIONS LOADS
----------  ---------- ---------- ---------- ---------- ---------- ------
PATS_M56_P        2728          0          4       1802    3710068  41106

With a stored procedure, I would expect to have version count = 1 and loaded versions = 1 with maybe some number of loads (in which case I could "peg" the procedure into the SGA).

We're running Oracle 7.0.15 on AIX 3.2.5. Clients are DOS boxes running Forms 3.0 over SQL*Net TCP/IP.

If anybody can shed some light on what's going on here, I'd greatly appreciate it. Please respond via e-mail to:

     washide_at_cqo6.whirlpool.com or
     washide_at_cluster.mcs.com

Thanks in advance for your help!

Doug Washington
Whirlpool Corporation washide_at_cqo6.whirlpool.com Received on Mon Dec 19 1994 - 23:00:27 CET