Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Greg.

Asked: March 06, 2007 - 4:54 pm UTC

Last updated: May 27, 2010 - 1:54 pm UTC

Version: 9.2.0.6

Viewed 10K+ times! This question is

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 Tom 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.

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Greg Perkins, March 07, 2007 - 5:07 pm UTC

Thanks Tom,

Looking more closely at the statspack reports, the session pga memory and max are getting summed for the snapshot range, that doesn't give me a useful number, a single snapshot gives me better information.

I guess the real question is; If I have a large number of inactive jdbc sessions, and they get swapped out because the db server is running low on memory, does it matter? Our unix sa's hate swapping, but that's the whole purpose of swapping, age out what's not being used.

Obviously, I don't want the machine to thrash, but we have a hundreds of jdbc sessions that have last_call_et that exceed an hour, let them swap. The sa's want to know if we should be adding memory to the database server. I'm still waiting for a detailed os level report on the incident before I give them an answer.

I wish we were on 10g, then I'd have more os stats being captured by statspack.

Thanks again,

Greg
Tom Kyte
March 07, 2007 - 7:31 pm UTC

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?

Greg Perkins, March 08, 2007 - 5:11 pm UTC

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.

I guess I really meant paged out. Am I correct in assuming that paging out an idle session isn't a big deal?

Thanks,

Greg
Tom Kyte
March 08, 2007 - 8:41 pm UTC

paging is normal, expected, good.

UGA>PGA?

Vladimir Andreev, March 16, 2007 - 9:30 am UTC

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


Since in dedicated server UGA is part of PGA, how come session uga memory is almost ten times bigger than session pga memory?
Must be something wrong with the UGA number - that's ~48G we're looking at here, with a total of 16G physical memory...

Greg, paging may be normal and expected, but paging out critical parts of the SGA is never good. And to the Virtual Memory Manager, pages from the SGA look not much different than those from a PGA, so both are fair game. That's why I try to keep the SGA 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 :-)

The LOCK_SGA instance parameter can help you here by telling the VMM that SGA is off-limits for paging out. See MetaLink Note:1067569.6 for prerequisites ( https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1067569.6
)
and test to destruction. It's relatively easy to write a PL/SQL procedure of the sort
burn_pga(p_MB_to_allocate in number)
that would help you simulate high pga allocation and see how the test box behaves under memory stress.

Cheers,
Flado
Tom Kyte
March 17, 2007 - 3:53 pm UTC

got query?

No query - copied from the original post

Flado, March 19, 2007 - 9:56 am UTC

Sorry, Tom, I keep forgetting you read the follow-ups somewhat out-of-context.
The two statistic values I quoted were a straight copy-and-paste from the original post (well I did move a space character a bit to fix the column alignment - couldn't resist it).
Since Greg, the OP, says "we're running dedicated server", and his UGA seems very much bigger than his PGA, I got curious how can that be.
Now I noticed that these are Statspack report values, that is, diffed over 8 hours, so they mean nothing more than that he's allocating more than three times his total physical RAM (since his UGA has 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


and
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


So it must be a bug of sorts - not a problem for your site. I think I'll just ignore the 'session uga memory' statistic in the future.

Cheers,
Flado

Suraj Sharma, August 30, 2008 - 12:26 am UTC

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.


Tom Kyte
August 30, 2008 - 10:02 am UTC

1) yes

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:404110378143#70174025425719

2) with regards to Oracle, we use process (pga) memory and shared (sga) memory. Anything else would be related to other processes running on that machine (the operating system, anything else you have up and running)

Suraj Sharma, August 30, 2008 - 11:02 am UTC

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
Tom Kyte
August 31, 2008 - 9:21 am UTC

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.

Suraj Sharma, September 01, 2008 - 8:39 am UTC

Thanks a lot for this Tom

Swap usage in 10g

Ben, September 02, 2008 - 9:36 am UTC

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,
Tom Kyte
September 02, 2008 - 12:01 pm UTC

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.

Ben, September 02, 2008 - 10:55 pm UTC

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
Tom Kyte
September 03, 2008 - 10:36 am UTC

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.

Ben, September 03, 2008 - 11:24 am UTC

Hi Tom,

I have a small question here. What is the difference between swap in/out and SWAPPING? Do you mean page in/out?
Tom Kyte
September 03, 2008 - 12:18 pm UTC

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.

Suraj Sharma, September 04, 2008 - 12:33 pm UTC

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,

Tom Kyte
September 04, 2008 - 1:47 pm UTC

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.

Suraj Sharma, September 04, 2008 - 2:03 pm UTC

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
Tom Kyte
September 04, 2008 - 3:19 pm UTC

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

aliyar, May 27, 2010 - 1:44 pm UTC

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
Tom Kyte
May 27, 2010 - 1:54 pm UTC

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.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.