From usidba@yahoo.com Tue, 28 Aug 2001 16:54:49 -0700 From: "Jerry C" Date: Tue, 28 Aug 2001 16:54:49 -0700 Subject: Re: OT : kernel using 75% of CPU Message-ID: MIME-Version: 1.0 Content-Type: text/plain Hi again,   Another list member pointed me to look at sort_area_size and sort_area_retained_size. The are:   sort_area_retained_size              integer 1048576sort_area_size                       integer 31457280 Our db is doing a fair amount or sorts:   SQL> select * from v$sysstat where name like '%sort%';   STATISTIC# NAME---------- ----------------------------------------------------------------     CLASS      VALUE---------- ----------       188 sorts (memory)        64    6581194          189 sorts (disk)        64        649          190 sorts (rows)        64  414280310  -----     The db has about 50 users... 50*30Mb. = ~ 1.5Gb.... brings me much closer to the 4 Gb. mark.  The sort area does go to the PGA, right?   The performance manual kind of makes it sound like this memory never gets released: Considerations with SORT_AREA_RETAINED_SIZE The SORT_AREA_RETAINED_SIZE parameter determines the lower memory limit to which Oracle reduces the size of the sort area after the sort has started sending the sorted data to the user or to the next part of the query. With dedicated connections, the freed memory is not released to the operating system, rather the freed memory is made available to the session for reuse. What do you think?   Thanks again everybody for your suggestions!     - Jerry
----- Original Message ----- From: "John Kanagaraj" <john.kanagaraj@hds.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L@fatcity.com> Sent: Tuesday, August 28, 2001 5:36 PM Subject: RE: OT : kernel using 75% of CPU > Jerry,>  > I think it is time to look at what's happening inside the database. The> following queries will give us an overall idea..>  > select * from v$system_event order by total_waits desc>  > select event, count(*) from v$session_wait group by event>  > I have a feeling that you may see a lot of waits on latches. Do post the> result of these two queries.> > John Kanagaraj > Oracle Applications DBA > DB Soft Inc > Work : (408) 970 7002 > > Listen to great, commercial-free christian music 24x7x365 at> http://www.klove.com <http://www.klove.com/>  > > ** The opinions and facts contained in this message are entirely mine and do> not reflect those of my employer or customers **> >  >  -----Original Message-----> Sent: Tuesday, August 28, 2001 1:01 PM> To: Multiple recipients of list ORACLE-L> > > > Thanks for the reply, Chris.>  > I'm a bit ashamed, being as old as I am, that I don't have a better grasp on> swapping. I initially thought maybe it was a swap problem also, but top> shows 0.0% swap. I thought I had also checked vmstat earlier, but yikes:>  > csuaor46> csuaor46> vmstat 15 20>  procs     memory            page            disk          faults      cpu>  r b w   swap  free  re  mf pi po fr de sr s6 s1 s1 s5   in   sy   cs us sy> id>  2 0 0  15352 14472  68 1513 14 227 953 56488 260 0 2 2 0 638 78  933 25 24> 50>  11 0 0 6240696 63336 105 1759 41 246 1754 62760 545 0 8 8 0 1130 4956 773> 22 76 1>  11 0 0 6243360 62864 42 2594 82 236 2357 62760 757 0 7 6 0 1239 6960 987 40> 60 1>  8 0 0 6238120 62368 48 1746 25 260 3767 56488 1198 0 7 6 0 1052 4837 762 36> 63 1>  8 0 0 6239640 65200 33 1772 229 262 2092 62760 619 0 16 16 0 1232 5776 871> 28 70 2>  5 1 0 6247656 62440 57 2078 162 497 4025 62760 1308 0 15 15 0 1216 5808 815> 21 75 4>  5 0 0 6247776 63456 26 2445 149 285 2716 62760 2188 0 11 13 0 1164 6593 903> 17 79 4>  10 1 0 6240680 62648 80 3008 266 523 4527 62760 9226 0 25 25 0 1127 6725> 884 22 76 2>  6 0 0 6218216 68664 33 2251 66 105 1086 62760 377 0 6 6 0 847 20782 744 31> 67 2>  5 0 0 6201240 62840  9 1799 72 350 2490 62760 415 0 9 9 0 1207 8889 781 15> 80 5>  5 0 0 6199336 62760  6 1935 40 923 3564 62760 636 0 9 9 0 1373 5193 1082 21> 69 10>  10 0 0 6189552 63840 11 1476 33 722 3089 62760 548 0 9 8 0 1364 4530 957 21> 77 2>  10 0 0 6174304 70704 25 2705 86 759 6441 62760 1003 0 10 10 0 1258 5551 836> 29 67 4>  8 0 0 6186512 63824 51 1728 44 227 1413 56488 188 0 9 7 0 1319 4485 676 31> 68 0>  7 0 0 6196448 63064 49 1635 44 235 1179 62760 167 0 4 4 0 1207 4968 694 39> 61 1>  9 0 0 6188656 63872 11 1915 112 433 2065 62760 308 0 13 12 0 1140 4835 828> 37 62 1> >  > Do the pi (page in) and po (page out) statistics represent swapping?! >  >  > Thanks again,>  > Jerry> > ----- Original Message ----- > To: Multiple  <mailto:ORACLE-L@fatcity.com> recipients of list ORACLE-L > Sent: Tuesday, August 28, 2001 1:30 PM> > paging and swapping is the first thing that comes to mind, look at vmstat.>  > I think your question is completely on topic.>  > > "Do not criticize someone until you walked a mile in their shoes, that way> when you criticize them, you are a mile a way and have their shoes."> > Christopher R. Spence > Oracle DBA > Phone: (978) 322-5744 > Fax:    (707) 885-2275 > > Fuelspot > 73 Princeton Street > North, Chelmsford 01863 >   > > -----Original Message-----> Sent: Tuesday, August 28, 2001 11:20 AM> To: Multiple recipients of list ORACLE-L> > > Hi there,>  > I have a Sun e4500, running Solaris 2.7 and Oracle 8.1.7.1.0. Everything> looks normal from a database perspective, but when I run "top" it show the> kernel being very hog-like:>  > load averages: 14.38, 15.18, 15.18> 07:16:21> 126 processes: 118 sleeping, 4 running, 4 on cpu> CPU states:  0.6% idle, 26.6% user, 72.8% kernel,  0.0% iowait,  0.0% swap> Memory: 4096M real, 63M free, 216M swap in use, 5310M swap free>  >   PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND>  2286 oracle     1   0    0 1844M 1814M run     9:44 13.90% oracle> 11068 oracle     1   0    0 2056K 1536K cpu0    0:02  1.53% top> 11333 oracle     1   0    0 1150M 1124M cpu1    0:01  1.39% oracle>  5944 oracle     1  40    0 1820M 1789M sleep  14:40  1.36% oracle>  4797 root       1  50    0 2112K 1248K sleep   6:01  1.36% top> 11346 oracle     1   0    0  110M   92M cpu0    0:01  1.26% oracle> 11114 oracle     1   0    0 1009M  984M cpu1    0:00  0.66% oracle> 11157 oracle     1   0    0 1009M  984M run     0:00  0.63% oracle> 11368 oracle     1  33    0 1794M 1765M sleep   0:00  0.29% oracle> 19558 oracle     1  60    0 1797M 1751M sleep  78:28  0.28% oracle> 19554 oracle     1  60    0 1794M 1751M sleep  38:05  0.20% oracle> 11366 oracle     1  55    0 1793M 1763M sleep   0:00  0.19% oracle> 11292 oracle     1  26    2 2008K 1424K run     0:00  0.19% dsql>  > Any ideas on what I, as a lowly DBA, would be able to check? It's a bit out> of my area and I'm stumped...>  >  > Thanks!>  > Jerry> > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com> -- > Author: John Kanagaraj>   INET: john.kanagaraj@hds.com> > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051> San Diego, California        -- Public Internet access / Mailing Lists> --------------------------------------------------------------------> To REMOVE yourself from this mailing list, send an E-Mail message> to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in> the message BODY, include a line containing: UNSUB ORACLE-L> (or the name of mailing list you want to be removed from).  You may> also send the HELP command for other information (like subscribing).