Tuning a query for performance

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

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;

StackExchange Code Review Q#47453, answer score: 3