performance problem : tkprof show many fetches with high elapsed time

From: Benny <benny.derous_at_gmail.com>
Date: 4 Jan 2007 00:05:49 -0800
Message-ID: <1167897949.740166.99610@s34g2000cwa.googlegroups.com>

tkprof show many fetches with high elapsed time

Hi all,

could anyone give me a clue/hint where to look to solve this performance problem. This is output from tkprof. This statement is part of loading procedure and this statements takes the longest time. I can not figure out why the query which is only executed 12 times should take so much time. When I run it manually, it

responds in less than 1 minute. Query plan looks perfect to me. Some info : PROFILESTOREDATA_BATCH is small table with only 1 record and PROFILESTOREDATA is big table of 13GB with 12Mil

records. The s.ID column is a unique field

SELECT s.ID AS ID, s.DATA as DATA
FROM PROFILESTOREDATA s, PROFILESTOREDATA_BATCH b WHERE s.ID = b.STORE_ID and b.BATCH_ID= :1

call count cpu elapsed disk query current rows ------- ----- ------ ------- ------- ------- ------- -----

Parse      12   0.01    0.01       0       0       0      0
Execute    12   0.00    0.00       0       0       0      0
Fetch    7297  54.03  852.73   82456  330248       0  72935
------- ----- ------ ------- ------- ------- -------  -----
total    7321  54.04  852.74   82456  330248       0  72935

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44

Rows Row Source Operation

-------  --------------------------------------------

   6429 NESTED LOOPS
   6429 TABLE ACCESS FULL PROFILESTOREDATA_BATCH    6429 PARTITION RANGE ITERATOR PARTITION: KEY KEY    6429 TABLE ACCESS BY LOCAL INDEX ROWID PROFILESTOREDATA PARTITION: KEY KEY
   6429 INDEX UNIQUE SCAN IDX_PROFILESTOREDATA PARTITION: KEY KEY
(object id 18090)

Then we have an update in the procedure which also takes much time to complete. Cannot figure out why...when I run the update

manually, it runs also in less than 1 second. Anyone knows why this very simple update takes 868 seconds to complete for 12

updates of each time one record since ID is unique ?

UPDATE PROFILESTOREDATA SET DATA=:1
WHERE ID=:2 call count cpu elapsed disk query current rows ------- ----- ----- ------- ------ ------- -------- ------

Parse      12   0.00    0.00      0       0        0       0
Execute    12  59.22  868.48  65205  211734   176298   72221
Fetch       0   0.00    0.00      0       0        0       0
------- -----  ----- ------- ------ ------- --------  ------
total      24  59.22  868.48  65205  211734   176298   72221

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44

Rows Row Source Operation

-------  ---------------------------------------------------

6395 UPDATE
6395 PARTITION RANGE SINGLE PARTITION: KEY KEY 6395 INDEX UNIQUE SCAN IDX_PROFILESTOREDATA PARTITION: KEY KEY
(object id 18090)

Kind regards. Received on Thu Jan 04 2007 - 02:05:49 CST