Home » RDBMS Server » Performance Tuning » help me tune this (11g )
help me tune this [message #499148] Sun, 13 March 2011 18:46 Go to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
SELECT a.CURRENCY, a.REPORD, a.REPTYPE, 
       SUM(a.POLICY_COUNT) POLCOUNT, SUM(a.POLICY_COUNT)/TOTALCOUNT PCTCOUNT, 
       SUM(a.MODALPREM) MODALPREM, SUM(a.MODALPREM)/TOTALAMOUNT PCTAMOUNT
  FROM (SELECT CURRENCY,
               CASE REPTYPE
                  WHEN 'ON DUE DATE' THEN 1
                  WHEN 'WITHIN CONTRACT GRACE' THEN 2
                  WHEN 'WITHIN EXTENDED GRACE' THEN 3
                  WHEN 'BEYOND 45 DAYS' THEN 4
                  WHEN 'Thru PDO - RPU' THEN 5
                  WHEN 'Thru PDO - ETI' THEN 6
                  WHEN 'Thru PDO - APL' THEN 7
                  WHEN 'Thru PDO - APD' THEN 8
                  WHEN 'LAPSED' THEN 9
               END REPORD,  
               REPTYPE, COUNT(POLNO) POLICY_COUNT, SUM(MODALPREM) MODALPREM
          FROM View_PremiumPayment_Extract
         WHERE DUEDATE BETWEEN '01Jul2010' AND '15Jul2010'
         GROUP BY CURRENCY, REPTYPE
        ) a,
       (SELECT CURRENCY, COUNT(POLNO) TOTALCOUNT
          FROM View_PremiumPayment_Extract
         WHERE DUEDATE BETWEEN '01Jul2010' AND '15Jul2010'
         GROUP BY CURRENCY) b,
       (SELECT CURRENCY, SUM(MODALPREM) TOTALAMOUNT
          FROM View_PremiumPayment_Extract
         WHERE DUEDATE BETWEEN '01Jul2010' AND '15Jul2010'
         GROUP BY CURRENCY) c
  WHERE a.CURRENCY = b.CURRENCY
    AND a.CURRENCY = c.CURRENCY
    and a.currency = 'PHP'
  GROUP BY a.CURRENCY, a.REPORD, a.REPTYPE, b.TOTALCOUNT, c.TOTALAMOUNT
  ORDER BY CURRENCY, REPORD
Re: help me tune this [message #499151 is a reply to message #499148] Sun, 13 March 2011 19:04 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: help me tune this [message #499194 is a reply to message #499148] Mon, 14 March 2011 00:52 Go to previous messageGo to next message
flyboy
Messages: 1780
Registered: November 2006
Senior Member
Although it is hard without knowing used data (column types, indexes, cardinality of the view?, ratio of filtered rows), there are some points which could improve that query:

  • GROUP BY clause in the main query is useless - data is already aggregated in subqueries.
  • Aggregates from B and C subqueries may be achieved with analytic forms of used aggregate functions directly in the main query - no need to scan VIEW_PREMIUMPAYMENT_EXTRACT three times.
  • WHERE condition could be moved to subquery - although optimizer may do it itself.
  • What is the data type of DUEDATE? As e.g. '14Mar2011' is BETWEEN '01Jul2010' AND '15Jul2010' ('01Jul2010' is string and strings are sorted in alphabetical order - see description e.g. on http://en.wikipedia.org/wiki/Alphabetical_order#Alphabetical_order), I wonder whether it filters data you want.

Of course, relevance of all points except the first one could be obtained from explain plan of that query, which is not present in your post too.
Re: help me tune this [message #499247 is a reply to message #499148] Mon, 14 March 2011 04:55 Go to previous message
michael_bialik
Messages: 605
Registered: July 2006
Senior Member
Hi.

Flyboy already mentioned it : you are scanning the same data 3 times. Try following:

WITH a AS (SELECT CURRENCY,
               CASE REPTYPE
                  WHEN 'ON DUE DATE' THEN 1
                  WHEN 'WITHIN CONTRACT GRACE' THEN 2
                  WHEN 'WITHIN EXTENDED GRACE' THEN 3
                  WHEN 'BEYOND 45 DAYS' THEN 4
                  WHEN 'Thru PDO - RPU' THEN 5
                  WHEN 'Thru PDO - ETI' THEN 6
                  WHEN 'Thru PDO - APL' THEN 7
                  WHEN 'Thru PDO - APD' THEN 8
                  WHEN 'LAPSED' THEN 9
               END REPORD,  
               REPTYPE, COUNT(POLNO) POLICY_COUNT, SUM(MODALPREM) MODALPREM
          FROM View_PremiumPayment_Extract
         WHERE currency = 'PHP' AND 
           DUEDATE BETWEEN '01Jul2010' AND '15Jul2010'
         GROUP BY CURRENCY, REPTYPE
        ),
 B AS (SELECT CURRENCY, SUM(POLICY_COUNT) TOTALCOUNT,
        SUM(MODALPREM) TOTALAMOUNT FROM a )
SELECT a.CURRENCY, a.REPORD, a.REPTYPE, 
       SUM(a.POLICY_COUNT) POLCOUNT, SUM(a.POLICY_COUNT)/TOTALCOUNT PCTCOUNT, 
       SUM(a.MODALPREM) MODALPREM, SUM(a.MODALPREM)/TOTALAMOUNT PCTAMOUNT
  FROM  a,
        b
  WHERE a.CURRENCY = b.CURRENCY
  ... 


HTH
Previous Topic: Update in a Loop
Next Topic: INDEXES on TIMESTAMP
Goto Forum:
  


Current Time: Fri Nov 28 20:44:05 CST 2014

Total time taken to generate the page: 0.09650 seconds