Hi again,
Another list member pointed me to look at
sort_area_size and sort_area_retained_size. The are:
<FONT face="Courier New"
size=2>sort_area_retained_size
integer
1048576sort_area_size
integer 31457280
Our db is
doing a fair amount or sorts:
<FONT face="Courier New"
size=2>
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
<FONT
face="Courier New">-----
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:
<FONT color=#330099
face="Arial, Helvetica, sans-serif">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 <EM
class=Italic>not released to the operating system, rather <EM
class=Italic>the freed memory is made available to the session for reuse.
What do you think?
Thanks again everybody for your
suggestions!
- Jerry
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
- Original Message -----
From: "John Kanagaraj" <<A
href="mailto:john.kanagaraj_at_hds.com"><FONT face="Courier New"
size=2>john.kanagaraj_at_hds.com<FONT face="Courier New"
size=2>>
To: "Multiple recipients of list
ORACLE-L" <<FONT
face="Courier New" size=2>ORACLE-L_at_fatcity.com<FONT
face="Courier New" size=2>>
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> <A
href="http://www.klove.com"><FONT face="Courier New"
size=2>http://www.klove.com
<<FONT face="Courier New"
size=2>http://www.klove.com/<FONT face="Courier New"
size=2>> > > ** 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 <<FONT
face="Courier New" size=2>mailto:ORACLE-L_at_fatcity.com<FONT
face="Courier New" size=2>> 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: <A
href="http://www.orafaq.com"><FONT face="Courier New"
size=2>http://www.orafaq.com<FONT face="Courier New"
size=2>> -- > Author: John Kanagaraj> INET: <A
href="mailto:john.kanagaraj_at_hds.com"><FONT face="Courier New"
size=2>john.kanagaraj_at_hds.com<FONT face="Courier New"
size=2>> > 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:
<FONT face="Courier New"
size=2>ListGuru_at_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).
Received on Tue Aug 28 2001 - 18:54:49 CDT
