Hi,
Do a trace on the batch job on both environment and
compare the access plans and where time is spent
differently.
- Deepak Sharma <sharmakdeep_at_yahoo.com> a icrit : >
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).
>
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
spaquette_at_houra.fr
(33) 01 53 93 06 50
Received on Thu May 18 2000 - 12:30:57 CDT
