Re: Stored Procs spawning too many Processes

On 4 Dec 2003 11:48:41 -0800, kevin.olds_at_aventis.com (Kevin Olds) wrote:

>Hello All,
>
> The ASP/Oracle based web application I'm working on is running into
>a load testing issue. It appears that each stored procedure call is
>opening up at least one process on the database. Given that logging in
>launches about 7 stored procedure calls, and the maximum number of
>processes in the ora.ini file is set to 150, about 20 users is all
>that can log onto the app concurrently before failures start popping
>up.
> One option I'm sure is to bump up the maximum number of connections
>in ora.ini, but I'm still investigating if I will be able to get this
>accomplished (I don't maintain the DB server and may not have the
>clout to get it changed).
> Are there any techniques for reducing the number of processes
>spawned by db connections and stored procedure calls when using ASP in
>conjunction with Oracle?
>
>Note: ASP, not ASP.net and Oracle 9.2.0.4.
>
> I'm open to suggestions!
>
>Thanks,
>Kevin

Your problem is that you are apparently setting up a new connection for every procedure you call. You shouldn't do that. I'm sure even ASP can execute procedures in succession, if it can't you should throw it out of the window ASAP, or be prepared to be lynched by your DBA In short: the problem is not Oracle, the problem is ASP.

--
Sybrand Bakker, Senior Oracle DBA