Oracle logo

Real-Time SQL Monitoring

September 23rd, 2011 by igogo

As I mentioned in my last post, I’ve been looking at increasing the SQL Monitoring Retention at my current site using _sqlmon_max_plans but, as well as confirming with Oracle Support that they’re happy for us to do so, it would be nice to know what the resulting memory footprint would be to help us come up with a sensible value. Here is how :-

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL select * from v$sgastat where name like '%keswx%' ;

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  keswx:plan en                  645696
shared pool  keswxNotify:tabPlans            16384
shared pool  keswx:batch o                 3646864

Those are the values on a system with _sqlmon_max_plans=320.

Thanks to those who helped out with this – they know who they are.

Coming up with an appropriate value is going to involve considering each system’s workload, though, because it’s not a time-based retention parameter. If people are interested in statements that ran in the last 12 hours, then the value would be different on each system. But at least now we’ll be able to see the impact, which looks pretty reasonable to me.

Article source: http://www.oaktable.net/content/real-time-sql-monitoring-retention-part-2

Leave a Reply