Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: tkprof show many fetches with high elapsed time

Re: tkprof show many fetches with high elapsed time

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sat, 06 Jan 2007 18:34:49 GMT
Message-ID: <Xns98B06BA0C27CFanacedenthotmailcom@69.28.173.184>


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

SELECT s.ID AS ID, s.DATA as DATA
FROM PROFILESTOREDATA s,
WHERE s.ID IN

   (select STORE_ID from PROFILESTOREDATA_BATCH b where b.BATCH_ID= :1)

Never have in the FROM clause a table which does not contribute to the SELECT clause. Received on Sat Jan 06 2007 - 12:34:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US