Re: How to obtain session-specific memory dumps?

  • From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
  • To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 19 Aug 2009 10:38:46 -0500 (CDT)

Hi Tanel,

> What exactly are you trying to solve or find out?

I need to find out what user(s) are occasionally pulling a large amount of
data from the DB via a web-based adhoc tool that's causing the web engine to
heap dump.

> Do you need to find out what was the statement causing the large memory
> usage? *Cursor frame dump* (not the bind variable dump) should show how much
> private memory cursors are using.

Very long story short:  Web app.  Websphere.  Generic DB login.  Ad-hoc
query tool.  One or more users occasionally uses the ad-hoc query to pull
more data then Websphere can handle causing the associated Java engine in
Websphere to heap dump and taking down every other user connected to that
engine.  Apparently, there's no info in the dump to see which user/thread
caused it.  Since the DB login is generic, I'm looking for a way to tie back
the dump to the user.  At login, the user process in the DB queries an
authentication table using their application username in a bind variable. 
That cursor is (stupidly?) kept open the entire session.  I was hoping to
grab the value of the bind.

> Or you could just write a script which monitors v$sesstat or
> v$process_memory and captures info about any session who's using more than
> XX bytes of private memory?
> *
> *Another question is - how come a *private* memory heapdump can take all
> sessions down? Or is that application foolishly doing also a *shared
> pool*heapdump when there's private memory issue?

Sorry for the misunderstanding!  It's Websphere that's the problem and for
various long-winded reasons, it doesn't appear that we can correct it there.
 So I'm trying to find *any* workaround.  Tracing back from the DB is the
first avenue I'm testing, since I don't know (and don't think I want to
know!) much about Websphere.

So, my theory is to identify the handful of suspect users at the time of the
heap dump and use Log Miner to find any bit of info I can about possible DML
with the authentication table to hopefully identify them.  It's still a
reach, but I'm running out of options!  :)



Other related posts: