Re: Performance problem with pinned procedure

"shmeld" <dan_hayton_at_hotmail.com> wrote in message news:Xns90605B4EF2E06shmeldnauticomnet_at_64.154.60.92...
> I am having a problem with performance when I call a stored procedure that
> is pinned in ORACLE memory. The first time I call, it may take minute to
> return, and after that, it takes a couple of seconds. If I don't access
> the procedure in 30-40 minutes, it takes 45-60 seconds again to return.
 My
> users aren't that patient.
>
> I have been told that the explain plan has to be recreated after a period
> of time, due to "least-recently-used" algorithm taking the procedure out
 of
> the "active" list.
>
> Is there a way to instruct ORACLE to not re-calculate anything, and assume
> the procedure is okay as is?
>
> Thanks,
> Dan Hayton
>

Looks like you are confusing things.
Once procedure has been pinned, it remains in memory, forever. It is not aged out.
However, if you procedure selects data, the data of course is aged out. If you call it at point a , and your next call is 45-60 minutes later, it is not strange your data has been flushed from the buffer cache and needs to be retrieved from disk.
Apart from tuning your statements and increasing the buffer cache, there is not much you can do about this behavior. The cache is for frequently accessed data.

Hth,

Sybrand Bakker, Oracle DBA Received on Sat Mar 10 2001 - 10:09:38 CST