DBMS_SHARED_POOL / pinning for consistent performance

From: McPeak, Matt (Consultant) <"McPeak,>
Date: Tue, 31 Mar 2020 14:17:35 +0000
Message-ID: <BN7PR04MB52663C11BEA0DBBA4F067147DDC80_at_BN7PR04MB5266.namprd04.prod.outlook.com>

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.


Received on Tue Mar 31 2020 - 16:17:35 CEST

Original text of this message