RE: wait events

From: Deepak Sharma <sharmakdeep_at_yahoo.com>
Date: Thu, 18 May 2000 09:31:23 -0700 (PDT)
Message-Id: <10501.105980@fatcity.com>

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

---

Received on Thu May 18 2000 - 11:31:23 CDT