Statspack (shared pool) memory leak

From: Daniel Wittry <daniel.wittry_at_quest.com>
Date: Tue, 21 Dec 2004 11:42:32 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F6026210A19A727@irvmbxw02>

Has anyone else been collecting statspack snapshots on a regular interval and been experiencing shared pool memory leaks? Querying segment statistics (v$segment_statistics, v$segstat, or x$ksolsfts) leaks about 1400 bytes per query. After a short while, the dreaded ORA-4031 fragmented shared pool error occurs.

V9.2.0.1 thru 10.1.0.3 all leak (tested on Sun, Linux, Win, and OraMVS). The script below is one of many attempts that have resulted in a fragmented shared pool. I have tried with and without a large pool, execute_immediate, using rownum, and not using rownum, a pl/sql loop and not using pl/sql, just plain sql with a series of slashes /, /, /, /, (to repeat the last sql command). Everything leaks.

CAUTION: do not run this on an instance unless you can bounce it when done.

set heading off
column what format a40
column value format a30
spool expose_leak_2.log

select 'db instance' what, user || '@' || global_name value from global_name
UNION
select '# rows in v$segstat', to_char(count(*)) from v$segstat ;

set linesize 200
set time on
set serveroutput on size 300000

declare

  l_temp        char(1);
  l_before      number;
  l_after       number :=3D 0;
  l_loop_times  pls_integer :=3D 100;   -- try 1000
  l_sleep       number      :=3D 0.00;  -- makes no difference

=20
cursor c_seg is select * from v$segstat; r_seg c_seg%ROWTYPE;

function get_mem return number is

     cursor c_mem is select bytes from v$sgastat
        where name =3D 'free memory' and pool =3D 'shared pool';
     r_mem   c_mem%ROWTYPE;
     begin
       open c_mem; fetch c_mem into r_mem; close c_mem;
       return r_mem.bytes;
     end get_mem;

begin
l_after :=3D get_mem(); -- initialize

for x in 1..l_loop_times loop
l_before :=3D l_after;
--execute immediate ('select ''x'' from v$segstat s1 where rownum =
=3D
1')

into l_temp; OPEN c_seg; FETCH c_seg INTO r_seg; CLOSE c_seg;
--
l_after :=3D get_mem(); dbms_output.put_line ('Loop ' || x || ': (' || to_char(sysdate,'hh24:mi:ss') || ') from ' || to_char(l_before,'999,999,999') || ' to ' || to_char(l_after,'999,999,999') || ' (loss of ' || to_char((l_before-l_after),'9,999,999') || ')'); dbms_lock.sleep(l_sleep); end loop; end; /

spool off

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 21 2004 - 13:44:59 CST