Re: sequential waits -- how to proceed

Thanks for your reply ...

I understand that it is a sql per row update but the same update on UAT environment works at a rate of
about 2000 rows per sec. Though the data volume is definetly less compared to PROD, still there is night and day difference between prod and uat..In terms of the tables themselves, they are analyzed and the explain plan for update also looks exactly the same bet the environments. Both the instances are off the same SAN.

Sathish.

On Tue, 17 Jun 2003 19:00:00 -0800, "Wolfgang Breitling" <[EMAIL PROTECTED]> said:
> Your pl/sql procedure is obviously doing a sql per row updated rather
> than
> a set update. Unless you change the procedure you can expect only
> marginal
> improvement from any other measure.
>
> At 03:29 PM 6/17/2003 -0800, you wrote:
> >Hello ALL,
> > Oracle ver is 9.2 running on EMC array. I am executing a pl/sql
> > procedure which does an update on a fact table. There is an unique
> > index on the fact, with clearly shows up in the explain plan for
> > udapte.
> >I ran 10046 event for a 18 min duration during this update process and
> >then killed it.
> >On doing a tkprof on the trace file with waits set to Y, i get
> >
> >call count cpu elapsed disk query current
> > rows
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >Parse 1 0.00 0.00 0 0 0
> > 0
> >Execute 470509 238.31 1091.93 117854 1413284 479488
> >470508
> >Fetch 0 0.00 0.00 0 0 0
> > 0
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >total 470510 238.31 1091.94 117854 1413284 479488
> >470508
> >
> >Misses in library cache during parse: 0
> >Optimizer goal: CHOOSE
> >Parsing user id: 36 (NEVADMIN) (recursive depth: 1)
> >
> >Rows Row Source Operation
> >------- ---------------------------------------------------
> > 470508 UPDATE (cr=1413396 r=117854 w=0 time=1049454599 us)
> > 470509 INDEX UNIQUE SCAN DM_ACTUAL_CASHFLOW_HIST_UK (cr=1411527 r=3916
> > w=0 time=49102823 us)(object id 31693)
> >
> >
> >Rows Execution Plan
> >------- ---------------------------------------------------
> > 0 UPDATE STATEMENT GOAL: CHOOSE
> > 470508 UPDATE OF 'DM_ACTUAL_CASHFLOW_HIST'
> > 470509 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> > 'DM_ACTUAL_CASHFLOW_HIST_UK' (UNIQUE)
> >
> >
> >Elapsed times include waiting on following events:
> > Event waited on Times Max. Wait Total
> > Waited
> > ---------------------------------------- Waited ----------
> > ------------
> > db file sequential read 117854 2.81
> > 935.80
> > log file switch completion 19 1.00
> > 2.23
> > log file switch (checkpoint incomplete) 21 1.00
> > 17.45
> > log buffer space 2 0.07
> > 0.07
> >********************************************************************************
> >
> >As you can see the wait on db file sequential read is 935 ...i am
> >thinking it is in sec or is it centisec ??
> >i can see a degradation of perf as time continues. After 10 min , the
> >number of rows updated stays at 150 rows/sec which is pretty bad.
> >I have figured out the db file, table and block by looking into p1,p2.
> >That table is partioned and all the partitions are present on the same
> >tbs. It has 2 -- 8 gb files and p1 consistently points to either of the 2
> >data files.
> >I would like your help in trying to find out how to proceed from here ?.
> >I am stuck.
> >Ohter than moving the data files aound to different file systems ans
> >spreading them around, is there anything else thaty i can do to figure
> >out this problem.
> >
> >Thanks,
> >
> >Sathish.
> >
> >--
> >http://www.fastmail.fm - Same, same, but different…
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> >Author:
> > INET: [EMAIL PROTECTED]
> >
> >Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> >San Diego, California -- Mailing list and web hosting services
> >---------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: [EMAIL PROTECTED] (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).
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Wolfgang Breitling
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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).
>

-- 
http://www.fastmail.fm - The way an email service should be
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).