RE: Statspack (shared pool) memory leak

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 21 Dec 2004 15:56:32 -0500
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410511E6EB@usahm236.amer.corp.eds.com>

I ran your script a dozen times or so (9.2.0.5 64 bit RAC on AIX 5.2) and sure enough 04031 errors starting poping up. Unfortunately I do not have any more time to look at this, but perhaps one of the board guru's will look. I was wondering if you have brought this issue to Oracle support attention?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Daniel Wittry Sent: Tuesday, December 21, 2004 2:43 PM To: oracle-l_at_freelists.org
Subject: 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
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 21 2004 - 14:54:14 CST