sequential waits -- how to proceed

From: <sat0789_at_fastmail.fm>
Date: Tue, 17 Jun 2003 15:48:48 -0700
Message-ID: <F001.005B384C.20030617152917@fatcity.com>

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). Received on Tue Jun 17 2003 - 17:48:48 CDT