9 Replies Latest reply: Nov 21, 2012 8:10 AM by 772315 RSS

    Query that becomes slower and slower when runned several times

    772315
      Hi everybody,

      I have an issue with one querie...
      I have a select that is a join between 3 subqueries, 2 of them are using a function with pipeline.

      When I run this query it work fine.
      * First run lasts less than 1 sec.
      * Second run lasts less than 1 sec.
      * Third run lasts 2 sec.
      * Fourth run lasts 2 min.
      * Fifth run lasts 10 min.
      * ... and then worse an worse...

      Of course, I always use same bind variables and data within the database does not change.
      I looked at the execution plan that seems to be the same for all the runs.

      The problem is during the Execute phase :

      for the first run :
      ######################

      call count cpu elapsed disk query current rows
      Parse 1 0.00 0.00 0 0 0 0
      Execute 1 0.20 0.20 0 1215 0 0
      Fetch 3 0.12 0.14 0 246 0 19
      total 5 0.33 0.36 0 1461 0 19

      Misses in library cache during parse: 1
      Misses in library cache during execute: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 66


      for the fifth run
      ######################
      call count cpu elapsed disk query current rows
      Parse 1 0.01 0.01 0 0 0 0
      Execute 1 95.29 100.96 0 1215 0 0
      Fetch 3 0.27 0.39 0 246 0 19
      total 5 95.57 101.36 0 1461 0 19

      Misses in library cache during parse: 1
      Misses in library cache during execute: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 66

      I didn't find dynamic sampling during the execution plan.
      All subqueries are very stable in time.
      When the join query starts to have a strange behavior, subqueries are still very perfomant !
      If I flush the shared pool, the problem disappear and the cycle restarts...

      1) any idea of the root cause of the issue ?
      2) is there a solution (with hint for instance) for not recording the query inthe shared pool ?

      Thanks in advance...

      The query is :

      select
      a.anneefiscale,
      a.marque,
      a.priorite,
      a.de,
      b.eng_et_real_total "eng et real annee moins 1",
      c.estime "estime",
      a.engage_nip "engage nip",
      a.engage_autre "engage autre",
      a.engage_total "engage total",
      a.realise_nip "realise nip",
      a.realise_autre "realise autre",
      a.realise_total "realise total",
      a.eng_et_real_nip "eng et real nip",
      a.eng_et_real_autre "eng et real autre",
      a.eng_et_real_total "eng et real total",
      decode(c.estime,0,NULL,round(a.eng_et_real_total/c.estime*100,1)) "pourcentage"
      from
      (
      select
      anneefiscale,
      marque,
      priorite,
      de,
      round(sum(engage_nip)) engage_nip,
      round(sum(engage_autre)) engage_autre,
      round(sum(engage_nip)+sum(engage_autre)) engage_total,
      round(sum(realise_nip)) realise_nip,
      round(sum(realise_autre)) realise_autre,
      round(sum(realise_nip)+sum(realise_autre)) realise_total,
      round(sum(engage_nip)+sum(realise_nip)) eng_et_real_nip,
      round(sum(engage_autre)+ sum(realise_autre)) eng_et_real_autre,
      round(sum(engage_nip)+sum(engage_autre)+sum(realise_nip)+sum(realise_autre)) eng_et_real_total
      from
      (
      select * from table(mypackage.reporting_tbordmarque_promo(:P50_RESEAU,:P50_DE,:P50_GROUPE,:P50_ANNEE_FISCALE))
      union
      select * from table(mypackage.reporting_tbordmarque_nip(:P50_RESEAU,:P50_DE,:P50_GROUPE,:P50_ANNEE_FISCALE))
      union
      select * from table(mypackage.reporting_tbordmarque_eng(:P50_RESEAU,:P50_DE,:P50_GROUPE,:P50_ANNEE_FISCALE))
      )
      group by anneefiscale, marque, priorite, de
      ) a,
      (
      select
      anneefiscale,
      marque,
      de,
      round(sum(engage_nip)+sum(engage_autre)+sum(realise_nip)+sum(realise_autre)) eng_et_real_total
      from
      (
      select * from table(mypackage.reporting_tbordmarque_promo(:P50_RESEAU,:P50_DE,:P50_GROUPE,mypackage.get_previous_annee_fiscale(:P50_ANNEE_FISCALE)))
      union
      select * from table(mypackage.reporting_tbordmarque_nip(:P50_RESEAU,:P50_DE,:P50_GROUPE,mypackage.get_previous_annee_fiscale(:P50_ANNEE_FISCALE)))
      union
      select * from table(mypackage.reporting_tbordmarque_eng(:P50_RESEAU,:P50_DE,:P50_GROUPE,mypackage.get_previous_annee_fiscale(:P50_ANNEE_FISCALE)))
      )
      group by anneefiscale, marque, de
      ) b,
      (
      select
      annee_fiscale.nom anneefiscale,
      marque.nom marque,
      de.nom de,
      round(sum(montant)) estime
      from
      estime,
      annee_fiscale,
      marque,
      enseigne,
      groupe,
      de,
      reseau
      where
      estime.id_annee_fiscale_fk = annee_fiscale.id_annee_fiscale and
      estime.id_marque_fk = marque.id_marque and
      estime.id_enseigne_fk = enseigne.id_enseigne and
      estime.id_type_estime_fk = :P50_TYPE_ESTIME and
      enseigne.id_groupe_fk = groupe.id_groupe and
      groupe.id_de_fk = de.id_de and
      de.id_reseau_fk = reseau.id_reseau and
      (:P50_ANNEE_FISCALE is NULL OR :P50_ANNEE_FISCALE=0 OR estime.id_annee_fiscale_fk = :P50_ANNEE_FISCALE) and
      (:P50_RESEAU is NULL OR :P50_RESEAU=0 OR reseau.id_reseau = :P50_RESEAU) and
      (:P50_DE is NULL OR :P50_DE=0 OR de.id_de = :P50_RESEAU)
      group by annee_fiscale.nom, marque.nom, de.nom
      ) c
      where
      a.marque=b.marque(+) and a.de=b.de(+) and
      a.marque=c.marque(+) and a.de=c.de(+)
      order by
      a.anneefiscale, a.marque, a.de;

      Edited by: user7499092 on 17 nov. 2012 05:50

      Edited by: user7499092 on 17 nov. 2012 05:50
        • 1. Re: Query that becomes slower and slower when runned several times
          Nikolay Savvinov
          Hi,

          welcome to the forum!

          1) can you enable waits when tracing? during the 5th execution, the execute phase took 95 seconds, but there was no disk I/O and only 1215 consistent gets. It doesn't really make any sense. maybe the session was waiting on something? although that's unlikely given that cpu time is over 100 seconds, but I would check just the same
          2) did you check if the database was okay? could it be that for example the database was extremely busy on CPU because of some concurrent activity, and the situation was degrading as you were conducting your tests? given your symptoms, this looks like the most likely hypothesis

          Best regards,
          Nikolay

          P.S. Please place formatted text (like SQL code) between
           tags to preserve the formatting                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: Query that becomes slower and slower when runned several times
            772315
            Hi Nikolay

            Concerning point 2, the database seems perfectly OK and I encountered an issue only with this query :-(
            The database is hosted on a dedicated server and I am the only one user. Moreover the issue is reproducible as I want... Very strange for me...

            I am very interested on a method to check waits and more precisely IO waits. Can you describe me a procedure ?
            The only one thing I can produce is the tkprof output focused on the fifth run that is very long...
            TKPROF: Release 11.2.0.1.0 - Development on Sat Nov 17 04:45:56 2012
            
            Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
            
            Trace file: MYDB_ora_3493.trc
            Sort options: default
            
            ********************************************************************************
            count    = number of times OCI procedure was executed
            cpu      = cpu time in seconds executing 
            elapsed  = elapsed time in seconds executing
            disk     = number of physical reads of buffers from disk
            query    = number of buffers gotten for consistent read
            current  = number of buffers gotten in current mode (usually for update)
            rows     = number of rows processed by the fetch or execute call
            ********************************************************************************
            
            select
            a.anneefiscale,
            a.marque,
            a.priorite,
            a.de,
            b.eng_et_real_total "eng et real annee moins 1",
            c.estime "estime",
            a.engage_nip "engage nip",
            a.engage_autre "engage autre",
            a.engage_total "engage total",
            a.realise_nip "realise nip",
            a.realise_autre "realise autre",
            a.realise_total "realise total",
            a.eng_et_real_nip "eng et real nip",
            a.eng_et_real_autre "eng et real autre",
            a.eng_et_real_total "eng et real total",
            decode(c.estime,0,NULL,round(a.eng_et_real_total/c.estime*100,1)) "pourcentage"
            from
            (
              select
              anneefiscale,
              marque,
              priorite,
              de,
              round(sum(engage_nip)) engage_nip,
              round(sum(engage_autre)) engage_autre,
              round(sum(engage_nip)+sum(engage_autre)) engage_total,
              round(sum(realise_nip)) realise_nip,
              round(sum(realise_autre)) realise_autre,
              round(sum(realise_nip)+sum(realise_autre)) realise_total,
              round(sum(engage_nip)+sum(realise_nip)) eng_et_real_nip,
              round(sum(engage_autre)+ sum(realise_autre)) eng_et_real_autre,
              round(sum(engage_nip)+sum(engage_autre)+sum(realise_nip)+sum(realise_autre)) eng_et_real_total
              from
              (
                select * from table(mypackage.reporting_tbordmarque_promo(:P50_RESEAU,:P50_DE,:P50_GROUPE,:P50_ANNEE_FISCALE))
                union
                select * from table(mypackage.reporting_tbordmarque_nip(:P50_RESEAU,:P50_DE,:P50_GROUPE,:P50_ANNEE_FISCALE))
                union
                select * from table(mypackage.reporting_tbordmarque_eng(:P50_RESEAU,:P50_DE,:P50_GROUPE,:P50_ANNEE_FISCALE))
              )
              group by anneefiscale, marque, priorite, de
            ) a,
            (
              select
              anneefiscale,
              marque,
              de,
              round(sum(engage_nip)+sum(engage_autre)+sum(realise_nip)+sum(realise_autre)) eng_et_real_total
              from
              (
                select * from table(mypackage.reporting_tbordmarque_promo(:P50_RESEAU,:P50_DE,:P50_GROUPE,mypackage.get_previous_annee_fiscale(:P50_ANNEE_FISCALE)))
                union
                select * from table(mypackage.reporting_tbordmarque_nip(:P50_RESEAU,:P50_DE,:P50_GROUPE,mypackage.get_previous_annee_fiscale(:P50_ANNEE_FISCALE)))
                union
                select * from table(mypackage.reporting_tbordmarque_eng(:P50_RESEAU,:P50_DE,:P50_GROUPE,mypackage.get_previous_annee_fiscale(:P50_ANNEE_FISCALE)))
              )
              group by anneefiscale, marque, de
            ) b,
            (
              select
                annee_fiscale.nom anneefiscale,
                marque.nom marque,
                de.nom de,
                round(sum(montant)) estime
              from
                estime,
                annee_fiscale,
                marque,
                enseigne,
                groupe,
                de,
                reseau
              where
                estime.id_annee_fiscale_fk = annee_fiscale.id_annee_fiscale and
                estime.id_marque_fk = marque.id_marque and
                estime.id_enseigne_fk = enseigne.id_enseigne and
                estime.id_type_estime_fk = :P50_TYPE_ESTIME and
                enseigne.id_groupe_fk = groupe.id_groupe and
                groupe.id_de_fk = de.id_de and
                de.id_reseau_fk = reseau.id_reseau and
                (:P50_ANNEE_FISCALE is NULL OR :P50_ANNEE_FISCALE=0 OR estime.id_annee_fiscale_fk = :P50_ANNEE_FISCALE) and
                (:P50_RESEAU is NULL OR :P50_RESEAU=0 OR reseau.id_reseau = :P50_RESEAU) and
                (:P50_DE is NULL OR :P50_DE=0 OR de.id_de = :P50_RESEAU)
                group by annee_fiscale.nom, marque.nom, de.nom
            ) c
            where
              a.marque=b.marque(+) and a.de=b.de(+) and
              a.marque=c.marque(+) and a.de=c.de(+)
            order by
              a.anneefiscale, a.marque, a.de
            
            call     count       cpu    elapsed       disk      query    current        rows
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            Parse        1      0.01       0.01          0          0          0           0
            Execute      1     95.29     100.96          0       1215          0           0
            Fetch        3      0.27       0.39          0        246          0          19
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            total        5     95.57     101.36          0       1461          0          19
            
            Misses in library cache during parse: 1
            Misses in library cache during execute: 1
            Optimizer mode: ALL_ROWS
            Parsing user id: 66  
            
            Rows     Row Source Operation
            -------  ---------------------------------------------------
                 19  SORT ORDER BY (cr=55154 pr=0 pw=0 time=0 us cost=39985 size=175631625 card=60045)
                 19   HASH JOIN RIGHT OUTER (cr=55154 pr=0 pw=0 time=234 us cost=3348 size=175631625 card=60045)
                 19    VIEW  (cr=246 pr=0 pw=0 time=198 us cost=52 size=6233 card=23)
                 19     HASH GROUP BY (cr=246 pr=0 pw=0 time=108 us cost=52 size=1932 card=23)
                230      HASH JOIN  (cr=246 pr=0 pw=0 time=801 us cost=51 size=1932 card=23)
                230       HASH JOIN  (cr=238 pr=0 pw=0 time=114 us cost=48 size=1633 card=23)
                230        HASH JOIN  (cr=230 pr=0 pw=0 time=1259 us cost=45 size=1265 card=23)
                 28         NESTED LOOPS  (cr=8 pr=0 pw=0 time=999 us)
                 28          NESTED LOOPS  (cr=7 pr=0 pw=0 time=135 us cost=8 size=297 card=9)
                  4           NESTED LOOPS  (cr=5 pr=0 pw=0 time=39 us cost=5 size=75 card=3)
                  1            TABLE ACCESS FULL DE (cr=3 pr=0 pw=0 time=0 us cost=4 size=18 card=1)
                  4            TABLE ACCESS BY INDEX ROWID GROUPE (cr=2 pr=0 pw=0 time=33 us cost=1 size=49 card=7)
                  4             INDEX RANGE SCAN GROUPE_DE_FK (cr=1 pr=0 pw=0 time=18 us cost=0 size=0 card=7)(object id 69080)
                 28           INDEX RANGE SCAN ENSEIGNE_GROUPE_FK (cr=2 pr=0 pw=0 time=48 us cost=0 size=0 card=3)(object id 69079)
                 28          TABLE ACCESS BY INDEX ROWID ENSEIGNE (cr=1 pr=0 pw=0 time=0 us cost=1 size=24 card=3)
                852         TABLE ACCESS FULL ESTIME (cr=222 pr=0 pw=0 time=1337 us cost=37 size=11638 card=529)
                  7        VIEW  index$_join$_013 (cr=8 pr=0 pw=0 time=18 us cost=3 size=112 card=7)
                  7         HASH JOIN  (cr=8 pr=0 pw=0 time=18 us)
                  7          INDEX FAST FULL SCAN ANNEE_FISCALE_CON (cr=4 pr=0 pw=0 time=6 us cost=1 size=112 card=7)(object id 68294)
                  7          INDEX FAST FULL SCAN ANNEE_FISCALE_PK (cr=4 pr=0 pw=0 time=6 us cost=1 size=112 card=7)(object id 68293)
                 35       VIEW  index$_join$_014 (cr=8 pr=0 pw=0 time=340 us cost=3 size=455 card=35)
                 35        HASH JOIN  (cr=8 pr=0 pw=0 time=272 us)
                 35         INDEX FAST FULL SCAN MARQUE_PK (cr=4 pr=0 pw=0 time=0 us cost=1 size=455 card=35)(object id 68321)
                 35         INDEX FAST FULL SCAN MARQUE_UK (cr=4 pr=0 pw=0 time=102 us cost=1 size=455 card=35)(object id 68322)
                 19    HASH JOIN RIGHT OUTER (cr=54908 pr=0 pw=0 time=216 us cost=3295 size=159359430 card=60045)
                 19     VIEW  (cr=28629 pr=0 pw=0 time=666 us cost=94 size=6640584 card=24504)
                 19      HASH GROUP BY (cr=28629 pr=0 pw=0 time=504 us cost=94 size=7743264 card=24504)
                 41       VIEW  (cr=28629 pr=0 pw=0 time=80 us cost=92 size=7743264 card=24504)
                 41        SORT UNIQUE (cr=28629 pr=0 pw=0 time=40 us cost=92 size=49008 card=24504)
                 42         UNION-ALL  (cr=28629 pr=0 pw=0 time=1066 us)
                 11          COLLECTION ITERATOR PICKLER FETCH REPORTING_TBORDMARQUE_PROMO (cr=13107 pr=0 pw=0 time=230 us cost=29 size=16336 card=8168)
                 18          COLLECTION ITERATOR PICKLER FETCH REPORTING_TBORDMARQUE_NIP (cr=14242 pr=0 pw=0 time=136 us cost=29 size=16336 card=8168)
                 13          COLLECTION ITERATOR PICKLER FETCH REPORTING_TBORDMARQUE_ENG (cr=1280 pr=0 pw=0 time=84 us cost=29 size=16336 card=8168)
                 19     VIEW  (cr=26279 pr=0 pw=0 time=162 us cost=94 size=58393032 card=24504)
                 19      HASH GROUP BY (cr=26279 pr=0 pw=0 time=36 us cost=94 size=56800272 card=24504)
                 42       VIEW  (cr=26279 pr=0 pw=0 time=123 us cost=92 size=56800272 card=24504)
                 42        SORT UNIQUE (cr=26279 pr=0 pw=0 time=82 us cost=92 size=49008 card=24504)
                 42         UNION-ALL  (cr=26279 pr=0 pw=0 time=328 us)
                 13          COLLECTION ITERATOR PICKLER FETCH REPORTING_TBORDMARQUE_PROMO (cr=12209 pr=0 pw=0 time=72 us cost=29 size=16336 card=8168)
                 19          COLLECTION ITERATOR PICKLER FETCH REPORTING_TBORDMARQUE_NIP (cr=13006 pr=0 pw=0 time=108 us cost=29 size=16336 card=8168)
                 10          COLLECTION ITERATOR PICKLER FETCH REPORTING_TBORDMARQUE_ENG (cr=1064 pr=0 pw=0 time=36 us cost=29 size=16336 card=8168)
            
            ********************************************************************************
            
            SQL ID: cb21bacyh3c7d
            Plan Hash: 3452538079
            select metadata 
            from
             kopm$  where name='DB_FDO'
            
            
            call     count       cpu    elapsed       disk      query    current        rows
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            Parse        1      0.00       0.00          0          0          0           0
            Execute      1      0.00       0.00          0          0          0           0
            Fetch        1      0.00       0.00          0          2          0           1
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            total        3      0.00       0.00          0          2          0           1
            
            Misses in library cache during parse: 0
            Optimizer mode: CHOOSE
            Parsing user id: SYS   (recursive depth: 1)
            
            Rows     Row Source Operation
            -------  ---------------------------------------------------
                  1  TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=0 us cost=1 size=108 card=1)
                  1   INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 553)
            
            ********************************************************************************
            
            etc etc etc etc etc
            
            
            ********************************************************************************
            
            OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
            
            call     count       cpu    elapsed       disk      query    current        rows
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            Parse        1      0.01       0.01          0          0          0           0
            Execute      1     95.29     100.96          0       1215          0           0
            Fetch        3      0.27       0.39          0        246          0          19
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            total        5     95.57     101.36          0       1461          0          19
            
            Misses in library cache during parse: 1
            Misses in library cache during execute: 1
            
            
            OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
            
            call     count       cpu    elapsed       disk      query    current        rows
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            Parse       16      0.01       0.00          0          0          0           0
            Execute     23      0.02       0.03          1          5         26           2
            Fetch       33      0.67       0.74          0      54935          0        1447
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            total       72      0.70       0.79          1      54940         26        1449
            
            Misses in library cache during parse: 2
            Misses in library cache during execute: 2
            
               13  user  SQL statements in session.
               11  internal SQL statements in session.
               24  SQL statements in session.
            ********************************************************************************
            Trace file: MYDB_ora_3493.trc
            Trace file compatibility: 11.1.0.7
            Sort options: default
            
                   1  session in tracefile.
                  13  user  SQL statements in trace file.
                  11  internal SQL statements in trace file.
                  24  SQL statements in trace file.
                  12  unique SQL statements in trace file.
                 485  lines in trace file.
                 176  elapsed seconds in trace file.
            Edited by: easyoups on Nov 19, 2012 6:50 AM
            • 3. Re: Query that becomes slower and slower when runned several times
              sb92075
              submit a Bug Report to MOS.
              • 4. Re: Query that becomes slower and slower when runned several times
                Nikolay Savvinov
                Hi
                easyoups wrote:
                Hi Nikolay

                Concerning point 2, the database seems perfectly OK and I encountered an issue only with this query :-(
                The database is hosted on a dedicated server and I am the only one user. Moreover the issue is reproducible as I want... Very strange for me...

                I am very interested on a method to check waits and more precisely IO waits. Can you describe me a procedure ?
                The only one thing I can produce is the tkprof output focused on the fifth run that is very long...
                if you want to look at I/O, then you need to enable waits while tracing (i.e. level 8 or 12). Also, don't use tkprof -- look at the raw trace file because
                this way you'll see the individual wait events and not an aggregation.

                Best regards,
                Nikolay
                • 5. Re: Query that becomes slower and slower when runned several times
                  Iordan Iotzov
                  In addition to waits (IO, locks, etc) , it might be usefult to look at the session statistics.
                  You can use v$sesstat to find you what statistics were changed, and how much was the change during the execution.
                  You can save the content of the view before you run the query so you can compare it with the conent after the query is done.
                  Or you can use Snapper (http://blog.tanelpoder.com/files/scripts/snapper.sql) or similar free utilities.

                  Iordan Iotzov
                  http://iiotzov.wordpress.com/
                  • 6. Re: Query that becomes slower and slower when runned several times
                    Nikolay Savvinov
                    Hi,

                    your problem is a difficult one because the time is spent on CPU, and therefore there cannot be any additional detail about where this time is spent from the database side. If you have the access to the server, then you can use an OS tool to trace the call stack and see what's going on (e.g. pstack or DTrace on Solaris). Otherwise I'm afraid the only option may be submitting an SR with Oracle.

                    Best regards,
                    Nikolay
                    • 7. Re: Query that becomes slower and slower when runned several times
                      Peter vd Zwan
                      Hi,

                      Below is your query formated:
                      SELECT
                        a.anneefiscale
                      , a.marque
                      , a.priorite
                      , a.de
                      , b.eng_et_real_total "eng et real annee moins 1"
                      , c.estime "estime"
                      , a.engage_nip "engage nip"
                      , a.engage_autre "engage autre"
                      , a.engage_total "engage total"
                      , a.realise_nip "realise nip"
                      , a.realise_autre "realise autre"
                      , a.realise_total "realise total"
                      , a.eng_et_real_nip "eng et real nip"
                      , a.eng_et_real_autre "eng et real autre"
                      , a.eng_et_real_total "eng et real total"
                      , DECODE(c.estime,0,NULL,ROUND(a.eng_et_real_total/c.estime*100,1)) pourcentage
                      FROM
                        (
                          SELECT
                            anneefiscale
                          , marque
                          , priorite
                          , de
                          , ROUND(SUM(engage_nip)) engage_nip
                          , ROUND(SUM(engage_autre)) engage_autre
                          , ROUND(SUM(engage_nip)+SUM(engage_autre)) engage_total
                          , ROUND(SUM(realise_nip)) realise_nip
                          , ROUND(SUM(realise_autre)) realise_autre
                          , ROUND(SUM(realise_nip) +SUM(realise_autre)) realise_total
                          , ROUND(SUM(engage_nip)  +SUM(realise_nip)) eng_et_real_nip
                          , ROUND(SUM(engage_autre)+ SUM(realise_autre)) eng_et_real_autre
                          , ROUND(SUM(engage_nip)  +SUM(engage_autre)+SUM(realise_nip)+SUM(
                            realise_autre)) eng_et_real_total
                          FROM
                            (
                              SELECT
                                *
                              FROM
                                TABLE(mypackage.reporting_tbordmarque_promo(:P50_RESEAU,:P50_DE,
                                :P50_GROUPE,:P50_ANNEE_FISCALE))
                              UNION
                              SELECT
                                *
                              FROM
                                TABLE(mypackage.reporting_tbordmarque_nip(:P50_RESEAU,:P50_DE,
                                :P50_GROUPE,:P50_ANNEE_FISCALE))
                              UNION
                              SELECT
                                *
                              FROM
                                TABLE(mypackage.reporting_tbordmarque_eng(:P50_RESEAU,:P50_DE,
                                :P50_GROUPE,:P50_ANNEE_FISCALE))
                            )
                          GROUP BY
                            anneefiscale
                          , marque
                          , priorite
                          , de
                        ) a
                      , (
                          SELECT
                            anneefiscale
                          , marque
                          , de
                          , ROUND(SUM(engage_nip)+SUM(engage_autre)+SUM(realise_nip)+SUM(
                            realise_autre)) eng_et_real_total
                          FROM
                            (
                              SELECT
                                *
                              FROM
                                TABLE(mypackage.reporting_tbordmarque_promo(:P50_RESEAU,:P50_DE,
                                :P50_GROUPE,mypackage.get_previous_annee_fiscale(:P50_ANNEE_FISCALE))
                                )
                              UNION
                              SELECT
                                *
                              FROM
                                TABLE(mypackage.reporting_tbordmarque_nip(:P50_RESEAU,:P50_DE,
                                :P50_GROUPE,mypackage.get_previous_annee_fiscale(:P50_ANNEE_FISCALE))
                                )
                              UNION
                              SELECT
                                *
                              FROM
                                TABLE(mypackage.reporting_tbordmarque_eng(:P50_RESEAU,:P50_DE,
                                :P50_GROUPE,mypackage.get_previous_annee_fiscale(:P50_ANNEE_FISCALE))
                                )
                            )
                          GROUP BY
                            anneefiscale
                          , marque
                          , de
                        ) b
                      , (
                          SELECT
                            annee_fiscale.nom anneefiscale
                          , marque.nom marque
                          , de.nom de
                          , ROUND(SUM(montant)) estime
                          FROM
                            estime
                          , annee_fiscale
                          , marque
                          , enseigne
                          , groupe
                          , de
                          , reseau
                          WHERE
                            estime.id_annee_fiscale_fk   = annee_fiscale.id_annee_fiscale
                            AND estime.id_marque_fk      = marque.id_marque
                            AND estime.id_enseigne_fk    = enseigne.id_enseigne
                            AND estime.id_type_estime_fk = :P50_TYPE_ESTIME
                            AND enseigne.id_groupe_fk    = groupe.id_groupe
                            AND groupe.id_de_fk          = de.id_de
                            AND de.id_reseau_fk          = reseau.id_reseau
                            AND
                            (
                              :P50_ANNEE_FISCALE           IS NULL
                              OR :P50_ANNEE_FISCALE         =0
                              OR estime.id_annee_fiscale_fk = :P50_ANNEE_FISCALE
                            )
                            AND
                            (
                              :P50_RESEAU        IS NULL
                              OR :P50_RESEAU      =0
                              OR reseau.id_reseau = :P50_RESEAU
                            )
                            AND
                            (
                              :P50_DE    IS NULL
                              OR :P50_DE  =0
                              OR de.id_de = :P50_RESEAU
                            )
                          GROUP BY
                            annee_fiscale.nom
                          , marque.nom
                          , de.nom
                        ) c
                      
                      WHERE
                        a.marque    =b.marque(+)
                        AND a.de    =b.de(+)
                        AND a.marque=c.marque(+)
                        AND a.de    =c.de(+)
                      
                      ORDER BY
                        a.anneefiscale
                      , a.marque
                      , a.de;
                      What are the functions:
                      mypackage.reporting_tbordmarque_promo
                      mypackage.reporting_tbordmarque_nip
                      mypackage.reporting_tbordmarque_eng

                      Can you supply source code?

                      Regards,

                      Peter
                      • 8. Re: Query that becomes slower and slower when runned several times
                        772315
                        Hi everybody

                        I made a test yesterday on a 10gR2 database. I did not reproduce the issue !

                        I am currently working on a not patched database (ie 11.2.0.1). I am going to update the oracle kernel to 11.2.0.3 to check if the problem persists. If needed, I will contact oracle support for investigation.

                        I froze the thread for the moment and will communicate later for the result after pathset applying.
                        Thank to all for having help me. Some good tips were given.. I will try to explore them for having useful tools if I need to shoot issues in the future.
                        • 9. Re: Query that becomes slower and slower when runned several times
                          772315
                          Functions are pipelined functions. Basically, it is a loop of a select cursor with some conditional clauses. Nothing complex and I could write then with decode ... but I made this choice for having a more lisible code. I can not paste the package body for right now...

                          I will keep you informed on the follow on of the issue.
                          Thx