Re: Large Stored procedures

Murthy Jarugumilli <murthy_at_tcs.com> writes:

>Hi all
>Are there any issues to writing large stored procs other than the memory
>associated with loading it? I have a complex application where I can do most of
>the processing in a stored proc. This is a multi user system and there are lot
>of other applications running with the same database. If I make a single stored
>proc call from a Pro*C program and if this stored proc happens to run for a
>while, am I blocking any other processes in any way on the server? I was
>wondering, in whose context the stored procs actually get executed.
 

>Any thoughts will be greatly appreciated.
>Thanks
>Murthy.

Murthy,

I have been on some projects with several very large procedures. The only limiting factors are the size of your SHARED POOL (which is where the procedures are parsed + stored in memory), and the size of your SYSTEM tablespace (where the package/procedure code is physically stored).

Oracle uses a least-used algorithm to let SQL statements, procedures, etc. leave the shared pool. This occurs when there is no more room in the shared pool to process new SQL requests. The database does not halt, but may slow down if your shared pool is not large enough. The reason is that if your procedure has been removed from the shared pool, then Oracle will have to put it in the shared pool and/or re-compile it.

You can use SQL*DBA's monitor SQL-area to see current shared pool. This is a more limited view of the V$SQLAREA view. Look on page B-93/94 of the Administrator's Guide for a description of the table.

Bottom line: make sure that your shared_pool_size parameter is high enough and you will have no problems with large procedures.

-Ari Kaplan
akaplan_at_interaccess.com Received on Wed Jul 26 1995 - 00:00:00 CEST