SQL aging out of shared pool rapidly
KDeWeerdMar 24 2011 — edited Mar 24 2011On one of our databases we notice that most SQL are aging out of the shared pool rapidly. Nearly 95% of the sql in the sqlarea have a last_load_time within the last 30 minutes.
Nevertheless, according to v$sgastat, nearly 20% of the memory allocated to the shared pool is reported as free memory.
We are not having an application issue (no ORA-4031) but as for keeping an eye on performance using for example AWR or EM this poses a problem as most sqlstat-information is gone by the time we look into things, and with the default snapshots of the AWR being one hour apart, the information is not stored in the history-tables either.
We do not necessarily plan to increase the shared pool since we do not have any application issues, but I do wonder what the best approach would be to ensure we have proper execution information for most important SQLs. (The most obvious being taking an AWR snapshot every 15 minutes or so)
Also I wonder what a normal percentage of 'free memory' would be for the shared_pool and why in a system like this one with SQL aging out rapidly, so much of the memory remains 'unused'.
Also I am puzzled why some statements remain in the sqlarea, even though they have not been executed in days according to the last_active_time from sqlarea. Makes me think the sqlarea does not age out purely based on LRU.
Any thoughts?