Re: How to obtain session-specific memory dumps?

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.

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?

Tanel

On Wed, Aug 19, 2009 at 9:50 PM, Rich Jesse <rjoralist_at_society.servebeer.com
> wrote:

> Hi Tanel,
>
> Yup, I've already tried that. The problem is that even though the cursors
> appear to be open (some for the duration of the session!), there are no
> values for the binds. All I get are "No bind buffers allocated" messages:
>
> Cursor 10 (110520870): CURBOUND curiob: 1105d8b48
> curflg: 46 curpar: 0 curusr: 0 curses 7000005f967ebb0
> cursor name: SELECT * FROM someschema.sometable WHERE ((somecol1 = :1 ))
> ORDER BY somecol2 ASC
> child pin: 0, child lock: 7000005e5ab8368, parent lock: 7000005e5c203c8
> xscflg: 80110476, parent handle: 7000005cdb76ef8, xscfl2: 5240009, xscfl3:
> 22008
> Dumping Literal Information
> Bind Pos: 0, Bind Len: 4, Bind Val:
> bhp size: 3704/4296
> bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
> size=24 offset=0
> No bind buffers allocated
>
> I haven't dug into this path further, but there's some mention in the list
> archives of a similar situation of "No bind buffers" being a bug in 10.2
> (I'm at 10.1.0.5.0).
>
> I've resigned myself to the reality that I'm going to have to mine the
> archived redos if I want to get the info I'm looking for. Not ideal and
> somewhat time consuming, but it's useful information to help in
> troubleshooting.
>
> Thanks!
> Rich
>
>
> > Read this:
> >
> >
> http://blog.tanelpoder.com/2009/07/09/identify-the-sql-statement-causing-those-wait-x-lines-in-a-top-truncated-sql-tracefile/
> >
> > In the end I have comments on both getting bind variable values with
> > errorstack level 3 dump and also some private memory areas associated
> with
> > the cursor (kxscwhp stands for cursor workheap for example etc)
> >
> > Tanel.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Tanel Poder
http://blog.tanelpoder.com

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