RE: How to obtain session-specific memory dumps?

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Wed, 19 Aug 2009 07:50:35 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E360686C737_at_CWYMSX04.Corp.Acxiom.net>

If it helps any, I found in my notes how to dump various memory sections you referenced:

• To dump PGA, SGA, and/or UGA information. ALTER SESSION SET EVENTS 'immediate TRACE NAME global_area LEVEL <level>';

level 1: pga

2: sga
3: pga+sga
4: uga
5: pga+uga
6: sga+uga
7: pga+sga+uga

ALTER SESSION SET EVENTS 'immediate TRACE NAME heapdump LEVEL <level>';

level 1: PGA

2: SGA
4: UGA
8: CGA - current all

16: CGA - user call
32: large pool

I'm sure I got this from Tanel or some other Oracle guru.

David C. Herring | DBA, Acxiom Database Services  

630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax 1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse Sent: Tuesday, August 18, 2009 11:18 AM
To: Oracle L
Subject: Re: How to obtain session-specific memory dumps?

Hi Niall,

> If you can enable trace then they'll be in the 10046. As for dumping
> the uga I *think* there's an event for that, I'll look it up when I
> get back. If not I expect Tanel Poder has something on his site.

The issue at hand is that the identifying SQL would have been run potentially hours in the past, so a 10046 trace wouldn't help.

After an off-list conversation and experimentation, I'm going to be relegated to using Log Miner. I believe that the data I'm after is just too
transient to reliably capture in the UGA, PGA, SGA, or any other TLA.

Thanks much!
Rich

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


***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
****************************************************************************

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

Received on Wed Aug 19 2009 - 07:50:35 CDT