Re: DBMS_SHARED_POOL / pinning for consistent performance

Hi Mark,

what bothers me is the lack of good information, e.g. measurement. There seemed to be your underlying assumption that the time spent for the long waits is spent parsing and loading the package. That is possible. But time could be also spent on I/O. Is there any good evidence that parsing your package is causing the long waits?
If you had diagnostic and tuning pack installed, you would find good information in active session history.
If not, a trace might help too.

regards

Lothar

Am 31.03.2020 um 16:17 schrieb McPeak, Matt (Consultant):
>
> We have a very large, packaged software application with tons of
> PL/SQL code.
>
> One of the PL/SQL APIs, a package which relies on other packages, and
> so on – many levels deep – is showing intermittent terrible
> performance.  Usually, the API returns a response in less than one
> second.  Occasionally, it takes over a minute.
>
> We think we have identified a pattern showing that the poor
> performance seems to follow a period of no activity.  That is, if no
> call is made to the API for a while, the next call is likely to be
> excessively slow.
>
> For this reason, we are wondering if caching might have something to
> do with it and whether DBMS_SHARED_POOL could help stabilize performance.
>
> My questions are as follows:
>
> 1. Would we want to pin the PL/SQL packages or the SQL cursors
> involved, or both?
>
> 2. Would it be reasonable to purge **everything** from the shared
> pool (in test!), run the API, and then query the shared pool as a
> good way to identify the objects and/or cursors to pin?
>
> 3. What are the dangers and downsides of pinning?
>
> 4. We have tons of RAM – is there some way or setting that we could
> just size this cache big enough to cache ALL PL/SQL in the
> application?  It’s enormous (Oracle E-Business Suite), but so is
> our machine.
>
> Thanks in advance for your attention to any or all of these questions.
>
> Regards,
>
> Matt
>

-- 





--
http://www.freelists.org/webpage/oracle-l