X-Received: by 10.66.220.163 with SMTP id px3mr5317989pac.38.1381420178345; Thu, 10 Oct 2013 08:49:38 -0700 (PDT) X-Received: by 10.50.66.101 with SMTP id e5mr333871igt.12.1381420178255; Thu, 10 Oct 2013 08:49:38 -0700 (PDT) Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!209.85.160.88.MISMATCH!z6no50356100pbz.1!news-out.google.com!9ni24117qaf.0!nntp.google.com!a6no8626955qak.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Thu, 10 Oct 2013 08:49:37 -0700 (PDT) In-Reply-To: <69ce7ca2-e6bc-41e6-abb5-29d1135be030@googlegroups.com> Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=69.4.5.254; posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC NNTP-Posting-Host: 69.4.5.254 References: <69ce7ca2-e6bc-41e6-abb5-29d1135be030@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <99ac1363-9706-470e-bc8d-7b63e0c067ba@googlegroups.com> Subject: Re: Monitoring shared pool in 11g From: ddf Injection-Date: Thu, 10 Oct 2013 15:49:38 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: news.cambrium.nl On Thursday, October 10, 2013 12:31:21 AM UTC-6, vsevolod afanassiev wrote: > In 9i it was possible to predict ORA-04031 errors by monitoring size of '= sql area' in the shared pool using V$SGASTAT( SELECT bytes FROM V$SGASTAT W= HERE pool =3D 'shared pool' and name =3D 'sql area'). One had to take into = account subpools - Tanel Poder came up with query against x$ksmss that allo= ws to see this information for individual subpools. 9i has bugs that may ca= use size of 'miscellaneous' bucket to grow and 'sql area' to shink, once si= ze of 'sql area' falls below certain threshold one gets ORA-04031. >=20 >=20 >=20 > However in 11g (11.2.0.3 to be precise) shared pool components visible th= rough V$SGASTAT have different names and there are more of them. Is there e= quivalent of 'sql area'? Or I am looking for something that no longer exist= s? You mentioned the sgastatx.sql script and that is really what you should be= using to report the 'line items' in the subpools. I find an entry named S= QLA, which maps to the older 'sql area' entry. So, no, you're not looking = for something that no longer exists you just need to use a tool that correc= tly identifies it. David Fitzjarrell