Re: Fast Growing Shared Pool
From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 9 Jul 2012 16:50:08 +0100
Message-ID: <CABe10sZq2DSL+9ROmam+E=O12f99Dx6YKCXgor58d5kM83YYiw_at_mail.gmail.com>
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)
/
Date: Mon, 9 Jul 2012 16:50:08 +0100
Message-ID: <CABe10sZq2DSL+9ROmam+E=O12f99Dx6YKCXgor58d5kM83YYiw_at_mail.gmail.com>
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-lReceived on Mon Jul 09 2012 - 10:50:08 CDT