Re: sequential waits -- how to proceed

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 17 Jun 2003 19:32:16 -0700
Message-ID: <F001.005B3943.20030617185959@fatcity.com>

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