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 22.214.171.124.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