Performance problem - help appreciated.

From: Charlie Edwards <charlie3101_at_hotmail.com>
Date: 30 Mar 2004 08:16:19 -0800
Message-ID: <217ac5a8.0403300816.6f98df37@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 /

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: 5808
SQL> Ouch! 5.8 seconds for 2 rows.
And yes system_code and extraction_code are indexed on cpc_master (700,000 odd rows) and system_code and cpc_id are indexed on cpc_details (850,000 odd rows) and the tables/indexes have been analyzed (recently), but it's not using the index on cpc_details.

Let's get it down to the basics, I thought, and get rid of the grouping/summing:

SQL> select cpcm.system_code,
  2         cpcm.cpc_id,
  3         cpcm.extraction_code,
  4         cpcd.amount,
  5         cpcd.bonus,
  6         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 /

SY CPC_ID EXTRACTION_CODE AMOUNT BONUS CONTACT_DAT
-- --------------- --------------- --------- --------- -----------

B  9097207                      17   1496.76     26.67 01-MAR-2004
B  9191237                      17     13514     51.95 01-MAR-2004

 real: 5778
SQL> Hmm .. not much difference - Lets try a first_rows hint

SQL> select /*+ first_rows */ cpcm.system_code,

  2         cpcm.cpc_id,
  3         cpcm.extraction_code,
  4         cpcd.amount,
  5         cpcd.bonus,
  6         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 /

SY CPC_ID EXTRACTION_CODE AMOUNT BONUS CONTACT_DAT
-- --------------- --------------- --------- --------- -----------

B  9097207                      17   1496.76     26.67 01-MAR-2004
B  9191237                      17     13514     51.95 01-MAR-2004

 real: 70
SQL> 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 Received on Tue Mar 30 2004 - 10:16:19 CST