Your database has spent 4x more time waiting on latch free than
on direct path reads and writes. That's a little unusual. More than
half of the waits for latches have timed out.
direct path write 666089 0 747.64
.1122432587837361073370075170134921909835
direct path read 663888 0 801.73
.1207628395150989323500349456534837201456
SQL*Net break/
reset to client 3236571 0 1633.02
.0504552503251125960159687521145063710946
latch free 2798240 1694678 2658.02
0949889930813654296986677340042312310595
The "SQL*Net brak/reset to client" number seems a little unusual also. It
would appear
that in addition to your other performance issues, their is some kind of
issue with the
client software. I've not seen this wait before, so I don't know what it
is.
MetaLink is your friend. :)
Anjo has already stated that he thinks parsing is the culprit.
You might try this script to see how much time your database spends in
parsing.
====================================
col event format a40 head 'EVENT NAME'
col parse_time format a10 head "TIME IN|SECONDS"
col c_parse_time noprint new_value u_parse_time
--col c_parse_time print
--col c_cpu_time print
set feed off
select
--'Total DB File Wait Seconds' event,
-- why 'DB File Wait'?
-- I dunno
'Total Parse Time Wait Seconds' event,
lpad(to_char(round(sum(value)/100,0)),10) parse_time
,sum(value) c_parse_time
from v$sysstat
where name like 'parse time%'
/
set pages 0 head off
@cputime
set head on pages 60 feed on
col db_pct format a10 head "PCT OF CPU"
select
--&&u_parse_time "DB WAIT", &&u_cpu_time "CPU TIME",
'SQL Parse Time as PCT of Total CPU Time' event,
lpad(to_char( round(&&u_parse_time / &&u_cpu_time * 100,0)) || '%'
,10) db_pct
from dual
/
====================================
Steve Adams site www.ixora.com.au has a number of scripts that
can be used to display the actual latch statistics.
In addition, see if you can catch some sessions in the act of waiting on a
latch.
Given your statistics, that shouldn't be too hard.
select
s.username username,
e.event event,
s.sid,
e.p1text,
e.p1,
e.p2text,
e.p2,
e.wait_time,
e.seconds_in_wait,
e.state
from v$session s, v$session_wait e
where s.username is not null
and s.sid = e.sid
-- skip sqlnet idle session messages
and e.event not like '%message%client'
order by s.username, upper(e.event)
/
Appendix A of the Oracle Reference manual will tell you how to decode the
p1-p3 columns.
HTH,
Jared
Johnson Poovathummoottil <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/31/2002 02:50 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: extremely high number of executions
This is the result of the query you send. Hope the out
put is readable
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
AVERAGE_WAIT
buffer deadlock 3 3 0 0
instance state change 2 0 0 0
library cache lock 6 0 .04
.6666666666666666666666666666666666666667
local write wait 21 0 .06
2857142857142857142857142857142857142857
log file single write 96 0 .09
.09375
db file single write 136 0 .12
.0882352941176470588235294117647058823529
switch logfile command 3 0 .16
5.33333333333333333333333333333333333333
single-task message 8 0 .2 2.5
checkpoint completed 1 0 .22 22
LGWR wait for redo copy 802 4 .25
.0311720698254364089775561097256857855362
row cache lock 340 0 .31
.0911764705882352941176470588235294117647
SQL*Net more data from client 6579 0 .32
.004863961088311293509651922784617723058216
control file single write 35 0 .56 1.6
reliable message 1 0 .72 72
sort segment request 1 1 1.03 103
rdbms ipc reply 371 0 2.1
.5660377358490566037735849056603773584906
log file switch completion 55 0 3.28
5.96363636363636363636363636363636363636
Null event 1 1 4.11 411
file identify 775 0 6.52
.8412903225806451612903225806451612903226
db file parallel read 406 0 11.44
2.8177339901477832512315270935960591133
enqueue 617 2 13.97
2.26418152350081037277147487844408427877
log file sequential read 7045 0 22.5
.3193754435770049680624556422995031937544
refresh controlfile command 10335 0 43.19
.4179003386550556361877116594097726173198
db file parallel write 21631 0 60.34
2789515047847995931764597106005270214045
SQL*Net message to client 36898649 0 61.5
.000166672768967774402797240625259748669931
file open 60036 0 61.81
.1029548937304284096208941301885535345459
buffer busy waits 3676 8 68.43
1.86153427638737758433079434167573449402
library cache load lock 444 31 108.8
24.5045045045045045045045045045045045045
control file sequential read 96070 0 130.11
135432497137503903403768085770792130738
SQL*Net more data to client 1492134 0 142.27
009534666457570164609880882011937265687934
log file parallel write 275806 0 176.48
.0639870053588391840641610407315286832049
control file parallel write 74532 0 229.56
.3080019320560296248591209145065206891
log file sync 191978 14 381.49
.1987154778151663211409640687995499484316
library cache pin 29082 63 517.81
1.78051715837975379960112784540265456296
db file scattered read 118675 0 640.12
.5393890878449547082367811249210027385717
direct path write 666089 0 747.64
.1122432587837361073370075170134921909835
direct path read 663888 0 801.73
.1207628395150989323500349456534837201456
SQL*Net break/reset to client 3236571 0
1633.02
.0504552503251125960159687521145063710946
latch free 2798240 1694678 2658.02
.0949889930813654296986677340042312310595
db file sequential read 935776 0 4534.27
.4845465154053961631843518106897377150087
smon timer 776 738 227031.3
29256.6108247422680412371134020618556701
pmon timer 74918 73457 227158.05
303.208908406524466750313676286072772898
rdbms ipc message 761063 221114 1134403.5
149.055137353937847458094796357200389455
SQL*Net message from client 36898579 0
6764616.13
18.33299902958322595566620600755384103003
--- [EMAIL PROTECTED] wrote:
> What does this tell you?
>
> select
> event,
> total_waits,
> total_timeouts,
> time_waited/100 time_waited,
> average_wait
> from v$system_event
> order by time_waited
> /
>
>
>
>
>
> Johnson Poovathummoottil <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 07/31/2002 01:24 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> Subject: extremely high number of
> executions
>
>
> Hi All,
>
> We have an application which executes one sql
> statement more than 10 million times a day.
> Everything
> is good about the sql, well tuned, uses indexes,
> parse
> only once, etc. The number of concurrent users in
> this
> database seems to around 60, but we see an average
> 1500 executions/sec.
>
> We questioned the developers about the sql as we had
> seen 80% to 95% latch sleeps on library cache
> constantly. They seem to be hitting the database
> every
> time a page is refreshed instead of storing the
> retrieved data some where for later use.
>
> The developers are of the opinion that cookies and
> session variables are considered "the much
> detested and reviled Satan and Lucifer of all
> "stateful" web apps".
>
> Any comments/opinion?
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - Feel better, live better
> http://health.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Johnson Poovathummoottil
> 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).
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
> 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).
__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnson Poovathummoottil
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
