Monitoring shared pool in 11g
From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Wed, 9 Oct 2013 23:31:21 -0700 (PDT)
Message-ID: <69ce7ca2-e6bc-41e6-abb5-29d1135be030_at_googlegroups.com>
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.
Date: Wed, 9 Oct 2013 23:31:21 -0700 (PDT)
Message-ID: <69ce7ca2-e6bc-41e6-abb5-29d1135be030_at_googlegroups.com>
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? Received on Thu Oct 10 2013 - 08:31:21 CEST