5
\$\begingroup\$

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.

\$\endgroup\$
  • 2
    \$\begingroup\$ Could you tell us more about what the profiles table is for? I really have to question the sanity of a schema with so many columns. \$\endgroup\$ – 200_success Apr 17 '14 at 12:15
2
\$\begingroup\$

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;
|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ Why would using a a temp table or view improve performance? \$\endgroup\$ – 200_success May 21 '14 at 9:28
  • \$\begingroup\$ I would think at the very least performing the aggregate functions on an isolated data set would free up the tables from locks while the long sum and group operations are done. Also I would think if this were a view then the view and operation could be refreshed as the business need called for it. \$\endgroup\$ – Phrancis May 21 '14 at 22:19
  • \$\begingroup\$ I doubt it. Writing a row to a temporary table would probably be a lot more work than adding a number in memory. \$\endgroup\$ – 200_success May 21 '14 at 22:23
  • \$\begingroup\$ Possible. How would you approach it yourself? Would be curious to see your answer (other than scrapping it lol). There might be a way to make the data set more useful by pivoting it or something like that, but hard to say without seeing it. \$\endgroup\$ – Phrancis May 21 '14 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.