Re: TKPROF - Help needed interpreting results

From: <michael_bialik_at_my-deja.com>
Date: Wed, 15 Dec 1999 21:11:15 GMT
Message-ID: <83905f$k5h$1@nnrp1.deja.com>

Hi.

I have a couple of questions:

Do you have any indexes on that table?
How many distinct values of CAMPAIGNID you have ? How many distinct values of OBJECTIVE_NAME ?

EXPLAIN shows that FULL table scan is performed each time. Check it.

3. TKPROF stats say that you executed SELECT 16K times.

Does it make sense?

Try to define an index on ( OBJECTIVE_NAME , CAMPAIGNID ) and removing LTRIM/RTRIM functions from OBJECTIVE_NAME and :b2 parm.

HTH. Michael.

In article <837r7c$3e75_at_eccws12.dearborn.ford.com>, "Dipen Kotecha" <dkotecha_at_ford.com> 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.
>
> 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'
>
>

> ****
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 15 1999 - 15:11:15 CST