Real-Time SQL Monitoring - Retention (part 2)

As I mentioned in my last post, I've been looking at increasing the SQL Monitoring Retention at my current site using _sqlmon_max_plan 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_plan=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.

Updated later - thanks to Nick Affleck for pointing out the additional 's' I introduced on the parameter name. Fixed now to read _sqlmon_max_plan