Re: Oracle PGA memory allocation on Solaris

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Mon, 1 Mar 2010 11:26:08 -0600
Message-ID: <ad3aa4c91003010926g6d0bf5f0w12fa3bccd5e37e05_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 01 2010 - 11:26:08 CST