RE: wait events

Hi Deepak,
The db file sequential read occurs when there is a wait on a sequential read. Check to see if there are buffer busy waits too. Query v$session_wait to get the file#, block# and blocks and check with dba_extents to see if you are waiting constantly on some objects (tables, indexes). You can cache them or add db_block_buffers to alleviate the contention for those resources.

Hope this helps.
Regards,

Madhavan
IBM Corporation Webserver Div

>From: Deepak Sharma <sharmakdeep_at_yahoo.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: wait events
>Date: Wed, 17 May 2000 07:42:05 -0800
>
>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
> >
>=== message truncated ===
>
>
>__________________________________________________
>Do You Yahoo!?
>Send instant messages & get email alerts with Yahoo! Messenger.
>http://im.yahoo.com/
>--
>Author: Deepak Sharma
> INET: sharmakdeep_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).
Received on Wed May 17 2000 - 16:35:50 CDT