Statspack (shared pool) memory leak

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

Other related posts: