Oracle PGA memory allocation on Solaris

From: Taral Desai <taral.desai_at_gmail.com>
Date: Mon, 1 Mar 2010 10:36:36 -0600
Message-ID: <2b0cd5cd1003010836x527bdaebk6a6095e859e04e2b_at_mail.gmail.com>

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

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

Received on Mon Mar 01 2010 - 10:36:36 CST