1 2 Previous Next 15 Replies Latest reply: Nov 30, 2011 4:18 PM by Randolf Geist RSS

    improve sql statement - decrease executing time

    marco
      Hi all,

      As database grows day by day, my need is to improve sql plan in order to decrease executing time.

      Please find my code below, it executes about 96 msecs,
      but when it works on real data (65000 contragentid)
      it executes too long...
      Could you please show me how do I improve sql statement?

      I've got values for each workday of month in table. Chief said we don't load values of dayoff because of space and time economy. Dayoff value is copy of last workday. The purpose of sql statement is to count averages (including days off), actually it's weighted average.
      WITH datbeg as (select date '2011-09-28' as arcdate from dual DUAL1), 
           datend as (select date '2011-09-30' as arcdate from dual DUAL2), 
           acc as (select contragentid 
                     from creator.aaccount AA 
                      ), 
           archtab AS (select date '2011-09-28' as arcdate, 
                              1 as contragentid, 
                              22 as luah, 
                              22 as luah_kor, 
                              23 as lusd, 
                              23 as lusd_kor, 
                              23 as lusd_eq, 
                              23 as lusd_eq_kor, 
                              23 as leur, 
                              23 as leur_kor, 
                              21 as leur_eq, 
                              21 as leur_eq_kor, 
                              21 as lovrd, 
                              21 as lovrd_kor, 
                              21 as lsum, 
                              21 as lsum_kor, 
                              20 as DLDateUAH, 
                              20 as DLDateUAH_kor, 
                              20 as DLDateUSD, 
                              20 as DLDateUSD_kor, 
                              20 as DLDateUSD_eq, 
                              19 as DLDateUSD_eq_kor, 
                              19 as DLDateEUR, 
                              19 as DLDateEUR_kor, 
                              19 as DLDateEUR_eq, 
                              19 as DLDateEUR_eq_kor, 
                              19 as DLDateOther_eq, 
                              18 as DLDateOther_eq_kor, 
                              18 as DLDateAll_eq, 
                              18 as DLDateAll_eq_kor, 
                              18 as DUAH, 
                              18 as DUAH_kor, 
                              18 as DUSD, 
                              18 as DUSD_kor, 
                              18 as DUSD_eq, 
                              18 as DUSD_eq_kor, 
                              18 as DEUR, 
                              18 as DEUR_kor, 
                              18 as DEUR_eq, 
                              18 as DEUR_eq_kor, 
                              18 as DALL, 
                              18 as DALL_kor 
                         from dual J1
                         union all 
      select date '2011-09-30' as arcdate, 
                              1 as contragentid, 
                              22 as luah, 
                              22 as luah_kor, 
                              23 as lusd, 
                              23 as lusd_kor, 
                              23 as lusd_eq, 
                              23 as lusd_eq_kor, 
                              23 as leur, 
                              23 as leur_kor, 
                              21 as leur_eq, 
                              21 as leur_eq_kor, 
                              21 as lovrd, 
                              21 as lovrd_kor, 
                              21 as lsum, 
                              21 as lsum_kor, 
                              20 as DLDateUAH, 
                              20 as DLDateUAH_kor, 
                              20 as DLDateUSD, 
                              20 as DLDateUSD_kor, 
                              20 as DLDateUSD_eq, 
                              19 as DLDateUSD_eq_kor, 
                              19 as DLDateEUR, 
                              19 as DLDateEUR_kor, 
                              19 as DLDateEUR_eq, 
                              19 as DLDateEUR_eq_kor, 
                              19 as DLDateOther_eq, 
                              18 as DLDateOther_eq_kor, 
                              18 as DLDateAll_eq, 
                              18 as DLDateAll_eq_kor, 
                              18 as DUAH, 
                              18 as DUAH_kor, 
                              18 as DUSD, 
                              18 as DUSD_kor, 
                              18 as DUSD_eq, 
                              18 as DUSD_eq_kor, 
                              18 as DEUR, 
                              18 as DEUR_kor, 
                              18 as DEUR_eq, 
                              18 as DEUR_eq_kor, 
                              18 as DALL, 
                              18 as DALL_kor 
                         from dual J1                    
                        ), 
           cal AS (SELECT date '2011-09-28' as cal_date from dual union all 
                      SELECT date '2011-09-29' as cal_date from dual union all
                      SELECT date '2011-09-30' as cal_date from dual 
                     ),
           archgrp as (SELECT c.cal_date, 
                              t.arcdate, 
                              t.contragentid, 
                              t.luah, 
                              SUM(NVL2(t.luah, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) luah_grp, t.luah_kor, 
                              SUM(NVL2(t.luah_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) luah_kor_grp, t.lusd, 
                              SUM(NVL2(t.lusd, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) lusd_grp, t.lusd_kor, 
                              SUM(NVL2(t.lusd_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) lusd_kor_grp, lusd_eq, 
                              SUM(NVL2(t.lusd_eq, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) lusd_eq_grp, lusd_eq_kor, 
                              SUM(NVL2(t.lusd_eq_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) lusd_eq_kor_grp, leur, 
                              SUM(NVL2(t.leur, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) leur_grp, leur_kor, 
                              SUM(NVL2(t.leur_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) leur_kor_grp, leur_eq, 
                              SUM(NVL2(t.leur_eq, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) leur_eq_grp, leur_eq_kor, 
                              SUM(NVL2(t.leur_eq_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) leur_eq_kor_grp, lovrd, 
                              SUM(NVL2(t.lovrd, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) lovrd_grp, lovrd_kor, 
                              SUM(NVL2(t.lovrd_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) lovrd_kor_grp, lsum, 
                              SUM(NVL2(t.lsum, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) lsum_grp, lsum_kor, 
                              SUM(NVL2(t.lsum_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) lsum_kor_grp, t.dldateuah, 
                              SUM(NVL2(t.dldateuah, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) dldateuah_grp, DLDateUAH_kor, 
                              SUM(NVL2(t.DLDateUAH_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateUAH_kor_grp, DLDateUSD, 
                              SUM(NVL2(t.DLDateUSD, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateUSD_grp, DLDateUSD_kor, 
                              SUM(NVL2(t.DLDateUSD_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateUSD_kor_grp, DLDateUSD_eq, 
                              SUM(NVL2(t.DLDateUSD_eq, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateUSD_eq_grp, DLDateUSD_eq_kor, 
                              SUM(NVL2(t.DLDateUSD_eq_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateUSD_eq_kor_grp, DLDateEUR, 
                              SUM(NVL2(t.DLDateEUR, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateEUR_grp, DLDateEUR_kor, 
                              SUM(NVL2(t.DLDateEUR_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateEUR_kor_grp, DLDateEUR_eq,  
                              SUM(NVL2(t.DLDateEUR_eq, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateEUR_eq_grp, DLDateEUR_eq_kor, 
                              SUM(NVL2(t.DLDateEUR_eq_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateEUR_eq_kor_grp, DLDateOther_eq, 
                              SUM(NVL2(t.DLDateOther_eq, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateOther_eq_grp, DLDateOther_eq_kor, 
                              SUM(NVL2(t.DLDateOther_eq_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateOther_eq_kor_grp, DLDateAll_eq, 
                              SUM(NVL2(t.DLDateAll_eq, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateAll_eq_grp, DLDateAll_eq_kor, 
                              SUM(NVL2(t.DLDateAll_eq_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DLDateAll_eq_kor_grp, DUAH, 
                              SUM(NVL2(t.DUAH, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DUAH_grp, DUAH_kor, 
                              SUM(NVL2(t.DUAH_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DUAH_kor_grp, DUSD, 
                              SUM(NVL2(t.DUSD, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DUSD_grp, DUSD_kor, 
                              SUM(NVL2(t.DUSD_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DUSD_kor_grp, DUSD_eq, 
                              SUM(NVL2(t.DUSD_eq, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DUSD_eq_grp, DUSD_eq_kor, 
                              SUM(NVL2(t.DUSD_eq_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DUSD_eq_kor_grp, DEUR, 
                              SUM(NVL2(t.DEUR, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DEUR_grp, DEUR_kor, 
                              SUM(NVL2(t.DEUR_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DEUR_kor_grp, DEUR_eq, 
                              SUM(NVL2(t.DEUR_eq, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DEUR_eq_grp, DEUR_eq_kor, 
                              SUM(NVL2(t.DEUR_eq_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DEUR_eq_kor_grp, DALL, 
                              SUM(NVL2(t.DALL, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DALL_grp, DALL_kor, 
                              SUM(NVL2(t.DALL_kor, 1, 0)) OVER (PARTITION BY contragentid ORDER BY cal_date) DALL_kor_grp 
      
                              FROM 
                              cal c LEFT OUTER JOIN archtab t PARTITION BY (t.contragentid) ON t.arcdate = c.cal_date), 
                              archfirst as (SELECT cal_date, arcdate, contragentid, luah, first_value(luah) 
                              OVER (PARTITION BY contragentid, luah_grp ORDER BY cal_date) new_luah, luah_grp, luah_kor, first_value(luah_kor) 
                              OVER (PARTITION BY contragentid, luah_kor_grp ORDER BY cal_date) new_luah_kor, luah_kor_grp, lusd, first_value(lusd) 
                              OVER (PARTITION BY contragentid, lusd_grp ORDER BY cal_date) new_lusd, lusd_grp, lusd_kor, first_value(lusd_kor) 
                              OVER (PARTITION BY contragentid, lusd_kor_grp ORDER BY cal_date) new_lusd_kor, lusd_kor_grp, lusd_eq, first_value(lusd_eq) 
                              OVER (PARTITION BY contragentid, lusd_eq_grp ORDER BY cal_date) new_lusd_eq, lusd_eq_grp, lusd_eq_kor, first_value(lusd_eq_kor) 
                              OVER (PARTITION BY contragentid, lusd_eq_kor_grp ORDER BY cal_date) new_lusd_eq_kor, lusd_eq_kor_grp , leur, first_value(leur) 
                              OVER (PARTITION BY contragentid, leur_grp ORDER BY cal_date) new_leur, leur_grp, leur_kor, first_value(leur_kor) 
                              OVER (PARTITION BY contragentid, leur_kor_grp ORDER BY cal_date) new_leur_kor, leur_eq_grp, first_value(leur_eq) 
                              OVER (PARTITION BY contragentid, leur_eq_grp ORDER BY cal_date) new_leur_eq, leur_eq_kor, first_value(leur_eq_kor) 
                              OVER (PARTITION BY contragentid, leur_eq_kor_grp ORDER BY cal_date) new_leur_eq_kor, leur_eq_kor_grp, lovrd, first_value(lovrd) 
                              OVER (PARTITION BY contragentid, lovrd_grp ORDER BY cal_date) new_lovrd, lovrd_grp, lovrd_kor, first_value(lovrd_kor) 
                              OVER (PARTITION BY contragentid, lovrd_kor_grp ORDER BY cal_date) new_lovrd_kor, lovrd_kor_grp, lsum, first_value(lsum) 
                              OVER (PARTITION BY contragentid, lsum_grp ORDER BY cal_date) new_lsum, lsum_grp, lsum_kor, first_value(lsum_kor) 
                              OVER (PARTITION BY contragentid, lsum_kor_grp ORDER BY cal_date) new_lsum_kor, lsum_kor_grp, DLDateUAH, first_value(DLDateUAH) 
                              OVER (PARTITION BY contragentid, DLDateUAH_grp ORDER BY cal_date) new_DLDateUAH, DLDateUAH_grp, DLDateUAH_kor, first_value(DLDateUAH_kor) 
                              OVER (PARTITION BY contragentid, DLDateUAH_kor_grp ORDER BY cal_date) new_DLDateUAH_kor, DLDateUAH_kor_grp, DLDateUSD, first_value(DLDateUSD) 
                              OVER (PARTITION BY contragentid, DLDateUSD_grp ORDER BY cal_date) new_DLDateUSD, DLDateUSD_grp, DLDateUSD_kor, first_value(DLDateUSD_kor) 
                              OVER (PARTITION BY contragentid, DLDateUSD_kor_grp ORDER BY cal_date) new_DLDateUSD_kor, DLDateUSD_kor_grp, DLDateUSD_eq, first_value(DLDateUSD_eq) 
                              OVER (PARTITION BY contragentid, DLDateUSD_eq_grp ORDER BY cal_date) new_DLDateUSD_eq, DLDateUSD_eq_grp, DLDateUSD_eq_kor, first_value(DLDateUSD_eq_kor) 
                              OVER (PARTITION BY contragentid, DLDateUSD_eq_kor_grp ORDER BY cal_date) new_DLDateUSD_eq_kor, DLDateUSD_eq_kor_grp, DLDateEUR, first_value(DLDateEUR) 
                              OVER (PARTITION BY contragentid, DLDateEUR_grp ORDER BY cal_date) new_DLDateEUR, DLDateEUR_grp, DLDateEUR_kor, first_value(DLDateEUR_kor) 
                              OVER (PARTITION BY contragentid, DLDateEUR_kor_grp ORDER BY cal_date) new_DLDateEUR_kor, DLDateEUR_eq_grp, DLDateEUR_eq, first_value(DLDateEUR_eq) 
                              OVER (PARTITION BY contragentid, DLDateEUR_eq_grp ORDER BY cal_date) new_DLDateEUR_eq, DLDateEUR_eq_kor_grp, DLDateEUR_eq_kor, first_value(DLDateEUR_eq_kor) 
                              OVER (PARTITION BY contragentid, DLDateEUR_eq_kor_grp ORDER BY cal_date) new_DLDateEUR_eq_kor, DLDateOther_eq_grp, DLDateOther_eq, first_value(DLDateOther_eq) 
                              OVER (PARTITION BY contragentid, DLDateOther_eq_grp ORDER BY cal_date) new_DLDateOther_eq, DLDateOther_eq_kor_grp, DLDateOther_eq_kor, first_value(DLDateOther_eq_kor) 
                              OVER (PARTITION BY contragentid, DLDateOther_eq_kor_grp ORDER BY cal_date) new_DLDateOther_eq_kor, DLDateAll_eq_grp, DLDateAll_eq, first_value(DLDateAll_eq) 
                              OVER (PARTITION BY contragentid, DLDateAll_eq_grp ORDER BY cal_date) new_DLDateAll_eq, DLDateAll_eq_kor_grp, DLDateAll_eq_kor, first_value(DLDateAll_eq_kor) 
                              OVER (PARTITION BY contragentid, DLDateAll_eq_kor_grp ORDER BY cal_date) new_DLDateAll_eq_kor, DUAH_grp, DUAH, first_value(DUAH) 
                              OVER (PARTITION BY contragentid, DUAH_grp ORDER BY cal_date) new_DUAH, DUAH_kor_grp, DUAH_kor, first_value(DUAH_kor) 
                              OVER (PARTITION BY contragentid, DUAH_kor_grp ORDER BY cal_date) new_DUAH_kor, DUSD_grp, DUSD, first_value(DUSD) 
                              OVER (PARTITION BY contragentid, DUSD_grp ORDER BY cal_date) new_DUSD, DUSD_kor_grp, DUSD_kor, first_value(DUSD_kor) 
                              OVER (PARTITION BY contragentid, DUSD_kor_grp ORDER BY cal_date) new_DUSD_kor, DUSD_eq_grp, DUSD_eq, first_value(DUSD_eq) 
                              OVER (PARTITION BY contragentid, DUSD_eq_grp ORDER BY cal_date) new_DUSD_eq, DUSD_eq_kor_grp, DUSD_eq_kor, first_value(DUSD_eq_kor) 
                              OVER (PARTITION BY contragentid, DUSD_eq_kor_grp ORDER BY cal_date) new_DUSD_eq_kor, DEUR_grp, DEUR, first_value(DEUR) 
                              OVER (PARTITION BY contragentid, DEUR_grp ORDER BY cal_date) new_DEUR, DEUR_kor_grp, DEUR_kor, first_value(DEUR_kor) 
                              OVER (PARTITION BY contragentid, DEUR_kor_grp ORDER BY cal_date) new_DEUR_kor, DEUR_eq_grp, DEUR_eq, first_value(DEUR_eq) 
                              OVER (PARTITION BY contragentid, DEUR_eq_grp ORDER BY cal_date) new_DEUR_eq, DEUR_eq_kor_grp, DEUR_eq_kor, first_value(DEUR_eq_kor) 
                              OVER (PARTITION BY contragentid, DEUR_eq_kor_grp ORDER BY cal_date) new_DEUR_eq_kor, DALL_grp, DALL, first_value(DALL) 
                              OVER (PARTITION BY contragentid, DALL_grp ORDER BY cal_date) new_DALL, DALL_kor_grp, DALL_kor, first_value(DALL_kor) 
                              OVER (PARTITION BY contragentid, DALL_kor_grp ORDER BY cal_date) new_DALL_kor 
      
                              FROM archgrp) 
      SELECT contragentid, 
             AVG(new_luah) as luah, 
             AVG(new_luah_kor) as luah_kor, 
             AVG(new_lusd) as lusd, 
             AVG(new_lusd_kor) as lusd_kor, 
             AVG(new_lusd_eq) as lusd_eq, 
             AVG(new_lusd_eq_kor) as lusd_eq_kor, 
             AVG(new_leur) as leur, 
             AVG(new_leur_kor) as leur_kor, 
             AVG(new_leur_eq) as leur_eq, 
             AVG(new_leur_eq_kor) as leur_eq_kor, 
             AVG(new_lovrd) as lovrd, 
             AVG(new_lovrd_kor) as lovrd_kor, 
             AVG(new_lsum) as new_lsum, 
             AVG(new_lsum_kor) as lsum_kor, 
             AVG(new_dldateuah) as dldateuah, 
             AVG(new_DLDateUAH_kor) as DLDateUAH_kor, 
             AVG(new_DLDateUSD) as DLDateUSD, 
             AVG(new_DLDateUSD_kor) as DLDateUSD_kor, 
             AVG(new_DLDateUSD_eq) as DLDateUSD_eq, 
             AVG(new_DLDateUSD_eq_kor) as DLDateUSD_eq_kor, 
             AVG(new_DLDateEUR) as DLDateEUR, 
             AVG(new_DLDateEUR_kor) as DLDateEUR_kor, 
             AVG(new_DLDateEUR_eq) as DLDateEUR_eq, 
             AVG(new_DLDateEUR_eq_kor) as DLDateEUR_eq_kor, 
             AVG(new_DLDateOther_eq) as DLDateOther_eq, 
             AVG(new_DLDateOther_eq_kor) as DLDateOther_eq_kor, 
             AVG(new_DLDateAll_eq) as DLDateAll_eq, 
             AVG(new_DLDateAll_eq_kor) as DLDateAll_eq_kor, 
             AVG(new_DUAH) as DUAH, 
             AVG(new_DUAH_kor) as DUAH_kor, 
             AVG(new_DUSD) as DUSD, 
             AVG(new_DUSD_kor) as DUSD_kor, 
             AVG(new_DUSD_eq) as DUSD_eq, 
             AVG(new_DUSD_eq_kor) as DUSD_eq_kor, 
             AVG(new_DEUR) as DEUR, 
             AVG(new_DEUR_kor) as DEUR_kor, 
             AVG(new_DEUR_eq) as DEUR_eq, 
             AVG(new_DEUR_eq_kor) as DEUR_eq_kor, 
             AVG(new_DALL) as DALL, 
             AVG(new_DALL_kor) as new_DALL_kor 
       FROM archfirst 
       WHERE cal_date BETWEEN (select arcdate from datbeg) 
                                       AND (select arcdate from datend)
       GROUP BY contragentid 
       ORDER BY contragentid
      Edited by: marco on Nov 28, 2011 6:04 AM
      added the purpose of my sql statement
        • 1. Re: improve sql statement - decrease executing time
          Hoek
          See the links mentioned under
          *3. How to improve the performance of my query? / My query is running slow.*
          SQL and PL/SQL FAQ
          and post the additional details we need.
          • 2. Re: improve sql statement - decrease executing time
            marco
            select * from v$version
            
            1     Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
            2     PL/SQL Release 11.2.0.2.0 - Production                                      
            3     CORE     11.2.0.2.0     Production                                                  
            4     TNS for 64-bit Windows: Version 11.2.0.2.0 - Production                     
            5     NLSRTL Version 11.2.0.2.0 - Production
            • 3. Re: improve sql statement - decrease executing time
              marco
              My explain plan:
              http://www.mediafire.com/?qna2fff72eiudzl
              (don't know how to post xml data here, so I zipped it).
              • 4. Re: improve sql statement - decrease executing time
                6363
                Hi Marco, thanks for providing example data and explain plan. Unfortunately for a tuning request example data is not useful which is why the tuning thread requests explain plan and trace / tkprof output and has step by step instructions on how to format and post them.

                SQL and PL/SQL FAQ

                You should probably read them again, there is no mention of XML.
                • 5. Re: improve sql statement - decrease executing time
                  marco
                  Plan hash value: 46702699                                                                                                                      
                                                                                                                                                                 
                  -----------------------------------------------------------------------------------------------------------------------------------------------
                  | Id  | Operation                                                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
                  -----------------------------------------------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT                                             |                                |     1 |   534 |   925   (5)| 00:00:01 |
                  |   1 |  SORT GROUP BY                                               |                                |     1 |   534 |   925   (5)| 00:00:01 |
                  |*  2 |   VIEW                                                       |                                |   274 |   142K|   920   (5)| 00:00:01 |
                  |   3 |    WINDOW SORT                                               |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |   4 |     WINDOW SORT                                              |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |   5 |      WINDOW SORT                                             |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |   6 |       WINDOW SORT                                            |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |   7 |        WINDOW SORT                                           |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |   8 |         WINDOW SORT                                          |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |   9 |          WINDOW SORT                                         |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  10 |           WINDOW SORT                                        |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  11 |            WINDOW SORT                                       |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  12 |             WINDOW SORT                                      |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  13 |              WINDOW SORT                                     |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  14 |               WINDOW SORT                                    |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  15 |                WINDOW SORT                                   |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  16 |                 WINDOW SORT                                  |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  17 |                  WINDOW SORT                                 |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  18 |                   WINDOW SORT                                |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  19 |                    WINDOW SORT                               |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  20 |                     WINDOW SORT                              |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  21 |                      WINDOW SORT                             |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  22 |                       WINDOW SORT                            |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  23 |                        WINDOW SORT                           |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  24 |                         WINDOW SORT                          |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  25 |                          WINDOW SORT                         |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  26 |                           WINDOW SORT                        |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  27 |                            WINDOW SORT                       |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  28 |                             WINDOW SORT                      |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  29 |                              WINDOW SORT                     |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  30 |                               WINDOW SORT                    |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  31 |                                WINDOW SORT                   |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  32 |                                 WINDOW SORT                  |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  33 |                                  WINDOW SORT                 |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  34 |                                   WINDOW SORT                |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  35 |                                    WINDOW SORT               |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  36 |                                     WINDOW SORT              |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  37 |                                      WINDOW SORT             |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  38 |                                       WINDOW SORT            |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  39 |                                        WINDOW SORT           |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  40 |                                         WINDOW SORT          |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  41 |                                          WINDOW SORT         |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  42 |                                           WINDOW SORT        |                                |   274 |   282K|   920   (5)| 00:00:01 |
                  |  43 |                                            VIEW              |                                |   274 |   282K|   880   (1)| 00:00:01 |
                  |  44 |                                             WINDOW BUFFER    |                                |   274 |   142K|   880   (1)| 00:00:01 |
                  |  45 |                                              VIEW            |                                |   274 |   142K|   879   (1)| 00:00:01 |
                  |  46 | TITION OUTER                                  MERGE JOIN PAR |                                |   274 |   144K|   879   (1)| 00:00:01 |
                  |  47 |                                                SORT JOIN     |                                |   274 |  2192 |     8  (13)| 00:00:01 |
                  |  48 |                                                 VIEW         |                                |   274 |  2192 |     7   (0)| 00:00:01 |
                  |* 49 |  SCAN                                            INDEX RANGE | PK_CALENDAR                    |   274 |  2192 |     3   (0)| 00:00:01 |
                  |  50 |                                                   FAST DUAL  |                                |     1 |       |     2   (0)| 00:00:01 |
                  |  51 |                                                   FAST DUAL  |                                |     1 |       |     2   (0)| 00:00:01 |
                  |* 52 | N JOIN                                         SORT PARTITIO |                                |     1 |   533 |   870   (1)| 00:00:01 |
                  |  53 |                                                 VIEW         |                                |     1 |   533 |   869   (1)| 00:00:01 |
                  |  54 | S SEMI                                           NESTED LOOP |                                |     6 |   930 |   695   (1)| 00:00:01 |
                  |* 55 | RIGHT SEMI                                        HASH JOIN  |                                |     6 |   900 |   683   (1)| 00:00:01 |
                  |  56 |                                                    VIEW      | VW_NSO_1                       |   105 |  1365 |     2   (0)| 00:00:01 |
                  |* 57 | OPKEY                                               COUNT ST |                                |       |       |            |          |
                  |  58 | AST FULL SCAN                                        INDEX F | PK_CONTRAGENT                  |   107 |   535 |     2   (0)| 00:00:01 |
                  |  59 | ESS BY INDEX ROWID                                 TABLE ACC | REP184_MEDIATE                 |   362 | 49594 |   681   (1)| 00:00:01 |
                  |* 60 | NGE SCAN                                            INDEX RA | UK_REP184_CONTRAGENTID_ARCDATE |   652 |       |    57   (0)| 00:00:01 |
                  |  61 | LOOPS                                                NESTED  |                                |       |       |            |          |
                  |  62 |  LOOPS                                                NESTED |                                |     1 |    12 |     3   (0)| 00:00:01 |
                  |* 63 |  RANGE SCAN                                            INDEX | UNQ_CONTRAGENT_ID_SITE_TYPEID  |     1 |     7 |     2   (0)| 00:00:01 |
                  |* 64 |  UNIQUE SCAN                                           INDEX | PK_CONTRAGENTTYPE              |     1 |       |     0   (0)| 00:00:01 |
                  |* 65 | ACCESS BY INDEX ROWID                                 TABLE  | CONTRAGENTTYPE                 |     1 |     5 |     1   (0)| 00:00:01 |
                  |  66 | AL                                                   FAST DU |                                |     1 |       |     2   (0)| 00:00:01 |
                  |  67 | AL                                                   FAST DU |                                |     1 |       |     2   (0)| 00:00:01 |
                  |* 68 | E SCAN                                            INDEX RANG | I_AACCOUNT_CONTRAGID           |   951K|  4645K|     2   (0)| 00:00:01 |
                  |  69 |    FAST DUAL                                                 |                                |     1 |       |     2   (0)| 00:00:01 |
                  |  70 |    FAST DUAL                                                 |                                |     1 |       |     2   (0)| 00:00:01 |
                  -----------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                                 
                  Predicate Information (identified by operation id):                                                                                            
                  ---------------------------------------------------                                                                                            
                                                                                                                                                                 
                     2 - filter("CAL_DATE">= (SELECT TO_DATE(' 2011-10-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL1") AND                    
                                "CAL_DATE"<= (SELECT TO_DATE(' 2011-10-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL2"))                       
                    49 - access("ARCDATE">= (SELECT TO_DATE(' 2011-10-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL1")-10 AND                  
                                "ARCDATE"<= (SELECT TO_DATE(' 2011-10-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL2")+1)                      
                    52 - access("T"."ARCDATE"="C"."CAL_DATE")                                                                                                    
                         filter("T"."ARCDATE"="C"."CAL_DATE")                                                                                                    
                    55 - access("CONTRAGENTID"="ID")                                                                                                             
                    57 - filter(ROWNUM<=105)                                                                                                                     
                    60 - access("J1"."ARCDATE">= (SELECT TO_DATE(' 2011-10-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL1")-10 AND             
                                "J1"."ARCDATE"<= (SELECT TO_DATE(' 2011-10-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL2")+1)                 
                         filter( EXISTS (SELECT 0 FROM "CREATOR"."CONTRAGENT" "D","CREATOR"."CONTRAGENTTYPE" "CT" WHERE                                          
                                "D"."CONTRAGENTTYPEID"="CT"."CID" AND ("CT"."OFFICIALTYPE"=2 OR "CT"."OFFICIALTYPE"=3) AND "D"."ID"=:B1))                        
                    63 - access("D"."ID"=:B1)                                                                                                                    
                    64 - access("D"."CONTRAGENTTYPEID"="CT"."CID")                                                                                               
                    65 - filter("CT"."OFFICIALTYPE"=2 OR "CT"."OFFICIALTYPE"=3)                                                                                  
                    68 - access("CONTRAGENTID"="J1"."CONTRAGENTID")                                                                                              
                  • 6. Re: improve sql statement - decrease executing time
                    BrendanP
                    You don't say how long it takes on real data - it could be 1 s or 1000 s?

                    I would look to design something as complex as that, using diagrams principally - you will then likely find alternative structures occur to you that might work better.
                    • 7. Re: improve sql statement - decrease executing time
                      marco
                      BrendanP,
                      when I perform my statement for 1 month (arcdate between date '2011-09-01' and date '2011-09-30') - it executes about 3380 sec.
                      • 8. Re: improve sql statement - decrease executing time
                        sb92075
                        (arcdate between date '2011-09-01' and date '2011-09-30')
                        With Oracle characters between single quote marks are STRINGS!
                        'This is a string, 2009-12-31, not a date'
                        When a DATE datatype is desired, then use TO_DATE() function.
                        • 9. Re: improve sql statement - decrease executing time
                          Hoek
                          SB, OP is using the DATE alternative instead of TO_DATE.
                          date '2011-09-01'
                          will return a DATE.
                          • 10. Re: improve sql statement - decrease executing time
                            sb92075
                            hoek wrote:
                            SB, OP is using the DATE alternative instead of TO_DATE.
                            date '2011-09-01'
                            will return a DATE.
                            THANKS!
                            I am still on my first cup of coffee & need to wake up a bit more.
                            • 11. Re: improve sql statement - decrease executing time
                              Hoek
                              In that case: Good Morning ;)
                              • 12. Re: improve sql statement - decrease executing time
                                marco
                                Waiting for dba in order to get trace file...

                                Edited by: marco on Nov 28, 2011 9:33 AM
                                • 13. Re: improve sql statement - decrease executing time
                                  marco
                                  I've got no tkprof, I use oraSRP instead. Please find my session resource profile using link below, hope it helps.

                                  http://www.mediafire.com/?wecp63k6mikcr6m

                                  As it exceeds the maximum length of 30000 characters, I can't post it as plain text here.
                                  Please, be so kind, help me improve performance.
                                  • 14. Re: improve sql statement - decrease executing time
                                    Charles Hooper
                                    marco wrote:
                                    I've got no tkprof, I use oraSRP instead. Please find my session resource profile using link below, hope it helps.

                                    http://www.mediafire.com/?wecp63k6mikcr6m

                                    As it exceeds the maximum length of 30000 characters, I can't post it as plain text here.
                                    Please, be so kind, help me improve performance.
                                    Marco,

                                    That is one confusing SQL statement! A brief outline of the query with the dependent child views and tables indented:
                                    Main query
                                     archfirst - WHERE cal_date BETWEEN (select arcdate from datbeg) AND (select arcdate from datend)
                                      archgrp
                                       cal c 
                                        creator.calendar
                                        datend
                                         select date '2011-10-20' as arcdate from dual
                                       LEFT OUTER JOIN archtab t PARTITION BY (t.contragentid) ON t.arcdate = c.cal_date)
                                        ZOO.REP184_MEDIATE J1
                                         acc q where q.contragentid = J1.contragentid
                                         creator.contragent where rownum <= 15
                                         creator.contragenttype ct, creator.contragent d where ct.officialtype in (2,3) and d.contragenttypeid=ct.cid and d.id = J1.contragentid
                                         datbeg
                                          select date '2011-09-21' as arcdate from dual
                                         datend
                                          select date '2011-10-20' as arcdate from dual
                                    It appears that the primary problem is that the query hides information from the optimizer (the start and end dates in the datbeg and datend views, for instance), resulting in the optimizer grossly underestimating the number of rows that will be returned. For instance, in one of the two execution plans the optimizer determined that 1 row would be returned, when in fact 1,340,102 rows were returned. That underestimate likely resulted in several poor choices in the execution plan.

                                    Problems
                                    * Mixing ANSI joins (LEFT OUTER JOIN) with Oracle type joins (d.id = J1.contragentid)
                                    * Hiding information from the optimizer by placing the dates that are used as restrictions into separate SELECT statements rather than specifying the dates directly
                                    * Using "contragentid in" when the subquery contains "where rownum <= 15" - this results in a potentially non-deterministic result, and could potentially change the optimizer mode to FIRST_ROWS(15) - this is less likely to be an issue in Oracle Database 11.2.0.2 (and possibly 11.2.0.1) or higher.
                                    * The optimizer is significantly underestimating the number of rows returned by various operations in the execution plan, possibly due to hiding the specific dates in separate SELECT statements. There seems to be 2 execution plans for this SQL statement.

                                    A small portion of one of the execution plans that shows the estimate problem:
                                    Est     Actual
                                      1        780  SORT PARTITION JOIN (cr=5,440,798 pr=0 pw=0 time=10.4893s cost=947 size=533 card=1)
                                      1        780    VIEW  (cr=5,440,798 pr=0 pw=0 time=0.1546s cost=946 size=533 card=1)
                                      1        780      NESTED LOOPS SEMI (cr=5,440,798 pr=0 pw=0 time=0.1545s cost=699 size=1,240 card=8)
                                      8        780        HASH JOIN RIGHT SEMI (cr=5,440,747 pr=0 pw=0 time=0.1530s cost=683 size=1,200 card=8)
                                    150        150          VIEW  VW_NSO_1 (cr=7 pr=0 pw=0 time=0.0002s cost=2 size=1,950 card=150)
                                               150            COUNT STOPKEY (cr=7 pr=0 pw=0 time=0.0000s)
                                    150        150              INDEX FAST FULL SCAN PK_CONTRAGENT (cr=7 pr=0 pw=0 time=0.0000s cost=2 size=750 card=150) [179401]
                                    362  1,369,798          TABLE ACCESS BY INDEX ROWID REP184_MEDIATE (cr=5,440,740 pr=0 pw=0 time=10.3006s cost=681 size=49,594 card=362) [430474]
                                    652  1,369,798            INDEX RANGE SCAN UK_REP184_CONTRAGENTID_ARCDATE (cr=4,099,373 pr=0 pw=0 time=7.8966s cost=57 size=0 card=652) [430488]
                                         1,340,102              NESTED LOOPS  (cr=4,093,445 pr=0 pw=0 time=6.0650s)
                                      1  1,340,102                NESTED LOOPS  (cr=2,753,343 pr=0 pw=0 time=4.0375s cost=3 size=12 card=1)
                                      1  1,340,102                  INDEX RANGE SCAN UNQ_CONTRAGENT_ID_SITE_TYPEID (cr=1,413,241 pr=0 pw=0 time=2.3707s cost=2 size=7 card=1) [183909]
                                      1  1,340,102                  INDEX UNIQUE SCAN PK_CONTRAGENTTYPE (cr=1,340,102 pr=0 pw=0 time=1.2445s cost=0 size=0 card=1) [182510]
                                      1  1,340,102                TABLE ACCESS BY INDEX ROWID CONTRAGENTTYPE (cr=1,340,102 pr=0 pw=0 time=1.4637s cost=1 size=5 card=1) [55489]
                                                 1              FAST DUAL  (cr=0 pr=0 pw=0 time=0.0000s cost=2 size=0 card=1)
                                                 1              FAST DUAL  (cr=0 pr=0 pw=0 time=0.0000s cost=2 size=0 card=1)
                                     1M         55        INDEX RANGE SCAN I_AACCOUNT_CONTRAGID (cr=51 pr=0 pw=0 time=0.0002s cost=2 size=4,757,150 card=951,430) [53431]
                                    A small portion of the second execution plan that shows the estimate problem - note that the execution plan is different
                                    Est     Actual
                                      1         90  SORT PARTITION JOIN (cr=259 pr=0 pw=0 time=0.0021s cost=528 size=533 card=1)
                                      1         90    VIEW  (cr=259 pr=0 pw=0 time=0.0014s cost=527 size=533 card=1)
                                                90      FILTER  (cr=259 pr=0 pw=0 time=0.0014s)
                                     16         90        NESTED LOOPS SEMI (cr=247 pr=0 pw=0 time=0.0012s cost=498 size=2,480 card=16)
                                     16         90          NESTED LOOPS  (cr=239 pr=0 pw=0 time=0.0011s cost=466 size=2,400 card=16)
                                     15         15            VIEW  VW_NSO_1 (cr=7 pr=0 pw=0 time=0.0005s cost=2 size=195 card=15)
                                                15              HASH UNIQUE (cr=7 pr=0 pw=0 time=0.0005s)
                                                15                COUNT STOPKEY (cr=7 pr=0 pw=0 time=0.0000s)
                                     15         15                  INDEX FAST FULL SCAN PK_CONTRAGENT (cr=7 pr=0 pw=0 time=0.0000s cost=2 size=75 card=15) [179401]
                                      1         90            TABLE ACCESS BY INDEX ROWID REP184_MEDIATE (cr=232 pr=0 pw=0 time=0.0018s cost=64 size=137 card=1) [430474]
                                     61        192              INDEX RANGE SCAN IX_REP184_MED_CONTRAGENTID (cr=40 pr=0 pw=0 time=0.0002s cost=2 size=0 card=61) [430487]
                                      1          1              FAST DUAL  (cr=0 pr=0 pw=0 time=0.0000s cost=2 size=0 card=1)
                                      1          1              FAST DUAL  (cr=0 pr=0 pw=0 time=0.0000s cost=2 size=0 card=1)
                                     1M          3          INDEX RANGE SCAN I_AACCOUNT_CONTRAGID (cr=8 pr=0 pw=0 time=0.0000s cost=2 size=4,757,150 card=951,430) [53431]
                                                 3        NESTED LOOPS  (cr=12 pr=0 pw=0 time=0.0000s)
                                      1          3          NESTED LOOPS  (cr=9 pr=0 pw=0 time=0.0000s cost=3 size=12 card=1)
                                      1          3            INDEX RANGE SCAN UNQ_CONTRAGENT_ID_SITE_TYPEID (cr=6 pr=0 pw=0 time=0.0000s cost=2 size=7 card=1) [183909]
                                      1          3            INDEX UNIQUE SCAN PK_CONTRAGENTTYPE (cr=3 pr=0 pw=0 time=0.0000s cost=0 size=0 card=1) [182510]
                                      1          3          TABLE ACCESS BY INDEX ROWID CONTRAGENTTYPE (cr=3 pr=0 pw=0 time=0.0000s cost=1 size=5 card=1) [55489]
                                    Hopefully, someone else will be able to offer more advice. I would start by unwinding the views a bit by removing the datbeg and datend views and put those date values directly into the SQL statements where the views are currently referenced. Considering that the dates are hard-coded as constants in the example SQL statement, do those dates in fact change from one execution to the next (maybe excessive time required for hard parsing is a potential issue)?

                                    Charles Hooper
                                    http://hoopercharles.wordpress.com/
                                    IT Manager/Oracle DBA
                                    K&M Machine-Fabricating, Inc.
                                    1 2 Previous Next