Re: extremely high number of executions

Are you setting setting_cached_cursors?

This may be helpful if your app is using bind variables at all. Given the amount of parsing going on, it doesn't seem likely.

Jared

On Friday 02 August 2002 08:33, Johnson Poovathummoottil wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.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).