RE: wait events

From: <CHUCK_HAMILTON_at_qvc.com>
Date: Thu, 18 May 2000 11:45:58 -0400
Message-Id: <10501.105975@fatcity.com>

They may or may not represent a problem, depending on the type of application. They indicate that there's a lot of waiting for disk i/o. If it's a data warehouse application it's probably normal. If it's an oltp application, then you may be able to reduce them. Look at your db_buffer_cache hit ratio and try to improve it. Here are a couple of things to try

Increase the number of block buffers.
Look for smallish tables that are frequently scanned. Often these tables are used as code tables in joins to get a code's description. Pinning them in cache improves the overall hit ratio.
If it's a peoplesoft application and you have the process scheduler running, pin the PSPRCSRQST table in cache. It's scanned every 10 seconds by the process scheduler and unless it's pinned, the scans force it to end of the LRU list where it's constantly being removed from the cache.
Try increasing the db_file_mutltiblock_read_count. This can reduce wait times on table scans. You can alter this one at the session level to test different values.
Make sure you're not doing unnecesary table scans by indexing columns frequently used in WHERE clauses. -- Chuck Hamilton QVC Inc. Enterprise Technical Services Oracle DBA

                    Deepak Sharma                                                                                  
                    <sharmakdeep@        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    yahoo.com>           cc:     (bcc: CHUCK HAMILTON/QVC)                                         
                    Ext: NA              Subject:     RE: wait events                                              
                    Sent by:                                                                                       
                    root_at_fatcity.                                                                                  
                    com                                                                                            
                                                                                                                   
                                                                                                                   
                    05/16/00                                                                                       
                    07:34 PM                                                                                       
                    Please                                                                                         
                    respond to                                                                                     
                    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
> 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
> to receive it. If you
>

=== 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

Received on Thu May 18 2000 - 10:45:58 CDT