Re: extremely high number of executions

Hi,

  How did you identify this is the statement which is causing the problem ? Did you check v$latch_children for the library cache children ? I would assume if it's one single SQL, then the contention will be on one child latch and not distributed across all the library cache latches. If this is the case would it be possible to put different comments in the SQL statement to hash it to a different child latch ? Assuming you have n number of child latches, having n different SQL statements (different due to the comment) may help reduce the contention.

Regards,
Denny

Quoting Johnson Poovathummoottil <joni_65_at_yahoo.com>:

> 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
>
> --- Jared.Still_at_radisys.com 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 <joni_65_at_yahoo.com>
> > Sent by: root_at_fatcity.com
> > 07/31/2002 01:24 PM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > 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?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: groups_at_koovakattu.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).