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).
