Jared,
Thanks for the reply.
The parse to execute ratio is between 7% to 15%.
Output of your script to show parse time. is below.
EVENT PARSE_TIME C_PARSE_TIME
Total Parse Time Wait Seconds 31923 3192300
Although parsing may be an issue. I believe the bigger
problem is the high executions. With just 50 or 60
users we do see 1500 to 2000 executions per sec.
The "SQL*Net brak/reset to client" may be cused by
snipping sessions. We were seeing arround 100 sessions
per day were beeing snipped. Application people have
not yet figured out why or how this happens. We have a
job which runs every 20 minutes to kill sessions which
have been snipped.
- Jared.Still_at_radisys.com wrote:
> 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 <joni_65_at_yahoo.com>
> Sent by: root_at_fatcity.com
> 07/31/2002 02:50 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> 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
>
=== message truncated ===
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: joni_65_at_yahoo.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_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 Fri Aug 02 2002 - 10:33:29 CDT
