Re: Fast Growing Shared Pool

From: Purav Chovatia <puravc_at_gmail.com>
Date: Tue, 10 Jul 2012 13:35:14 +0530
Message-ID: <CADrzpjHZiPtdJUbQ-avhBzHYP4MVOgh=_+m2JPCjf39BACJ3eg_at_mail.gmail.com>

Thanks Martin.
There were 11662 entries of which 7704 were a call to the same SP. Checked with the App Dev Team and confirmed that it was being called from a perl script without binding variables. Sorry, my initial comment that there are no issues with bind variables was incorrect. But could that be causing this issue?

Also,
select sql_id,count(*) from v$sql_shared_cursor group by sql_id having count(*) > 10;

SQL_ID COUNT(*)
------------- ----------

0cn2wm9d7zq8d         12
10xj8nynmpqtq         14
14566d856s6hs         14
5an8d9ctcysja         17
akh9zqqkx3wj7         16
b5yyh2vc9g8tm         14
gvynt9bqh451z         14

7 rows selected.

And none of the above are application sqls. These are all Oracle internal sqls.
Actually the major workload of the application is contained in 4 SPs which are executed regularly. Each SP has a few insert update statements all with bind variables.

Thanks.

On Mon, Jul 9, 2012 at 8:36 PM, Martin Klier <usn_at_usn-it.de> wrote:

> Hi Purav,
>
> you may want to ensure that you don't have too many versions of the same
> SQL in the Library Cache. So-called version count issues are the most
> common issue when shared pool grows.
>
> A count of entries in v$sql and group by sql_id, is the most simple check.
> More insight allows v$sql_shared_cursor
>
> Best regards
> Martin
>
> Purav Chovatia schrieb:
> > 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
> >
> >
> >
>
> --
> Usn's IT Blog for Linux, Oracle, Asterisk
> http://www.usn-it.de
>
>

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

Received on Tue Jul 10 2012 - 03:05:14 CDT