Re: Fast Growing Shared Pool

From: Purav Chovatia <puravc_at_gmail.com>
Date: Wed, 11 Jul 2012 19:33:57 +0530
Message-ID: <CADrzpjHes5KWhrywJ7Ck5E3wZuxUV3CCw8rGQ9gigVnkZn8UBA_at_mail.gmail.com>

Hi Mark,
Thanks for the explanation.
SP => Stored Procedure
We are at 10205.

It was already running in my mind that why is Oracle's ASMM stealing granules away from buffer cache? The buffer cache is indeed not enough and slow response time because of physical IO is causing performance issues (infact that is where all this troubleshooting started from). So thanks for sharing what you have observed.

This is what I see in v$sql:

SQL> select trunc(last_active_time), sum(sharable_mem), sum(persistent_mem), sum(runtime_mem), count(*)  from v$sql
 group by trunc(last_active_time)
 order by 1;

TRUNC(LAS SUM(SHARABLE_MEM) SUM(PERSISTENT_MEM) SUM(RUNTIME_MEM) COUNT(*)

--------- ----------------- ------------------- ---------------- ----------
01-JAN-70            196715               55096            50464          3
29-MAR-12             73111              137440           124840          9
17-JUN-12              7294               20488            16344          1
25-JUN-12             67819               15008            12416          2
26-JUN-12             12252               11712             8312          2
28-JUN-12            331219              104936            88672         13
29-JUN-12             39300               13424            12256          1
03-JUL-12              9438               20488            16344          1
04-JUL-12             26426               20896            17168          4
05-JUL-12            126531              323496           300344          8
06-JUL-12             22183                4840             3856          1
07-JUL-12            230410              387752           355168         13
08-JUL-12              6571                5176             4376          1
09-JUL-12            663555              618064           494376        104
10-JUL-12          12183643             3840792          3158488        632
11-JUL-12          61754532            15022424         12414168       2375
                      14001                4928                0          3

Believe the above returns in bytes and hence total of all 3 columns for all days is 108 MB. So I dont think it is correct to add and reconcile against the 1.7 GB of shared pool. Need to dig more and also understand the shared pool breakup.

I just wanted the root cause of the performance issue (*we have an app running in active-active mode and each instance of the app making 65 DB connections; when 1 of the instance is stopped, the other suffers from slow DB response times for almost a couple of minutes with high "buffer busy waits" and "enq: tx - index contention"; vmstat shows high sys cpu*) and in the process of getting there, observed this behaviour. Ofcourse I want the root cause of the shared pool behaviour too and will dig a bit more; atleast until I have found what all is causing the shared pool to grow. And if I find that, I dont think I'll raise a SR for this shared pool issue to ask Oracle to check why does it tilt in favour of moving memory to sharedpool.

I agree to your thought that once we have a good idea of the operating range, ASMM should be turned off. Its just that in all of our deployments, ASMM has always been ON and has never posed an issue yet. For this deployment, we might turn it off.

Many Thanks

On Wed, Jul 11, 2012 at 4:27 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

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

Received on Wed Jul 11 2012 - 09:03:57 CDT