Tuning a query for performance

Question

I am running this query and it takes several minutes to complete. How can I tune it (unfortunately I don't have the privileges to run an SQL tuning advisor)?

select 'Master' || d.ntt_id || '_' || d.deal_nummas, p.prof_currency, 
sum(p.prof_ts1), sum(p.prof_ts2), sum(p.prof_ts3), sum(p.prof_ts4), sum(p.prof_ts5), 
sum(p.prof_ts6), sum(p.prof_ts7), sum(p.prof_ts8), sum(p.prof_ts9), sum(p.prof_ts10), 
sum(p.prof_ts11), sum(p.prof_ts12), sum(p.prof_ts13), sum(p.prof_ts14), sum(p.prof_ts15), 
sum(p.prof_ts16), sum(p.prof_ts17), sum(p.prof_ts18), sum(p.prof_ts19), sum(p.prof_ts20), 
sum(p.prof_ts21), sum(p.prof_ts22), sum(p.prof_ts23), sum(p.prof_ts24), sum(p.prof_ts25), 
sum(p.prof_ts26), sum(p.prof_ts27), sum(p.prof_ts28), sum(p.prof_ts29), sum(p.prof_ts30), 
sum(p.prof_ts31), sum(p.prof_ts32), sum(p.prof_ts33), sum(p.prof_ts34), sum(p.prof_ts35), 
sum(p.prof_ts36), sum(p.prof_ts37), sum(p.prof_ts38), sum(p.prof_ts39), sum(p.prof_ts40), 
sum(p.prof_ts41), sum(p.prof_ts42), sum(p.prof_ts43), sum(p.prof_ts44), sum(p.prof_ts45), 
sum(p.prof_ts46), sum(p.prof_ts47), sum(p.prof_ts48), sum(p.prof_ts49), sum(p.prof_ts50), 
sum(p.prof_ts51), sum(p.prof_ts52), sum(p.prof_ts53), sum(p.prof_ts54), sum(p.prof_ts55), 
sum(p.prof_ts56), sum(p.prof_ts57) from profiles p 
join deals d on d.deal_id = p.deal_id and d.deal_scope='Y' 
join runs r on r.deal_cnt_id = d.deal_cnt_id 
where r.run_id=7 and d.deal_nummas!=0 and d.deal_nummas is not null 
group by d.ntt_id, d.deal_nummas, p.prof_currency;

Some explanation on what I am doing:

I want to compute the sum on each timestep of a deal's profile, with deals being grouped by the node they belong to (identified by their deal_nummas). I only take into account deals which are in scope, and in the specified run.

The execution plan:

SELECT STATEMENT                                                                                3540470
    |_ HASH                                                                 GROUP BY            3540470
        |_ HASH JOIN                                                                            2957025
        |   |_ Access Predicates
        |   |   |_ D.DEAL_ID = P.DEAL_ID
        |   |_ HASH JOIN                                                                        624679
        |       |_ Access Predicates
        |       |   |_ R.DEAL_CNT_ID = D.DEAL_CNT_ID
        |       |_ TABLE ACCESS                                 RUNS        BY INDEX ROWID      1
        |       |   |_ INDEX                                    PK_RUN_ID   UNIQUE SCAN         0
        |       |       |_ Access Predicates
        |       |           |_ R.RUN_ID = 7
        |       |_ TABLE ACCESS                                 DEALS       FULL                624091
        |           |_ Filter Predicates
        |               |_ AND
        |                   |_ D.DEAL_SCOPE = 'Y'
        |                   |_ TO_NUMBER(D.DEAL_NUMMAS) <> 0
        |                   |_ D.DEAL_NUMMAS IS NOT NULL
        |_ TABLE ACCESS                                         PROFILES    FULL                922142

The table profiles stores what we could call curves, each with an identifier and 57 points (y-axis values), the columns being the x-axis values.

Answer 1

I'll echo what 200_success said about the schema. But working with what you have, I would think breaking it down into two steps (via temp table if this is a one-time aggregation, or via a view if repeat query) may improve performance. Then just perform your SUM() operations on the temp table/view. Preferably though, I would think a better organized schema would be more useful, but this may not be an option in your situation. I would also use a more explicit JOIN to reduce the amount of guessing the SQL engine has to do. I'm less familiar with what seems to be Oracle syntax but here is my take on it based on SQL Server syntax. Anyone else feel free to correct any Oracle-related syntax errors I make.

-- Create temp table, leaving out group by clause
select || d.ntt_id || '_' || d.deal_nummas, p.*
INTO #tmp_nummas
from profiles p 
inner join deals d on d.deal_id = p.deal_id and d.deal_scope='Y' 
inner join runs r on r.deal_cnt_id = d.deal_cnt_id 
where r.run_id=7 and d.deal_nummas!=0 and d.deal_nummas is not null;

-- Perform operation
select 'Master' ntt_id || '_' || deal_nummas, prof_currency
sum(prof_ts1), sum(prof_ts2), sum(prof_ts3), sum(prof_ts4), sum(prof_ts5), 
sum(prof_ts6), sum(prof_ts7), sum(prof_ts8), sum(prof_ts9), sum(prof_ts10), 
sum(prof_ts11), sum(prof_ts12), sum(prof_ts13), sum(prof_ts14), sum(prof_ts15), 
sum(prof_ts16), sum(prof_ts17), sum(prof_ts18), sum(prof_ts19), sum(prof_ts20), 
sum(prof_ts21), sum(prof_ts22), sum(prof_ts23), sum(prof_ts24), sum(prof_ts25), 
sum(prof_ts26), sum(prof_ts27), sum(prof_ts28), sum(prof_ts29), sum(prof_ts30), 
sum(prof_ts31), sum(prof_ts32), sum(prof_ts33), sum(prof_ts34), sum(prof_ts35), 
sum(prof_ts36), sum(prof_ts37), sum(prof_ts38), sum(prof_ts39), sum(prof_ts40), 
sum(prof_ts41), sum(prof_ts42), sum(prof_ts43), sum(prof_ts44), sum(prof_ts45), 
sum(prof_ts46), sum(prof_ts47), sum(prof_ts48), sum(prof_ts49), sum(prof_ts50), 
sum(prof_ts51), sum(prof_ts52), sum(prof_ts53), sum(prof_ts54), sum(prof_ts55), 
sum(prof_ts56), sum(prof_ts57) 
from #tmp_nummas
group by ntt_id, deal_nummas, prof_currency;