RE: wait events

Steve,

I checked the ratio a couple of times between yesterday and today. It is always around 4.5 and I also checked the current maximum extents on the indexes; which is 24. So, I believe fragmentation is not a problem. If you look at the UTLBSTAT/ESTAT tuning document on Metalink, it mentions that for the 'db file sequential read', one of the solutions is to increase db_block_buffers. Any suggestions on that ?

Thanks,
Deepak

• "Briggs, Stephen" <SBriggs_at_pacificaccess.com.au> wrote:
  > Deepak,
  >
  > Check the ratio 'db file sequential reads' to 'table
  > fetch by rowid' for
  > each session. If the ratio is GT 10% then you may
  > have excessive
  > fragmentation in an index. The solution is to
  > rebuild the index.
  >
  > Steve.
  >
  > -----Original Message-----
  > Sent: Wednesday, May 17, 2000 9:35 AM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  > Hi All,
  >
  > We are getting large waits for 'db file sequential
  > read' and 'db file scattered read' on the production
  > system. How has your individual experience been on
  > changing (increase/decrease) the db_block_buffers
  > and
  > db_file_multiblock_read_count in such cases ?
  >
  > Thanks,
  >
  > Deepak
  >
  >
  > --- "Gait, Christopher" <cgait_at_condor.nrl.navy.mil>
  > wrote:
  > > Jack,
  > >
  > > I have a few comments on places to look. A lot of
  > > the items near the top of
  > > your list are normally ignored (like the 'SQL*Net
  > > message from client') but
  > > very high figures can indicate a networking issue
  > > either in how the network
  > > itself is doing (competition from other traffic,
  > > packet size, presence of a
  > > 'chattering' card on the net, etc.) or a Net8
  > issue.
  > > The high number of db
  > > file scattered reads may be of more concern,
  > > however, since it shows a lot
  > > of full table scans are going on. This would point
  > > to getting on your
  > > developers 'top ten SQL queries from hell' and
  > > optimizing them. This usually
  > > represents a large part of the problem that is
  > > 'Oracle's fault' or 'that
  > > dang DBA.' Be nice to your developers, as a rule,
  > > but if they continue to
  > > write badly tuned SQL that ignores indexes and
  > just
  > > look at you oddly when
  > > you talk about cost-based optimization and hints,
  > > consider having one of
  > > them hung in a cage above the cubicles as an
  > example
  > > to the others.
  > >
  > > I would be interested in seeing the results of
  > this
  > > query on your system at
  > > a busy time:
  > >
  > > SELECT
  > > Event,
  > > SUM(Total_Waits),
  > > SUM(Total_Timeouts)
  > > FROM
  > > V$Session_Event
  > > WHERE
  > > total_timeouts > 0
  > > GROUP BY
  > > Event
  > >
  > > This should pop some problem children to the
  > > surface. Some of your values
  > > look like you have a similar problem to us: bad
  > I/O,
  > > particularly redo. We
  > > have always had problems with redo, and could
  > still
  > > use more tuning.
  > >
  > > How balanced is your I/O? Do you have the luxury
  > of
  > > having things spread
  > > out, or are you stuck with what an SA laid down in
  > > concrete for disk/array
  > > setup? Also, what kind of a system is it, OLTP,
  > DSS,
  > > mixed? High transaction
  > > rate?
  > >
  > > I just read in your next message in this thread
  > that
  > > you moved to a 4K block
  > > size. Generally not a good idea, though I suppose
  > > there are some really
  > > high-transaction OLTP systems that can actually
  > > benefit. We're comfortable
  > > with an 8K on both our OLTP and DSS instances, but
  > > then we have a
  > > transaction rate a fast typist could probably keep
  > > up with (~1,000
  > > transactions a day) and you should probably
  > consider
  > > 16 or higher for
  > > anything faintly resembling a warehouse.
  > >
  > > Regards,
  > > Chris Gait
  > >
  > > -----Original Message-----
  > >
  > >
  > > Hi All,
  > >
  > >
  > > I have run utlestat & utlbstat plus some other
  > > scripts and have a lot of
  > > wait
  > > events
  > >
  >

  ---

  
  > > *************************
  > > Event Name Count
  > Total
  > > Time Avg Time
  > > -------------------------------- -------------
  > > ------------- -------------
  > > SQL*Net message from client 443489
  >
  > > 4645139 10.47
  > > rdbms ipc message 1995
  >
  > > 999996 501.25
  > > db file scattered read 1087432
  >
  > > 798071 .73
  > > PL/SQL lock timer 1
  >
  > > 10001 10001
  > > buffer busy waits 3542
  >
  > > 2415 .68
  > > latch free 6804
  >
  > > 2149 .32
  > > db file sequential read 35326
  >
  > > 1812 .05
  > > SQL*Net message to client 443501
  >
  > > 617 0
  > > log file sync 1354
  >
  > > 514 .38
  > > SQL*Net more data to client 5142
  >
  > > 98 .02
  > > control file sequential read 18
  >
  > > 4 .22
  > > enqueue 1
  >
  > > 0 0
  > > file open 13
  >
  > > 0 0
  > > refresh controlfile command 6
  >
  > > 0 0
  > > 14 rows selected.
  > > SVRMGR>
  > > SVRMGR>
  > > SVRMGR> Rem System wide wait events for background
  > > processes (PMON, SMON,
  > > etc)
  > > SVRMGR> select n1.event "Event Name",
  > > 2> n1.event_count "Count",
  > > 3> n1.time_waited "Total Time",
  > > 4> round(n1.time_waited/n1.event_count, 2)
  > > "Avg Time"
  > > 5> from stats$bck_event n1
  > > 6> where n1.event_count > 0
  > > 7> order by n1.time_waited desc;
  > > Event Name Count
  > Total
  > > Time Avg Time
  > > -------------------------------- -------------
  > > ------------- -------------
  > > rdbms ipc message 5441
  >
  > > 1383003 254.18
  > > smon timer 7