RE: wait events

If the programrs run slow, the wait stat clearly shows that the problem is in IO. Make an additional effort to tune your SQL statements.

-----Original Message-----
From: Jared Still [mailto:jkstill_at_bcbso.com] Sent: Thursday, May 18, 2000 8:34 PM
To: Multiple recipients of list ORACLE-L Subject: RE: wait events

It's already been mentioned, but I'll repeat it.

What does iostat show you?

That is the % IO wait time per CPU?

Jared

On Thu, 18 May 2000, Deepak Sharma wrote:

> Ok let me explain this problem in more detail. We have
> 2 development environments A and B. The only
> difference is that Env B has All of Env A's objects +
> some objects of its own. Thus, Env B is a super-set of
> Env A. The platform is Digital Unix with Oracle 8.0.4.
>
>
> Comparing the Init.ora params, these are the ones
> which are different:
>
> Initialization Parameters Env B Env A
> --------------------------------------------------
> db_block_lru_latches 4 1
> db_file_multiblock_read_count 64 8
> shared_pool_reserved_size 4500000 250000
> shared_pool_size 90000000 5000000
> sort_area_retained_size 1002400 65536
> sort_area_size 2000000 524288
> --------------------------------------------------
>
> By the looks of it Env B seems better tuned. Both are
> running under CHOOSE with exactly same tables being
> analyzed on each environment.
>
> A Batch job which involves only Env A's objects, takes
> 20 Mins in Env A against 4 Hrs in Env B. This is where
> I noticed a large waits for 'db file seqential read'
> and 'db file scattered read' using this SQL:
>
> select event
> , total_waits
> , round(time_waited / 100) time_waited
> , round(average_wait / 100) average_wait
> from v$system_event
> order by total_waits desc
> /
>
> Event Waits Waited(sec) Avg.wait
> ------------------------------------------------------
> db file sequential read 1,467,902 2,612 0
> SQL*Net message to client 553,059 4 0
> SQL*Net message from client 553,045 452,801 1
> db file scattered read 361,710 4,800 0
> ------------------------------------------------------
>
> Any reason why there's so much difference in the
> response time.
>
> Thanks,
> Deepak
>
>
> --- john.j.kanagaraj_at_shell.com.bn wrote:
> > >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 ?
> >
> > Deepak,
> >
> > What is your platform? If UNIX, check the output of
> > 'vmstat' and
> > 'iostat' at times of high activity? (ignore first
> > line/set from these
> > two commands as they are the cumulative values from
> > system reboot). If
> > you see a high IOWAIT on 'vmstat', you can use
> > 'iostat' to measure the
> > I/O on individual disks. Tie this to output of
> > V$FILESTAT to identify
> > if indeed these are related to DB File IO. Identify
> > hot-spot
> > tablespaces/datafiles/tables and move them around.
> > In the longer term,
> > you will need to identify the Top SQLs based on Disk
> > Reads and tune
> > them iteratively...
> >
> > You need to have TIMED_STATISTICS = TRUE set for
> > this instance - I
> > assume you already have this set.
> >
> > Hth,
> > John Kanagaraj
> > Brunei Shell Petroleum
> > http://www.geocities.com/john_sharmila
> >
> > God so loved the world that He didn't send a
> > committee! (See John 3:16
> > for details)
> > ** Opinions expressed here are solely mine and not
> > necessarily those of
> > my employer **
> >
> >
>
> > ATTACHMENT part 2 application/ms-tnef
> name=WINMAIL.DAT
>
>
>
> __________________________________________________
> 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).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address jkstill_at_teleport.com - private

-- 
Author: Jared Still
  INET: jkstill_at_bcbso.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