Oracle Concepts - General Guidelines for Shared Pool Sizing Oracle Tips by Burleson Consulting

Putting it All in Perspective

So what have we seen so far? We have examined reports that show both gross and detailed shared pool usage and whether or not shared areas are being reused. What can we do with this data? Ideally we will use the results to size our shared pool properly. Let's set out a few general guidelines for shared pool sizing:

* Guideline 1: If gross usage of the shared pool in a non-ad-hoc environment exceeds 95% (rises to 95% or greater and stays there) establish a shared pool size large enough to hold the fixed size portions, pin reusable packages and procedures. Increase shared pool by 20% increments until usage drops below 90% on the average.

* Guideline 2: If the shared pool shows a mixed ad-hoc and reuse environment establish a shared pool size large enough to hold the fixed size portions, pin reusable packages and establish a comfort level above this required level of pool fill.  Establish a routine flush cycle to filter non-reusable code from the pool.

* Guideline 3: If the shared pool shows that no reusable SQL is being used establish a shared pool large enough to hold the fixed size portions plus a few megabytes (usually not more than 40) and allow the shared pool modified least recently used (LRU) algorithm to manage the pool.

In guidelines 1, 2 and 3, start at around 40 megabytes for a standard size system. Notice in guideline 2 it is stated that a routine flush cycle should be instituted. This flies in the face of what Oracle Support pushes in their shared pool white papers, however, they work from the assumption that proper SQL is being generated and you want to reuse the SQL present in the shared pool. In a mixed environment where there is a mixture of reusable and non-reusable SQL the non-reusable SQL will act as a drag against the other SQL (I call this shared pool thrashing) unless it is periodically removed by flushing. Source 16 shows a PL/SQL package that can be used by the DBMS_JOB job queues to periodically flush the shared pool only when it exceeds a specified percent full.

PROCEDURE flush_it(p_free IN NUMBER) IS
--
CURSOR get_share IS
 SELECT
  SUM(a.bytes)
 FROM
  v$sgastat a
 WHERE
  a.pool = 'shared pool' AND
  a.name <> 'free memory';
--
CURSOR get_var IS
 SELECT
  value
 FROM
  v$parameter
 WHERE
   name = 'shared_pool_size';
--
CURSOR get_time IS
 SELECT
  sysdate
 FROM
  dual;
--
  todays_date     DATE;
  mem_ratio       NUMBER;
  share_mem       NUMBER;
  variable_mem NUMBER;
  cur             INTEGER;
  sql_com   VARCHAR2(60);
  row_proc NUMBER;
--
BEGIN
 OPEN get_share;
 OPEN get_var;
 FETCH get_share INTO share_mem;
 DBMS_OUTPUT.PUT_LINE('share_mem: '||TO_CHAR(share_mem));
 FETCH get_var INTO variable_mem;
 DBMS_OUTPUT.PUT_LINE('variable_mem: '||TO_CHAR(variable_mem));
 mem_ratio:=share_mem/variable_mem;
 DBMS_OUTPUT.PUT_LINE(TO_CHAR(mem_ratio,'99.999')||
 ' '||TO_CHAR(p_free/100,'99.999'));
 IF mem_ratio>p_free/100 THEN
  cur:=DBMS_SQL.OPEN_CURSOR;
  sql_com:='ALTER SYSTEM FLUSH SHARED_POOL';
  DBMS_SQL.PARSE(cur,sql_com,dbms_sql.v7);
  row_proc:=DBMS_SQL.EXECUTE(cur);
  DBMS_SQL.CLOSE_CURSOR(cur);
  OPEN get_time;
  FETCH get_time INTO todays_date;
  INSERT INTO dba_running_stats VALUES (
   'Flush of Shared Pool',mem_ratio,35,todays_date,0);
  COMMIT;
 END IF;
END flush_it;

Source 16: Example Script to Create a Shared Pool Flush Routine

The command set to perform a flush on a once every 30 minute cycle when the pool reaches 95% full would be:

VARIABLE x NUMBER;
BEGIN
 dbms_job.submit(
  :X,'BEGIN flush_it(95); END;',SYSDATE,'SYSDATE+(30/1440)?);
END;
/
COMMIT;

(Always commit after assigning a job or the job will not be run and queued)

There is always a discussion as to whether this really does help performance so I set up a test on a production instance where on day 1 I did no automated flushing and on day 2 I instituted the automated flushing. Figure 7 shows the graphs of performance indicators and users.

Figure 7: Graphs Showing Effects of Flushing

The thing to notice about the graphs in figure 7 is the overall trend of the performance indicator between day 1 and day 2. On day 1 (the day with an initial flush as indicated by the steep plunge on the pool utilization graph followed by the buildup to maximum and the flattening of the graph) the performance indicator shows an upward trend. The performance indicator is a measure of how long the database takes to do a specific set of tasks (from the Q Diagnostic tool from Savant Corporation). Therefore an increase in the performance indicator indicates a net decrease in performance. On day 2 the overall trend is downward with the average value less than the average value from day 1. Overall the flushing improved the performance as indicated by the performance indicator by 10 to 20 percent. Depending on the environment I have seen improvements of up to 40-50 percent. At a recent job site the shared pool was sized at 210 megabytes and was filled to 170 megabytes. The response time for an internally stored complex PL/SQL routine (the running_stats procedure) was 30 minutes. I flushed the shared pool and response time dropped to a little over a minute.

One thing that made the analysis difficult was that on day 2 there were several large batch jobs run which weren?t run on day 1.  The results still show that flushing has a positive effect on performance when the database is a mixed SQL environment with a large percentage of non-reusable SQL areas.

Guideline 3 also brings up an interesting point, you may already have over allocated the shared pool, and in this case guideline 3 may result in you decreasing the size of the shared pool. In this situation the shared pool has become a cesspool filled with nothing but garbage SQL. After allocating enough memory for dictionary objects and other fixed areas and ensuring that the standard packages and such are pinned, you should only maintain a few megabytes above and beyond this level of memory for SQL statements. Since none of the code is being reused you want to reduce the hash search overhead as much as possible, you do this by reducing the size of the available SQL area memory so as few a number of statements are kept as possible.

This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.