RE: Fast Growing Shared Pool

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 10 Jul 2012 18:57:45 -0400
Message-ID: <02ec01cd5eef$6bfa4190$43eec4b0$_at_rsiz.com>



Is an SP a stored procedure? (That's go nothing to do with your question, I just want to be sure about the shorthand you've used.)

Now, about your problem:

Indeed the tiny amount of memory from this one "SP" cannot be the root cause of your 900MB problem.

select count(*) from v$sql;
select count(*) from v$sql where last_active_time < sysdate - &1; and answer 0, then 1, etc. until you get an idea of how much infrequently used parsed statements are laying around.

Automatic memory management, given a choice of tossing stuff out or stealing from the buffer cache to expand the shared pool often (always?) chooses the latter. I am not privy to Oracle's intentions on this matter, but I have seen this behavior many times.

That is just one of several reasons I say after you've let automatic memory management give you a good idea of a reasonable operating range, turn it off. If you have certain workshifts where a small number of sqls do a ton of fresh i/o batch processes, it would be nice if Oracle tossed a bunch of infrequently used sqls and increased the buffer cache, but I've never actually seen it do that by more than a few granules. Over time every case I have been asked to look at seems to tilt toward more shared pool even though it often seems to be retaining long inactive sql.

Your mileage may vary. This is decidedly a case of measuring actuals. I see very little downside in turning automatic memory management off in your case if there are in fact long idle sqls it can toss out instead of failing.

If it takes 3 months to grow from 700MB to 1600MB, that is only 300MB per month. It is possible that is all new stuff that needs to stay available (in which case you need more sga if you want to preserve buffer cache), but I doubt it.

Of course this does become a question of whether you just want your problem to go away or whether you want an analysis of the root cause. I *suspect* the root cause (suspect means I don't really know, this is just a theory that matches the facts I've seen so far and there certainly could be a different explanation) is that Oracle is biased against taking a risk of tossing sql out and is aggressive in pushing granules into the shared pool to avoid out of memory errors.

Unfortunately, when it takes 3 months to accrue a substantial problem, it becomes a serious problem to create a reproducible test case for support. Good luck on that if you're really after the root cause. The behavior seems to persist through 11.2, so while it may be different on your release, I do not believe this has been seriously addressed.

Maybe they'd accept the ordered count of inactive sql as evidence something is wrong. Again, good luck. You seem to *want* 700MB of shared pool and 3300MB of buffer cache. If you wire that down and you get consistently good performance without sql's erroring out of memory in parsing and you're willing to not know the exact root cause, then you can mark this done.

Now if they had a "flush the shared pool sqls that have not been used in a week" Bob would be yer uncle. Flushing the shared pool is not a friendly thing to do operationally and is pretty much deprecated unless it is the only way you can keep the ship afloat.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Purav Chovatia
Sent: Tuesday, July 10, 2012 4:06 AM
To: Niall Litchfield
Cc: oracle-l_at_freelists.org
Subject: Re: Fast Growing Shared Pool

Thanks Niall.
Found an SP which is called from a perl script at regular intervals without binding variables.

But pls see below:

SQL> select sum(sharable_mem), sum(persistent_mem), sum(runtime_mem) SQL> from
v$sql

            where substr(sql_text,1,27)='<The Culprit SP>';

SUM(SHARABLE_MEM) SUM(PERSISTENT_MEM) SUM(RUNTIME_MEM)

----------------- ------------------- ----------------
         59039689             9121536          2773440

SQL> That does not seem to be much. Could it have resulted in shared pool growing so much? We have many deployments of this product and have never seen the shared pool going beyond 500m.

Thanks.

On Mon, Jul 9, 2012 at 9:20 PM, Niall Litchfield <niall.litchfield_at_gmail.com
> wrote:

> How have you determined that you don't have bind variable issues? Have
> you also checked for the oldest culprit of all, namely SQL with
> literals scattered through it. I typically look for this with
> something like
>
> select substr(name,1,60), sum(sharable_mem), count(*) from
> v$db_object_cache group by substr(name,1,60) having count(*)>50 order
> by sum(sharable_mem) /
>
>
>
> On Mon, Jul 9, 2012 at 3:38 PM, Purav Chovatia <puravc_at_gmail.com> wrote:
>
>> Hi,
>> We have a 10205 database on Solaris SPARC with ASMM enabled and
>> sga_target & sga_max_size = 4G. What we observe is that shared_pool
>> has grown from 700MB to 1.6GB in last 3 months whereas buffer cache
>> has shrunk from 3.3G to 2.5G. This inspite of the database having to
>> do physical reads i.e some of the hot objects not fitting in the buffer
cache.
>> What could be the reason?
>> How do I find what is the breakup of the so big shared pool? (There
>> are no bind variable issues)
>>
>> Thanks.
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

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


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 10 2012 - 17:57:45 CDT

Original text of this message