RE: Statspack (shared pool) memory leak

I just ran this script on a 10.1.0.2 database on Linux. Most loops lost = 41K
of memory, but about every 5th to 10th loop, a large chunk of memory was added. If I have a chance I'll look a bit more carefully.

Loop 55: (16:49:55) from 6,864,908 to 7,177,944 (loss of =
-313,036)

Loop 56: (16:49:55) from 7,177,944 to 7,136,184 (loss of = 41,760)

Loop 57: (16:49:55) from 7,136,184 to 7,094,412 (loss of = 41,772)

Loop 58: (16:49:55) from 7,094,412 to 7,052,652 (loss of = 41,760)

Loop 59: (16:49:55) from 7,052,652 to 7,010,892 (loss of = 41,760)

Loop 60: (16:49:55) from 7,010,892 to 6,969,132 (loss of = 41,760)

Loop 61: (16:49:55) from 6,969,132 to 6,927,372 (loss of = 41,760)

Loop 62: (16:49:55) from 6,927,372 to 7,235,508 (loss of =
-308,136)

Loop 63: (16:49:55) from 7,235,508 to 7,193,748 (loss of = 41,760)

Loop 64: (16:49:55) from 7,193,748 to 7,151,972 (loss of = 41,776)

Loop 65: (16:49:55) from 7,151,972 to 7,110,212 (loss of = 41,760)

Loop 66: (16:49:55) from 7,110,212 to 7,068,452 (loss of = 41,760)

Loop 67: (16:49:55) from 7,068,452 to 7,026,692 (loss of = 41,760)

Loop 68: (16:49:55) from 7,026,692 to 6,984,932 (loss of = 41,760)

Loop 69: (16:49:55) from 6,984,932 to 6,943,172 (loss of = 41,760)

Loop 70: (16:49:55) from 6,943,172 to 7,259,208 (loss of =
-316,036)

Loop 71: (16:49:55) from 7,259,208 to 7,217,448 (loss of = 41,760)

Loop 72: (16:49:55) from 7,217,448 to 7,175,688 (loss of = 41,760)

Loop 73: (16:49:55) from 7,175,688 to 7,133,928 (loss of = 41,760)

Loop 74: (16:49:55) from 7,133,928 to 7,092,168 (loss of = 41,760)

Loop 75: (16:49:55) from 7,092,168 to 7,050,408 (loss of = 41,760)

Loop 76: (16:49:55) from 7,050,408 to 7,008,632 (loss of = 41,776)

Loop 77: (16:49:55) from 7,008,632 to 6,966,872 (loss of = 41,760)

Loop 78: (16:49:55) from 6,966,872 to 6,925,112 (loss of = 41,760)

Loop 79: (16:49:55) from 6,925,112 to 7,238,044 (loss of =
-312,932)

Henry

-----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 4:43 PM To: oracle-l_at_freelists.org
Subject: RE: Statspack (shared pool) memory leak

Oracle claims to have fixed the leak in 9.2.0.4 and 10g (Bug #3519807). (Mark - you're running 9205 and you confirmed the leak) I reOpened my = iTAR
and will press them harder this time for a fix. Is anyone on OraDev = reading
this?

Interesting enough, we traced ADDM and it does not use the x$ksolsfts structure, yet creates a horizontal structure for segment statistics = that is
deallocated when ADDM exits. Maybe OraDev knows it leaks?

The long and short of it seems to be: when select * from v$segment_statistics is typed, 1400 bytes of shared pool goes poof! That smells bad. Granted, there are cases where we can run the query in a = test
loop and not leak memory, but it is unpredictable, even on the same = instance
(and usually does leak).

__that's our findings

-----Original Message-----

From: Powell, Mark D [mailto:mark.powell_at_eds.com]=3D20 Sent: Tuesday, December 21, 2004 12:57 PM To: Daniel Wittry; oracle-l_at_freelists.org Subject: RE: Statspack (shared pool) memory leak

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?

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Dec 21 2004 - 15:55:26 CST