Re: Performance problem - help appreciated.

"Charlie Edwards" <charlie3101_at_hotmail.com> wrote in message news:217ac5a8.0403300816.6f98df37_at_posting.google.com...
> I've been given the task of improving the performance of the following
> query:
>
> SQL> select cpcm.system_code,
> 2 cpcm.cpc_id,
> 3 cpcm.extraction_code,
> 4 SUM(cpcd.amount) tot_amount,
> 5 SUM(cpcd.bonus) tot_bonus,
> 6 MAX(contact_date) last_contact_date
> 7 from cpc_details cpcd,
> 8 cpc_master cpcm
> 9 where cpcm.system_code = 'B'
> 10 and cpcm.cpc_id = cpcd.cpc_id(+)
> 11 and cpcm.system_code = cpcd.system_code(+)
> 12 and cpcm.extraction_code = '17'
> 13 group by cpcm.system_code,
> 14 cpcm.cpc_id,
> 15 cpcm.extraction_code
> 16 /

--snip--
> Woo-hoo - Problem solved, I thought - Ill just do my grouping at a
> higher level.
>
> SQL> select system_code,
> 2 cpc_id,
> 3 extraction_code,
> 4 sum(amount) tot_amount,
> 5 sum(bonus) tot_bonus,
> 6 max(contact_date) last_contact_date
> 7 from (select /*+ first_rows */ cpcm.system_code,
> 8 cpcm.cpc_id,
> 9 cpcm.extraction_code,
> 10 cpcd.amount,
> 11 cpcd.bonus,
> 12 contact_date
> 13 from cpc_details cpcd,
> 14 cpc_master cpcm
> 15 where cpcm.system_code = 'B'
> 16 and cpcm.cpc_id = cpcd.cpc_id(+)
> 17 and cpcm.system_code = cpcd.system_code(+)
> 18 and cpcm.extraction_code = '17')
> 19 group by system_code,cpc_id,extraction_code
> 20 /
>
> SY CPC_ID EXTRACTION_CODE TOT_AMOUNT TOT_BONUS LAST_CONTAC
> -- --------------- --------------- ---------- --------- -----------
> B 9097207 17 1496.76 26.67 01-MAR-2004
> B 9191237 17 13514 51.95 01-MAR-2004
>
> real: 5758
> SQL>
>
> Huh?????
> Just as bad as before.
>
> So can anyone help?
> Why does it suddenly ignore my first_rows hint?
> Has anyone got any other suggestions on how I can solve my problem.
>
> I can post plans / tkprof output etc but it isn't very enlightening.
>
> TIA
>
> CE

Try the following:

• Make sure all tables and their indexes used in this query are analyzed
• A 10053 event might be of help in finding the reson for the optimizer plan (If you know how to set it and use it).
• Try the following:

SQL> select system_code,
> 2 cpc_id,
> 3 extraction_code,
> 4 sum(amount) tot_amount,
> 5 sum(bonus) tot_bonus,
> 6 max(contact_date) last_contact_date
> 7 from (select /*+ first_rows */ rownum r1, cpcm.system_code,
> 8 cpcm.cpc_id,
> 9 cpcm.extraction_code,
> 10 cpcd.amount,
> 11 cpcd.bonus,
> 12 contact_date
> 13 from cpc_details cpcd,
> 14 cpc_master cpcm
> 15 where cpcm.system_code = 'B'
> 16 and cpcm.cpc_id = cpcd.cpc_id(+)
> 17 and cpcm.system_code = cpcd.system_code(+)
> 18 and cpcm.extraction_code = '17')
> 19 group by system_code,cpc_id,extraction_code
> 20 /

• Note: the rownum in the select clause.

Anurag Received on Tue Mar 30 2004 - 16:02:56 CST