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.
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:
- Would we want to pin the PL/SQL packages or the SQL cursors involved, or both?
- 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?
- What are the dangers and downsides of pinning?
- 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-lReceived on Tue Mar 31 2020 - 16:17:35 CEST