Saturday, February 22, 2014

emergency monitoring and forcing a flush of the shared pool ... use with caution ONLY if really needed

Flushing the shared pool has an impact on systems forcing at least the re parsing of sql statements and new ( perhaps even changed ) execution plans.  Use with caution on any production environment and test/test before deploying anything like this.

The usual approach for a system suffering from shared pool fragmentation and/or 4031 is to identify sql not using bind variables and consider implementing ( after logon session based database trigger best ) CURSOR_SHARING = FORCE ... but at times even that may need to be supplemented with some emergency monitoring and flushing.

This code below needs to be looked at and tested in your environment ( test system first ) ... may need setup and grants put in place to get it operational.  It currently is hard coded to flush when less than 256 mb is available as free memory ... could be kicked off every minute by some kind of scheduler or cron job or database job.

CREATE OR REPLACE PROCEDURE FLUSH_SHARE_POOL is
   v_free_space_meg NUMBER;
BEGIN
   SELECT round(bytes/1024/1024,0) INTO v_free_space_meg FROM v$sgastat
    WHERE name = 'free memory' AND pool =  'shared pool';
   IF ( v_free_space_meg < 256 ) THEN
      EXECUTE IMMEDIATE 'alter system flush shared_pool';
   END IF;    
END FLUSH_SHARE_POOL;
/

2 comments:

  1. Also worth mentioning is that a single statement can be flushed out of the shared pool (since 10.2.0.4) so all statements don't have to reparse if it is only a few that are causing the issues. A good example can be found at: https://blogs.oracle.com/mandalika/entry/oracle_rdbms_flushing_a_single

    ReplyDelete