TKPROF - Help needed interpreting results

From: Dipen Kotecha <dkotecha_at_ford.invalid>
Date: Wed, 15 Dec 1999 11:40:34 +0100
Message-ID: <837r7c$3e75@eccws12.dearborn.ford.com>

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.

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'

****************************************************************************

Received on Wed Dec 15 1999 - 04:40:34 CST