Re: Monitoring shared pool in 11g

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 WHERE pool = 'shared pool' and name = 'sql area'). One had to take into account subpools - Tanel Poder came up with query against x$ksmss that allows to see this information for individual subpools. 9i has bugs that may cause size of 'miscellaneous' bucket to grow and 'sql area' to shink, once size of 'sql area' falls below certain threshold one gets ORA-04031.
>
>
>
> However in 11g (11.2.0.3 to be precise) shared pool components visible through V$SGASTAT have different names and there are more of them. Is there equivalent of 'sql area'? Or I am looking for something that no longer exists?

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 SQLA, 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 correctly identifies it.

David Fitzjarrell Received on Thu Oct 10 2013 - 17:49:37 CEST