Home » RDBMS Server » Server Administration » HIGH LOAD AVERAGE even CPU usage is not high. (Linux 5.4 oracle 11g R2.)
HIGH LOAD AVERAGE even CPU usage is not high. [message #445938] Thu, 04 March 2010 15:56 Go to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
Experts,

I am facing one issue on my Linux server OEL 5.4 running oracle 11gR2 on this with ASM.

Load average goes as high as 665 and server completely hangs .Users are unable to login to database during high load.

top - 05:52:08 up 19 days, 16:54, 5 users, load average: 667.01, 662.64, 638.63Tasks: 1320 total, 3 running, 1316 sleeping, 0 stopped, 1 zombie
Cpu(s): 3.4%us, 1.5%sy, 0.0%ni, 30.7%id, 61.7%wa, 0.4%hi, 2.1%si, 0.0%stMem: 32898704k total, 32845948k used, 52756k free, 2660k buffersSwap: 25165812k total, 7255164k used, 17910648k free, 16865452k cached

I would like to debug this issue.What are thing that I need to check to diagnose this issue?

Is this points to some resource starvation on server or some database problem ?
Your inputs are highly appreciated.

Thanks in Advance.


Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #445941 is a reply to message #445938] Thu, 04 March 2010 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

following will take 1 minute to complete. issue following command

vmstat 6 10


After command above complete post results back here using <code tags>

How much RAM does this system have?
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #445946 is a reply to message #445941] Thu, 04 March 2010 18:50 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
No Message Body

[Updated on: Thu, 04 March 2010 18:54]

Report message to a moderator

Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #445947 is a reply to message #445946] Thu, 04 March 2010 18:57 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Below is the description of few parameters of top command:

wa = Waiting for I/O
us = User space
sy = system/kernel
ni = Nice Process
id = Idle
hi = Hardware Interrupts
si = Software Interrupts

30.7%id, -> 30.7% idle
61.7%wa -> 61.7% Waiting for I/O

the "si" column from vmstat is SwapIn rate

This system is resource starved for additional RAM & I/O capacity

post results from following command

ps -ef | grep -i pmon
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #445948 is a reply to message #445947] Thu, 04 March 2010 19:38 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
Server has 32 G RAM
-bash-3.2$ ps -ef |grep -i pmon
oracle   14361     1  0 Feb27 ?        00:05:37 ora_pmon_INDIGO_WS
oracle   23959     1  0 Feb13 ?        00:01:04 asm_pmon_+ASM
oracle   29935 20933  0 09:38 pts/2    00:00:00 grep -i pmon



-bash-3.2$ vmstat 6 10
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache      si   so    bi    bo    in   cs    us sy id wa st
96  4 5503632 561360 163276 19638416   65   28  1248   408    0    1     9  4 74 13  0
109  5 5498520 528224 163384 19646948 2747   0  6252  8130 19126 216349 96  4  0  0  0
105  3 5493236 493736 163420 19654180 2823   0  6354  8097 17937 210925 94  4  1  1  0
 5  4 5488536 556788 163488 19662184 2500    0  4427 10136 18746 134043 52  6 36  6  0
14  2 5480652 831792 163612 19671452 3636    0 11028 15209 19433 147188 46 14 28 11  0
20  4 5474284 729204 163800 19680768 3033    0 30435 33488 21337 88758  57 10 24  9  0
17  6 5468404 672732 163860 19687528 2943    0 85250 92409 19990 65888  19  8 57 17  0
18  5 5461664 623468 163920 19698928 3269    0  5607 11263 20500 71944  30  7 51 12  0
29  1 5455992 575628 163960 19708472 2713    0  4681 10152 20314 77300  53  7 33  8  0
 2  1 5450648 637684 163996 19717376 2185    0  3948 10410 19241 157340 77  6 14  4  0

Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #445949 is a reply to message #445948] Thu, 04 March 2010 20:24 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Thanks for answers so far, but more to come

post results from following command

free -mt

post results from following SQL using sqlplus

SQL> SHOW SGA


as in below

SQL> show sga

Total System Global Area  281018368 bytes
Fixed Size		    1218944 bytes
Variable Size		   88082048 bytes
Database Buffers	  184549376 bytes
Redo Buffers		    7168000 bytes
SQL> 
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #445955 is a reply to message #445949] Thu, 04 March 2010 21:39 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
-bash-3.2$ free -mt
             total       used       free     shared    buffers     cached
Mem:         32127      30885       1242          0        185      20262
-/+ buffers/cache:      10437      21690
Swap:        24575       3735      20840
Total:       56703      34621      22082
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 5 11:38:07 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> show sga

Total System Global Area 2.4586E+10 bytes
Fixed Size		    2213616 bytes
Variable Size		 7784630544 bytes
Database Buffers	 1.6643E+10 bytes
Redo Buffers		  155959296 bytes



Activity is less now I will post the result of vmstat 6 10 and free -vm again when I see high load.

Thanks for the prompt replies.
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #445959 is a reply to message #445955] Thu, 04 March 2010 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Fascinating!
What I think I see a a system with 32GB RAM & the Oracle instance with an SGA sized at around 24GB.

Are the numbers above close to reality?

What is the size of the SGA for the ASM instance?

the output show decent "si" Swap In activity; which should be 0 given the amount of RAM.

I can not reconcile the exceedingly large run queue while the "id" column (CPU idle) is non-zero.

In round numbers when load is high & response time bad
post results from following SQL

SELECT COUNT(*) FROM V$SESSION

Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #446157 is a reply to message #445959] Fri, 05 March 2010 14:27 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
ASM
SQL> show sga

Total System Global Area  283930624 bytes
Fixed Size		    2212656 bytes
Variable Size		  256552144 bytes
ASM Cache		   25165824 bytes

Database

SQL> select count(*) from v$session;

  COUNT(*)
----------
       580


-bash-3.2$ ps -ef|grep oracle|wc -l
621




I have seen processes goes high to 1060 during high activity.
I am still waiting for high activity to get more stats.
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #446168 is a reply to message #446157] Fri, 05 March 2010 15:24 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
-bash-3.2$ vmstat 6 10
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 7 10 3831044  94140   6560 21532916   64   27  1345   434    1    1 10  4 73 13  0
 8  8 3855848  77676   6644 21517212 4231  423 79404 24250 19889 49006 30 10 37 24  0
12  5 3931900 119808   6716 21466876 3115 2381 78615 27692 17587 35682 29 13 31 27  0
 4 18 3956788 135700   6444 21393244 2929 1425 70203 45861 18789 41968 26 11 38 26  0
 6  5 4038904 177832   4756 21301856 2161 3045 77121 27808 17166 33052 27 12 34 27  0
14 18 4038708  59288   4916 21415572 1859 2111 79524 27115 16890 33534 29  6 39 26  0
 5 17 4056220 113456   4852 21368820 1705 4439 81893 29834 16071 103913 29 13 28 30  0
 6  6 4039512  84256   4872 21445404 3140 1302 82505 28497 17934 36824 31 12 33 23  0
 3 32 4035812  64844   4652 21472436 1345 3165 59738 45623 14261 25989 22 16 22 40  0
49 59 4035884  52664   4776 21487612  581 2855 25153 13360 6617 37275 23  8 22 47  0
-bash-3.2$ free -mt
             total       used       free     shared    buffers     cached
Mem:         32127      32075         52          0          4      20990
-/+ buffers/cache:      11080      21047
Swap:        24575       3941      20634
Total:       56703      36016      20686
-bash-3.2$ vmstat 6 10
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 8 48 4147172  56280   5384 21522264   64   27  1345   435    1    1 10  4 73 13  0
18 38 4168496 105080   5348 21524808 2123 2913 25996 17456 12181 30913 36  6 14 44  0
11 25 4177872  57524   5304 21543020 2494 2586 31340 15004 17029 60628 43  7 19 31  0
 5 43 4216212  62900   5272 21503464 1713 2796 16246 12728 16784 36326 13  9 18 60  0
12 17 4240908  60020   5312 21473516 2239 2749 70677 20509 19000 50802 28  9 22 41  0
10  6 4255736 304352   6632 21362948 3523 1464 77515 30366 19127 64477 41 11 23 26  0
10  9 4248912  89744   7040 21525476 3847    0 81763 29720 19809 57673 35  8 38 19  0
 5 13 4280624  94384   6872 21468860 2816 1478 74486 43649 18009 39064 27  9 36 28  0
10  8 4333936 120364   6980 21383588 2266 2400 75486 32160 18526 39902 29 10 22 39  0
 6  4 4372552 107764   7092 21356488 2779 1780 79404 28276 19312 45932 32  9 33 26  0
-bash-3.2$ free -mt
             total       used       free     shared    buffers     cached
Mem:         32127      32070         57          0          6      20887
-/+ buffers/cache:      11175      20952
Swap:        24575       4281      20294
Total:       56703      36352      20351



Above data is during one spike.
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #446174 is a reply to message #446168] Fri, 05 March 2010 17:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The SGA is set to ~270 MB?

That is definitely to small for more for that many sessions.

What is the DB_CACHE_SIZE set to?

With 32GB available, when nothing else is running on that box, I would give the Oracle SGA at least 2/3 of it.

---
Edit: Nope, it isn't. Should have read everything first. Confused

[Updated on: Fri, 05 March 2010 17:40]

Report message to a moderator

Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #446175 is a reply to message #446174] Fri, 05 March 2010 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>The SGA is set to ~270 MB?

I do believe this is for the ASM instance; not Production OLTP instance
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #446221 is a reply to message #446168] Sat, 06 March 2010 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
at your earliest convenience please run the following SQL & post results nicely formatted inside <code tags>.

select * from v$sga_target_advice

SWAPPING is a performance killer.
Your system is swapping.
SWAPPING results from Operating System (OS) being memory starved.
OS need more memory.
One way to give more memory to OS is to reduce size of SGA.
Exactly how much to reduce SGA is To Be Determined.

Good Luck!
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #446467 is a reply to message #445938] Mon, 08 March 2010 12:55 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
SQL> select * from v$sga_target_advice;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
      5888	       .25     12036556 	     1.0206	     2395916235
      8832	      .375     11896212 	     1.0087	     1343460655
     11776		.5     11849038 	     1.0047	     1181138246
     14720	      .625     11826630 	     1.0028	     1015664772
     17664	       .75     11812478 	     1.0016	      964566427
     20608	      .875     11799505 	     1.0005	      893284235
     23552		 1     11793608 		  1	      851639084
     26496	     1.125     11788891 	      .9996	      818425160
     29440	      1.25     11785352 	      .9993	      790150742
     32384	     1.375     11781815 	       .999	      780186565
     35328	       1.5     11779457 	      .9988	      762046652

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
     38272	     1.625     11777099 	      .9986	      745865510
     41216	      1.75     11774740 	      .9984	      770477879
     44160	     1.875     11772382 	      .9982	      754381901
     47104		 2     11771203 	      .9981	      745865510

15 rows selected.



Yes that is for ASM instance .
Database Instance SGA is 22G.

Thanks for your support.
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #446468 is a reply to message #446467] Mon, 08 March 2010 12:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
I suggest the SGA be made smaller by 4 - 5 GB.
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #446477 is a reply to message #446468] Mon, 08 March 2010 14:27 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
Thanks for your time and suggestions.
I will discuss with my managemnt to decrease the SGA.

As it is production it might take some time to implement.
Will keep you guys posted.

Again Thanks for you help!

--Mehtab

Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #446479 is a reply to message #446477] Mon, 08 March 2010 14:43 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
      5888	       .25     12036556 	     1.0206	     2395916235
      8832	      .375     11896212 	     1.0087	     1343460655
     11776		.5     11849038 	     1.0047	     1181138246
     14720	      .625     11826630 	     1.0028	     1015664772
     17664	       .75     11812478 	     1.0016	      964566427
     20608	      .875     11799505 	     1.0005	      893284235
     23552		 1     11793608 		  1	      851639084

The proposed reduction would result in SGA that would be about 75% of the original size.
The hope is that this would free up enough RAM so that the OS is no longer forced to swap out user processes.
A different perspective is that it would increase OS RAM from about 8GB to more than 12GB or a 50% increase.
Some previously posted statistics showed 5GB+ swap in use.
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #452223 is a reply to message #445938] Tue, 20 April 2010 13:40 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
System is infact swapping and isue is resolved by setting hugepages in linux.

Ref :
Slow Performance with High CPU Usage on 64-bit Linux with Large SGA [ID 361670.1]
Re: HIGH LOAD AVERAGE even CPU usage is not high. [message #452224 is a reply to message #445938] Tue, 20 April 2010 13:41 Go to previous message
sonumalhi
Messages: 62
Registered: April 2008
Member
Thanks All for your support.
Previous Topic: Monitoring script required for Oracle 9.2.0.7.0 on Windows 2000 Server
Next Topic: ORA:16000: database open for read-only access during Select on standby 11gr2 read only.
Goto Forum:
  


Current Time: Fri Dec 02 18:17:20 CST 2016

Total time taken to generate the page: 0.26563 seconds