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