Oracle Concepts - General Guidelines for Shared Pool Sizing

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

Putting it All inPerspective

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

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

* Guideline 2: Ifthe shared pool shows a mixed ad-hoc and reuse environment establish a sharedpool size large enough to hold the fixed size portions, pin reusable packagesand 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: Ifthe shared pool shows that no reusable SQL is being used establish a sharedpool large enough to hold the fixed size portions plus a few megabytes (usuallynot more than 40) and allow the shared pool modified least recently used (LRU)algorithm to manage the pool.

In guidelines 1, 2and 3, start at around 40 megabytes for a standard size system. Notice inguideline 2 it is stated that a routine flush cycle should be instituted. Thisflies in the face of what Oracle Support pushes in their shared pool whitepapers, however, they work from the assumption that proper SQL is beinggenerated and you want to reuse the SQL present in the shared pool. In a mixedenvironment where there is a mixture of reusable and non-reusable SQL thenon-reusable SQL will act as a drag against the other SQL (I call this sharedpool thrashing) unless it is periodically removed by flushing. Source 16 showsa PL/SQL package that can be used by the DBMS_JOB job queues to periodicallyflush 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: ExampleScript to Create a Shared Pool Flush Routine

The command set toperform a flush on a once every 30 minute cycle when the pool reaches 95% fullwould be:

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

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

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

Figure 7: GraphsShowing Effects of Flushing

The thing tonotice about the graphs in figure 7 is the overall trend of the performanceindicator between day 1 and day 2. On day 1 (the day with an initial flush asindicated by the steep plunge on the pool utilization graph followed by thebuildup to maximum and the flattening of the graph) the performance indicatorshows an upward trend. The performance indicator is a measure of how long thedatabase takes to do a specific set of tasks (from the Q Diagnostic tool fromSavant Corporation). Therefore an increase in the performance indicatorindicates a net decrease in performance. On day 2 the overall trend is downwardwith the average value less than the average value from day 1. Overall theflushing improved the performance as indicated by the performance indicator by10 to 20 percent. Depending on the environment I have seen improvements of upto 40-50 percent. At a recent job site the shared pool was sized at 210megabytes and was filled to 170 megabytes. The response time for an internallystored complex PL/SQL routine (the running_stats procedure) was 30 minutes. Iflushed the shared pool and response time dropped to a little over a minute.

One thing thatmade the analysis difficult was that on day 2 there were several large batchjobs run which weren?t run on day 1.  The results still show that flushinghas a positive effect on performance when the database is a mixed SQL environmentwith a large percentage of non-reusable SQL areas.

Guideline 3 alsobrings up an interesting point, you may already have over allocated the sharedpool, and in this case guideline 3 may result in you decreasing the size of theshared pool. In this situation the shared pool has become a cesspool filledwith nothing but garbage SQL. After allocating enough memory for dictionaryobjects and other fixed areas and ensuring that the standard packages and suchare pinned, you should only maintain a few megabytes above and beyond thislevel of memory for SQL statements. Since none of the code is being reused youwant to reduce the hash search overhead as much as possible, you do this byreducing the size of the available SQL area memory so as few a number ofstatements are kept as possible.