tkprof show many fetches with high elapsed time

From: Benny <benny.derous_at_gmail.com>
Date: 3 Jan 2007 23:45:50 -0800
Message-ID: <1167896750.654906.83760@31g2000cwt.googlegroups.com>

Hi all,

could anyone give me a clue where to look concerning this performance problem. This is output from tkprof. This statement is part of loading procedure and this statement below 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 - 01:45:50 CST