shared_pool_size and percentage utilized

  • From: Sami Seerangan <dba.orcl@xxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 10 Apr 2005 09:00:15 -0400

Hi :  

I did run the following program(from metalink) in 8i (2 node OPS) 
 during peak hours and it says shared_pool utilization is  165%. Do I 
 have to increase the shared_pool_size to improve the performance? 
 

DOC>* * 
 DOC>* TITLE : Shared Pool Estimation * 
 DOC>* CATEGORY : Information, Utility * 
 DOC>* SUBJECT AREA : Shared Pool * 
 DOC>* DESCRIPTION : Estimates shared pool utilization * 
 DOC>* based on current database usage. This should be * 
 DOC>* run during peak operation, after all stored * 
 DOC>* objects i.e. packages, views have been loaded. * 
 DOC>* * 
 DOC>* * 
 DOC>********************************************************/ 
  Obj mem: 420192059 bytes 
  Shared sql: 404843921 bytes 
 Cursors: 2170375 bytes 
  Free memory: 76526336 bytes (72.98MB) 
  Shared pool utilization (total): 992647626 bytes (946.66MB) 
  Shared pool allocation (actual): 600000000 bytes (572.2MB) 
 Percentage Utilized: 165% 
 

PL/SQL procedure successfully completed. 
-----------------------------------------------------------------------------------------------------------------------------------
The program I used to get the above output:

set serveroutput on; 
 declare 
 object_mem number; 
 shared_sql number; 
 cursor_mem number; 
 mts_mem number; 
 used_pool_size number; 
 free_mem number; 
  pool_size varchar2(512); -- same as V$PARAMETER.VALUE 
 begin 
 -- Stored objects (packages, views) 
 select sum(sharable_mem) into object_mem from v$db_object_cache; 
  -- Shared SQL -- need to have additional memory if dynamic SQL used 
  select sum(sharable_mem) into shared_sql from v$sqlarea; 
 -- User Cursor Usage -- run this during peak usage. 
 -- assumes 250 bytes per open cursor, for each concurrent user. 
  select sum(250*users_opening) into cursor_mem from v$sqlarea; 
 -- For a test system -- get usage for one user, multiply by # users 
 -- select (250 * value) bytes_per_user 
  -- from v$sesstat s, v$statname n 
 -- where s.statistic# = n.statistic# 
 -- and n.name = 'opened cursors current' 
  -- and s.sid = 25; -- where 25 is the sid of the process 
 -- MTS memory needed to hold session information for shared server 
 users 
 -- This query computes a total for all currently logged on users (run 
 -- during peak period). Alternatively calculate for a single user and 
 -- multiply by # users. 
  select sum(value) into mts_mem from v$sesstat s, v$statname n 
 where s.statistic#=n.statistic# 
  and n.name='session uga memory max'; 
  -- Free (unused) memory in the SGA: gives an indication of how much 
 memory 
 -- is being wasted out of the total allocated. 
  select sum(bytes) into free_mem from v$sgastat where name = 'free 
 memory'; 
  -- For non-MTS add up object, shared sql, cursors and 20% overhead. 
 used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem)); 
  -- For MTS mts contribution needs to be included (comment out previous 
 line) 
 -- used_pool_size := 
 round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem)); 
 select value into pool_size from v$parameter where 
 name='shared_pool_size'; 
 -- Display results 
  dbms_output.put_line ('Obj mem: '||to_char (object_mem) || ' bytes'); 
  dbms_output.put_line ('Shared sql: '||to_char (shared_sql) || ' 
 bytes'); 
 dbms_output.put_line ('Cursors: '||to_char (cursor_mem) || ' bytes'); 
 -- dbms_output.put_line ('MTS session: '||to_char (mts_mem) || ' 
 bytes'); 
 dbms_output.put_line ('Free memory: '||to_char (free_mem) || ' bytes ' 
 || '('|| to_char(round(free_mem/1024/1024,2)) || 'MB)'); 
 dbms_output.put_line ('Shared pool utilization (total): '|| 
 to_char(used_pool_size) || ' bytes ' || '(' || 
 to_char(round(used_pool_size/1024/1024,2)) || 'MB)'); 
 dbms_output.put_line ('Shared pool allocation (actual): '|| pool_size 
 ||' bytes ' || '(' || to_char(round(pool_size/1024/1024,2)) || 'MB)'); 
 dbms_output.put_line ('Percentage Utilized: 
 '||to_char(round(used_pool_size/pool_size*100)) || '%'); 
 end; 
 / 

 Thanks in advance 
  --Sami
--
//www.freelists.org/webpage/oracle-l

Other related posts: