Re: performance problem : tkprof show many fetches with high elapsed time

"Benny" <benny.derous_at_gmail.com> wrote in message news:1167897949.740166.99610_at_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.
>

Combining comment from several postings:

Your ID is unique, but the number of rows in the batch table is FAR greater than one.

This line in the rowsource operation shows that on one execution you got 6,429 rows from the table.

> 6429 TABLE ACCESS FULL PROFILESTOREDATA_BATCH
Similarly, on one execution of the update you got:

> Execute 12 59.22 868.48 65205 211734 176298 72221
> 6395 PARTITION RANGE SINGLE PARTITION: KEY KEY
Because the update statement is an ARRAY update, e.g.

    for r in m_array

        update ... set ... where ID = m_array(r);

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html