Re: Oracle PGA memory allocation on Solaris
Date: Mon, 1 Mar 2010 11:41:45 -0600
Message-ID: <2b0cd5cd1003010941t49a38cddye0bec0962c9f9551_at_mail.gmail.com>
Thanks Mark and Kerber for information
Here are details for memory
- Memory Configuration
Memory Available Memory DIMM # of Mirror Interleave
LSB Group Size Status Size DIMMs Mode Factor --- ------ ------------------ ------- ------ ----- ------- ---------- 00 A 16384MB okay 1024MB 16 no 8-way 00 B 16384MB okay 1024MB 16 no 8-way
And this is SWAP
total: 4152288k bytes allocated + 1288288k reserved = 5440576k used,
83982080k available
Hi Mark,
Well as this occurs we are not able to log into box (Unix host) itself. So, i can't get number of processes nor get into sqlplus. So i set error stack for all three error but as it's not able to spawn process it didn't dump anything and SA's had to force reboot box.
Well coming to Questions you said
1.
"UGA Is subheap of PGA"
So, does this mean int's inside PGA memory(Process) memory. If yes then
stats are showing PGA as low and UGA as high memory allocation.
2.
Well second question is was in context of
Hist
Avg Std Dev Max Max
Alloc Used Alloc Alloc Alloc Alloc Num
Num
Category (MB) (MB) (MB) (MB) (MB) (MB) Proc
Alloc
- -------- --------- --------- -------- -------- ------- ------- ------
B SQL 15,169.8 15,104.8 197.0 578.8 1,961 2,104 77 71
E SQL 39,162.3 39,095.9 522.2 1,517.7 5,082 5,082 75 71
This from the hour which thrown this error frequently. I thought SQL area is used for storing SQL results. Let's say we have massive hash join operation but this would not be stored in SQL area as this will spill to TEMP(If not in memory sort). So, why this much of memory usage. Is this like let's say my hash join complete and it's expected to give 100GB data back. So, all this 100GB will be there in SQL area and then given to client ?
On Mon, Mar 1, 2010 at 11:26 AM, Andrew Kerber <andrew.kerber_at_gmail.com>wrote:
> I have seen this exact error when insufficient swap is allocated. Looks
> like more than 8G memory on server, so you should have allocated .75*RAM for
> swap space. Is that much allocated?
> >
> On Mon, Mar 1, 2010 at 10:36 AM, Taral Desai <taral.desai_at_gmail.com>wrote:
> >> 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 >> > > >
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
-- Thanks & Regards, Taral Desai -- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 01 2010 - 11:41:45 CST