-
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
-
stats pack report
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.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
-
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
;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|