RE: Fast Growing Shared Pool

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 10 Jul 2012 18:57:45 -0400

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Purav Chovatia
Sent: Tuesday, July 10, 2012 4:06 AM
To: Niall Litchfield
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx
> 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@xxxxxxxxx> 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.
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>


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


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


Other related posts: