1 2 3 Previous Next 39 Replies Latest reply: Aug 14, 2012 6:26 AM by Dom Brooks Go to original post RSS
      • 15. Re: Query on 11gR2 much slower than on 10gR2
        UW (Germany)
        Thank you Alexander and Mohamed for your input. I will check on your suggestions tomorrow and see, whether they will help me to understand where the database goes astray.
        To answer your question Mohamed: I used "set autotrace traceonly expl" in sqlplus to get the execution plan of the never ending query without running it.
        • 16. Re: Query on 11gR2 much slower than on 10gR2
          jgarry
          Also see http://kerryosborne.oracle-guy.com/2010/02/autotrace-lies/
          • 17. Re: Query on 11gR2 much slower than on 10gR2
            UW (Germany)
            Sorry Mohamed, I created the index yesterday, when I wrote <If I create an additional index on "RE" and/or "DA", it is used, but this does not solve the problem> and I didn't drop it after this.
            So the index wasn't there when the first execution plan was created and this led you on the wrong trail. When I create this plan once more now, it uses the index but this does not change the fact, that the query doesn't respond.
            ----------------------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                                              | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
            ----------------------------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                                       |                             |       |    39M|       |   123K  (2)| 00:24:44 |
            |   1 |  TEMP TABLE TRANSFORMATION                             |                             |       |       |       |            |          |
            |   2 |   LOAD AS SELECT                                       | SYS_TEMP_0FD9D6732_7D2B1EF2 |       |       |       |            |          |
            |   3 |    SORT UNIQUE                                         |                             |   115K|  8179K|  9792K|  4779  (22)| 00:00:58 |
            |   4 |     UNION-ALL                                          |                             |       |       |       |            |          |
            |*  5 |      HASH JOIN                                         |                             |   112K|  8019K|  4232K|  1821   (1)| 00:00:22 |
            |   6 |       TABLE ACCESS FULL                                | PD                          | 96175 |  3099K|       |   395   (1)| 00:00:05 |
            |*  7 |       HASH JOIN                                        |                             |   112K|  4408K|  2912K|   940   (1)| 00:00:12 |
            |   8 |        TABLE ACCESS FULL                               | PD                          | 96175 |  1784K|       |   395   (1)| 00:00:05 |
            |   9 |        TABLE ACCESS FULL                               | PV                          |   112K|  2314K|       |   224   (1)| 00:00:03 |
            |* 10 |      HASH JOIN                                         |                             |  2645 |   160K|       |  1016   (1)| 00:00:13 |
            |* 11 |       HASH JOIN                                        |                             |  2645 |   100K|       |   620   (1)| 00:00:08 |
            |* 12 |        TABLE ACCESS FULL                               | PG                          |  2645 | 52900 |       |   224   (1)| 00:00:03 |
            |  13 |        TABLE ACCESS FULL                               | PD                          | 96175 |  1784K|       |   395   (1)| 00:00:05 |
            |  14 |       TABLE ACCESS FULL                                | PD                          | 96175 |  2160K|       |   395   (1)| 00:00:05 |
            |  15 |   LOAD AS SELECT                                       | SYS_TEMP_0FD9D6733_7D2B1EF2 |       |       |       |            |          |
            |  16 |    SORT UNIQUE                                         |                             |   151K|  2808K|  4184K|  2354  (37)| 00:00:29 |
            |  17 |     UNION-ALL                                          |                             |       |       |       |            |          |
            |* 18 |      HASH JOIN                                         |                             |   112K|  2094K|  2168K|   834   (1)| 00:00:11 |
            |  19 |       TABLE ACCESS FULL                                | PD                          | 96175 |  1033K|       |   395   (1)| 00:00:05 |
            |  20 |       TABLE ACCESS FULL                                | PV                          |   112K|   881K|       |   224   (1)| 00:00:03 |
            |* 21 |      HASH JOIN                                         |                             | 38507 |   714K|       |   620   (1)| 00:00:08 |
            |* 22 |       TABLE ACCESS FULL                                | PG                          | 38507 |   300K|       |   224   (1)| 00:00:03 |
            |  23 |       TABLE ACCESS FULL                                | PD                          | 96175 |  1033K|       |   395   (1)| 00:00:05 |
            |  24 |   SORT ORDER BY                                        |                             |       |    39M|    78M|   116K  (1)| 00:23:18 |
            |* 25 |    FILTER                                              |                             |       |       |       |            |          |
            |  26 |     NESTED LOOPS                                       |                             | 10055 |    39M|       |   107K  (1)| 00:21:35 |
            |* 27 |      HASH JOIN                                         |                             |   106K|  6670K|       |  1108   (1)| 00:00:14 |
            |  28 |       MERGE JOIN CARTESIAN                             |                             |    95 |  3800 |       |    44   (0)| 00:00:01 |
            |* 29 |        TABLE ACCESS BY INDEX ROWID                     | PD                          |     1 |    22 |       |    41   (0)| 00:00:01 |
            |* 30 |         INDEX RANGE SCAN                               | I_PD_1                      |   108 |       |       |     1   (0)| 00:00:01 |
            |  31 |        BUFFER SORT                                     |                             |   351 |  6318 |       |     3   (0)| 00:00:01 |
            |  32 |         TABLE ACCESS FULL                              | SR                          |   351 |  6318 |       |     3   (0)| 00:00:01 |
            |  33 |       VIEW                                             |                             |   394K|  9239K|       |  1062   (1)| 00:00:13 |
            |  34 |        SORT UNIQUE                                     |                             |       |       |       |            |          |
            |  35 |         UNION-ALL                                      |                             |       |       |       |            |          |
            |  36 |          NESTED LOOPS                                  |                             |   355K|  9378K|       |   978   (4)| 00:00:12 |
            |  37 |           TABLE ACCESS FULL                            | PR                          |   355K|  7989K|       |   945   (1)| 00:00:12 |
            |* 38 |           INDEX UNIQUE SCAN                            | PKPD                        |     1 |     4 |       |     0   (0)| 00:00:01 |
            |  39 |          NESTED LOOPS                                  |                             | 38507 |  1015K|       |   227   (3)| 00:00:03 |
            |* 40 |           TABLE ACCESS FULL                            | PG                          | 38507 |   864K|       |   224   (1)| 00:00:03 |
            |* 41 |           INDEX UNIQUE SCAN                            | PKPD                        |     1 |     4 |       |     0   (0)| 00:00:01 |
            |* 42 |      VIEW                                              |                             |     1 |  4037 |       |     1   (0)| 00:00:01 |
            |  43 |       SORT UNIQUE                                      |                             |       |       |       |            |          |
            |  44 |        UNION-ALL PARTITION                             |                             |       |       |       |            |          |
            |* 45 |         TABLE ACCESS BY INDEX ROWID                    | PD                          |     1 |    28 |       |     2   (0)| 00:00:01 |
            |* 46 |          INDEX UNIQUE SCAN                             | PKPD                        |     1 |       |       |     1   (0)| 00:00:01 |
            |  47 |         NESTED LOOPS                                   |                             |     1 |  8108 |       |   304K (21)| 01:00:51 |
            |  48 |          TABLE ACCESS BY INDEX ROWID                   | PD                          |     1 |    28 |       |     2   (0)| 00:00:01 |
            |* 49 |           INDEX UNIQUE SCAN                            | PKPD                        |     1 |       |       |     1   (0)| 00:00:01 |
            |* 50 |          VIEW                                          |                             |     1 |  8080 |       |   304K (21)| 01:00:51 |
            |  51 |           SORT GROUP BY                                |                             |     1 | 10121 |       |   304K (21)| 01:00:51 |
            |  52 |            MERGE JOIN                                  |                             |   871M|  8213G|       |   247K  (2)| 00:49:33 |
            |  53 |             SORT JOIN                                  |                             |   115K|  1109M|  1799M|   242K  (1)| 00:48:25 |
            |* 54 |              VIEW                                      |                             |   115K|  1109M|       |  3927 (100)| 00:00:48 |
            |* 55 |               CONNECT BY NO FILTERING WITH START-WITH  |                             |       |       |       |            |          |
            |  56 |                VIEW                                    |                             |   115K|    16M|       |     4  (50)| 00:00:01 |
            |  57 |                 TABLE ACCESS FULL                      | SYS_TEMP_0FD9D6732_7D2B1EF2 |   115K|    16M|       |     4  (50)| 00:00:01 |
            |* 58 |             SORT JOIN                                  |                             |   151K|  1921K|  5944K|  1450   (2)| 00:00:18 |
            |  59 |              VIEW                                      |                             |   151K|  1921K|       |   727   (2)| 00:00:09 |
            |  60 |               SORT UNIQUE                              |                             |   151K|  1921K|  2976K|   727   (2)| 00:00:09 |
            |  61 |                VIEW                                    |                             |   151K|  1921K|       |  1356 (100)| 00:00:17 |
            |* 62 |                 CONNECT BY NO FILTERING WITH START-WITH|                             |       |       |       |            |          |
            |  63 |                  VIEW                                  |                             |   151K|  4878K|       |     3  (34)| 00:00:01 |
            |  64 |                   TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6733_7D2B1EF2 |   151K|  4878K|       |     3  (34)| 00:00:01 |
            ----------------------------------------------------------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               5 - access("P4"."II"="V1"."VI")
               7 - access("V1"."PI"="P3"."II")
              10 - access("P6"."II"="G1"."VI")
              11 - access("G1"."PI"="P5"."II")
              12 - filter("G1"."VI" IS NOT NULL AND "G1"."RG"='RE')
              18 - access("V2"."PI"="P7"."II")
              21 - access("G2"."PI"="P8"."II")
              22 - filter("G2"."RG"='RE')
              25 - filter(TO_DATE(:STARTDATE)<=TO_DATE(:ENDDATE)+1)
              27 - access("R1"."KE"="RV"."RK")
              29 - filter("P0"."DA"<=TO_DATE(:ENDDATE)+1 AND "P0"."DA">=TO_DATE(:STARTDATE))
              30 - access("P0"."RE"=:RE)
              38 - access("PR"."PI"="P"."II")
              40 - filter("PG"."RG"='RO')
              41 - access("PG"."PI"="P"."II")
              42 - filter("P0"."II"="BV"."ROOT_II")
              45 - filter("P1"."RE"=:RE)
              46 - access("P1"."II"="RV"."PI")
              49 - access("P2"."II"="RV"."PI")
              50 - filter("Z1"."ROOT_RE"=:RE AND "Z1"."VI"="P2"."II" AND "Z1"."VI"="RV"."PI")
              54 - filter("Z3"."ROOT_RE"=:RE)
              55 - access("II"=PRIOR "VI")
                   filter("RE"=:RE)
              58 - access(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                   filter(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
              62 - access("II"=PRIOR "VI")
                   filter("RE"=:RE)
            • 18. Re: Query on 11gR2 much slower than on 10gR2
              UW (Germany)
              I’m not sure whether you’re interested in this, but I created a small script to build a test case with data that are a little bit similar to our real world data. At least the similarity is good enough to reproduce the problem.
              When I run the query with the optimizer hint
              /*+
                    BEGIN_OUTLINE_DATA
                    OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
                */
              I get a result in less the 10 seconds. When I remove the hint … Well, I’m waiting for more than two hours now.

              If I understand the description of DBMS_RANDOM.SEED right, everyone who runs this script should get exactly the same data. The query at the end is the same, that I posted at the beginning of this thread, but I removed the schema name so you can use any test schema to build my example.

              The five tables will have about 500.000 rows together. Tables and indexes will need about 70MB in your tablespace. Filling the tables with the random data shouldn’t last more than a few minutes, even on a slow machine.

              I wonder whether you will experience the same behavior on another 11gR2 database. Maybe it depends on the parameterization of the database (at least compatible = 10.2.0.3 wouldn’t be a good parameter to reproduce the problem) or it depends on the OS and the hardware (here I have a Windows machine with 32GB memory).
              --- creating the tables and indexes
              
              create table sr
              (ke varchar2(7) not null,
               an varchar2(12),
               ro varchar2(30) not null,
               pr number,
               ed number,
               ko number);
              
              create unique index pksr on sr (ke) ;
              alter table sr add (constraint pksr primary key (ke) using index pksr);
              
              create table pd
              (bn number not null,
               at varchar2(5) not null,
               da date not null,
               fa number,
               re varchar2(10) not null,
               ge number not null,
               tt number not null,
               tg number not null,
               be varchar2(200),
               ii number not null);
              
              create index i_pd_1 on pd (re);
              create unique index pkpd on pd (ii);
              alter table pd add (constraint pk_proddat primary key (ii) using index pkpd);
              
              create table pr
              (rk varchar2(7) not null,
               om number,
               pi number not null,
               hm number,
               ko number,
               oc number);
              
              create unique index pkpr on pr (pi, rk);
              alter table pr add (constraint pkpr primary key (pi, rk) using index pkpr);
              
              create table pv
              (vr varchar2(10) not null,
               cf number,
               pi number not null,
               vi number);
              
              create unique index pkpv on pv (pi, vr);
              alter table pv add (constraint pk_pv primary key (pi, vr) using index pkpv);
              
              create table pg
              (pi number not null,
               gk varchar2(10) not null,
               om number not null,
               oc number,
               cf number,
               ko number,
               hm number not null,
               mk varchar2(10) not null,
               rg varchar2(2) not null,
               vi number);
              
              create unique index pkpg on pg (pi, gk);
              alter table pg add (constraint pkpg primary key (pi, gk) using index pkpg);
              
              --- filling the table with data
              
              declare
              i number;
              j number;
              begin
              
              DBMS_RANDOM.SEED ('11GR2TEST');
              
              for i in 1 .. 400 loop
                insert into sr values (to_char(i),i,  to_char(round(dbms_random.value(1000,9999))), dbms_random.value(1,1000), to_char(dbms_random.value(50,2000)) ,dbms_random.value(1,100));
              end loop;
              
              for i in 1 .. 80000 loop
                insert into pd values (i,'m'|| to_char(round(dbms_random.value(1,100))), to_date('01.01.2002', 'dd.mm.yyyy')+i/21, dbms_random.value(), to_char(round(dbms_random.value(1000,9999))) ,dbms_random.value(100,1000), dbms_random.value(100,1000), dbms_random.value(), null, i);
              end loop;
              
              for i in 1 .. 80000 loop
                 for j in 1 .. 4 loop
                   insert into pr values (to_char(round(dbms_random.value(100*(j-1),100*(j-1)+99))),dbms_random.value(10,400),i,null,25*dbms_random.value(1,4),null);
                 end loop;
              end loop;
              
              update pr set hm = om / ko * 2;
              
              for i in 70000 .. 80000 loop
                 insert into pv values (to_char(round(dbms_random.value(1,i/2-1))), dbms_random.value(), i, round(dbms_random.value(1,i/2-1)));
                 insert into pv values (to_char(round(dbms_random.value(i/2, i-1))), dbms_random.value(), i, round(dbms_random.value(i/2, i-1)));
              end loop;
              
              update pv set vr = (select re from pd where pd.ii = pv.vi);
              
              for i in 70000 .. 80000 loop
                 for j in 1 .. 5 loop
                   insert into pg values (i,to_char(j)||to_char(round(dbms_random.value(100,999))),dbms_random.value(10,400),null, null,25*dbms_random.value(1,4),0,to_char(round(dbms_random.value(20*(j-1),20*(j-1)+19))),'RO',null);
                 end loop;
              end loop;
              
              for i in 70000 .. 80000 loop
                   insert into pg values (i,'6'||to_char(round(dbms_random.value(100,999))),dbms_random.value(10,400), null, dbms_random.value(),25*dbms_random.value(1,4),0,'-','RE',round(dbms_random.value(1,i/2-1)));
                   insert into pg values (i,'7'||to_char(round(dbms_random.value(100,999))),dbms_random.value(10,400), null, dbms_random.value(),25*dbms_random.value(1,4),0,'-','RE',round(dbms_random.value(i/2, i-1)));
              end loop;
              
              update pg set hm = om / ko * 2;
              
              update pg set mk = (select re from pd where pd.ii = pg.vi) where pg.vi is not null;
              
              end;
              /
              
              commit;
              
              -- Creating the statistics 
              
              exec dbms_stats.gather_schema_stats('/*your test schema here*/');
              
              -- Some preparations
              
              alter session set NLS_NUMERIC_CHARACTERS = '.,';
              alter session set NLS_DATE_FORMAT = 'DD.MM.YYYY';
              
              var re varchar2(10);
              var startdate varchar2(20);
              var enddate varchar2(20);
              
              exec :re := '3000';
              exec :startdate := '01.01.2007';
              exec :enddate := '08.08.2012';
              
              set linesize 400
              set pagesize 300
              set timing on
              
              column sf format a50
              column hk format a12
              
              -- and here is the query:
              
              with
              z0 as
              ( select
                  p3.ii,
                  v1.vi,
                  p3.re re,
                  p4.re vr,
                  to_char(p4.bn) bn,
                  p4.at,
                  to_char(p3.bn) rba,
                  p3.at rtk,
                  to_char(round(v1.cf / p4.fa, 7)) fa,
                  to_char(v1.cf,'0.999999')||'/'||
                  to_char(p4.fa,'0.999999') sf,
                  'V' pi,
                  to_char(p4.bn)||'('||p4.at||')' bk
                from
                     pd p3
                join pv v1 on v1.pi = p3.ii
                join pd p4 on p4.ii = v1.vi
                union
                select
                  p5.ii,
                  g1.vi,
                  p5.re,
                  p6.re,
                  to_char(p6.bn) bn,
                  p6.at,
                  to_char(p5.bn) rba,
                  p5.at rtk,
                  to_char(round(g1.om / p6.tt, 7)),
                  to_char(g1.om,'99990.999')||'/'||
                  to_char(p6.tt,'99990.999'),
                  'G' pi,
                  to_char(p6.bn)||'('||p6.at||')' bk
                from
                     pd p5
                join pg g1 on g1.pi = p5.ii
                join pd p6 on p6.ii = g1.vi
                where
                   g1.rg = 'RE'
              ),
              z3 as
              ( select
                  connect_by_root ii root_ii,
                  ii,
                  vi,
                  bn,
                  at,
                  connect_by_root re root_re,
                  connect_by_root rba root_ba,
                  connect_by_root rtk rt,
                  sys_connect_by_path (vr, ' < ') kt,
                  sys_connect_by_path (bn||'('||at||')', ' < ') bkt,
                  sys_connect_by_path (fa,'*') cfa,
                  sys_connect_by_path (sf,'x') sf,
                  sys_connect_by_path (pi,' < ') pi,
                  sys_connect_by_path (bk,' < ') bk,
                  level lvl
                from z0
                start with re = :re
                connect by prior vi = ii
              ),
              z6 as
              ( select
                  p7.ii,
                  p7.re,
                  v2.vi
                from
                     pd p7
                join pv v2 on v2.pi = p7.ii
                union
                select
                  p8.ii,
                  p8.re,
                  g2.vi
                from
                     pd p8
                join pg g2 on g2.pi = p8.ii
                where 
                   g2.rg = 'RE'
              ),
              z5 as
              ( select
                  level lvl
                from
                  z6
                start with re = :re
                connect by prior vi = ii
              ),
              z4 as
              ( select
                  distinct lvl as n
                from z5
              ),
              z2 as
              ( select
                  z3.root_ii,
                  z3.vi,
                  z3.root_re,
                  z3.root_ba,
                  z3.rt,
                  z3.kt,
                  z3.bn,
                  z3.at,
                  z3.sf,
                  z3.pi,
                  z3.bk,
                  to_number(regexp_substr(z3.cfa,'[^*]+',1,z4.n)) as cfa
                from z3
                join z4 on z4.n <= z3.lvl
                where z3.root_re = :re),
              z1 as
              ( select
                  z2.root_ii,
                  z2.vi,
                  z2.root_re,
                  z2.root_ba,
                  z2.rt,
                  z2.kt,
                  z2.bn,
                  z2.at,
                  z2.pi,
                  z2.bk,
                  z2.sf,
                  round(exp(sum(ln(z2.cfa))),5) fa
                from
                     z2
                where
                  root_re = :re
                group by
                  root_ii,
                  vi,
                  root_re,
                  root_ba,
                  rt,
                  kt,
                  bn,
                  at,
                  pi,
                  bk,
                  sf
              ),
              bv as
              ( select
                  p1.bn,
                  p1.at,
                  p1.da,
                  p1.re,
                  p1.ii,
                  p1.ii root_ii,
                  p1.re rh,
                  'R' hk,
                  to_char(p1.bn)||'('||p1.at||')' bk,
                  '1' sf,
                  1 fa,
                  p1.be
                from
                  pd p1
                where
                  p1.re = :re
                union
                select
                  p2.bn,
                  p2.at,
                  p2.da,
                  p2.re,
                  p2.ii,
                  z1.root_ii,
                  z1.root_re||z1.kt,
                  'R'||z1.pi,
                  to_char(z1.root_ba)||'('||z1.rt||')'||Z1.bk bk,
                  '1'||replace(z1.sf,'x','*') sf,
                  z1.fa,
                  p2.be
                from
                  pd p2
                  join z1 on z1.vi = p2.ii
                  where z1.root_re = :re
              ),
              rv as
              ( select
                  pr.pi,
                  pr.rk,
                  pr.om
                from
                  pr pr
                  join pd p on pr.pi = p.ii
                union
                select
                  pg.pi,
                  pg.mk,
                  pg.om
                from
                  pg pg
                  join pd p on pg.pi = p.ii
                where pg.rg = 'RO'
              )
              select 
                /*+
                    BEGIN_OUTLINE_DATA
                    OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
                */
                p0.da,
                p0.tt,
                bv.hk,
                bv.sf,
                round(bv.fa,6) fa,
                r1.ke,
                round(om,3) ov,
                round(om*bv.fa,3) oe
              from rv
              join bv on pi = bv.ii
              join pd p0 on p0.ii = bv.root_ii
              join sr r1 on r1.ke = rk
              where
                p0.re = :re and
                p0.da between to_date(:startdate) and to_date(:enddate)+1
              order by
                p0.da desc,
                bv.rh,
                r1.ro;
              The result should look like this:
              DA                 TT HK        SF                                                 FA KE      OV         OE
              ---------- ---------- --------- -------------------------------------------- -------- ------- ---------- ----------
              08.12.2011 955.241049 R         1                                                   1 242      82.312        82.312
              08.12.2011 955.241049 R         1                                                   1 80       25.556        25.556
              08.12.2011 955.241049 R         1                                                   1 382     319.223    319.223
              08.12.2011 955.241049 R         1                                                   1 18      265.509    265.509
              08.12.2011 955.241049 R         1                                                   1 69       60.145        60.145
              08.12.2011 955.241049 R         1                                                   1 11       97.194        97.194
              08.12.2011 955.241049 R         1                                                   1 29      271.759    271.759
              08.12.2011 955.241049 R         1                                                   1 103     279.485    279.485
              08.12.2011 955.241049 R         1                                                   1 44        59.74         59.74
              08.12.2011 955.241049 R < V     1* 0.003895/ 0.857140                          .00454 33         19.1          .087
              08.12.2011 955.241049 R < V     1* 0.003895/ 0.857140                          .00454 319      79.216           .36
              08.12.2011 955.241049 R < V     1* 0.003895/ 0.857140                          .00454 186     118.156          .536
              08.12.2011 955.241049 R < V     1* 0.003895/ 0.857140                          .00454 225     353.392         1.604
              08.12.2011 955.241049 R < G     1*   390.001/   152.720                       2.55369 21      310.631    793.256
              08.12.2011 955.241049 R < G     1*   390.001/   152.720                       2.55369 157      62.919    160.677
              08.12.2011 955.241049 R < G     1*   390.001/   152.720                       2.55369 348     342.282    874.082
              08.12.2011 955.241049 R < G     1*   390.001/   152.720                       2.55369 272     303.363    774.694
              08.12.2011 955.241049 R < G     1*    52.435/   144.282                        .36342 130      87.409        31.766
              08.12.2011 955.241049 R < G     1*    52.435/   144.282                        .36342 79      370.577    134.675
              08.12.2011 955.241049 R < G     1*    52.435/   144.282                        .36342 239      46.213        16.795
              08.12.2011 955.241049 R < G     1*    52.435/   144.282                        .36342 398     165.245        60.053
              08.12.2011 955.241049 R < V     1* 0.592435/ 0.936700                          .63247 15      172.252    108.944
              08.12.2011 955.241049 R < V     1* 0.592435/ 0.936700                          .63247 219      32.608        20.623
              08.12.2011 955.241049 R < V     1* 0.592435/ 0.936700                          .63247 5         18.58        11.751
              08.12.2011 955.241049 R < V     1* 0.592435/ 0.936700                          .63247 65      398.878    252.279
              08.12.2011 955.241049 R < V     1* 0.592435/ 0.936700                          .63247 378      38.802        24.541
              08.12.2011 955.241049 R < V     1* 0.592435/ 0.936700                          .63247 26      125.891        79.622
              08.12.2011 955.241049 R < V     1* 0.592435/ 0.936700                          .63247 98      118.785        75.128
              08.12.2011 955.241049 R < V     1* 0.592435/ 0.936700                          .63247 51      104.091        65.834
              08.12.2011 955.241049 R < V     1* 0.592435/ 0.936700                          .63247 184     200.043    126.521
              08.12.2011 955.241049 R < V < V 1* 0.592435/ 0.936700* 0.102030/ 0.624643      .10331 295      11.085         1.145
              08.12.2011 955.241049 R < V < V 1* 0.592435/ 0.936700* 0.102030/ 0.624643      .10331 173     360.532        37.247
              08.12.2011 955.241049 R < V < V 1* 0.592435/ 0.936700* 0.102030/ 0.624643      .10331 310      135.63        14.012
              08.12.2011 955.241049 R < V < V 1* 0.592435/ 0.936700* 0.102030/ 0.624643      .10331 7        83.985         8.676
              08.12.2011 955.241049 R < V < V 1* 0.592435/ 0.936700* 0.718903/ 0.105311     4.31755 344     320.697   1384.624
              08.12.2011 955.241049 R < V < V 1* 0.592435/ 0.936700* 0.718903/ 0.105311     4.31755 244     261.975    1131.09
              08.12.2011 955.241049 R < V < V 1* 0.592435/ 0.936700* 0.718903/ 0.105311     4.31755 26      206.304    890.728
              08.12.2011 955.241049 R < V < V 1* 0.592435/ 0.936700* 0.718903/ 0.105311     4.31755 173      92.236    398.234
              08.12.2011 955.241049 R < V < G 1* 0.592435/ 0.936700*   397.598/   914.241    .27506 73        29.68         8.164
              08.12.2011 955.241049 R < V < G 1* 0.592435/ 0.936700*   397.598/   914.241    .27506 241      19.555         5.379
              08.12.2011 955.241049 R < V < G 1* 0.592435/ 0.936700*   397.598/   914.241    .27506 386     343.465        94.474
              08.12.2011 955.241049 R < V < G 1* 0.592435/ 0.936700*   397.598/   914.241    .27506 145      26.173         7.199
              08.12.2011 955.241049 R < V < G 1* 0.592435/ 0.936700*   231.832/   455.615    .32182 351     395.965    127.429
              08.12.2011 955.241049 R < V < G 1* 0.592435/ 0.936700*   231.832/   455.615    .32182 265      51.318        16.515
              08.12.2011 955.241049 R < V < G 1* 0.592435/ 0.936700*   231.832/   455.615    .32182 118      71.524        23.018
              08.12.2011 955.241049 R < V < G 1* 0.592435/ 0.936700*   231.832/   455.615    .32182 44       45.824        14.747
              29.04.2010  147.58957 R         1                                                   1 257     307.068    307.068
              29.04.2010  147.58957 R         1                                                   1 1       245.752    245.752
              29.04.2010  147.58957 R         1                                                   1 103      98.866        98.866
              29.04.2010  147.58957 R         1                                                   1 324     218.757    218.757
              27.03.2010 164.586938 R         1                                                   1 109     301.015    301.015
              27.03.2010 164.586938 R         1                                                   1 208     354.091    354.091
              27.03.2010 164.586938 R         1                                                   1 328     213.495    213.495
              27.03.2010 164.586938 R         1                                                   1 87      330.457    330.457
              04.07.2008 810.795021 R         1                                                   1 234     333.602    333.602
              04.07.2008 810.795021 R         1                                                   1 335     341.234    341.234
              04.07.2008 810.795021 R         1                                                   1 79      363.028    363.028
              04.07.2008 810.795021 R         1                                                   1 142     111.326    111.326
              15.03.2007 657.973337 R         1                                                   1 135     122.512    122.512
              15.03.2007 657.973337 R         1                                                   1 47       49.658        49.658
              15.03.2007 657.973337 R         1                                                   1 331     392.425    392.425
              15.03.2007 657.973337 R         1                                                   1 220     369.325    369.325
              
              62 rows selected.
              
              Elapsed: 00:00:02.12
              • 19. Re: Query on 11gR2 much slower than on 10gR2
                UW (Germany)
                I’d like to replace my old „connect-by-prior“-query with a new query using the recursive with clause, because it makes my query shorter and easier to understand.
                I tested the modified query with the test case, which I described above. Without an optimizer hint I still have the problem, that I get no response of the query in a reasonable time.
                Interestingly the optimizer hint OPTIMIZER_FEATURES_ENABLE('10.2.0.3') speeds up also this query even if Oracle 10.2 knew nothing about recursive with clauses.

                But when I look at the execution plan without the hint it seems reasonable to me and I don’t understand where the database spends hours and hours for calculations.

                Here is the modified query and the execution plan:
                alter session set NLS_DATE_FORMAT = 'DD.MM.YYYY';
                
                var re varchar2(10);
                var startdate varchar2(20);
                var enddate varchar2(20);
                exec :re := '3000';
                exec :startdate := '01.01.2007';
                exec :enddate := '08.08.2012';
                set linesize 400
                set pagesize 300
                set autotrace off
                set timing on
                column sf format a50
                column hk format a12
                
                with
                z0 as
                ( select
                    p3.ii,
                    v1.vi,
                    p3.re re,
                    p4.re vr,
                    to_char(p4.bn) bn,
                    p4.at,
                    to_char(p3.bn) rba,
                    p3.at rtk,
                    to_char(round(v1.cf / p4.fa, 7)) fa,
                    to_char(v1.cf,'0.999999')||'/'||
                    to_char(p4.fa,'0.999999') sf,
                    'V' pi,
                    to_char(p4.bn)||'('||p4.at||')' bk,
                    v1.cf/p4.fa mu
                  from
                       pd p3
                  join pv v1 on v1.pi = p3.ii
                  join pd p4 on p4.ii = v1.vi
                  union
                  select
                    p5.ii,
                    g1.vi,
                    p5.re,
                    p6.re,
                    to_char(p6.bn) bn,
                    p6.at,
                    to_char(p5.bn) rba,
                    p5.at rtk,
                    to_char(round(g1.om / p6.tt, 7)),
                    to_char(g1.om,'99990.999')||'/'||
                    to_char(p6.tt,'99990.999'),
                    'G' pi,
                    to_char(p6.bn)||'('||p6.at||')' bk,
                    g1.om / p6.tt mu
                  from
                       pd p5
                  join pg g1 on g1.pi = p5.ii
                  join pd p6 on p6.ii = g1.vi
                  where
                    g1.rg = 'RE'
                ),
                z2 ( root_ii, ii, vi, bn, at, root_re, root_ba, rt, kt, bkt, cfa, sf, pi, bk, lvl, mu)
                as
                ( select ii root_ii,
                         ii,
                         vi,
                         bn,
                         at,
                         re root_re,
                         rba root_ba,
                         rtk rt,
                         vr kt,
                         bn||'('||at||')' bkt,
                         fa cfa,
                         sf sf,
                         pi,
                         bk,
                         1 lvl,
                         mu
                  from z0
                  where re = :re
                  union all
                  select 
                     z2.root_ii,
                     z0.ii,
                     z0.vi,
                     z0.bn,
                     z0.at,
                     z2.root_re,
                     z2.root_ba,
                     z2.rt,
                     z2.kt||' < '||z0.vr,
                     z2.bkt||', '||z0.bn||'('||z0.at||')',
                     z2.cfa||'*'||z0.fa,
                     z2.sf||'x'||z0.sf,
                     z2.pi||' < '||z0.pi,
                     z2.bk||' < '||z0.bk,
                     z2.lvl+1,
                     z2.mu * z0.mu
                  from z0 join z2 on z2.vi = z0.ii),
                z1 as
                ( select
                    z2.root_ii,
                    z2.vi,
                    z2.root_re,
                    z2.root_ba,
                    z2.rt,
                    z2.kt,
                    z2.bn,
                    z2.at,
                    z2.pi,
                    z2.bk,
                    z2.sf,
                    round(exp(sum(ln(z2.mu))),5) fa
                  from
                       z2
                  where
                    root_re = :re
                  group by
                    root_ii,
                    vi,
                    root_re,
                    root_ba,
                    rt,
                    kt,
                    bn,
                    at,
                    pi,
                    bk,
                    sf
                ),
                bv as
                ( select
                    p1.bn,
                    p1.at,
                    p1.da,
                    p1.re,
                    p1.ii,
                    p1.ii root_ii,
                    p1.re rh,
                    'R' hk,
                    to_char(p1.bn)||'('||p1.at||')' bk,
                    '1' sf,
                    1 fa,
                    p1.be
                  from
                    pd p1
                  where
                    p1.re = :re
                  union
                  select
                    p2.bn,
                    p2.at,
                    p2.da,
                    p2.re,
                    p2.ii,
                    z1.root_ii,
                    z1.root_re||z1.kt,
                    'R'||z1.pi,
                    to_char(z1.root_ba)||'('||z1.rt||')'||Z1.bk bk,
                    '1'||replace(z1.sf,'x','*') sf,
                    z1.fa,
                    p2.be
                  from
                    pd p2
                    join z1 on z1.vi = p2.ii
                    where z1.root_re = :re
                ),
                ro as
                ( select
                    pr.pi,
                    pr.rk,
                    pr.om
                  from
                    pr pr
                    join pd p on pr.pi = p.ii
                  union
                  select
                    pg.pi,
                    pg.mk,
                    pg.om
                  from
                    pg pg
                    join pd p on pg.pi = p.ii
                  where pg.rg = 'RO'
                )
                select
                 /*+
                      gather_plan_statistics
                  */
                  p0.da,
                  p0.tt,
                  bv.hk,
                  bv.sf,
                  round(bv.fa,6) fa,
                  r1.ke,
                  round(ro.om,3) ov,
                  round(ro.om*bv.fa,3) oe
                from pd p0
                join bv on bv.root_ii  = p0.ii
                join ro on ro.pi       = bv.ii
                join sr r1 on r1.ke = ro.rk
                where
                  p0.re = :re and
                  p0.da between to_date(:startdate) and to_date(:enddate)+1
                order by
                  p0.da desc,
                  bv.rh,
                  r1.ro;
                
                
                -------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                  
                | Id  | Operation                                           | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                  
                -------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                  
                |   0 | SELECT STATEMENT                                    |                             |       |       |       |  3850 (100)|          |                                                                                                                                                                  
                |   1 |  TEMP TABLE TRANSFORMATION                          |                             |       |       |       |            |          |                                                                                                                                                                  
                |   2 |   LOAD AS SELECT                                    |                             |       |       |       |            |          |                                                                                                                                                                  
                |   3 |    SORT UNIQUE                                      |                             | 25717 |  2277K|  2064K|  2235  (47)| 00:00:27 |                                                                                                                                                                  
                |   4 |     UNION-ALL                                       |                             |       |       |       |            |          |                                                                                                                                                                  
                |*  5 |      HASH JOIN                                      |                             | 20002 |  1757K|       |   790   (1)| 00:00:10 |                                                                                                                                                                  
                |*  6 |       HASH JOIN                                     |                             | 20002 |   976K|       |   412   (1)| 00:00:05 |                                                                                                                                                                  
                |   7 |        TABLE ACCESS FULL                            | PV                          | 20002 |   605K|       |    34   (0)| 00:00:01 |                                                                                                                                                                  
                |   8 |        TABLE ACCESS FULL                            | PD                          | 80000 |  1484K|       |   377   (1)| 00:00:05 |                                                                                                                                                                  
                |   9 |       TABLE ACCESS FULL                             | PD                          | 80000 |  3125K|       |   377   (1)| 00:00:05 |                                                                                                                                                                  
                |* 10 |      HASH JOIN                                      |                             |  5715 |   519K|       |  1029   (1)| 00:00:13 |                                                                                                                                                                  
                |* 11 |       HASH JOIN                                     |                             |  5715 |   290K|       |   651   (1)| 00:00:08 |                                                                                                                                                                  
                |* 12 |        TABLE ACCESS FULL                            | PG                          |  5715 |   184K|       |   274   (1)| 00:00:04 |                                                                                                                                                                  
                |  13 |        TABLE ACCESS FULL                            | PD                          | 80000 |  1484K|       |   377   (1)| 00:00:05 |                                                                                                                                                                  
                |  14 |       TABLE ACCESS FULL                             | PD                          | 80000 |  3203K|       |   377   (1)| 00:00:05 |                                                                                                                                                                  
                |  15 |   SORT ORDER BY                                     |                             |       |   145 |       |  1615   (1)| 00:00:20 |                                                                                                                                                                  
                |* 16 |    FILTER                                           |                             |       |       |       |            |          |                                                                                                                                                                  
                |  17 |     NESTED LOOPS                                    |                             |     1 |   145 |       |  1614   (1)| 00:00:20 |                                                                                                                                                                  
                |* 18 |      HASH JOIN                                      |                             |   416 | 33280 |       |  1203   (1)| 00:00:15 |                                                                                                                                                                  
                |  19 |       MERGE JOIN CARTESIAN                          |                             |     9 |   441 |       |    13   (0)| 00:00:01 |                                                                                                                                                                  
                |* 20 |        TABLE ACCESS BY INDEX ROWID                  | PD                          |     1 |    40 |       |    10   (0)| 00:00:01 |                                                                                                                                                                  
                |* 21 |         INDEX RANGE SCAN                            | I_PD_1                      |     9 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                  
                |  22 |        BUFFER SORT                                  |                             |   400 |  3600 |       |     3   (0)| 00:00:01 |                                                                                                                                                                  
                |  23 |         TABLE ACCESS FULL                           | SR                          |   400 |  3600 |       |     3   (0)| 00:00:01 |                                                                                                                                                                  
                |  24 |       VIEW                                          |                             |   366K|    10M|       |  1188   (1)| 00:00:15 |                                                                                                                                                                  
                |  25 |        SORT UNIQUE                                  |                             |       |       |       |            |          |                                                                                                                                                                  
                |  26 |         UNION-ALL                                   |                             |       |       |       |            |          |                                                                                                                                                                  
                |  27 |          NESTED LOOPS                               |                             |   316K|    10M|       |  1023   (4)| 00:00:13 |                                                                                                                                                                  
                |  28 |           TABLE ACCESS FULL                         | PR                          |   320K|  9687K|       |   993   (1)| 00:00:12 |                                                                                                                                                                  
                |* 29 |           INDEX UNIQUE SCAN                         | PKPD                        |     1 |     5 |       |     0   (0)|          |                                                                                                                                                                  
                |  30 |          NESTED LOOPS                               |                             | 50005 |  1904K|       |   278   (3)| 00:00:04 |                                                                                                                                                                  
                |* 31 |           TABLE ACCESS FULL                         | PG                          | 50005 |  1660K|       |   274   (1)| 00:00:04 |                                                                                                                                                                  
                |* 32 |           INDEX UNIQUE SCAN                         | PKPD                        |     1 |     5 |       |     0   (0)|          |                                                                                                                                                                  
                |* 33 |      VIEW                                           |                             |     1 |    65 |       |     1   (0)| 00:00:01 |                                                                                                                                                                  
                |  34 |       SORT UNIQUE                                   |                             |       |       |       |            |          |                                                                                                                                                                  
                |  35 |        UNION-ALL PARTITION                          |                             |       |       |       |            |          |                                                                                                                                                                  
                |* 36 |         TABLE ACCESS BY INDEX ROWID                 | PD                          |     1 |   113 |       |     2   (0)| 00:00:01 |                                                                                                                                                                  
                |* 37 |          INDEX UNIQUE SCAN                          | PKPD                        |     1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                  
                |  38 |         NESTED LOOPS                                |                             |  6609K|  1752M|       |   369  (98)| 00:00:05 |                                                                                                                                                                  
                |  39 |          TABLE ACCESS BY INDEX ROWID                | PD                          |     1 |   113 |       |     2   (0)| 00:00:01 |                                                                                                                                                                  
                |* 40 |           INDEX UNIQUE SCAN                         | PKPD                        |     1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                  
                |* 41 |          VIEW                                       |                             |  6609K|  1040M|       |   367  (99)| 00:00:05 |                                                                                                                                                                  
                |  42 |           SORT GROUP BY                             |                             |  6609K|  1203M|       |   367  (99)| 00:00:05 |                                                                                                                                                                  
                |* 43 |            VIEW                                     |                             |  6609K|  1203M|       |    40  (85)| 00:00:01 |                                                                                                                                                                  
                |  44 |             UNION ALL (RECURSIVE WITH) BREADTH FIRST|                             |       |       |       |            |          |                                                                                                                                                                  
                |  45 |              VIEW                                   |                             |   257 | 42919 |       |     2   (0)| 00:00:01 |                                                                                                                                                                  
                |* 46 |               TABLE ACCESS FULL                     | SYS_TEMP_0FD9D673C_7D2B1EF2 |   257 | 42919 |       |     2   (0)| 00:00:01 |                                                                                                                                                                  
                |* 47 |              HASH JOIN                              |                             |  6609K|  2546M|       |    37  (90)| 00:00:01 |                                                                                                                                                                  
                |  48 |               RECURSIVE WITH PUMP                   |                             |       |       |       |            |          |                                                                                                                                                                  
                |  49 |               VIEW                                  |                             | 25717 |  3365K|       |     2   (0)| 00:00:01 |                                                                                                                                                                  
                |  50 |                TABLE ACCESS FULL                    | SYS_TEMP_0FD9D673C_7D2B1EF2 | 25717 |  4194K|       |     2   (0)| 00:00:01 |                                                                                                                                                                  
                -------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                                             
                Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
                ---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                             
                5 - access("P4"."II"="V1"."VI")                                                                                                                                                                                                                                                                              
                6 - access("V1"."PI"="P3"."II")                                                                                                                                                                                                                                                                              
                10 - access("P6"."II"="G1"."VI")                                                                                                                                                                                                                                                                             
                11 - access("G1"."PI"="P5"."II")                                                                                                                                                                                                                                                                             
                12 - filter(("G1"."VI" IS NOT NULL AND "G1"."RG"='RE'))                                                                                                                                                                                                                                                      
                16 - filter(TO_DATE(:STARTDATE)<=TO_DATE(:ENDDATE)+1)                                                                                                                                                                                                                                                        
                18 - access("R1"."KE"="RO"."RK")                                                                                                                                                                                                                                                                             
                20 - filter(("P0"."DA">=TO_DATE(:STARTDATE) AND "P0"."DA"<=TO_DATE(:ENDDATE)+1))                                                                                                                                                                                                                             
                21 - access("P0"."RE"=:RE)                                                                                                                                                                                                                                                                                   
                29 - access("PR"."PI"="P"."II")                                                                                                                                                                                                                                                                              
                31 - filter("PG"."RG"='RO')                                                                                                                                                                                                                                                                                  
                32 - access("PG"."PI"="P"."II")                                                                                                                                                                                                                                                                              
                33 - filter("BV"."ROOT_II"="P0"."II")                                                                                                                                                                                                                                                                        
                36 - filter("P1"."RE"=:RE)                                                                                                                                                                                                                                                                                   
                37 - access("P1"."II"="RO"."PI")                                                                                                                                                                                                                                                                             
                40 - access("P2"."II"="RO"."PI")                                                                                                                                                                                                                                                                             
                41 - filter(("Z1"."ROOT_RE"=:RE AND "Z1"."VI"="P2"."II" AND "Z1"."VI"="RO"."PI"))                                                                                                                                                                                                                            
                43 - filter("ROOT_RE"=:RE)                                                                                                                                                                                                                                                                                   
                46 - filter("C2"=:RE)                                                                                                                                                                                                                                                                                        
                47 - access("Z2"."VI"="Z0"."II")    
                In the lines 1 to 14 of the plan the “with-view” “z0” of my query is build and put into a temporary table. When I run only this select I get:

                I'll continue in an other posting because ot the 30000 characters limitation.
                • 20. Re: Query on 11gR2 much slower than on 10gR2
                  UW (Germany)
                  -------------------------------------------------------------------------------------------------------------------
                  | Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                  -------------------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT      |      |      1 |        |  40004 |00:00:03.54 |    6476 |       |       |          |
                  |   1 |  SORT UNIQUE          |      |      1 |  25717 |  40004 |00:00:03.54 |    6476 |  6927K|  1052K| 6157K (0)|
                  |   2 |   UNION-ALL           |      |      1 |        |  40004 |00:00:03.18 |    6476 |       |       |          |
                  |*  3 |    HASH JOIN          |      |      1 |  20002 |  20002 |00:00:01.57 |    2811 |  2120K|  1131K| 3020K (0)|
                  |*  4 |     HASH JOIN         |      |      1 |  20002 |  20002 |00:00:00.96 |    1466 |  1841K|  1227K| 2509K (0)|
                  |   5 |      TABLE ACCESS FULL| PV   |      1 |  20002 |  20002 |00:00:00.06 |     121 |       |       |          |
                  |   6 |      TABLE ACCESS FULL| PD   |      1 |  80000 |  80000 |00:00:00.19 |    1345 |       |       |          |
                  |   7 |     TABLE ACCESS FULL | PD   |      1 |  80000 |  80000 |00:00:00.13 |    1345 |       |       |          |
                  |*  8 |    HASH JOIN          |      |      1 |   5715 |  20002 |00:00:01.22 |    3665 |  2112K|  1127K| 2964K (0)|
                  |*  9 |     HASH JOIN         |      |      1 |   5715 |  20002 |00:00:00.62 |    2320 |  1829K|  1219K| 2519K (0)|
                  |* 10 |      TABLE ACCESS FULL| PG   |      1 |   5715 |  20002 |00:00:00.04 |     975 |       |       |          |
                  |  11 |      TABLE ACCESS FULL| PD   |      1 |  80000 |  80000 |00:00:00.12 |    1345 |       |       |          |
                  |  12 |     TABLE ACCESS FULL | PD   |      1 |  80000 |  80000 |00:00:00.13 |    1345 |       |       |          |
                  -------------------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     3 - access("P4"."II"="V1"."VI")
                     4 - access("V1"."PI"="P3"."II")
                     8 - access("P6"."II"="G1"."VI")
                     9 - access("G1"."PI"="P5"."II")
                    10 - filter(("G1"."VI" IS NOT NULL AND "G1"."RG"='RE'))
                  So the estimation is not bad. The temporary table is a little bit larger than estimated but less than twice of the estimated size.

                  In the lines 34 to 50 this temporary table is used to calculate the recursive “with-view” and the view “bv” is created.
                  When I only run this part of the view I get this plan:
                  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  | Id  | Operation                                        | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
                  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT                                 |                             |      1 |        |     17 |00:00:08.85 |   10287 |    608 |    608 |       |       |          |
                  |   1 |  TEMP TABLE TRANSFORMATION                       |                             |      1 |        |     17 |00:00:08.85 |   10287 |    608 |    608 |       |       |          |
                  |   2 |   LOAD AS SELECT                                 |                             |      1 |        |      0 |00:00:06.26 |    7095 |      0 |    608 |   523K|   523K|  523K (0)|
                  |   3 |    SORT UNIQUE                                   |                             |      1 |  25717 |  40004 |00:00:05.89 |    6476 |      0 |      0 |  6927K|  1052K| 6157K (0)|
                  |   4 |     UNION-ALL                                    |                             |      1 |        |  40004 |00:00:05.43 |    6476 |      0 |      0 |       |       |          |
                  |*  5 |      HASH JOIN                                   |                             |      1 |  20002 |  20002 |00:00:02.34 |    2811 |      0 |      0 |  2217K|  1109K| 3047K (0)|
                  |*  6 |       HASH JOIN                                  |                             |      1 |  20002 |  20002 |00:00:01.17 |    1466 |      0 |      0 |  1841K|  1227K| 2550K (0)|
                  |   7 |        TABLE ACCESS FULL                         | PV                          |      1 |  20002 |  20002 |00:00:00.06 |     121 |      0 |      0 |       |       |          |
                  |   8 |        TABLE ACCESS FULL                         | PD                          |      1 |  80000 |  80000 |00:00:00.24 |    1345 |      0 |      0 |       |       |          |
                  |   9 |       TABLE ACCESS FULL                          | PD                          |      1 |  80000 |  80000 |00:00:00.25 |    1345 |      0 |      0 |       |       |          |
                  |* 10 |      HASH JOIN                                   |                             |      1 |   5715 |  20002 |00:00:02.35 |    3665 |      0 |      0 |  2324K|  1094K| 3001K (0)|
                  |* 11 |       HASH JOIN                                  |                             |      1 |   5715 |  20002 |00:00:01.19 |    2320 |      0 |      0 |  1936K|  1192K| 2522K (0)|
                  |* 12 |        TABLE ACCESS FULL                         | PG                          |      1 |   5715 |  20002 |00:00:00.08 |     975 |      0 |      0 |       |       |          |
                  |  13 |        TABLE ACCESS FULL                         | PD                          |      1 |  80000 |  80000 |00:00:00.24 |    1345 |      0 |      0 |       |       |          |
                  |  14 |       TABLE ACCESS FULL                          | PD                          |      1 |  80000 |  80000 |00:00:00.25 |    1345 |      0 |      0 |       |       |          |
                  |  15 |   VIEW                                           |                             |      1 |    661M|     17 |00:00:02.59 |    3189 |    608 |      0 |       |       |          |
                  |  16 |    SORT UNIQUE                                   |                             |      1 |    661M|     17 |00:00:02.59 |    3189 |    608 |      0 |  4096 |  4096 | 4096  (0)|
                  |  17 |     UNION-ALL                                    |                             |      1 |        |     17 |00:00:02.59 |    3189 |    608 |      0 |       |       |          |
                  |  18 |      TABLE ACCESS BY INDEX ROWID                 | PD                          |      1 |      9 |      9 |00:00:00.01 |      11 |      0 |      0 |       |       |          |
                  |* 19 |       INDEX RANGE SCAN                           | I_PD_1                      |      1 |      9 |      9 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
                  |* 20 |      HASH JOIN                                   |                             |      1 |    661M|      8 |00:00:02.59 |    3178 |    608 |      0 |  5790K|  2439K| 7860K (0)|
                  |  21 |       TABLE ACCESS FULL                          | PD                          |      1 |  80000 |  80000 |00:00:00.24 |    1345 |      0 |      0 |       |       |          |
                  |  22 |       VIEW                                       |                             |      1 |    661M|      8 |00:00:01.66 |    1833 |    608 |      0 |       |       |          |
                  |  23 |        HASH GROUP BY                             |                             |      1 |    661M|      8 |00:00:01.66 |    1833 |    608 |      0 |   736K|   736K|   15M (0)|
                  |* 24 |         VIEW                                     |                             |      1 |    661M|      8 |00:00:01.64 |    1833 |    608 |      0 |       |       |          |
                  |  25 |          UNION ALL (RECURSIVE WITH) BREADTH FIRST|                             |      1 |        |      8 |00:00:01.64 |    1833 |    608 |      0 |       |       |          |
                  |* 26 |           VIEW                                   |                             |      1 |  25717 |      4 |00:00:00.25 |     613 |    608 |      0 |       |       |          |
                  |  27 |            TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6746_7D2B1EF2 |      1 |  25717 |  40004 |00:00:00.14 |     613 |    608 |      0 |       |       |          |
                  |* 28 |           HASH JOIN                              |                             |      2 |    661M|      4 |00:00:01.39 |    1220 |      0 |      0 |  6009K|  2047K| 9070K (0)|
                  |  29 |            VIEW                                  |                             |      2 |  25717 |  80008 |00:00:00.67 |    1220 |      0 |      0 |       |       |          |
                  |  30 |             TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6746_7D2B1EF2 |      2 |  25717 |  80008 |00:00:00.24 |    1220 |      0 |      0 |       |       |          |
                  |  31 |            RECURSIVE WITH PUMP                   |                             |      2 |        |      8 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
                  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     5 - access("P4"."II"="V1"."VI")
                     6 - access("V1"."PI"="P3"."II")
                    10 - access("P6"."II"="G1"."VI")
                    11 - access("G1"."PI"="P5"."II")
                    12 - filter(("G1"."VI" IS NOT NULL AND "G1"."RG"='RE'))
                    19 - access("P1"."RE"=:RE)
                    20 - access("Z1"."VI"="P2"."II")
                    24 - filter("ROOT_RE"=:RE)
                    26 - filter("RE"=:RE)
                    28 - access("Z2"."VI"="Z0"."II")
                  Only 17 rows are returned at this part of the statement.

                  In the lines 24 to 32 of the plan the “with-view” “ro” of my query is build. When I run only this part of the select I get:
                  ------------------------------------------------------------------------------------------------------------------
                  | Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                  ------------------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT     |      |      1 |        |    370K|00:00:16.80 |   48028 |       |       |          |
                  |   1 |  SORT UNIQUE         |      |      1 |    366K|    370K|00:00:16.80 |   48028 |    31M|  2012K|   28M (0)|
                  |   2 |   UNION-ALL          |      |      1 |        |    370K|00:00:13.59 |   48028 |       |       |          |
                  |   3 |    NESTED LOOPS      |      |      1 |    316K|    320K|00:00:08.33 |   42431 |       |       |          |
                  |   4 |     TABLE ACCESS FULL| PR   |      1 |    320K|    320K|00:00:00.96 |    3711 |       |       |          |
                  |*  5 |     INDEX UNIQUE SCAN| PKPD |    320K|      1 |    320K|00:00:03.10 |   38720 |       |       |          |
                  |   6 |    NESTED LOOPS      |      |      1 |  50005 |  50005 |00:00:01.31 |    5597 |       |       |          |
                  |*  7 |     TABLE ACCESS FULL| PG   |      1 |  50005 |  50005 |00:00:00.16 |     975 |       |       |          |
                  |*  8 |     INDEX UNIQUE SCAN| PKPD |  50005 |      1 |  50005 |00:00:00.48 |    4622 |       |       |          |
                  ------------------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     5 - access("PR"."PI"="P"."II")
                     7 - filter("PG"."RG"='RO')
                     8 - access("PG"."PI"="P"."II")
                                                                                                                                                                               
                  So the estimation for this part is quite accurate.

                  The index access on Table PD in line 20 returns not only one but five rows. So the Cartesian Join with the 400 rows of table SR gives a result set of 2000 rows.

                  This 2000 rows get hash joined with the 370k rows of line 24 to 32.

                  I can query only this part as well and get a result of 1.8 Million rows. This is indeed more, than the 33280 rows expected.
                  with
                  ro as
                  ( select
                      pr.pi,
                      pr.rk,
                      pr.om
                    from
                      pr pr
                      join pd p on pr.pi = p.ii
                    union
                    select
                      pg.pi,
                      pg.mk,
                      pg.om
                    from
                      pg pg
                      join pd p on pg.pi = p.ii
                    where pg.rg = 'RO'
                  )
                  select
                    p0.da,
                    p0.tt,
                    r1.ke,
                    round(ro.om,3) ov
                  from pd p0
                  join sr r1 on 1=1
                  join ro on r1.ke = ro.rk
                  where
                    p0.re = :re and
                    p0.da between to_date(:startdate) and to_date(:enddate)+1
                  order by
                    p0.da desc,
                    r1.ro;
                  
                  -------------------------------------------------------------------------------------------------------------------------------
                  | Id  | Operation                       | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                  -------------------------------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT                |        |      1 |        |   1846K|00:00:51.24 |   48045 |       |       |          |
                  |   1 |  SORT ORDER BY                  |        |      1 |   1694K|   1846K|00:00:51.24 |   48045 |   130M|  3864K|  115M (0)|
                  |*  2 |   FILTER                        |        |      1 |        |   1846K|00:00:35.66 |   48045 |       |       |          |
                  |*  3 |    HASH JOIN                    |        |      1 |   1694K|   1846K|00:00:26.28 |   48045 |   876K|   876K| 1403K (0)|
                  |   4 |     MERGE JOIN CARTESIAN        |        |      1 |   1852 |   2000 |00:00:00.02 |      17 |       |       |          |
                  |*  5 |      TABLE ACCESS BY INDEX ROWID| PD     |      1 |      5 |      5 |00:00:00.01 |      11 |       |       |          |
                  |*  6 |       INDEX RANGE SCAN          | I_PD_1 |      1 |      9 |      9 |00:00:00.01 |       2 |       |       |          |
                  |   7 |      BUFFER SORT                |        |      5 |    400 |   2000 |00:00:00.01 |       6 | 18432 | 18432 |16384  (0)|
                  |   8 |       TABLE ACCESS FULL         | SR     |      1 |    400 |    400 |00:00:00.01 |       6 |       |       |          |
                  |   9 |     VIEW                        |        |      1 |    366K|    370K|00:00:18.05 |   48028 |       |       |          |
                  |  10 |      SORT UNIQUE                |        |      1 |    366K|    370K|00:00:16.16 |   48028 |    31M|  2012K|   28M (0)|
                  |  11 |       UNION-ALL                 |        |      1 |        |    370K|00:00:13.21 |   48028 |       |       |          |
                  |  12 |        NESTED LOOPS             |        |      1 |    316K|    320K|00:00:08.06 |   42431 |       |       |          |
                  |  13 |         TABLE ACCESS FULL       | PR     |      1 |    320K|    320K|00:00:00.93 |    3711 |       |       |          |
                  |* 14 |         INDEX UNIQUE SCAN       | PKPD   |    320K|      1 |    320K|00:00:03.01 |   38720 |       |       |          |
                  |  15 |        NESTED LOOPS             |        |      1 |  50005 |  50005 |00:00:01.32 |    5597 |       |       |          |
                  |* 16 |         TABLE ACCESS FULL       | PG     |      1 |  50005 |  50005 |00:00:00.16 |     975 |       |       |          |
                  |* 17 |         INDEX UNIQUE SCAN       | PKPD   |  50005 |      1 |  50005 |00:00:00.48 |    4622 |       |       |          |
                  -------------------------------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     2 - filter(TO_DATE(:STARTDATE)<=TO_DATE(:ENDDATE)+1)
                     3 - access("R1"."KE"="RO"."RK")
                     5 - filter(("P0"."DA">=TO_DATE(:STARTDATE) AND "P0"."DA"<=TO_DATE(:ENDDATE)+1))
                     6 - access("P0"."RE"=:RE)
                    14 - access("PR"."PI"="P"."II")
                    16 - filter("PG"."RG"='RO')
                    17 - access("PG"."PI"="P"."II")
                  But finally this 1846470 rows have to be nested loop joined with the 17 rows mentioned above, leading to a result of 62 rows, as we know from the query with the optimizer hint.
                  I can’t imagine that the database needs several hours for this task. When it needs 51 seconds for the 1.8 Million rows it might be something about 17 minutes in the nested loop but not so much more.
                  • 21. Re: Query on 11gR2 much slower than on 10gR2
                    jgarry
                    I’m not sure whether you’re interested in this, but I created a small script to build a test case with data that are a little bit similar to our real world data. At least the similarity is good enough to reproduce the problem.
                    Someone is usually going to be interested in test cases, keep up the good work.
                    • 22. Re: Query on 11gR2 much slower than on 10gR2
                      Mohamed Houri
                      Thanks a lot for having reproduced your model. Simply that is fantastic.

                      I have started the query in both 10.2.0.4 and 11.2.0.1.0 and have got the response in less than 2 seconds in both databases
                      mohamed@mhouri> select * from v$version;
                      
                      BANNER
                      --------------------------------------------------------------------------------
                      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                      PL/SQL Release 11.2.0.1.0 - Production
                      CORE     11.2.0.1.0     Production
                      TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                      NLSRTL Version 11.2.0.1.0 - Production
                      
                      
                      mohamed@mhouri> start C:\otn\qry.sql
                      
                      DA                 TT HK           SF                                                         FA KE              OV         OE
                      ---------- ---------- ------------ -------------------------------------------------- ---------- ------- ---------- ----------
                      08.12.2011 955.241049 R            1                                                           1 242         82.312     82.312
                      08.12.2011 955.241049 R            1                                                           1 80          25.556     25.556
                      08.12.2011 955.241049 R            1                                                           1 382        319.223    319.223
                      08.12.2011 955.241049 R            1                                                           1 18         265.509    265.509
                      08.12.2011 955.241049 R            1                                                           1 69          60.145     60.145
                      08.12.2011 955.241049 R            1                                                           1 11          97.194     97.194
                      08.12.2011 955.241049 R            1                                                           1 29         271.759    271.759
                      08.12.2011 955.241049 R            1                                                           1 103        279.485    279.485
                      08.12.2011 955.241049 R            1                                                           1 44           59.74      59.74
                      08.12.2011 955.241049 R < V        1* 0.003895/ 0.857140                                  .00454 33            19.1       .087
                      08.12.2011 955.241049 R < V        1* 0.003895/ 0.857140                                  .00454 319         79.216        .36
                      08.12.2011 955.241049 R < V        1* 0.003895/ 0.857140                                  .00454 186        118.156       .536
                      08.12.2011 955.241049 R < V        1* 0.003895/ 0.857140                                  .00454 225        353.392      1.604
                      08.12.2011 955.241049 R < G        1*   390.001/   152.720                               2.55369 21         310.631    793.256
                      08.12.2011 955.241049 R < G        1*   390.001/   152.720                               2.55369 157         62.919    160.677
                      08.12.2011 955.241049 R < G        1*   390.001/   152.720                               2.55369 348        342.282    874.082
                      08.12.2011 955.241049 R < G        1*   390.001/   152.720                               2.55369 272        303.363    774.694
                      08.12.2011 955.241049 R < G        1*    52.435/   144.282                                .36342 130         87.409     31.766
                      08.12.2011 955.241049 R < G        1*    52.435/   144.282                                .36342 79         370.577    134.675
                      08.12.2011 955.241049 R < G        1*    52.435/   144.282                                .36342 239         46.213     16.795
                      08.12.2011 955.241049 R < G        1*    52.435/   144.282                                .36342 398        165.245     60.053
                      08.12.2011 955.241049 R < V        1* 0.592435/ 0.936700                                  .63247 15         172.252    108.944
                      08.12.2011 955.241049 R < V        1* 0.592435/ 0.936700                                  .63247 219         32.608     20.623
                      08.12.2011 955.241049 R < V        1* 0.592435/ 0.936700                                  .63247 5            18.58     11.751
                      08.12.2011 955.241049 R < V        1* 0.592435/ 0.936700                                  .63247 65         398.878    252.279
                      08.12.2011 955.241049 R < V        1* 0.592435/ 0.936700                                  .63247 378         38.802     24.541
                      08.12.2011 955.241049 R < V        1* 0.592435/ 0.936700                                  .63247 26         125.891     79.622
                      08.12.2011 955.241049 R < V        1* 0.592435/ 0.936700                                  .63247 98         118.785     75.128
                      08.12.2011 955.241049 R < V        1* 0.592435/ 0.936700                                  .63247 51         104.091     65.834
                      08.12.2011 955.241049 R < V        1* 0.592435/ 0.936700                                  .63247 184        200.043    126.521
                      08.12.2011 955.241049 R < V < V    1* 0.592435/ 0.936700* 0.102030/ 0.624643              .10331 295         11.085      1.145
                      08.12.2011 955.241049 R < V < V    1* 0.592435/ 0.936700* 0.102030/ 0.624643              .10331 173        360.532     37.247
                      08.12.2011 955.241049 R < V < V    1* 0.592435/ 0.936700* 0.102030/ 0.624643              .10331 310         135.63     14.012
                      08.12.2011 955.241049 R < V < V    1* 0.592435/ 0.936700* 0.102030/ 0.624643              .10331 7           83.985      8.676
                      08.12.2011 955.241049 R < V < V    1* 0.592435/ 0.936700* 0.718903/ 0.105311             4.31755 344        320.697   1384.624
                      08.12.2011 955.241049 R < V < V    1* 0.592435/ 0.936700* 0.718903/ 0.105311             4.31755 244        261.975    1131.09
                      08.12.2011 955.241049 R < V < V    1* 0.592435/ 0.936700* 0.718903/ 0.105311             4.31755 26         206.304    890.728
                      08.12.2011 955.241049 R < V < V    1* 0.592435/ 0.936700* 0.718903/ 0.105311             4.31755 173         92.236    398.234
                      08.12.2011 955.241049 R < V < G    1* 0.592435/ 0.936700*   397.598/   914.241            .27506 73           29.68      8.164
                      08.12.2011 955.241049 R < V < G    1* 0.592435/ 0.936700*   397.598/   914.241            .27506 241         19.555      5.379
                      08.12.2011 955.241049 R < V < G    1* 0.592435/ 0.936700*   397.598/   914.241            .27506 386        343.465     94.474
                      08.12.2011 955.241049 R < V < G    1* 0.592435/ 0.936700*   397.598/   914.241            .27506 145         26.173      7.199
                      08.12.2011 955.241049 R < V < G    1* 0.592435/ 0.936700*   231.832/   455.615            .32182 351        395.965    127.429
                      08.12.2011 955.241049 R < V < G    1* 0.592435/ 0.936700*   231.832/   455.615            .32182 265         51.318     16.515
                      08.12.2011 955.241049 R < V < G    1* 0.592435/ 0.936700*   231.832/   455.615            .32182 118         71.524     23.018
                      08.12.2011 955.241049 R < V < G    1* 0.592435/ 0.936700*   231.832/   455.615            .32182 44          45.824     14.747
                      29.04.2010  147.58957 R            1                                                           1 257        307.068    307.068
                      29.04.2010  147.58957 R            1                                                           1 1          245.752    245.752
                      29.04.2010  147.58957 R            1                                                           1 103         98.866     98.866
                      29.04.2010  147.58957 R            1                                                           1 324        218.757    218.757
                      27.03.2010 164.586938 R            1                                                           1 109        301.015    301.015
                      27.03.2010 164.586938 R            1                                                           1 208        354.091    354.091
                      27.03.2010 164.586938 R            1                                                           1 328        213.495    213.495
                      27.03.2010 164.586938 R            1                                                           1 87         330.457    330.457
                      04.07.2008 810.795021 R            1                                                           1 234        333.602    333.602
                      04.07.2008 810.795021 R            1                                                           1 335        341.234    341.234
                      04.07.2008 810.795021 R            1                                                           1 79         363.028    363.028
                      04.07.2008 810.795021 R            1                                                           1 142        111.326    111.326
                      15.03.2007 657.973337 R            1                                                           1 135        122.512    122.512
                      15.03.2007 657.973337 R            1                                                           1 47          49.658     49.658
                      15.03.2007 657.973337 R            1                                                           1 331        392.425    392.425
                      15.03.2007 657.973337 R            1                                                           1 220        369.325    369.325
                      
                      62 rows selected.
                      
                      Elapsed: 00:00:01.50
                      mohamed@mhouri> select * from table(dbms_xplan.display_cursor);
                      
                      PLAN_TABLE_OUTPUT
                      -----------------------------------------------------------------------------------------------------------------------------------------------
                      SQL_ID  22kdrcmxqjg4z, child number 0
                      -------------------------------------
                      Plan hash value: 4257550792
                      
                      -----------------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                                                | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                      -----------------------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                                         |                            |       |       |       |  4120 (100)|          |
                      |   1 |  TEMP TABLE TRANSFORMATION                               |                            |       |       |       |            |          |
                      |   2 |   LOAD AS SELECT                                         |                            |       |       |       |            |          |
                      |   3 |    SORT UNIQUE                                           |                            | 30003 |  2666K|  3144K|  2663  (48)| 00:00:32 |
                      |   4 |     UNION-ALL                                            |                            |       |       |       |            |          |
                      |*  5 |      HASH JOIN                                           |                            | 20002 |  1757K|  1624K|   994   (2)| 00:00:12 |
                      |*  6 |       HASH JOIN                                          |                            | 20002 |  1386K|       |   415   (2)| 00:00:05 |
                      |   7 |        TABLE ACCESS FULL                                 | PV                         | 20002 |   605K|       |    35   (3)| 00:00:01 |
                      |   8 |        TABLE ACCESS FULL                                 | PD                         | 80000 |  3125K|       |   379   (2)| 00:00:05 |
                      |   9 |       TABLE ACCESS FULL                                  | PD                         | 80000 |  1484K|       |   379   (2)| 00:00:05 |
                      |* 10 |      HASH JOIN                                           |                            | 10001 |   908K|       |  1036   (2)| 00:00:13 |
                      |* 11 |       HASH JOIN                                          |                            | 10001 |   507K|       |   656   (2)| 00:00:08 |
                      |* 12 |        TABLE ACCESS FULL                                 | PG                         | 10001 |   322K|       |   276   (2)| 00:00:04 |
                      |  13 |        TABLE ACCESS FULL                                 | PD                         | 80000 |  1484K|       |   379   (2)| 00:00:05 |
                      |  14 |       TABLE ACCESS FULL                                  | PD                         | 80000 |  3203K|       |   379   (2)| 00:00:05 |
                      |  15 |   LOAD AS SELECT                                         |                            |       |       |       |            |          |
                      |  16 |    SORT UNIQUE                                           |                            | 55006 |  1108K|  1672K|  1427  (63)| 00:00:18 |
                      |  17 |     UNION-ALL                                            |                            |       |       |       |            |          |
                      |* 18 |      HASH JOIN                                           |                            | 20002 |   390K|       |   415   (2)| 00:00:05 |
                      |  19 |       TABLE ACCESS FULL                                  | PV                         | 20002 |   195K|       |    35   (3)| 00:00:01 |
                      |  20 |       TABLE ACCESS FULL                                  | PD                         | 80000 |   781K|       |   379   (2)| 00:00:05 |
                      |* 21 |      HASH JOIN                                           |                            | 35004 |   717K|       |   656   (2)| 00:00:08 |
                      |* 22 |       TABLE ACCESS FULL                                  | PG                         | 35004 |   376K|       |   276   (2)| 00:00:04 |
                      |  23 |       TABLE ACCESS FULL                                  | PD                         | 80000 |   781K|       |   379   (2)| 00:00:05 |
                      |  24 |   SORT ORDER BY                                          |                            |     1 |  4116 |       |    29   (4)| 00:00:01 |
                      |* 25 |    FILTER                                                |                            |       |       |       |            |          |
                      |  26 |     NESTED LOOPS                                         |                            |     1 |  4116 |       |    28   (0)| 00:00:01 |
                      |  27 |      NESTED LOOPS                                        |                            |    20 | 82140 |       |    27   (0)| 00:00:01 |
                      |  28 |       NESTED LOOPS                                       |                            |     5 | 20380 |       |    12   (0)| 00:00:01 |
                      |  29 |        VIEW                                              |                            |    10 | 40360 |       |     2   (0)| 00:00:01 |
                      |  30 |         SORT UNIQUE                                      |                            |       |       |       |            |          |
                      |  31 |          UNION-ALL                                       |                            |       |       |       |            |          |
                      |  32 |           TABLE ACCESS BY INDEX ROWID                    | PD                         |     9 |  1017 |       |    10   (0)| 00:00:01 |
                      |* 33 |            INDEX RANGE SCAN                              | I_PD_1                     |     9 |       |       |     1   (0)| 00:00:01 |
                      |  34 |           NESTED LOOPS                                   |                            |     1 |  8193 |       | 75193  (16)| 00:15:03 |
                      |* 35 |            VIEW                                          |                            |     1 |  8080 |       | 75192  (16)| 00:15:03 |
                      |  36 |             HASH GROUP BY                                |                            |     1 | 10121 |       | 75192  (16)| 00:15:03 |
                      |  37 |              MERGE JOIN                                  |                            |    82M|   777G|       | 64576   (2)| 00:12:55 |
                      |  38 |               SORT JOIN                                  |                            | 30003 |   289M|   468M| 63140   (1)| 00:12:38 |
                      |* 39 |                VIEW                                      |                            | 30003 |   289M|       |  1030 (100)| 00:00:13 |
                      |* 40 |                 CONNECT BY NO FILTERING WITH START-WITH  |                            |       |       |       |            |          |
                      |  41 |                  VIEW                                    |                            | 30003 |  4512K|       |     3  (34)| 00:00:01 |
                      |  42 |                   TABLE ACCESS FULL                      | SYS_TEMP_0FD9D6605_13F7EFF | 30003 |  4512K|       |     3  (34)| 00:00:01 |
                      |* 43 |               SORT JOIN                                  |                            | 55006 |   698K|  2168K|   541   (3)| 00:00:07 |
                      |  44 |                VIEW                                      |                            | 55006 |   698K|       |   272   (3)| 00:00:04 |
                      |  45 |                 HASH UNIQUE                              |                            | 55006 |   698K|  1088K|   272   (3)| 00:00:04 |
                      |  46 |                  VIEW                                    |                            | 55006 |   698K|       |   501 (100)| 00:00:07 |
                      |* 47 |                   CONNECT BY NO FILTERING WITH START-WITH|                            |       |       |       |            |          |
                      |  48 |                    VIEW                                  |                            | 55006 |  1772K|       |     3  (34)| 00:00:01 |
                      |  49 |                     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6606_13F7EFF | 55006 |  1772K|       |     3  (34)| 00:00:01 |
                      |  50 |            TABLE ACCESS BY INDEX ROWID                   | PD                         |     1 |   113 |       |     1   (0)| 00:00:01 |
                      |* 51 |             INDEX UNIQUE SCAN                            | PKPD                       |     1 |       |       |     0   (0)|          |
                      |* 52 |        TABLE ACCESS BY INDEX ROWID                       | PD                         |     1 |    40 |       |     1   (0)| 00:00:01 |
                      |* 53 |         INDEX UNIQUE SCAN                                | PKPD                       |     1 |       |       |     0   (0)|          |
                      |  54 |       VIEW                                               |                            |     4 |   124 |       |     3   (0)| 00:00:01 |
                      |  55 |        SORT UNIQUE                                       |                            |       |       |       |            |          |
                      |  56 |         UNION-ALL PARTITION                              |                            |       |       |       |            |          |
                      |  57 |          NESTED LOOPS                                    |                            |     1 |    36 |       |     4   (0)| 00:00:01 |
                      |* 58 |           INDEX UNIQUE SCAN                              | PKPD                       |     1 |     5 |       |     1   (0)| 00:00:01 |
                      |  59 |           TABLE ACCESS BY INDEX ROWID                    | PR                         |     1 |    31 |       |     3   (0)| 00:00:01 |
                      |* 60 |            INDEX RANGE SCAN                              | PKPR                       |     1 |       |       |     2   (0)| 00:00:01 |
                      |  61 |          NESTED LOOPS                                    |                            |     1 |    39 |       |     3   (0)| 00:00:01 |
                      |* 62 |           INDEX UNIQUE SCAN                              | PKPD                       |     1 |     5 |       |     1   (0)| 00:00:01 |
                      |* 63 |           TABLE ACCESS BY INDEX ROWID                    | PG                         |     1 |    34 |       |     2   (0)| 00:00:01 |
                      |* 64 |            INDEX RANGE SCAN                              | PKPG                       |     1 |       |       |     1   (0)| 00:00:01 |
                      |  65 |      TABLE ACCESS BY INDEX ROWID                         | SR                         |     1 |     9 |       |     1   (0)| 00:00:01 |
                      |* 66 |       INDEX UNIQUE SCAN                                  | PKSR                       |     1 |       |       |     0   (0)|          |
                      -----------------------------------------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         5 - access("V1"."PI"="P3"."II")
                         6 - access("P4"."II"="V1"."VI")
                        10 - access("P6"."II"="G1"."VI")
                        11 - access("G1"."PI"="P5"."II")
                        12 - filter(("G1"."VI" IS NOT NULL AND "G1"."RG"='RE'))
                        18 - access("V2"."PI"="P7"."II")
                        21 - access("G2"."PI"="P8"."II")
                        22 - filter("G2"."RG"='RE')
                        25 - filter(TO_DATE(:STARTDATE)<=TO_DATE(:ENDDATE)+1)
                        33 - access("P1"."RE"=:RE)
                        35 - filter("Z1"."ROOT_RE"=:RE)
                        39 - filter("Z3"."ROOT_RE"=:RE)
                        40 - access("II"=PRIOR NULL)
                             filter("RE"=:RE)
                        43 - access(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                             filter(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                        47 - access("II"=PRIOR NULL)
                             filter("RE"=:RE)
                        51 - access("Z1"."VI"="P2"."II")
                        52 - filter(("P0"."RE"=:RE AND "P0"."DA">=TO_DATE(:STARTDATE) AND "P0"."DA"<=TO_DATE(:ENDDATE)+1))
                        53 - access("P0"."II"="BV"."ROOT_II")
                        58 - access("P"."II"="BV"."II")
                        60 - access("PR"."PI"="P"."II")
                             filter("PR"."PI"="BV"."II")
                        62 - access("P"."II"="BV"."II")
                        63 - filter("PG"."RG"='RO')
                        64 - access("PG"."PI"="P"."II")
                             filter("PG"."PI"="BV"."II")
                        66 - access("R1"."KE"="RV"."RK")
                      I am really frustrated to don't have a 11.2.0.3 data base release at my disposal to be in exact situation as yours.

                      Finally, it will be worth to post your optimizer parameters. My 11.2.0.1.0 optimizer parameters are
                      mohamed@mhouri> show parameter optimizer%
                      
                      NAME                                 TYPE        VALUE
                      ------------------------------------ ----------- ----------
                      optimizer_capture_sql_plan_baselines boolean     FALSE
                      optimizer_dynamic_sampling           integer     2
                      optimizer_features_enable            string      11.2.0.1
                      optimizer_index_caching              integer     0
                      optimizer_index_cost_adj             integer     100
                      optimizer_mode                       string      ALL_ROWS
                      optimizer_secure_view_merging        boolean     TRUE
                      optimizer_use_invisible_indexes      boolean     FALSE
                      optimizer_use_pending_statistics     boolean     FALSE
                      optimizer_use_sql_plan_baselines     boolean     TRUE
                      Mohamed Houri
                      www.hourim.wordpress.com
                      • 23. Re: Query on 11gR2 much slower than on 10gR2
                        Dom Brooks
                        I don’t understand where the database spends hours and hours for calculations.
                        If you're licensed for Diagnostic + Tuning pack then use real time sql monitoring to monitor the query real-time and find where the query current is and what the time has been spent on without it needing to finish, e.g.
                        select dbms_sqltune.report_sql_monitor('<sql_id>') from dual;
                        • 24. Re: Query on 11gR2 much slower than on 10gR2
                          UW (Germany)
                          Thanks for this information Mohamed.
                          I just tried my query on 11.2.0.3 with the optimizer hint OPTIMIZER_FEATURES_ENABLE('11.2.0.1') and it was quite fast, giving me this plan:
                          -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          | Id  | Operation                                            | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
                          -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT                                     |                             |      1 |        |     62 |00:00:30.49 |   58332 |   6898 |    608 |       |       |          |
                          |   1 |  TEMP TABLE TRANSFORMATION                           |                             |      1 |        |     62 |00:00:30.49 |   58332 |   6898 |    608 |       |       |          |
                          |   2 |   LOAD AS SELECT                                     |                             |      1 |        |      0 |00:00:06.34 |    7095 |   2427 |    608 |   523K|   523K|  523K (0)|
                          |   3 |    SORT UNIQUE                                       |                             |      1 |  25717 |  40004 |00:00:05.98 |    6476 |   2427 |      0 |  6927K|  1052K| 6157K (0)|
                          |   4 |     UNION-ALL                                        |                             |      1 |        |  40004 |00:00:05.55 |    6476 |   2427 |      0 |       |       |          |
                          |*  5 |      HASH JOIN                                       |                             |      1 |  20002 |  20002 |00:00:02.45 |    2811 |   1458 |      0 |  2217K|  1109K| 3011K (0)|
                          |*  6 |       HASH JOIN                                      |                             |      1 |  20002 |  20002 |00:00:01.32 |    1466 |   1458 |      0 |  1841K|  1227K| 2511K (0)|
                          |   7 |        TABLE ACCESS FULL                             | PV                          |      1 |  20002 |  20002 |00:00:00.10 |     121 |    119 |      0 |       |       |          |
                          |   8 |        TABLE ACCESS FULL                             | PD                          |      1 |  80000 |  80000 |00:00:00.37 |    1345 |   1339 |      0 |       |       |          |
                          |   9 |       TABLE ACCESS FULL                              | PD                          |      1 |  80000 |  80000 |00:00:00.24 |    1345 |      0 |      0 |       |       |          |
                          |* 10 |      HASH JOIN                                       |                             |      1 |   5715 |  20002 |00:00:02.37 |    3665 |    969 |      0 |  2324K|  1094K| 2947K (0)|
                          |* 11 |       HASH JOIN                                      |                             |      1 |   5715 |  20002 |00:00:01.24 |    2320 |    969 |      0 |  1936K|  1192K| 2513K (0)|
                          |* 12 |        TABLE ACCESS FULL                             | PG                          |      1 |   5715 |  20002 |00:00:00.16 |     975 |    969 |      0 |       |       |          |
                          |  13 |        TABLE ACCESS FULL                             | PD                          |      1 |  80000 |  80000 |00:00:00.23 |    1345 |      0 |      0 |       |       |          |
                          |  14 |       TABLE ACCESS FULL                              | PD                          |      1 |  80000 |  80000 |00:00:00.24 |    1345 |      0 |      0 |       |       |          |
                          |  15 |   SORT ORDER BY                                      |                             |      1 |    140M|     62 |00:00:24.15 |   51234 |   4471 |      0 |  9216 |  9216 | 8192  (0)|
                          |* 16 |    FILTER                                            |                             |      1 |        |     62 |00:00:24.15 |   51234 |   4471 |      0 |       |       |          |
                          |* 17 |     HASH JOIN                                        |                             |      1 |    140M|     62 |00:00:24.15 |   51234 |   4471 |      0 |  1316K|  1003K|   63M (0)|
                          |* 18 |      HASH JOIN                                       |                             |      1 |     15M|   5200 |00:00:02.56 |    3206 |    616 |      0 |   811K|   811K|  791K (0)|
                          |  19 |       MERGE JOIN CARTESIAN                           |                             |      1 |   1852 |   2000 |00:00:00.04 |      17 |      8 |      0 |       |       |          |
                          |* 20 |        TABLE ACCESS BY INDEX ROWID                   | PD                          |      1 |      5 |      5 |00:00:00.01 |      11 |      2 |      0 |       |       |          |
                          |* 21 |         INDEX RANGE SCAN                             | I_PD_1                      |      1 |      9 |      9 |00:00:00.01 |       2 |      2 |      0 |       |       |          |
                          |  22 |        BUFFER SORT                                   |                             |      5 |    400 |   2000 |00:00:00.02 |       6 |      6 |      0 | 18432 | 18432 |16384  (0)|
                          |  23 |         TABLE ACCESS FULL                            | SR                          |      1 |    400 |    400 |00:00:00.01 |       6 |      6 |      0 |       |       |          |
                          |  24 |       VIEW                                           |                             |      1 |    661M|     17 |00:00:02.49 |    3189 |    608 |      0 |       |       |          |
                          |  25 |        SORT UNIQUE                                   |                             |      1 |    661M|     17 |00:00:02.49 |    3189 |    608 |      0 |  4096 |  4096 | 4096  (0)|
                          |  26 |         UNION-ALL                                    |                             |      1 |        |     17 |00:00:02.49 |    3189 |    608 |      0 |       |       |          |
                          |  27 |          TABLE ACCESS BY INDEX ROWID                 | PD                          |      1 |      9 |      9 |00:00:00.01 |      11 |      0 |      0 |       |       |          |
                          |* 28 |           INDEX RANGE SCAN                           | I_PD_1                      |      1 |      9 |      9 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
                          |* 29 |          HASH JOIN                                   |                             |      1 |    661M|      8 |00:00:02.49 |    3178 |    608 |      0 |  5790K|  2439K| 7859K (0)|
                          |  30 |           TABLE ACCESS FULL                          | PD                          |      1 |  80000 |  80000 |00:00:00.23 |    1345 |      0 |      0 |       |       |          |
                          |  31 |           VIEW                                       |                             |      1 |    661M|      8 |00:00:01.59 |    1833 |    608 |      0 |       |       |          |
                          |  32 |            SORT GROUP BY                             |                             |      1 |    661M|      8 |00:00:01.59 |    1833 |    608 |      0 |  2048 |  2048 | 2048  (0)|
                          |* 33 |             VIEW                                     |                             |      1 |    661M|      8 |00:00:01.59 |    1833 |    608 |      0 |       |       |          |
                          |  34 |              UNION ALL (RECURSIVE WITH) BREADTH FIRST|                             |      1 |        |      8 |00:00:01.59 |    1833 |    608 |      0 |       |       |          |
                          |* 35 |               VIEW                                   |                             |      1 |  25717 |      4 |00:00:00.24 |     613 |    608 |      0 |       |       |          |
                          |  36 |                TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6778_7D2B1EF2 |      1 |  25717 |  40004 |00:00:00.13 |     613 |    608 |      0 |       |       |          |
                          |* 37 |               HASH JOIN                              |                             |      2 |    661M|      4 |00:00:01.35 |    1220 |      0 |      0 |  6009K|  2047K| 9105K (0)|
                          |  38 |                VIEW                                  |                             |      2 |  25717 |  80008 |00:00:00.65 |    1220 |      0 |      0 |       |       |          |
                          |  39 |                 TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6778_7D2B1EF2 |      2 |  25717 |  80008 |00:00:00.23 |    1220 |      0 |      0 |       |       |          |
                          |  40 |                RECURSIVE WITH PUMP                   |                             |      2 |        |      8 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
                          |  41 |      VIEW                                            |                             |      1 |    366K|    370K|00:00:18.48 |   48028 |   3855 |      0 |       |       |          |
                          |  42 |       SORT UNIQUE                                    |                             |      1 |    366K|    370K|00:00:16.58 |   48028 |   3855 |      0 |    31M|  2012K|   28M (0)|
                          |  43 |        UNION-ALL                                     |                             |      1 |        |    370K|00:00:13.65 |   48028 |   3855 |      0 |       |       |          |
                          |  44 |         NESTED LOOPS                                 |                             |      1 |    316K|    320K|00:00:08.50 |   42431 |   3855 |      0 |       |       |          |
                          |  45 |          TABLE ACCESS FULL                           | PR                          |      1 |    320K|    320K|00:00:01.17 |    3711 |   3705 |      0 |       |       |          |
                          |* 46 |          INDEX UNIQUE SCAN                           | PKPD                        |    320K|      1 |    320K|00:00:03.18 |   38720 |    150 |      0 |       |       |          |
                          |  47 |         NESTED LOOPS                                 |                             |      1 |  50005 |  50005 |00:00:01.27 |    5597 |      0 |      0 |       |       |          |
                          |* 48 |          TABLE ACCESS FULL                           | PG                          |      1 |  50005 |  50005 |00:00:00.16 |     975 |      0 |      0 |       |       |          |
                          |* 49 |          INDEX UNIQUE SCAN                           | PKPD                        |  50005 |      1 |  50005 |00:00:00.47 |    4622 |      0 |      0 |       |       |          |
                          -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                          
                             5 - access("P4"."II"="V1"."VI")
                             6 - access("V1"."PI"="P3"."II")
                            10 - access("P6"."II"="G1"."VI")
                            11 - access("G1"."PI"="P5"."II")
                            12 - filter(("G1"."VI" IS NOT NULL AND "G1"."RG"='RE'))
                            16 - filter(TO_DATE(:STARTDATE)<=TO_DATE(:ENDDATE)+1)
                            17 - access("R1"."KE"="RO"."RK" AND "RO"."PI"="BV"."II")
                            18 - access("BV"."ROOT_II"="P0"."II")
                            20 - filter(("P0"."DA">=TO_DATE(:STARTDATE) AND "P0"."DA"<=TO_DATE(:ENDDATE)+1))
                            21 - access("P0"."RE"=:RE)
                            28 - access("P1"."RE"=:RE)
                            29 - access("Z1"."VI"="P2"."II")
                            33 - filter("ROOT_RE"=:RE)
                            35 - filter("RE"=:RE)
                            37 - access("Z2"."VI"="Z0"."II")
                            46 - access("PR"."PI"="P"."II")
                            48 - filter("PG"."RG"='RO')
                            49 - access("PG"."PI"="P"."II")
                          I removed the hint and now I’m waiting again. Yesterday I installed an Oracle 11g Express Edition on a Linux machine at home and I was able to reproduce the problem. The Express Edition has the parameter optimizer_features_enable = 11.2.0.2
                          Apart from this one all my optimizer parameters are the same as yours, here in the company and on the Linux machine at home.

                          When I reproduced the problem at home I took the scripts and the queries that I posted here in the forum and I found, that there is a copy and paste bug in some queries, which I wrote here, leading to an error:
                          ORA-01756: quoted string not properly terminated 
                          when trying to execute them.

                          So when there is a line
                          z2.kt||' < ', '||z0.bn||'('||z0.at||')',
                          it has to be replaced with the two lines
                          z2.kt||' < '||z0.vr,
                          z2.bkt||', '||z0.bn||'('||z0.at||')',
                          Sorry for this mistake.
                          • 25. Re: Query on 11gR2 much slower than on 10gR2
                            UW (Germany)
                            Oh, sorry another mistake.
                            I just posted the plan for the modified shortened query, that is not comparable with the plan for my first query that you posted for your 11.2.0.1 database. So here is the right plan:
                            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                            | Id  | Operation                                     | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
                            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT                              |                             |      1 |        |     62 |00:00:32.17 |   60243 |    598 |    598 |       |       |          |
                            |   1 |  TEMP TABLE TRANSFORMATION                    |                             |      1 |        |     62 |00:00:32.17 |   60243 |    598 |    598 |       |       |          |
                            |   2 |   LOAD AS SELECT                              |                             |      1 |        |      0 |00:00:05.41 |    6973 |      0 |    488 |   523K|   523K|  523K (0)|
                            |   3 |    SORT UNIQUE                                |                             |      1 |  25717 |  40004 |00:00:05.05 |    6476 |      0 |      0 |  5935K|   992K| 5275K (0)|
                            |   4 |     UNION-ALL                                 |                             |      1 |        |  40004 |00:00:04.66 |    6476 |      0 |      0 |       |       |          |
                            |*  5 |      HASH JOIN                                |                             |      1 |  20002 |  20002 |00:00:01.86 |    2811 |      0 |      0 |  2217K|  1109K| 3043K (0)|
                            |*  6 |       HASH JOIN                               |                             |      1 |  20002 |  20002 |00:00:01.16 |    1466 |      0 |      0 |  1841K|  1227K| 2517K (0)|
                            |   7 |        TABLE ACCESS FULL                      | PV                          |      1 |  20002 |  20002 |00:00:00.06 |     121 |      0 |      0 |       |       |          |
                            |   8 |        TABLE ACCESS FULL                      | PD                          |      1 |  80000 |  80000 |00:00:00.23 |    1345 |      0 |      0 |       |       |          |
                            |   9 |       TABLE ACCESS FULL                       | PD                          |      1 |  80000 |  80000 |00:00:00.14 |    1345 |      0 |      0 |       |       |          |
                            |* 10 |      HASH JOIN                                |                             |      1 |   5715 |  20002 |00:00:02.25 |    3665 |      0 |      0 |  2324K|  1094K| 2986K (0)|
                            |* 11 |       HASH JOIN                               |                             |      1 |   5715 |  20002 |00:00:01.08 |    2320 |      0 |      0 |  1936K|  1192K| 2537K (0)|
                            |* 12 |        TABLE ACCESS FULL                      | PG                          |      1 |   5715 |  20002 |00:00:00.04 |     975 |      0 |      0 |       |       |          |
                            |  13 |        TABLE ACCESS FULL                      | PD                          |      1 |  80000 |  80000 |00:00:00.23 |    1345 |      0 |      0 |       |       |          |
                            |  14 |       TABLE ACCESS FULL                       | PD                          |      1 |  80000 |  80000 |00:00:00.25 |    1345 |      0 |      0 |       |       |          |
                            |  15 |   LOAD AS SELECT                              |                             |      1 |        |      0 |00:00:03.42 |    3899 |      0 |    110 |   523K|   523K|  523K (0)|
                            |  16 |    SORT UNIQUE                                |                             |      1 |  40004 |  40004 |00:00:03.06 |    3786 |      0 |      0 |  2675K|   736K| 2377K (0)|
                            |  17 |     UNION-ALL                                 |                             |      1 |        |  40004 |00:00:02.77 |    3786 |      0 |      0 |       |       |          |
                            |* 18 |      HASH JOIN                                |                             |      1 |  20002 |  20002 |00:00:01.16 |    1466 |      0 |      0 |  1608K|  1608K| 2510K (0)|
                            |  19 |       TABLE ACCESS FULL                       | PV                          |      1 |  20002 |  20002 |00:00:00.06 |     121 |      0 |      0 |       |       |          |
                            |  20 |       TABLE ACCESS FULL                       | PD                          |      1 |  80000 |  80000 |00:00:00.23 |    1345 |      0 |      0 |       |       |          |
                            |* 21 |      HASH JOIN                                |                             |      1 |  20002 |  20002 |00:00:01.18 |    2320 |      0 |      0 |  1577K|  1485K| 2510K (0)|
                            |* 22 |       TABLE ACCESS FULL                       | PG                          |      1 |  20002 |  20002 |00:00:00.07 |     975 |      0 |      0 |       |       |          |
                            |  23 |       TABLE ACCESS FULL                       | PD                          |      1 |  80000 |  80000 |00:00:00.23 |    1345 |      0 |      0 |       |       |          |
                            |  24 |   SORT ORDER BY                               |                             |      1 |      2 |     62 |00:00:23.34 |   49365 |    598 |      0 | 18432 | 18432 |16384  (0)|
                            |* 25 |    FILTER                                     |                             |      1 |        |     62 |00:00:23.33 |   49365 |    598 |      0 |       |       |          |
                            |  26 |     NESTED LOOPS                              |                             |      1 |      2 |     62 |00:00:23.33 |   49365 |    598 |      0 |       |       |          |
                            |* 27 |      HASH JOIN                                |                             |      1 |      2 |     62 |00:00:23.33 |   49299 |    598 |      0 |   756K|   756K| 1142K (0)|
                            |  28 |       NESTED LOOPS                            |                             |      1 |      1 |     13 |00:00:01.88 |    1271 |    598 |      0 |       |       |          |
                            |  29 |        VIEW                                   |                             |      1 |     10 |     17 |00:00:01.88 |    1237 |    598 |      0 |       |       |          |
                            |  30 |         SORT UNIQUE                           |                             |      1 |     10 |     17 |00:00:01.88 |    1237 |    598 |      0 |  4096 |  4096 | 4096  (0)|
                            |  31 |          UNION-ALL                            |                             |      1 |        |     17 |00:00:01.88 |    1237 |    598 |      0 |       |       |          |
                            |  32 |           TABLE ACCESS BY INDEX ROWID         | PD                          |      1 |      9 |      9 |00:00:00.01 |      11 |      0 |      0 |       |       |          |
                            |* 33 |            INDEX RANGE SCAN                   | I_PD_1                      |      1 |      9 |      9 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
                            |  34 |           NESTED LOOPS                        |                             |      1 |      1 |      8 |00:00:01.88 |    1226 |    598 |      0 |       |       |          |
                            |  35 |            VIEW                               |                             |      1 |      1 |      8 |00:00:01.88 |    1208 |    598 |      0 |       |       |          |
                            |  36 |             SORT GROUP BY                     |                             |      1 |      1 |      8 |00:00:01.88 |    1208 |    598 |      0 |  2048 |  2048 | 2048  (0)|
                            |  37 |              MERGE JOIN                       |                             |      1 |    205M|     12 |00:00:01.88 |    1208 |    598 |      0 |       |       |          |
                            |  38 |               SORT JOIN                       |                             |      1 |  51434 |      8 |00:00:00.96 |     983 |    488 |      0 |  2048 |  2048 | 2048  (0)|
                            |* 39 |                VIEW                           |                             |      1 |  51434 |      8 |00:00:00.96 |     983 |    488 |      0 |       |       |          |
                            |* 40 |                 CONNECT BY WITHOUT FILTERING  |                             |      1 |        |      8 |00:00:00.96 |     983 |    488 |      0 |       |       |          |
                            |* 41 |                  VIEW                         |                             |      1 |  25717 |      4 |00:00:00.25 |     493 |    488 |      0 |       |       |          |
                            |  42 |                   TABLE ACCESS FULL           | SYS_TEMP_0FD9D6780_7D2B1EF2 |      1 |  25717 |  40004 |00:00:00.13 |     493 |    488 |      0 |       |       |          |
                            |  43 |                  VIEW                         |                             |      1 |  25717 |  40004 |00:00:00.34 |     490 |      0 |      0 |       |       |          |
                            |  44 |                   TABLE ACCESS FULL           | SYS_TEMP_0FD9D6780_7D2B1EF2 |      1 |  25717 |  40004 |00:00:00.12 |     490 |      0 |      0 |       |       |          |
                            |* 45 |               SORT JOIN                       |                             |      8 |  80008 |     12 |00:00:00.92 |     225 |    110 |      0 |  2048 |  2048 | 2048  (0)|
                            |  46 |                VIEW                           |                             |      1 |  80008 |      2 |00:00:00.92 |     225 |    110 |      0 |       |       |          |
                            |  47 |                 SORT UNIQUE                   |                             |      1 |  80008 |      2 |00:00:00.92 |     225 |    110 |      0 |  2048 |  2048 | 2048  (0)|
                            |  48 |                  VIEW                         |                             |      1 |  80008 |      8 |00:00:00.92 |     225 |    110 |      0 |       |       |          |
                            |* 49 |                   CONNECT BY WITHOUT FILTERING|                             |      1 |        |      8 |00:00:00.92 |     225 |    110 |      0 |       |       |          |
                            |* 50 |                    VIEW                       |                             |      1 |  40004 |      4 |00:00:00.22 |     114 |    110 |      0 |       |       |          |
                            |  51 |                     TABLE ACCESS FULL         | SYS_TEMP_0FD9D6781_7D2B1EF2 |      1 |  40004 |  40004 |00:00:00.11 |     114 |    110 |      0 |       |       |          |
                            |  52 |                    VIEW                       |                             |      1 |  40004 |  40004 |00:00:00.33 |     111 |      0 |      0 |       |       |          |
                            |  53 |                     TABLE ACCESS FULL         | SYS_TEMP_0FD9D6781_7D2B1EF2 |      1 |  40004 |  40004 |00:00:00.12 |     111 |      0 |      0 |       |       |          |
                            |  54 |            TABLE ACCESS BY INDEX ROWID        | PD                          |      8 |      1 |      8 |00:00:00.01 |      18 |      0 |      0 |       |       |          |
                            |* 55 |             INDEX UNIQUE SCAN                 | PKPD                        |      8 |      1 |      8 |00:00:00.01 |      10 |      0 |      0 |       |       |          |
                            |* 56 |        TABLE ACCESS BY INDEX ROWID            | PD                          |     17 |      1 |     13 |00:00:00.01 |      34 |      0 |      0 |       |       |          |
                            |* 57 |         INDEX UNIQUE SCAN                     | PKPD                        |     17 |      1 |     17 |00:00:00.01 |      19 |      0 |      0 |       |       |          |
                            |  58 |       VIEW                                    |                             |      1 |    366K|    370K|00:00:18.50 |   48028 |      0 |      0 |       |       |          |
                            |  59 |        SORT UNIQUE                            |                             |      1 |    366K|    370K|00:00:16.53 |   48028 |      0 |      0 |    31M|  2012K|   28M (0)|
                            |  60 |         UNION-ALL                             |                             |      1 |        |    370K|00:00:13.55 |   48028 |      0 |      0 |       |       |          |
                            |  61 |          NESTED LOOPS                         |                             |      1 |    316K|    320K|00:00:08.30 |   42431 |      0 |      0 |       |       |          |
                            |  62 |           TABLE ACCESS FULL                   | PR                          |      1 |    320K|    320K|00:00:00.95 |    3711 |      0 |      0 |       |       |          |
                            |* 63 |           INDEX UNIQUE SCAN                   | PKPD                        |    320K|      1 |    320K|00:00:03.11 |   38720 |      0 |      0 |       |       |          |
                            |  64 |          NESTED LOOPS                         |                             |      1 |  50005 |  50005 |00:00:01.31 |    5597 |      0 |      0 |       |       |          |
                            |* 65 |           TABLE ACCESS FULL                   | PG                          |      1 |  50005 |  50005 |00:00:00.16 |     975 |      0 |      0 |       |       |          |
                            |* 66 |           INDEX UNIQUE SCAN                   | PKPD                        |  50005 |      1 |  50005 |00:00:00.49 |    4622 |      0 |      0 |       |       |          |
                            |  67 |      TABLE ACCESS BY INDEX ROWID              | SR                          |     62 |      1 |     62 |00:00:00.01 |      66 |      0 |      0 |       |       |          |
                            |* 68 |       INDEX UNIQUE SCAN                       | PKSR                        |     62 |      1 |     62 |00:00:00.01 |       4 |      0 |      0 |       |       |          |
                            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                            
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                            
                               5 - access("P4"."II"="V1"."VI")
                               6 - access("V1"."PI"="P3"."II")
                              10 - access("P6"."II"="G1"."VI")
                              11 - access("G1"."PI"="P5"."II")
                              12 - filter(("G1"."VI" IS NOT NULL AND "G1"."RG"='RE'))
                              18 - access("V2"."PI"="P7"."II")
                              21 - access("G2"."PI"="P8"."II")
                              22 - filter("G2"."RG"='RE')
                              25 - filter(TO_DATE(:STARTDATE)<=TO_DATE(:ENDDATE)+1)
                              27 - access("PI"="BV"."II")
                              33 - access("P1"."RE"=:RE)
                              39 - filter("Z3"."ROOT_RE"=:RE)
                              40 - access("II"=PRIOR NULL)
                              41 - filter("RE"=:RE)
                              45 - access(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                                   filter(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                              49 - access("II"=PRIOR NULL)
                              50 - filter("RE"=:RE)
                              55 - access("Z1"."VI"="P2"."II")
                              56 - filter(("P0"."RE"=:RE AND "P0"."DA">=TO_DATE(:STARTDATE) AND "P0"."DA"<=TO_DATE(:ENDDATE)+1))
                              57 - access("P0"."II"="BV"."ROOT_II")
                              63 - access("PR"."PI"="P"."II")
                              65 - filter("PG"."RG"='RO')
                              66 - access("PG"."PI"="P"."II")
                              68 - access("R1"."KE"="RV"."RK")
                            It’s similar but not identic with the plan, you posted here Mohamed. In line 27 for example I get a “Hash Join” where you had a “Nested Loop”.

                            And when I worked on this posting I found one more error in the queries I posted here.

                            Where you find
                            sys_connect_by_path (vr, ' < '('||at||')', ' < ') bkt,
                            you have to replace it with
                            sys_connect_by_path (vr, ' < ') kt,
                            sys_connect_by_path (bn||'('||at||')', ' < ') bkt,
                            It seems to me, that the forum software has a bug when it tries to insert html code for coloring certain elements in the posted code. Maybe it gets irritated by the '<'-signs in my query because I don't believe that I made the same mistake several times when I posted some code here.
                            • 26. Re: Query on 11gR2 much slower than on 10gR2
                              Mohamed Houri
                              Hi UW,

                              Why not use Tanel poder snapper to see where time is spent during the execution of your query?

                              http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper

                              You can also trace it using the 10046 events
                              alter session set events '10046 trace name context forever, level 12';
                               run your query (for a couple of minutes)
                              alter session set events '10046 trace name context off';
                              By the way, I tried the same query with different OPTIMIZER_FEATURES_ENABLE less than 11.2.0.1 and have not succeed to reproduce the performance problem

                              For the syntax error, yes I managed to correct it before I succeeded to run the query

                              Best regards

                              Mohamed Houri
                              • 27. Re: Query on 11gR2 much slower than on 10gR2
                                UW (Germany)
                                Thank you for the link Mohamed. It's always interesting to get recommendations for good tools. And thank you as well for not giving up and correcting the wrong statement when you tried my test case example. And maybe installing an 11g Express Edition on some machine is an option for you, if you would like to see the problem.

                                Unfortunately the first results with Tanel Poder's session snapper seem not very interesting for me. I tried the tool on a session, already running for about two hours:
                                SQL> @snapper ash 60 1 203
                                Sampling SID 203 with interval 60 seconds, taking 1 snapshots...
                                
                                -- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )
                                
                                
                                -----------------------------------------------------------------------
                                Active% | SQL_ID          | EVENT                     | WAIT_CLASS
                                -----------------------------------------------------------------------
                                   100% | 9g97wu3szsduz   | ON CPU                    | ON CPU
                                
                                --  End of ASH snap 1, end=2012-08-13 10:57:13, seconds=60, samples_taken=98
                                … and then …
                                SQL> @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 60 1 203
                                Sampling SID 203 with interval 60 seconds, taking 1 snapshots...
                                
                                -- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )
                                
                                -------------------------------------------------------------------------------------------------------------------------------------
                                    SID, USERNAME  , TYPE, STATISTIC                                                 ,     HDELTA, HDELTA/SEC,    %TIME, GRAPH
                                -------------------------------------------------------------------------------------------------------------------------------------
                                    203, IT1       , TIME, DB CPU                                                    ,     60,44s,   990,78ms,    99.1%, |@@@@@@@@@@|
                                --  End of Stats snap 1, end=2012-08-13 11:01:11, seconds=61
                                
                                -----------------------------------------------------
                                Active% | EVENT                     | WAIT_CLASS
                                -----------------------------------------------------
                                   100% | ON CPU                    | ON CPU
                                
                                --  End of ASH snap 1, end=2012-08-13 11:01:11, seconds=60, samples_taken=99
                                About your other suggestion: I have no idea how to run the problematical query only for a few minutes and then stop it and execute an "alter session command".

                                Here are some more discoveries about my problem:

                                When I insert the optimizer hint

                                BEGIN_OUTLINE_DATA
                                OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

                                on my 11.2.0.3 database I get a quick response and the same plan, that I got with the 11.2.0.1 hint. But when I remove this hint totally, then I get the well-known problem, that there is no answer on the query in a reasonable time of waiting. I don't understand why I get this difference with a hint, that should be absolutely redundant on an 11.2.0.3 database.

                                When I look for the plan of this not ending query I find this:
                                PLAN_TABLE_OUTPUT
                                -----------------------------------------------------------------------------------------------------------------------------------------------
                                SQL_ID  9g97wu3szsduz, child number 0
                                -------------------------------------                            
                                with z0 as ( select     p3.ii,     v1.vi,     p3.re re,     p4.re vr,
                                to_char(p4.bn) bn,     p4.at,     to_char(p3.bn) rba,     p3.at rtk,
                                to_char(round(v1.cf / p4.fa, 7)) fa,
                                to_char(v1.cf,'0.999999')||'/'||     to_char(p4.fa,'0.999999') sf,
                                'V' pi,     to_char(p4.bn)||'('||p4.at||')' bk   from        pd p3
                                join pv v1 on v1.pi = p3.ii   join pd p4 on p4.ii = v1.vi   union
                                select     p5.ii,     g1.vi,     p5.re,     p6.re,     to_char(p6.bn)
                                bn,     p6.at,     to_char(p5.bn) rba,     p5.at rtk,
                                to_char(round(g1.om / p6.tt, 7)),     to_char(g1.om,'99990.999')||'/'||
                                to_char(p6.tt,'99990.999'),     'G' pi,
                                to_char(p6.bn)||'('||p6.at||')' bk   from        pd p5   join pg g1 on
                                g1.pi = p5.ii   join pd p6 on p6.ii = g1.vi   where      g1.rg = 'RE'
                                ), z3 as ( select     connect_by_root ii root_ii,     ii,     vi,
                                bn,     at,     connect_by_root re root_re,     connect_by_root rba
                                root_ba,     connect_by_root rtk rt,     sys_connect_by_path (vr, ' <
                                ') kt,
                                
                                Plan hash value: 1795286512
                                
                                -----------------------------------------------------------------------------------------------------------------------------------------------
                                | Id  | Operation                                               | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                -----------------------------------------------------------------------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT                                        |                             |       |       |       | 23661 (100)|          |
                                |   1 |  TEMP TABLE TRANSFORMATION                              |                             |       |       |       |            |          |
                                |   2 |   LOAD AS SELECT                                        |                             |       |       |       |            |          |
                                |   3 |    SORT UNIQUE                                          |                             | 25717 |  2277K|  2064K|  2235  (47)| 00:00:27 |
                                |   4 |     UNION-ALL                                           |                             |       |       |       |            |          |
                                |*  5 |      HASH JOIN                                          |                             | 20002 |  1757K|       |   790   (1)| 00:00:10 |
                                |*  6 |       HASH JOIN                                         |                             | 20002 |   976K|       |   412   (1)| 00:00:05 |
                                |   7 |        TABLE ACCESS FULL                                | PV                          | 20002 |   605K|       |    34   (0)| 00:00:01 |
                                |   8 |        TABLE ACCESS FULL                                | PD                          | 80000 |  1484K|       |   377   (1)| 00:00:05 |
                                |   9 |       TABLE ACCESS FULL                                 | PD                          | 80000 |  3125K|       |   377   (1)| 00:00:05 |
                                |* 10 |      HASH JOIN                                          |                             |  5715 |   519K|       |  1029   (1)| 00:00:13 |
                                |* 11 |       HASH JOIN                                         |                             |  5715 |   290K|       |   651   (1)| 00:00:08 |
                                |* 12 |        TABLE ACCESS FULL                                | PG                          |  5715 |   184K|       |   274   (1)| 00:00:04 |
                                |  13 |        TABLE ACCESS FULL                                | PD                          | 80000 |  1484K|       |   377   (1)| 00:00:05 |
                                |  14 |       TABLE ACCESS FULL                                 | PD                          | 80000 |  3203K|       |   377   (1)| 00:00:05 |
                                |  15 |   LOAD AS SELECT                                        |                             |       |       |       |            |          |
                                |  16 |    SORT UNIQUE                                          |                             | 40004 |   800K|       |  1067  (62)| 00:00:13 |
                                |  17 |     UNION-ALL                                           |                             |       |       |       |            |          |
                                |* 18 |      HASH JOIN                                          |                             | 20002 |   390K|       |   412   (1)| 00:00:05 |
                                |  19 |       TABLE ACCESS FULL                                 | PV                          | 20002 |   195K|       |    34   (0)| 00:00:01 |
                                |  20 |       TABLE ACCESS FULL                                 | PD                          | 80000 |   781K|       |   377   (1)| 00:00:05 |
                                |* 21 |      HASH JOIN                                          |                             | 20002 |   410K|       |   651   (1)| 00:00:08 |
                                |* 22 |       TABLE ACCESS FULL                                 | PG                          | 20002 |   214K|       |   274   (1)| 00:00:04 |
                                |  23 |       TABLE ACCESS FULL                                 | PD                          | 80000 |   781K|       |   377   (1)| 00:00:05 |
                                |  24 |   SORT ORDER BY                                         |                             |       |  2765K|  5512K| 20359   (1)| 00:04:05 |
                                |* 25 |    FILTER                                               |                             |       |       |       |            |          |
                                |* 26 |     HASH JOIN                                           |                             |   688 |  2765K|       | 19767   (1)| 00:03:58 |
                                |* 27 |      TABLE ACCESS BY INDEX ROWID                        | PD                          |     5 |   200 |       |    10   (0)| 00:00:01 |
                                |* 28 |       INDEX RANGE SCAN                                  | I_PD_1                      |     9 |       |       |     1   (0)| 00:00:01 |
                                |  29 |      NESTED LOOPS                                       |                             |    11M|    45G|       | 19699   (1)| 00:03:57 |
                                |* 30 |       HASH JOIN                                         |                             | 18730 |   731K|       |  1193   (1)| 00:00:15 |
                                |  31 |        TABLE ACCESS FULL                                | SR                          |   400 |  3600 |       |     3   (0)| 00:00:01 |
                                |  32 |        VIEW                                             |                             |   366K|    10M|       |  1188   (1)| 00:00:15 |
                                |  33 |         SORT UNIQUE                                     |                             |       |       |       |            |          |
                                |  34 |          UNION-ALL                                      |                             |       |       |       |            |          |
                                |  35 |           NESTED LOOPS                                  |                             |   316K|    10M|       |  1023   (4)| 00:00:13 |
                                |  36 |            TABLE ACCESS FULL                            | PR                          |   320K|  9687K|       |   993   (1)| 00:00:12 |
                                |* 37 |            INDEX UNIQUE SCAN                            | PKPD                        |     1 |     5 |       |     0   (0)|          |
                                |  38 |           NESTED LOOPS                                  |                             | 50005 |  1904K|       |   278   (3)| 00:00:04 |
                                |* 39 |            TABLE ACCESS FULL                            | PG                          | 50005 |  1660K|       |   274   (1)| 00:00:04 |
                                |* 40 |            INDEX UNIQUE SCAN                            | PKPD                        |     1 |     5 |       |     0   (0)|          |
                                |  41 |       VIEW                                              |                             |   635 |  2502K|       |     1   (0)| 00:00:01 |
                                |  42 |        SORT UNIQUE                                      |                             |       |       |       |            |          |
                                |  43 |         UNION-ALL PARTITION                             |                             |       |       |       |            |          |
                                |* 44 |          TABLE ACCESS BY INDEX ROWID                    | PD                          |     1 |   113 |       |     2   (0)| 00:00:01 |
                                |* 45 |           INDEX UNIQUE SCAN                             | PKPD                        |     1 |       |       |     1   (0)| 00:00:01 |
                                |  46 |          NESTED LOOPS                                   |                             |     1 |  8193 |       | 57201   (6)| 00:11:27 |
                                |  47 |           TABLE ACCESS BY INDEX ROWID                   | PD                          |     1 |   113 |       |     2   (0)| 00:00:01 |
                                |* 48 |            INDEX UNIQUE SCAN                            | PKPD                        |     1 |       |       |     1   (0)| 00:00:01 |
                                |* 49 |           VIEW                                          |                             |     1 |  8080 |       | 57199   (6)| 00:11:27 |
                                |  50 |            SORT GROUP BY                                |                             |     1 | 10121 |       | 57199   (6)| 00:11:27 |
                                |  51 |             MERGE JOIN                                  |                             |    51M|   484G|       | 54322   (1)| 00:10:52 |
                                |  52 |              SORT JOIN                                  |                             | 25717 |   247M|   401M| 54066   (1)| 00:10:49 |
                                |* 53 |               VIEW                                      |                             | 25717 |   247M|       |   880 (100)| 00:00:11 |
                                |* 54 |                CONNECT BY NO FILTERING WITH START-WITH  |                             |       |       |       |            |          |
                                |  55 |                 VIEW                                    |                             | 25717 |  3867K|       |     2   (0)| 00:00:01 |
                                |  56 |                  TABLE ACCESS FULL                      | SYS_TEMP_0FD9D6784_7D2B1EF2 | 25717 |  3867K|       |     2   (0)| 00:00:01 |
                                |* 57 |              SORT JOIN                                  |                             | 40004 |   507K|       |     7  (72)| 00:00:01 |
                                |  58 |               VIEW                                      |                             | 40004 |   507K|       |     5  (60)| 00:00:01 |
                                |  59 |                SORT UNIQUE                              |                             | 40004 |   507K|       |     5  (60)| 00:00:01 |
                                |  60 |                 VIEW                                    |                             | 40004 |   507K|       |   362 (100)| 00:00:05 |
                                |* 61 |                  CONNECT BY NO FILTERING WITH START-WITH|                             |       |       |       |            |          |
                                |  62 |                   VIEW                                  |                             | 40004 |  1289K|       |     2   (0)| 00:00:01 |
                                |  63 |                    TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6785_7D2B1EF2 | 40004 |  1289K|       |     2   (0)| 00:00:01 |
                                -----------------------------------------------------------------------------------------------------------------------------------------------
                                    
                                Predicate Information (identified by operation id):
                                ---------------------------------------------------
                                    
                                5 - access("P4"."II"="V1"."VI")
                                6 - access("V1"."PI"="P3"."II")
                                10 - access("P6"."II"="G1"."VI")
                                11 - access("G1"."PI"="P5"."II")
                                12 - filter(("G1"."VI" IS NOT NULL AND "G1"."RG"='RE'))
                                18 - access("V2"."PI"="P7"."II")
                                21 - access("G2"."PI"="P8"."II")
                                22 - filter("G2"."RG"='RE')
                                25 - filter(TO_DATE(:STARTDATE)<=TO_DATE(:ENDDATE)+1)
                                26 - access("P0"."II"="BV"."ROOT_II")
                                27 - filter(("P0"."DA">=TO_DATE(:STARTDATE) AND "P0"."DA"<=TO_DATE(:ENDDATE)+1))
                                28 - access("P0"."RE"=:RE)
                                30 - access("R1"."KE"="RV"."RK")
                                37 - access("PR"."PI"="P"."II")
                                39 - filter("PG"."RG"='RO')
                                40 - access("PG"."PI"="P"."II")
                                44 - filter("P1"."RE"=:RE)
                                45 - access("P1"."II"="PI")
                                48 - access("P2"."II"="PI")
                                49 - filter(("Z1"."ROOT_RE"=:RE AND "Z1"."VI"="P2"."II" AND "Z1"."VI"="PI"))
                                53 - filter("Z3"."ROOT_RE"=:RE)
                                54 - access("II"=PRIOR NULL)
                                filter("RE"=:RE)
                                57 - access(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                                filter(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                                61 - access("II"=PRIOR NULL)
                                filter("RE"=:RE)
                                I’m still learning how to read those execution plans and how the database works, but my general understanding what’s happening here is this:

                                For some reason with the "new" optimizer the database changes the inner "table" (or data source) and the outer "table" in a nested loop join. With the example of my improved query, that I described on Friday: for one table the optimizer expects only 33280 rows but it gets 1.8 Million rows. For the other table the optimizer has now expectation about the result but only 17 rows are returned.

                                Instead of running the 1,8-Million-rows-query, which needs for example 8 seconds every time, for 17 times resulting in 136 seconds, the optimizer decides to run the 17-rows-query, which needs only one second, 1,8 Million times, making the query running for estimated 1,8 Million seconds or about 21 days.

                                Can someone confirm the general idea of my explanation? And has there been a known modification from optimizer 11.2.0.1 to 11.2.0.2 that would explain such a dramatic change?
                                • 28. Re: Query on 11gR2 much slower than on 10gR2
                                  UW (Germany)
                                  Strange! Once more the forum software has modified the code, that I pasted in the message box. In the previous posting I wanted to write this
                                  PLAN_TABLE_OUTPUT
                                  -----------------------------------------------------------------------------------------------------------------------------------------------
                                  SQL_ID  9g97wu3szsduz, child number 0
                                  -------------------------------------                            
                                  with z0 as ( select     p3.ii,     v1.vi,     p3.re re,     p4.re vr,
                                  to_char(p4.bn) bn,     p4.at,     to_char(p3.bn) rba,     p3.at rtk,
                                  to_char(round(v1.cf / p4.fa, 7)) fa,
                                  to_char(v1.cf,'0.999999')||'/'||     to_char(p4.fa,'0.999999') sf,
                                  'V' pi,     to_char(p4.bn)||'('||p4.at||')' bk   from        pd p3
                                  join pv v1 on v1.pi = p3.ii   join pd p4 on p4.ii = v1.vi   union
                                  select     p5.ii,     g1.vi,     p5.re,     p6.re,     to_char(p6.bn)
                                  bn,     p6.at,     to_char(p5.bn) rba,     p5.at rtk,
                                  to_char(round(g1.om / p6.tt, 7)),     to_char(g1.om,'99990.999')||'/'||
                                  to_char(p6.tt,'99990.999'),     'G' pi,
                                  to_char(p6.bn)||'('||p6.at||')' bk   from        pd p5   join pg g1 on
                                  g1.pi = p5.ii   join pd p6 on p6.ii = g1.vi   where      g1.rg = 'RE'
                                  ), z3 as ( select     connect_by_root ii root_ii,     ii,     vi,
                                  bn,     at,     connect_by_root re root_re,     connect_by_root rba
                                  root_ba,     connect_by_root rtk rt,     sys_connect_by_path (vr, ' <
                                  ') kt,
                                  
                                  Plan hash value: 1795286512
                                  
                                  -----------------------------------------------------------------------------------------------------------------------------------------------
                                  | Id  | Operation                                               | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                  -----------------------------------------------------------------------------------------------------------------------------------------------
                                  |   0 | SELECT STATEMENT                                        |                             |       |       |       | 23661 (100)|          |
                                  |   1 |  TEMP TABLE TRANSFORMATION                              |                             |       |       |       |            |          |
                                  |   2 |   LOAD AS SELECT                                        |                             |       |       |       |            |          |
                                  |   3 |    SORT UNIQUE                                          |                             | 25717 |  2277K|  2064K|  2235  (47)| 00:00:27 |
                                  |   4 |     UNION-ALL                                           |                             |       |       |       |            |          |
                                  |*  5 |      HASH JOIN                                          |                             | 20002 |  1757K|       |   790   (1)| 00:00:10 |
                                  |*  6 |       HASH JOIN                                         |                             | 20002 |   976K|       |   412   (1)| 00:00:05 |
                                  |   7 |        TABLE ACCESS FULL                                | PV                          | 20002 |   605K|       |    34   (0)| 00:00:01 |
                                  |   8 |        TABLE ACCESS FULL                                | PD                          | 80000 |  1484K|       |   377   (1)| 00:00:05 |
                                  |   9 |       TABLE ACCESS FULL                                 | PD                          | 80000 |  3125K|       |   377   (1)| 00:00:05 |
                                  |* 10 |      HASH JOIN                                          |                             |  5715 |   519K|       |  1029   (1)| 00:00:13 |
                                  |* 11 |       HASH JOIN                                         |                             |  5715 |   290K|       |   651   (1)| 00:00:08 |
                                  |* 12 |        TABLE ACCESS FULL                                | PG                          |  5715 |   184K|       |   274   (1)| 00:00:04 |
                                  |  13 |        TABLE ACCESS FULL                                | PD                          | 80000 |  1484K|       |   377   (1)| 00:00:05 |
                                  |  14 |       TABLE ACCESS FULL                                 | PD                          | 80000 |  3203K|       |   377   (1)| 00:00:05 |
                                  |  15 |   LOAD AS SELECT                                        |                             |       |       |       |            |          |
                                  |  16 |    SORT UNIQUE                                          |                             | 40004 |   800K|       |  1067  (62)| 00:00:13 |
                                  |  17 |     UNION-ALL                                           |                             |       |       |       |            |          |
                                  |* 18 |      HASH JOIN                                          |                             | 20002 |   390K|       |   412   (1)| 00:00:05 |
                                  |  19 |       TABLE ACCESS FULL                                 | PV                          | 20002 |   195K|       |    34   (0)| 00:00:01 |
                                  |  20 |       TABLE ACCESS FULL                                 | PD                          | 80000 |   781K|       |   377   (1)| 00:00:05 |
                                  |* 21 |      HASH JOIN                                          |                             | 20002 |   410K|       |   651   (1)| 00:00:08 |
                                  |* 22 |       TABLE ACCESS FULL                                 | PG                          | 20002 |   214K|       |   274   (1)| 00:00:04 |
                                  |  23 |       TABLE ACCESS FULL                                 | PD                          | 80000 |   781K|       |   377   (1)| 00:00:05 |
                                  |  24 |   SORT ORDER BY                                         |                             |       |  2765K|  5512K| 20359   (1)| 00:04:05 |
                                  |* 25 |    FILTER                                               |                             |       |       |       |            |          |
                                  |* 26 |     HASH JOIN                                           |                             |   688 |  2765K|       | 19767   (1)| 00:03:58 |
                                  |* 27 |      TABLE ACCESS BY INDEX ROWID                        | PD                          |     5 |   200 |       |    10   (0)| 00:00:01 |
                                  |* 28 |       INDEX RANGE SCAN                                  | I_PD_1                      |     9 |       |       |     1   (0)| 00:00:01 |
                                  |  29 |      NESTED LOOPS                                       |                             |    11M|    45G|       | 19699   (1)| 00:03:57 |
                                  |* 30 |       HASH JOIN                                         |                             | 18730 |   731K|       |  1193   (1)| 00:00:15 |
                                  |  31 |        TABLE ACCESS FULL                                | SR                          |   400 |  3600 |       |     3   (0)| 00:00:01 |
                                  |  32 |        VIEW                                             |                             |   366K|    10M|       |  1188   (1)| 00:00:15 |
                                  |  33 |         SORT UNIQUE                                     |                             |       |       |       |            |          |
                                  |  34 |          UNION-ALL                                      |                             |       |       |       |            |          |
                                  |  35 |           NESTED LOOPS                                  |                             |   316K|    10M|       |  1023   (4)| 00:00:13 |
                                  |  36 |            TABLE ACCESS FULL                            | PR                          |   320K|  9687K|       |   993   (1)| 00:00:12 |
                                  |* 37 |            INDEX UNIQUE SCAN                            | PKPD                        |     1 |     5 |       |     0   (0)|          |
                                  |  38 |           NESTED LOOPS                                  |                             | 50005 |  1904K|       |   278   (3)| 00:00:04 |
                                  |* 39 |            TABLE ACCESS FULL                            | PG                          | 50005 |  1660K|       |   274   (1)| 00:00:04 |
                                  |* 40 |            INDEX UNIQUE SCAN                            | PKPD                        |     1 |     5 |       |     0   (0)|          |
                                  |  41 |       VIEW                                              |                             |   635 |  2502K|       |     1   (0)| 00:00:01 |
                                  |  42 |        SORT UNIQUE                                      |                             |       |       |       |            |          |
                                  |  43 |         UNION-ALL PARTITION                             |                             |       |       |       |            |          |
                                  |* 44 |          TABLE ACCESS BY INDEX ROWID                    | PD                          |     1 |   113 |       |     2   (0)| 00:00:01 |
                                  |* 45 |           INDEX UNIQUE SCAN                             | PKPD                        |     1 |       |       |     1   (0)| 00:00:01 |
                                  |  46 |          NESTED LOOPS                                   |                             |     1 |  8193 |       | 57201   (6)| 00:11:27 |
                                  |  47 |           TABLE ACCESS BY INDEX ROWID                   | PD                          |     1 |   113 |       |     2   (0)| 00:00:01 |
                                  |* 48 |            INDEX UNIQUE SCAN                            | PKPD                        |     1 |       |       |     1   (0)| 00:00:01 |
                                  |* 49 |           VIEW                                          |                             |     1 |  8080 |       | 57199   (6)| 00:11:27 |
                                  |  50 |            SORT GROUP BY                                |                             |     1 | 10121 |       | 57199   (6)| 00:11:27 |
                                  |  51 |             MERGE JOIN                                  |                             |    51M|   484G|       | 54322   (1)| 00:10:52 |
                                  |  52 |              SORT JOIN                                  |                             | 25717 |   247M|   401M| 54066   (1)| 00:10:49 |
                                  |* 53 |               VIEW                                      |                             | 25717 |   247M|       |   880 (100)| 00:00:11 |
                                  |* 54 |                CONNECT BY NO FILTERING WITH START-WITH  |                             |       |       |       |            |          |
                                  |  55 |                 VIEW                                    |                             | 25717 |  3867K|       |     2   (0)| 00:00:01 |
                                  |  56 |                  TABLE ACCESS FULL                      | SYS_TEMP_0FD9D6784_7D2B1EF2 | 25717 |  3867K|       |     2   (0)| 00:00:01 |
                                  |* 57 |              SORT JOIN                                  |                             | 40004 |   507K|       |     7  (72)| 00:00:01 |
                                  |  58 |               VIEW                                      |                             | 40004 |   507K|       |     5  (60)| 00:00:01 |
                                  |  59 |                SORT UNIQUE                              |                             | 40004 |   507K|       |     5  (60)| 00:00:01 |
                                  |  60 |                 VIEW                                    |                             | 40004 |   507K|       |   362 (100)| 00:00:05 |
                                  |* 61 |                  CONNECT BY NO FILTERING WITH START-WITH|                             |       |       |       |            |          |
                                  |  62 |                   VIEW                                  |                             | 40004 |  1289K|       |     2   (0)| 00:00:01 |
                                  |  63 |                    TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6785_7D2B1EF2 | 40004 |  1289K|       |     2   (0)| 00:00:01 |
                                  -----------------------------------------------------------------------------------------------------------------------------------------------
                                      
                                  Predicate Information (identified by operation id):
                                  ---------------------------------------------------
                                      
                                  5 - access("P4"."II"="V1"."VI")
                                  6 - access("V1"."PI"="P3"."II")
                                  10 - access("P6"."II"="G1"."VI")
                                  11 - access("G1"."PI"="P5"."II")
                                  12 - filter(("G1"."VI" IS NOT NULL AND "G1"."RG"='RE'))
                                  18 - access("V2"."PI"="P7"."II")
                                  21 - access("G2"."PI"="P8"."II")
                                  22 - filter("G2"."RG"='RE')
                                  25 - filter(TO_DATE(:STARTDATE)<=TO_DATE(:ENDDATE)+1)
                                  26 - access("P0"."II"="BV"."ROOT_II")
                                  27 - filter(("P0"."DA">=TO_DATE(:STARTDATE) AND "P0"."DA"<=TO_DATE(:ENDDATE)+1))
                                  28 - access("P0"."RE"=:RE)
                                  30 - access("R1"."KE"="RV"."RK")
                                  37 - access("PR"."PI"="P"."II")
                                  39 - filter("PG"."RG"='RO')
                                  40 - access("PG"."PI"="P"."II")
                                  44 - filter("P1"."RE"=:RE)
                                  45 - access("P1"."II"="PI")
                                  48 - access("P2"."II"="PI")
                                  49 - filter(("Z1"."ROOT_RE"=:RE AND "Z1"."VI"="P2"."II" AND "Z1"."VI"="PI"))
                                  53 - filter("Z3"."ROOT_RE"=:RE)
                                  54 - access("II"=PRIOR NULL)
                                  filter("RE"=:RE)
                                  57 - access(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                                  filter(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                                  61 - access("II"=PRIOR NULL)
                                  filter("RE"=:RE)
                                  • 29. Re: Query on 11gR2 much slower than on 10gR2
                                    UW (Germany)
                                    The softeware realy gets irritated by a <-sign in my code and removes the next lines.
                                    Now I removed this sign at the end of the line ending now with "sys_connect_by_path (vr, '"
                                    PLAN_TABLE_OUTPUT
                                    -----------------------------------------------------------------------------------------------------------------------------------------------
                                    SQL_ID  9g97wu3szsduz, child number 0
                                    -------------------------------------                            
                                    with z0 as ( select     p3.ii,     v1.vi,     p3.re re,     p4.re vr,
                                    to_char(p4.bn) bn,     p4.at,     to_char(p3.bn) rba,     p3.at rtk,
                                    to_char(round(v1.cf / p4.fa, 7)) fa,
                                    to_char(v1.cf,'0.999999')||'/'||     to_char(p4.fa,'0.999999') sf,
                                    'V' pi,     to_char(p4.bn)||'('||p4.at||')' bk   from        pd p3
                                    join pv v1 on v1.pi = p3.ii   join pd p4 on p4.ii = v1.vi   union
                                    select     p5.ii,     g1.vi,     p5.re,     p6.re,     to_char(p6.bn)
                                    bn,     p6.at,     to_char(p5.bn) rba,     p5.at rtk,
                                    to_char(round(g1.om / p6.tt, 7)),     to_char(g1.om,'99990.999')||'/'||
                                    to_char(p6.tt,'99990.999'),     'G' pi,
                                    to_char(p6.bn)||'('||p6.at||')' bk   from        pd p5   join pg g1 on
                                    g1.pi = p5.ii   join pd p6 on p6.ii = g1.vi   where      g1.rg = 'RE'
                                    ), z3 as ( select     connect_by_root ii root_ii,     ii,     vi,
                                    bn,     at,     connect_by_root re root_re,     connect_by_root rba
                                    root_ba,     connect_by_root rtk rt,     sys_connect_by_path (vr, ' 
                                    ') kt,
                                    
                                    Plan hash value: 1795286512
                                    
                                    -----------------------------------------------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                                               | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                    -----------------------------------------------------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT                                        |                             |       |       |       | 23661 (100)|          |
                                    |   1 |  TEMP TABLE TRANSFORMATION                              |                             |       |       |       |            |          |
                                    |   2 |   LOAD AS SELECT                                        |                             |       |       |       |            |          |
                                    |   3 |    SORT UNIQUE                                          |                             | 25717 |  2277K|  2064K|  2235  (47)| 00:00:27 |
                                    |   4 |     UNION-ALL                                           |                             |       |       |       |            |          |
                                    |*  5 |      HASH JOIN                                          |                             | 20002 |  1757K|       |   790   (1)| 00:00:10 |
                                    |*  6 |       HASH JOIN                                         |                             | 20002 |   976K|       |   412   (1)| 00:00:05 |
                                    |   7 |        TABLE ACCESS FULL                                | PV                          | 20002 |   605K|       |    34   (0)| 00:00:01 |
                                    |   8 |        TABLE ACCESS FULL                                | PD                          | 80000 |  1484K|       |   377   (1)| 00:00:05 |
                                    |   9 |       TABLE ACCESS FULL                                 | PD                          | 80000 |  3125K|       |   377   (1)| 00:00:05 |
                                    |* 10 |      HASH JOIN                                          |                             |  5715 |   519K|       |  1029   (1)| 00:00:13 |
                                    |* 11 |       HASH JOIN                                         |                             |  5715 |   290K|       |   651   (1)| 00:00:08 |
                                    |* 12 |        TABLE ACCESS FULL                                | PG                          |  5715 |   184K|       |   274   (1)| 00:00:04 |
                                    |  13 |        TABLE ACCESS FULL                                | PD                          | 80000 |  1484K|       |   377   (1)| 00:00:05 |
                                    |  14 |       TABLE ACCESS FULL                                 | PD                          | 80000 |  3203K|       |   377   (1)| 00:00:05 |
                                    |  15 |   LOAD AS SELECT                                        |                             |       |       |       |            |          |
                                    |  16 |    SORT UNIQUE                                          |                             | 40004 |   800K|       |  1067  (62)| 00:00:13 |
                                    |  17 |     UNION-ALL                                           |                             |       |       |       |            |          |
                                    |* 18 |      HASH JOIN                                          |                             | 20002 |   390K|       |   412   (1)| 00:00:05 |
                                    |  19 |       TABLE ACCESS FULL                                 | PV                          | 20002 |   195K|       |    34   (0)| 00:00:01 |
                                    |  20 |       TABLE ACCESS FULL                                 | PD                          | 80000 |   781K|       |   377   (1)| 00:00:05 |
                                    |* 21 |      HASH JOIN                                          |                             | 20002 |   410K|       |   651   (1)| 00:00:08 |
                                    |* 22 |       TABLE ACCESS FULL                                 | PG                          | 20002 |   214K|       |   274   (1)| 00:00:04 |
                                    |  23 |       TABLE ACCESS FULL                                 | PD                          | 80000 |   781K|       |   377   (1)| 00:00:05 |
                                    |  24 |   SORT ORDER BY                                         |                             |       |  2765K|  5512K| 20359   (1)| 00:04:05 |
                                    |* 25 |    FILTER                                               |                             |       |       |       |            |          |
                                    |* 26 |     HASH JOIN                                           |                             |   688 |  2765K|       | 19767   (1)| 00:03:58 |
                                    |* 27 |      TABLE ACCESS BY INDEX ROWID                        | PD                          |     5 |   200 |       |    10   (0)| 00:00:01 |
                                    |* 28 |       INDEX RANGE SCAN                                  | I_PD_1                      |     9 |       |       |     1   (0)| 00:00:01 |
                                    |  29 |      NESTED LOOPS                                       |                             |    11M|    45G|       | 19699   (1)| 00:03:57 |
                                    |* 30 |       HASH JOIN                                         |                             | 18730 |   731K|       |  1193   (1)| 00:00:15 |
                                    |  31 |        TABLE ACCESS FULL                                | SR                          |   400 |  3600 |       |     3   (0)| 00:00:01 |
                                    |  32 |        VIEW                                             |                             |   366K|    10M|       |  1188   (1)| 00:00:15 |
                                    |  33 |         SORT UNIQUE                                     |                             |       |       |       |            |          |
                                    |  34 |          UNION-ALL                                      |                             |       |       |       |            |          |
                                    |  35 |           NESTED LOOPS                                  |                             |   316K|    10M|       |  1023   (4)| 00:00:13 |
                                    |  36 |            TABLE ACCESS FULL                            | PR                          |   320K|  9687K|       |   993   (1)| 00:00:12 |
                                    |* 37 |            INDEX UNIQUE SCAN                            | PKPD                        |     1 |     5 |       |     0   (0)|          |
                                    |  38 |           NESTED LOOPS                                  |                             | 50005 |  1904K|       |   278   (3)| 00:00:04 |
                                    |* 39 |            TABLE ACCESS FULL                            | PG                          | 50005 |  1660K|       |   274   (1)| 00:00:04 |
                                    |* 40 |            INDEX UNIQUE SCAN                            | PKPD                        |     1 |     5 |       |     0   (0)|          |
                                    |  41 |       VIEW                                              |                             |   635 |  2502K|       |     1   (0)| 00:00:01 |
                                    |  42 |        SORT UNIQUE                                      |                             |       |       |       |            |          |
                                    |  43 |         UNION-ALL PARTITION                             |                             |       |       |       |            |          |
                                    |* 44 |          TABLE ACCESS BY INDEX ROWID                    | PD                          |     1 |   113 |       |     2   (0)| 00:00:01 |
                                    |* 45 |           INDEX UNIQUE SCAN                             | PKPD                        |     1 |       |       |     1   (0)| 00:00:01 |
                                    |  46 |          NESTED LOOPS                                   |                             |     1 |  8193 |       | 57201   (6)| 00:11:27 |
                                    |  47 |           TABLE ACCESS BY INDEX ROWID                   | PD                          |     1 |   113 |       |     2   (0)| 00:00:01 |
                                    |* 48 |            INDEX UNIQUE SCAN                            | PKPD                        |     1 |       |       |     1   (0)| 00:00:01 |
                                    |* 49 |           VIEW                                          |                             |     1 |  8080 |       | 57199   (6)| 00:11:27 |
                                    |  50 |            SORT GROUP BY                                |                             |     1 | 10121 |       | 57199   (6)| 00:11:27 |
                                    |  51 |             MERGE JOIN                                  |                             |    51M|   484G|       | 54322   (1)| 00:10:52 |
                                    |  52 |              SORT JOIN                                  |                             | 25717 |   247M|   401M| 54066   (1)| 00:10:49 |
                                    |* 53 |               VIEW                                      |                             | 25717 |   247M|       |   880 (100)| 00:00:11 |
                                    |* 54 |                CONNECT BY NO FILTERING WITH START-WITH  |                             |       |       |       |            |          |
                                    |  55 |                 VIEW                                    |                             | 25717 |  3867K|       |     2   (0)| 00:00:01 |
                                    |  56 |                  TABLE ACCESS FULL                      | SYS_TEMP_0FD9D6784_7D2B1EF2 | 25717 |  3867K|       |     2   (0)| 00:00:01 |
                                    |* 57 |              SORT JOIN                                  |                             | 40004 |   507K|       |     7  (72)| 00:00:01 |
                                    |  58 |               VIEW                                      |                             | 40004 |   507K|       |     5  (60)| 00:00:01 |
                                    |  59 |                SORT UNIQUE                              |                             | 40004 |   507K|       |     5  (60)| 00:00:01 |
                                    |  60 |                 VIEW                                    |                             | 40004 |   507K|       |   362 (100)| 00:00:05 |
                                    |* 61 |                  CONNECT BY NO FILTERING WITH START-WITH|                             |       |       |       |            |          |
                                    |  62 |                   VIEW                                  |                             | 40004 |  1289K|       |     2   (0)| 00:00:01 |
                                    |  63 |                    TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6785_7D2B1EF2 | 40004 |  1289K|       |     2   (0)| 00:00:01 |
                                    -----------------------------------------------------------------------------------------------------------------------------------------------
                                        
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                        
                                    5 - access("P4"."II"="V1"."VI")
                                    6 - access("V1"."PI"="P3"."II")
                                    10 - access("P6"."II"="G1"."VI")
                                    11 - access("G1"."PI"="P5"."II")
                                    12 - filter(("G1"."VI" IS NOT NULL AND "G1"."RG"='RE'))
                                    18 - access("V2"."PI"="P7"."II")
                                    21 - access("G2"."PI"="P8"."II")
                                    22 - filter("G2"."RG"='RE')
                                    25 - filter(TO_DATE(:STARTDATE)<=TO_DATE(:ENDDATE)+1)
                                    26 - access("P0"."II"="BV"."ROOT_II")
                                    27 - filter(("P0"."DA">=TO_DATE(:STARTDATE) AND "P0"."DA"<=TO_DATE(:ENDDATE)+1))
                                    28 - access("P0"."RE"=:RE)
                                    30 - access("R1"."KE"="RV"."RK")
                                    37 - access("PR"."PI"="P"."II")
                                    39 - filter("PG"."RG"='RO')
                                    40 - access("PG"."PI"="P"."II")
                                    44 - filter("P1"."RE"=:RE)
                                    45 - access("P1"."II"="PI")
                                    48 - access("P2"."II"="PI")
                                    49 - filter(("Z1"."ROOT_RE"=:RE AND "Z1"."VI"="P2"."II" AND "Z1"."VI"="PI"))
                                    53 - filter("Z3"."ROOT_RE"=:RE)
                                    54 - access("II"=PRIOR NULL)
                                    filter("RE"=:RE)
                                    57 - access(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                                    filter(INTERNAL_FUNCTION("Z4"."N")<=INTERNAL_FUNCTION("Z3"."LVL"))
                                    61 - access("II"=PRIOR NULL)
                                    filter("RE"=:RE)