Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: extremely high number of executions

Re: extremely high number of executions

From: <Jared.Still_at_radisys.com>
Date: Thu, 01 Aug 2002 10:11:14 -0800
Message-ID: <F001.004A91AD.20020801101114@fatcity.com> 





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





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

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





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).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.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 Thu Aug 01 2002 - 13:11:14 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US