RE: wait events

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
  > 210005 30000.71
  > pmon timer 668
  > 200693 300.44
  > log file parallel write 1456
  > 499 .34
  > control file parallel write 666
  > 420 .63
  > latch free 8
  > 5 .63
  > control file sequential read 12
  > 0 0
  >
  >
  

  ---

  
  > *************************
  > The most of the buffer busy waits were on data
  > blocks belonging to one table
  > only.
  > The point is that performance of som infrastructure
  > testing has decreased
  > dramatically between two releases of this new soon
  > to be live application.
  > One of the requirements of the suppliers was to
  > switch from 4k to 8K block
  > size.
  > so I rebuild the database and left all other parms
  > the same.
  >
  > Now performance is poor and they all point the
  > finger at ORACLE. I defend
  > oracle
  > and say it's the application, but in the meantime
  > will be rebuilding the
  > database to 4K. Does anybody see anything else I
  > should be looking at ?
  >
  >
  > Jack
  >
  >
  >
  >
  

  ---

  
  > De informatie verzonden met dit E-mail bericht is
  > uitsluitend bestemd voor
  > de geadresseerde. Gebruik van deze informatie door
  > anderen dan de
  > geadresseerde is verboden. Openbaarmaking,
  > vermenigvuldiging, verspreiding
  > en/of verstrekking van deze informatie aan derden is
  > niet toegestaan.
  > Ernst & Young staat niet in voor de juiste en
  > volledige overbrenging van de
  > inhoud van een verzonden E-mail, noch voor tijdige
  > ontvangst daarvan.
  >
  

  ---

  
  > The information contained in this communication is
  > confidential and may be
  > legally privileged. It is intended solely for the
  > use of the individual or
  > entity to whom it is addressed and others authorised