Re: Fast Growing Shared Pool

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@xxxxxxxxx> 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

Other related posts: