Re: TKPROF - Help needed interpreting results

A copy of this was sent to "Dipen Kotecha" <dkotecha_at_ford.invalid> (if that email address didn't require changing) On Wed, 15 Dec 1999 11:40:34 +0100, you wrote:

>We have a package that seems to take a long time to execute and I carried
>out a trace on a session executing this package. Below is one of the results
>I got from the TKPROF. I do not understand what could take it so long and
>why there are no disk reads. If all the data is in the buffer why does it
>take so long to fetch? Any help or pointers would be greatly appreciated.
>The table concerned only has ~6000 rows.
>

It is not taking very long at ALL to execute the query.

You execute and fetch 1 row from this query 16,380 times. That means that each query takes .053314408 seconds (5/100'ths of a second). Given that if you have an 8k block size this would be about 1.2 meg of data (600k for 4k, 300k for 2k) -- this sounds reasonable, 5/100'ths of a second to search 6000 rows exhaustively -- perform functions on the data and so forth.

Questions for you:

• do you need to execute it 16,380 times? is there some other way to do the logic such that this query does not need to be executed so much (give that there are 6,000 rows in the table -- the answer almost certainly is YES. You've queried every row almost 3 times)....

>Regards,
>
>Dipen
>
>SELECT COUNT(*),MIN(CAR_TYPE_DEFINITION_NAME),MIN(SALEID),MIN(DEALER_CODE),
>
>MIN(NA_TYPE),MIN(DECODE(NA_TYPE,'F',FOREIGN_CAMPAIGNID,'O',OTHER_CAMPAIGNID,
> 'V', NULL ,'C',OTHER_CAMPAIGNID,'D',DOCUMENTID,'P',DOCUMENTID,'N',
> FOREIGN_CAMPAIGNID, NULL )),MIN(FOREIGN_OBJECTIVE_NAME),
> MIN(OBJECTIVE_DATE_TYPE),MIN(OBJECTIVE_DATE_FROM),MIN(OBJECTIVE_DATE_TO)
>FROM
> MS2000.T_CAMPAIGN_OBJECTIVES WHERE CAMPAIGNID = :b1 AND
> LTRIM(RTRIM(OBJECTIVE_NAME)) = LTRIM(RTRIM(:b2)) AND
> (LTRIM(RTRIM(DEALER_CODE)) = 'DEALER' OR LTRIM(RTRIM(DEALER_CODE)) = :b3
> OR LTRIM(RTRIM(DEALER_CODE)) = 'DEALERTARGETS' )
>
>
>call count cpu elapsed disk query current
>rows
>------- ------ -------- ---------- ---------- ---------- ---------- ------
>----
>Parse 1 0.00 0.00 0 0 0
>0
>Execute 16380 1.15 1.05 0 0 0
>1040130
>Fetch 16380 873.29 876.53 0 2538900 32760
>16380
>------- ------ -------- ---------- ---------- ---------- ---------- ------
>----
>total 32761 874.44 877.58 0 2538900 32760
>1056510
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 13 (ENGINE) (recursive depth: 1)
>
>Rows Execution Plan
>------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 SORT (AGGREGATE)
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'T_CAMPAIGN_OBJECTIVES'
>
>****************************************************************************
>****
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 15 1999 - 06:59:15 CST