How to handle shared pool

From OraWiki

A large shared pool can decrease performance by times. To avoid shared pool problems you have to avoid debug mode of PL/SQL code in production environments. Setting debug mode can increase shared pool utilization and decrease performance. Shared pool sizes above 250 meg are a reason to investigate.

OraForecast.com offers fsora which offers a lot of functions for shared pool analysis and a module to install a shared pool related database startup trigger.

As a workaround it makes sense to flush shared pool periodically.

If you implement this procedure you should always remember that a flush shared pool resets consistent_gets in V$BUFFER_POOL_STATISTICS. Some of the fsora scripts (BUFFER_POOL_STATISTICS / TPS) will produce in such cases false results.

I've implemented a procedure and an Oracle Job to flush shared pool periodically. This is only a workaround if your shared pool growth rapidly.

Install both in schema sys:

CREATE OR REPLACE PROCEDURE SYS.flush_shared_pool IS
a varchar2(200);

/******************************************************************************
   NAME:       flush_shared_pool
   PURPOSE:    well
   Author:     Gerald.Roehrbein@OraForecast.com
   (c)2008 OraForecast.com Licensed under GPL 2.0

   Called by an Oracle job to flush shared pool periodically

******************************************************************************/
BEGIN
   a := 'alter system flush shared_pool';
   execute immediate  a;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       NULL;
END flush_shared_pool;
/

The next code is an job call the procedure above periodically every 90 minutes. In RAC environments you should create for each instance one dedicated job using parameter instance.

set serveroutput on size 1000000

DECLARE
/******************************************************************************
   NAME:       job call flush_shared_pool periodically
   PURPOSE:    well
   Author:     Gerald.Roehrbein@OraForecast.com
   (c)2008 OraForecast.com Licensed under GPL 2.0

   A job which calls flush_shared_pool every 90 minutes.
   This is a workaorund to avoid to large shared pools.
******************************************************************************/

  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT ( job=> X ,what=> 'declare begin  sys.flush_shared_pool(); end;'
     ,next_date => to_date(sysdate+90/1440,'dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'SYSDATE+90/1440 '
     ,no_parse  => TRUE
     ,instance  => 1
    );

  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
  sys.dbms_job.run(x,true)

END;
/

commit;

Hint for PL/SQL developers:

If you implement a PL/SQL package than add a procedure called "dummy" to your packages.
This can be used to load packages initially during execution of a database startup trigger into the shared pool and will also allow to PIN your package(s) in the shared pool using:


exec dbms_shared_pool.keep('USERNAME.PACKAGE');


Use fsora function shared_pool_install_pin_handler available at this site as a template for your own database startup triggers.

To flush a shared pool is a workaround. The reason are in most cases bugs in the application. Most often usage of SQL which contains literals.

For example:

select * from address where customer_id=123456;

instead of

var=123456;
select * from address where customer_id=:var;

Oracle can support this false programming using database parameter "CURSOR_SHARING". If you set this to "SIMILAR" than Oracle will replace the literal with a system generated bind variable. Sounds good? Well for some queries which have a runtime of a few microseconds this is in relation expensive and there are some bugs related to "CURSOR_SHARING=SIMILAR". You may hit:

ORA-01802

If it is only one Query hitting 1802 than it is possible to change CURSOR_SHARING for the session which hits the bug to EXACT.

Well cheap programmers can be really cheap and not worth the money your Company pay for them because you may save costs with wages of software developers but you will throw all of the hard earned money out of the window with oversized hardware and slow performance.

See also CURSOR_SHARING=SIMILAR and Waits on buffer exterminate (Automated Memory Management Issues).