How to find the most pga consumer sql statement?

Hi folks,

We suffered this early morning from significant virtual memory paging on our main production server during the ETL processes .
Eventually, we were forced to reboot the server.
I need to investigate who were the most pga memory consumers sessions before the server's crashing and how many bytes the session used from the pga?
Is there any historical session table which store this information?

DB version : 10.2.0.4
OS : Sun Solaris 10 64 bit

Thanks in advance,
Nir

You could generate statspack report for the snapshots from that period of time.

If you are using EM dbconsole or Grid Control, you can generate awr report.
;)

Hi LKBrwn_DBA,

Thanks.
Unfortunately,statspack or even AWR report does not contain this information.
Any more ideas?

Thanks,
Nir

Are you certain it was an Oracle related process the one that went spinning?
Is your ETL tool running on the same server?
I would start by asking systems to identify offending process - that would at least tell who owns it.

Hi Pablo,

Yes, I know it's an oracle process and I have a major suspect :)
We set to a specific etl session a huge manual pga , meaning , We set in session level:
workarea_size_policy=manual
sore_area_size=1Gb
hash_area_size=1Gb
After we canceled those manual setting and changed back the parameter workarea_size_policy=auto , the ssupected etl process run well.
Therefore , I wanted to check if Oracle keep pga consuming statistics per session?

Regards,
Nir

Quote:

---

Originally Posted by nir_s

I wanted to check if Oracle keep pga consuming statistics per session?

---

Yes... at least as long as the session is in the system - check v$sesstat

Feel free to customize query below to show the data you are looking for as well as the particular session/s you are interested in. Found this query some time ago, find it usefull.

Code:

---

set pages 500 lines 100 trims on
clear col
col name    format a30
col username format a40
break on username nodup skip 1

select  vses.username||':'||vsst.sid||','||vses.serial# username,
        vstt.name,
        max(vsst.value) value
from    v$sesstat  vsst,
        v$statname vstt,
        v$session  vses
where    vstt.statistic# = vsst.statistic#
    and vsst.sid = vses.sid
    and vstt.name in ('session pga memory','session pga memory max','session uga memory',
                      'session uga memory max','session cursor cache count',
                      'session cursor cache hits','session stored procedure space',
                      'opened cursors current','opened cursors cumulative')
    and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name
order by vses.username, vsst.sid, vses.serial#, vstt.name
;

---

Hi Pablo,

Thanks a lot!!
It looks very nice :)
This query is good to catch current sessions.
What about historical sessions?
For example, from AWR report I have only sql_id.
How can I find the information from your query by sql_id instead of sid number?

Thanks in advance,
Nir