Oracle PGA memory allocation on Solaris
- From: Taral Desai <taral.desai@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 1 Mar 2010 10:36:36 -0600
Hi ,
We are using oracle 10.2.0.3 On solaris 10 sparc-64. There was error occured
where OS was not able to create/start any new process and we were getting
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn3
Now i tried to put error dump on this three error but again it occured and
due to process didn't get statred it coudent dump error stack. So we need to
reboot box ever time when this process run because no one will be able to
log into this server as it's using 100% memory and also swap. Nor from
terminal itself
PGA is 16GB and SGA is 4GB. This is using TEMP space and it's i think group
by but not sure. Here are some more details from workload when this happen
% Blocks changed per Read: 1.67 Recursive Call %: 99.27
Rollback per transaction %: 1.67 Rows per Sort: ########
Statistic Total per Second per
Trans
-------------------------------- ------------------ --------------
-------------
buffer is pinned count 489,903,833 134,602.6
1,022,763.7
session pga memory 14,134,257,088 3,883,430.3
29,507,843.6
session pga memory max 16,519,833,024 4,538,874.5
34,488,169.2
session uga memory 299,167,113,240 82,197,077.4
#############
session uga memory max 15,913,830,584 4,372,373.5
33,223,028.4
sorts (rows) 1,091,041,609 299,767.0
2,277,748.7
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global
Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem
Bound(K)
- ---------- ---------- ---------- ---------- ------ ------ ------
----------
B 15,360 13,664 6,782.8 2,126.0 31.3 100.0 .0
1,048,576
E 15,360 13,283 15,948.9 15,106.4 94.7 100.0 .0
1,048,576
Hist
Avg Std Dev Max Max
Alloc Used Alloc Alloc Alloc Alloc Num
Num
Category (MB) (MB) (MB) (MB) (MB) (MB) Proc
Alloc
- -------- --------- --------- -------- -------- ------- ------- ------
------
B Freeable 4,288.0 .0 54.3 124.6 442 N/A 79
79
Other 2,294.9 N/A 20.7 69.4 269 269 111
111
SQL 195.3 187.3 2.1 3.9 12 2,104 93
88
OLAP 2.8 2.8 2.8 .0 3 3 1
1
PL/SQL 1.8 .9 .0 .0 0 16 109
109
JAVA .0 .0 .0 .0 0 1 1
0
E SQL 15,169.8 15,104.8 197.0 578.8 1,961 2,104 77
71
Other 658.0 N/A 6.9 18.2 69 90 95
95
Freeable 121.3 .0 1.9 3.2 18 N/A 63
63
PL/SQL 1.8 .9 .0 .0 0 16 93
93
OLAP .1 .1 .1 .0 0 3 1
1
JAVA .0 .0 .0 .0 0 1 1
0
Pct Avg Wait
Pct
Get Get Slps Time NoWait
NoWait
Latch Name Requests Miss /Miss (s) Requests
Miss
------------------------ -------------- ------ ------ ------ ------------
------
process queue reference 652,632,185 0.0 0.0 0 41,945,115
0.1
simulator hash latch 12,381,269 0.0 0.0 0 0
N/A
simulator lru latch 25,206 0.0 N/A 0 12,356,027
0.0
I have few question regarding this. I know PGA can grow beyond target
specified due to various things.
1. Do UGA is outside PGA in 10gr2. If yes how and what kind of data it
contains
2. IS SQL area is also scalable in oracle. because this is the only one that
has grown.
3. How to track this kind of issue. I mean if some memory flaw is there and
some memory didn't get free and may be bug. Because we are not able to log
into server when this occur. Only way is to put some memory threshold and
then when it cross that dump information. Is this a correct way or we can do
something else.
--
Thanks & Regards,
Taral Desai
Other related posts:
