Re: Fast Growing Shared Pool
- From: Purav Chovatia <puravc@xxxxxxxxx>
- To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- Date: Tue, 10 Jul 2012 13:36:18 +0530
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) 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
Other related posts:
