Jerry,
Riyaj is right - latch contention would not show up against the kernel
counters. A cursory glance shows that there is no problem from your database
side of things. I would pursue the options suggested by Riyaj and Kevin...
John
-----Original Message-----
Sent: Tuesday, August 28, 2001 3:42 PM
To: Multiple recipients of list ORACLE-L
Okeee dokieee...
SQL> select * from v$system_event order by total_waits desc
2 /
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
-------------- ----------- ------------
SQL*Net message to client 47853242
0 5982 .000125007
SQL*Net message from client 47853068
0 176307308 3.68434701
SQL*Net more data to client 24087092
0 786625 .032657533
db file sequential read 20905281
0 1590147 .076064369
db file scattered read 20663495
0 1557148 .075357436
rdbms ipc message 3177469
416528 56288363 17.7148425
log file parallel write 2563234
0 793354 .309512904
control file sequential read 2558269
0 6004 .002346899
log file sync 1499923
2027 1744894 1.16332238
direct path read 1112982
0 87 .000078168
file open 745799
0 14647 .01963934
log file sequential read 622354
0 23257 .037369407
refresh controlfile command 505107
0 11013 .021803301
SQL*Net more data from client 317900
0 428063 1.3465335
library cache lock 153050
152954 1822 .011904606
control file parallel write 141746
0 116254 .820157183
pmon timer 140927
140178 8344102 59.2086825
buffer busy waits 136846
180 141365 1.03302252
latch free 131862
123294 79725 .604609364
db file parallel write 112323
0 61 .000543077
SQL*Net break/reset to client 13705
0 2367 .17271069
LGWR wait for redo copy 13022
2762 3594 .275994471
db file single write 11152
0 1437 .128855811
library cache pin 6159
5122 48505 7.8754668
enqueue 4802
1631 4565 .950645564
direct path write 2592
0 1 .000385802
PX Idle Wait 2267
2253 0 0
rdbms ipc reply 2264
0 425 .187720848
smon timer 1423
1406 8296096 5830.00422
file identify 1097
0 7 .006381039
log file switch completion 418
12 4020 9.61722488
log file single write 403
0 167 .41439206
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
-------------- ----------- ------------
row cache lock 250
0 163 .652
Wait for a undo record 84
15 0 0
library cache load lock 68
27 1783 26.2205882
buffer deadlock 50
50 0 0
inactive session 48
47 854 17.7916667
control file single write 35
0 60 1.71428571
PX Deq: Txn Recovery Start 34
0 0 0
Wait for stopper event to be increased 29
17 0 0
process startup 26
0 0 0
single-task message 16
0 27 1.6875
PX Deq: Signal ACK 12
0 0 0
PX Deq: Join ACK 9
0 0 0
db file parallel read 6
0 1 .166666667
local write wait 5
0 29 5.8
switch logfile command 5
0 17 3.4
sort segment request 2
2 0 0
instance state change 2
0 0 0
Null event 1
1 0 0
reliable message 1
0 0 0
checkpoint completed 1
0 0 0
52 rows selected.
SQL> select event, count(*) from v$session_wait group by event
2 /
EVENT COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client 44
SQL*Net message to client 1
pmon timer 1
rdbms ipc message 7
smon timer 1
Thanks,
Jerry
----- Original Message -----
[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L" < <mailto:[EMAIL PROTECTED]>
[EMAIL PROTECTED]>
Sent: Tuesday, August 28, 2001 5:36 PM
> 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 < <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:[EMAIL PROTECTED]>
mailto:[EMAIL PROTECTED]> 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>
http://www.orafaq.com
> --
> Author: John Kanagaraj
> INET: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
>
> 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: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).