Greg -- Thanks for the question regarding "PGA memory causing swapping?", version 9.2.0.6
Submitted on 6-Mar-2007 16:54 Central time zone
Last updated 27-May-2010 13:54
You Asked
Tom,
Last Thursday, I got a call from one of our systems administrators that users were having problems logging in. We were not under heavy load on the database, but we had a lot of jdbc connections (from oracle apps self service), we're running dedicated server. We hit our max processes at the kernel level (HP/UX), but there seemed to be a lot of swapping going on. We ended up having to reboot the server to increase the kernel parameters.
When I was investigating the problem, I noticed that we were using a lot more pga memory than I would have expected. Our pga aggregate target is 4g, pretty close to where statspack recommends. We have 16g of real memory and the sga is about 3g. We had 1300 sessions as of the last snap. This is a snippet from statspack immediately before the we reached our process max.
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
512 0.1 6,866,574.8 2,494,712.4 73.0 69,753
1,024 0.3 6,866,574.8 1,835,095.6 79.0 49,404
2,048 0.5 6,866,574.8 873,746.1 89.0 16,875
3,072 0.8 6,866,574.8 448,302.8 94.0 3,872
4,096 1.0 6,866,574.8 216,221.0 97.0 2,085
4,915 1.2 6,866,574.8 175,407.1 98.0 187
Here's a snapsot from 6am to 2pm, right before we started having problems.
session pga memory 5,747,373,736 199,554.7 39,892.4
session pga memory max 7,886,431,176 273,824.9 54,739.5
session uga memory 52,020,142,696 1,806,192.2 361,070.5
session uga memory max 24,756,850,168 859,583.0 171,836.7
compared to the day before, Wednesday 6am-5pm.
session pga memory 2,131,736,416 53,831.7 17,701.3
session pga memory max 2,997,231,992 75,687.7 24,888.2
session uga memory 61,902,483,504 1,563,194.0 514,020.7
session uga memory max 25,466,098,192 643,083.3 211,463.3
Here are the values from Sunday, should be almost entirely idle.
session pga memory 210,587,568 5,317.9 10,526.8
session pga memory max 271,785,792 6,863.3 13,585.9
session uga memory 60,166,462,184 1,519,355.1 3,007,571.2
session uga memory max 7,395,998,480 186,767.6 369,707.5
I've been trying to find out what the session pga memory max means, and why the uga memory is so high. Should I really care about those numbers, or just the session pga memory? I've looked through your book and askTom, but haven't really found anything that gives a good definition of the numbers and their importance.
We expect a high load on this coming Thursday, but not as high as last week, so I'll be watching more closely.
Thanks in advance, and I've really enjoyed your examples over the years,
Greg
and we said...
pga max is the max pga used by a session at a point in time. it does not make sense to "add it up" (query v$pgastat instead please)
UGA memory is session memory - in dedicated server, it is contained in the PGA (so the UGA is "inside" the PGA, the PGA number already covered the UGA memory). The UGA memory would/could be for things like plsql variables, session state and the like.
March 7, 2007 - 5pm Central time zone
Reviewer: Greg Perkins
Thanks Tom,
Followup March 7, 2007 - 7pm Central time zone:
they are probably not swapping out but slowing PAGING out.
swapping is really rare
swapping is very drastic
maybe you can make everyone feel better by setting a timeout in the middle tier?
March 8, 2007 - 5pm Central time zone
Reviewer: Greg Perkins
Yeah, we're running against the recommendations for Apache/Jserv due to user resistance. Apps ties the timeout of jdbc session to the professional apps (forms) timeout. The users have insisted on an 8 hour timeout, which is the root cause of the problem. As we expand our use of the self service module, we're using a lot more jdbc connections. There's supposed to be an override for the timeout that can be tied to the responsibility, but it doesn't seem to work. I have a tar open for that.
Followup March 8, 2007 - 8pm Central time zone:
paging is normal, expected, good.
UGA>PGA?
March 16, 2007 - 9am Central time zone
Reviewer: Vladimir Andreev from Germany
Tom, am I miscounting the commas?
session pga memory 5,747,373,736 199,554.7 39,892.4
session uga memory 52,020,142,696 1,806,192.2 361,070.5
session uga memory is almost ten times bigger than session pga memory ?
and all PGAs in physical memory - I could go on a while on reasons why anything else is risky, complete with a first-hand horor story, but let's save it for my blog when I start one :-)
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1067569
.6 test box behaves under memory stress.
Followup March 17, 2007 - 3pm Central time zone:
got query?
No query - copied from the original post
March 19, 2007 - 9am Central time zone
Reviewer: Flado from Germany
Sorry, Tom, I keep forgetting you read the follow-ups somewhat out-of-context.
grown by 48GB), which is bound to result in some super heavy paging. Then I looked at my own Statspack statistics and thought otherwise:
select * from (
select snap_id, round(sum(decode(name,'session pga memory', value))/power(1024,2),3) "session pga
memory",
round(sum(decode(name,'session uga memory', value))/power(1024,2),3) "session uga
memory"
from STATS$SYSSTAT
where name like 'session _ga memory'
and snap_id>=9800
group by snap_id)
where "session uga memory">"session pga memory"
order by snap_id;
SNAP_ID session pga memory session uga memory
---------------------- ---------------------- ----------------------
9800 5559,463 2985082,779
9801 5917,388 2989540,272
9802 6378,106 2994102,059
9803 6410,333 2998230,262
9804 6415,358 3002324,222
9805 6548,914 3006537,134
9806 6593,053 3010662,813
9807 6526,676 3014685,774
9808 6816,258 3019060,105
9809 6941,433 3023278,211
9810 7024,63 3027463,66
9811 7066,855 3031596,136
select sysdate,round(sum(decode(name,'session pga memory', value))/power(1024,2),3) "session pga
memory",
round(sum(decode(name,'session uga memory', value))/power(1024,2),3) "session uga
memory"
from V$SYSSTAT
where name like 'session _ga memory';
SYSDATE session pga memory session uga memory
------------------------- ---------------------- ----------------------
19-MAR-2007 14:48:19 7218,549 3035837,261
1 rows selected
August 30, 2008 - 12am Central time zone
Reviewer: Suraj Sharma from India
I have couple of questions regarding this:
1. Can PGA actually grows more than the value specified in PGA_AGGREGATE_TARGET. If it does what
is the significance of having this parameter??
2. Other than PGA & sga, what are the parameter which can cause swapting or memory utilization of
OS.
August 30, 2008 - 11am Central time zone
Reviewer: Suraj Sharma from India
Thanks a lot for the clearification Tom. Actually I am working on capacity planing from the Memory
point of view (how much memory would require if my users will increase by certain percentage), your
input will really help me in the same. So when I say how much memory my Oracle is consuming from OS
I should consider PGA and SGA?? Am I correct or is there any other metric also which i should
consider.
Thanks Followup August 31, 2008 - 9am Central time zone:
Your SGA won't change a considerable amount as the user count goes up typically (assuming dedicated server whereby the session memory is in the PGA not the large pool of the SGA). The buffer cache, shared pool, etc would all be more or less the same - the fixed overhead (allocated by setting processes, sessions, transactions etc - not a very large component) might go up, but not by an egregious amount.
The PGA would be the thing that would go up and it depends on how you architect your application.
So, when planning for memory needs, yes, you need to determine about how large your SGA will be (fixed amount) and how much PGA you think each session should be using - and then multiply by number of sessions.
September 1, 2008 - 8am Central time zone
Reviewer: Suraj Sharma from India
Thanks a lot for this Tom
Swap usage in 10g
September 2, 2008 - 9am Central time zone
Reviewer: Ben
Hello Tom,
I am trying to find the hourly swap utilization data by the following SQL:
select * from (
select i.instance_name, v.snap_id, to_char(s.END_INTERVAL_TIME, 'dd-Mon-yyyy hh:mi PM') "DT_TIME",
round(sum(decode(v.stat_name,'OS Swaps', value))) "Swap Util"
from dba_hist_SYSSTAT v, gv$instance i, dba_hist_snapshot s
where s.snap_id=v.snap_id and v.stat_name in ('OS Swaps')
and s.END_INTERVAL_TIME >= to_date('&1','dd-mon-yy hh24:mi:ss') and s.END_INTERVAL_TIME <=
to_date('&2','dd-mon-yy hh24:mi:ss')
and v.instance_number=i.instance_number
group by i.instance_name, v.snap_id, to_char(s.END_INTERVAL_TIME, 'dd-Mon-yyyy hh:mi PM'))
order by instance_name, snap_id;
But in the Output I am getting 0 all the time (I know the server has swap issue), not sure the
reason for the same.
Thanks, Followup September 2, 2008 - 12pm Central time zone:
if your server has a "swap" issue (in the year 2008 for swap to actually be used would be exceedingly rare), you wouldn't even be able to run these queries likely - the machine would appear almost frozen.
how do you KNOW you have a swap issue.
September 2, 2008 - 10pm Central time zone
Reviewer: Ben
Hello Tom,
I can see the swap utilization from OEM, its about 50%, but the same can not see from this script.
Is something wrong with the Script Followup September 3, 2008 - 10am Central time zone:
I think you mean swap in/out - which is normal process stuff and not SWAPPING in the old conventional sense.
do you have a serious performance issue - if you were truly swapping you would know it. The machine would be grinding to a halt.
September 3, 2008 - 11am Central time zone
Reviewer: Ben
Hi Tom,
I have a small question here. What is the difference between swap in/out and SWAPPING? Do you mean
page in/out? Followup September 3, 2008 - 12pm Central time zone:
yes, normal paging.
if you were swapping - well - you would know it. swapping of a process is a do or die thing. I think you are looking at normal paging activity.
September 4, 2008 - 12pm Central time zone
Reviewer: Suraj Sharma from India
Tom,
I am sorry if I am misunderstanding swap here. I have a database, which has too much of swapping:
myDB $ > free -t
total used free shared buffers cached
Mem: 32465476 31800660 664816 0 8888 605280
-/+ buffers/cache: 31186492 1278984
Swap: 12578768 12101172 477596
Total: 45044244 43901832 1142412
But I am able to login to the server. The DB doesn't have anything running than the database. Also,
when I looked the SGA and PGA utilization its 60% of the total memory (still 40% free), not sure
why its swapping still.
Thanks,
Followup September 4, 2008 - 1pm Central time zone:
you do not have too much swapping!!!!
you have swap allocated - that is normal, that is expected, that is in fact demanded.
That you have swap allocated doesn't mean you are swapping.
Processes allocate swap right off the bat. That is the way it works.
September 4, 2008 - 2pm Central time zone
Reviewer: Suraj Sharma from India
So you mean to say that in this case The swap USED (12101172) is NOT the matter of concern??
total used free shared buffers cached
Swap: 12578768 12101172 477596
Thanks,
Suraj Sharma Followup September 4, 2008 - 3pm Central time zone:
every process you start will allocate a backing store of swap, it does not mean they are swapping.
use vmstat or something to monitor what it really going on
reg . swap
May 27, 2010 - 1pm Central time zone
Reviewer: aliyar from INDIA
Dear Tom ,
$ vmstat 2 4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
6 0 340716 238396 4252 2593120 0 0 7 13 0 1 22 2 76 0
7 0 340716 227140 4260 2593112 0 0 0 10 2307 2797 70 1 29 0
6 0 340716 233604 4260 2593112 0 0 0 0 3025 3620 71 1 28 0
9 0 340716 267836 4268 2593172 0 0 0 56 2753 3480 64 1 35 0
$
from the above vmstat ouput
value for SI and SO is zero .. that means no swapping at all
if some non-zero value is displayed for SI and SO.. then swapping is happening in the server ...
please correct me if i am wrong ..
2 ) is there any way to find out swap usage at Database level ?
3) what are the steps we can do to control swap at database level.
Appreciate your service for DBA world..
Thanks
Aliyar Followup May 27, 2010 - 1pm Central time zone:
I'd ask the person that provides you with vmstat to tell you what vmstat is reporting on your system.
If, in the year 2010, you are actually "swapping", you'd know it - the machine would basically freeze.
don't over allocate memory, you won't swap. Allocate more memory than you have, you will swap.