This discussion is archived
9 Replies Latest reply: Nov 21, 2012 6:10 AM by 772315 RSS

Query that becomes slower and slower when runned several times

772315 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    submit a Bug Report to MOS.
  • 4. Re: Query that becomes slower and slower when runned several times
    Nikolay Savvinov Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points