This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Nov 25, 2009 12:26 PM by Randolf Geist RSS

I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????

705249 Newbie
Currently Being Moderated
Hi,

I have a query which runs in 10 minutes instead of 10 seconds usually.
The issue has appeared since the migration from 9i to 10G.
If I disable the "_OPTIMIZER_COST_BASED_TRANSFORMATION" parameter my query run in 10 seconds.
A lot of our customer who use our application have encountered such issues since they use 10g .

My question is why I have to deactivate this parameter to get the right plan ?

here is the BAD explain plan:
ALTER session SET "_optimizer_cost_based_transformation" = LINEAR;

explain plan for
SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0 
FROM TOTO X  
WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')  
AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%' 
GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;

select * from table(dbms_xplan.display);

Plan hash value: 1719824681
2      
3     ------------------------------------------------------------------------------------------------------------------------------------
4     | Id  | Operation                                               | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
5     ------------------------------------------------------------------------------------------------------------------------------------
6     |   0 | SELECT STATEMENT                                        |                  |    19 |  2907 |       |    10M  (1)| 34:53:59 |
7     |   1 |  SORT AGGREGATE                                         |                  |     1 |    88 |       |            |          |
8     |*  2 |   VIEW                                                  |                  |     1 |    88 |       |     7  (15)| 00:00:01 |
9     |*  3 |    WINDOW SORT PUSHED RANK                              |                  |     1 |    51 |       |     7  (15)| 00:00:01 |
10     |*  4 |     TABLE ACCESS BY INDEX ROWID                         | HISMVC           |     1 |    51 |       |     6   (0)| 00:00:01 |
11     |*  5 |      INDEX RANGE SCAN                                   | HISMVC1          |     6 |       |       |     3   (0)| 00:00:01 |
12     |   6 |  SORT GROUP BY                                          |                  |    19 |  2907 |       |    10M  (1)| 34:53:59 |
13     |   7 |   NESTED LOOPS OUTER                                    |                  |    19 |  2907 |       |    10M  (1)| 34:53:59 |
14     |   8 |    NESTED LOOPS OUTER                                   |                  |    19 |  2831 |       |    10M  (1)| 34:53:59 |
15     |*  9 |     HASH JOIN OUTER                                     |                  |    19 |  2527 |       |    10M  (1)| 34:53:59 |
16     |* 10 |      HASH JOIN                                          |                  |    19 |  2451 |       |    10M  (1)| 34:53:59 |
17     |* 11 |       HASH JOIN                                         |                  |    19 |  2337 |       |    10M  (1)| 34:53:59 |
18     |* 12 |        HASH JOIN                                        |                  |    19 |  2071 |       |    10M  (1)| 34:53:53 |
19     |  13 |         VIEW                                            |                  |    37 |  3552 |       |    10M  (1)| 34:53:53 |
20     |* 14 |          HASH JOIN RIGHT OUTER                          |                  |    37 | 11988 |       |    10M  (1)| 34:53:53 |
21     |* 15 |           INDEX FAST FULL SCAN                          | RGCCAL1          |    45 |   495 |       |     2   (0)| 00:00:01 |
22     |  16 |           NESTED LOOPS OUTER                            |                  |    37 | 11581 |       |    10M  (1)| 34:53:53 |
23     |* 17 |            HASH JOIN OUTER                              |                  |    37 | 11248 |       |    10M  (1)| 34:53:53 |
24     |* 18 |             HASH JOIN RIGHT OUTER                       |                  |    37 | 10767 |       |    10M  (1)| 34:53:53 |
25     |  19 |              TABLE ACCESS FULL                          | COMPTE           |   308 |  3696 |       |     5   (0)| 00:00:01 |
26     |  20 |              NESTED LOOPS                               |                  |    37 | 10323 |       |    10M  (1)| 34:53:53 |
27     |  21 |               NESTED LOOPS OUTER                        |                  |    37 | 10175 |       |    10M  (1)| 34:53:53 |
28     |  22 |                NESTED LOOPS OUTER                       |                  |    37 | 10064 |       |    10M  (1)| 34:53:53 |
29     |  23 |                 NESTED LOOPS OUTER                      |                  |    37 |  9731 |       |    10M  (1)| 34:53:53 |
30     |  24 |                  NESTED LOOPS OUTER                     |                  |    37 |  9398 |       |    10M  (1)| 34:53:53 |
31     |  25 |                   NESTED LOOPS OUTER                    |                  |    37 |  9065 |       |    10M  (1)| 34:53:53 |
32     |  26 |                    NESTED LOOPS OUTER                   |                  |    37 |  8732 |       |    10M  (1)| 34:53:53 |
33     |  27 |                     NESTED LOOPS OUTER                  |                  |    37 |  8399 |       |    10M  (1)| 34:53:53 |
34     |* 28 |                      HASH JOIN                          |                  |    37 |  8066 |       |    10M  (1)| 34:53:53 |
35     |* 29 |                       HASH JOIN                         |                  |   129 | 17286 |       | 35145   (2)| 00:07:02 |
36     |  30 |                        TABLE ACCESS BY INDEX ROWID      | HISNEG           | 14478 |   975K|       |  7220   (1)| 00:01:27 |
37     |* 31 |                         INDEX RANGE SCAN                | HISNEG4          | 14478 |       |       |    42   (0)| 00:00:01 |
38     |* 32 |                        TABLE ACCESS FULL                | IHSDEP           | 32994 |  2094K|       | 27925   (2)| 00:05:36 |
39     |  33 |                       VIEW                              | VCRDAL           |  3589K|   287M|       |    10M  (1)| 34:46:51 |
40     |  34 |                        HASH GROUP BY                    |                  |  3589K|   314M|   692M|    10M  (1)| 34:46:51 |
41     |  35 |                         MERGE JOIN CARTESIAN            |                  |  3589K|   314M|       |    10M  (1)| 34:31:37 |
42     |* 36 |                          HASH JOIN                      |                  |  3589K|   301M|       |   128K  (1)| 00:25:45 |
43     |* 37 |                           TABLE ACCESS FULL             | TYPSCR           |    14 |   112 |       |     5   (0)| 00:00:01 |
44     |* 38 |                           HASH JOIN                     |                  |  2564K|   195M|       |   128K  (1)| 00:25:45 |
45     |  39 |                            TABLE ACCESS FULL            | DEVISE           |    44 |   308 |       |     5   (0)| 00:00:01 |
46     |* 40 |                            HASH JOIN                    |                  |  2564K|   178M|   185M|   128K  (1)| 00:25:45 |
47     |  41 |                             TABLE ACCESS BY INDEX ROWID | HISMVC           |  2564K|   156M|       |   119K  (1)| 00:23:53 |
48     |  42 |                              BITMAP CONVERSION TO ROWIDS|                  |       |       |       |            |          |
49     |* 43 |                               BITMAP INDEX SINGLE VALUE | HISMVC6_BMP      |       |       |       |            |          |
50     |  44 |                             TABLE ACCESS FULL           | PARMAR           |    72 |   648 |       |     5   (0)| 00:00:01 |
51     |  45 |                          BUFFER SORT                    |                  |     1 |     4 |       |    10M  (1)| 34:46:51 |
52     |  46 |                           TABLE ACCESS FULL             | RENGEN           |     1 |     4 |       |     3   (0)| 00:00:01 |
53     |* 47 |                      INDEX UNIQUE SCAN                  | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
54     |* 48 |                     INDEX UNIQUE SCAN                   | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
55     |* 49 |                    INDEX UNIQUE SCAN                    | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
56     |* 50 |                   INDEX UNIQUE SCAN                     | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
57     |* 51 |                  INDEX UNIQUE SCAN                      | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
58     |* 52 |                 INDEX UNIQUE SCAN                       | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
59     |* 53 |                INDEX UNIQUE SCAN                        | PORTEF2          |     1 |     3 |       |     0   (0)| 00:00:01 |
60     |* 54 |               INDEX UNIQUE SCAN                         | COMPTE3          |     1 |     4 |       |     0   (0)| 00:00:01 |
61     |  55 |             VIEW                                        | index$_join$_028 |   379 |  4927 |       |     3   (0)| 00:00:01 |
62     |* 56 |              HASH JOIN                                  |                  |       |       |       |            |          |
63     |  57 |               INDEX FAST FULL SCAN                      | INTERV1          |   379 |  4927 |       |     1   (0)| 00:00:01 |
64     |  58 |               INDEX FAST FULL SCAN                      | INTERV3          |   379 |  4927 |       |     1   (0)| 00:00:01 |
65     |* 59 |            INDEX UNIQUE SCAN                            | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
66     |  60 |         TABLE ACCESS FULL                               | INTERV           |   379 |  4927 |       |     5   (0)| 00:00:01 |
67     |  61 |        TABLE ACCESS FULL                                | NATACF           | 62466 |   854K|       |   481   (1)| 00:00:06 |
68     |  62 |       INDEX FAST FULL SCAN                              | OBJCON1          | 11541 | 69246 |       |     7   (0)| 00:00:01 |
69     |  63 |      INDEX FULL SCAN                                    | MOIECH1          |    12 |    48 |       |     1   (0)| 00:00:01 |
70     |* 64 |     INDEX UNIQUE SCAN                                   | COMPTE1          |     1 |    16 |       |     0   (0)| 00:00:01 |
71     |* 65 |    INDEX UNIQUE SCAN                                    | SIEGE1           |     1 |     4 |       |     0   (0)| 00:00:01 |
72     ------------------------------------------------------------------------------------------------------------------------------------
73      
74     Predicate Information (identified by operation id):
75     ---------------------------------------------------
76      
77        2 - filter("H"."RK"=1)
78        3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX" ORDER BY 
79                   "H"."CTCOF")<=1)
80        4 - filter("H"."DAVAL"=:B1 AND "H"."DATRA"=:B2 AND "H"."NCCOF"=11 AND "H"."CDCOF"=:B3)
81        5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
82        9 - access("M"."CMECH"(+)="X"."CMECH")
83       10 - access("O"."COBCN"="A"."COBCN")
84       11 - access("A"."CNACT"="X"."CNACT")
85       12 - access("I"."COINT"="X"."COINL")
86       14 - access("R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR")
87       15 - filter("R"."COMAR"(+)='ICE')
88       17 - access("I"."IDINT"(+)="H"."ID_COINC")
89       18 - access("C"."ID_NUCPT"(+)="H"."ID_NUCPT")
90       28 - access("V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP")
91            filter("V"."MTHEX"<>0 OR "H"."CTFDE"='N')
92       29 - access("Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA")
93       31 - access("Z"."DATOP">=TO_DATE(' 2009-11-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE(' 2009-11-08 
94                   23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
95       32 - filter("H"."COINL" IS NOT NULL AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("H"."CTFDE"='A' OR 
96                   "H"."CTFDE"='E' OR "H"."CTFDE"='L' OR "H"."CTFDE"='N' OR "H"."CTFDE"='S'))
97       36 - access("S"."CTCOF"="X"."CTCOF")
98       37 - filter("S"."COLAN"='A')
99       38 - access("D"."CODEV"="X"."CDCOF")
100       40 - access("P"."COMAR"="X"."COMAR")
101       43 - access("X"."NCCOF"=11)
102       47 - access("O"."COINT"(+)="H"."COINA")
103       48 - access("M"."COINT"(+)="H"."COINV")
104       49 - access("L"."COINT"(+)="H"."COINI")
105       50 - access("N"."COINT"(+)="Z"."COINK")
106       51 - access("K"."COINT"(+)="Z"."COINN")
107       52 - access("J"."COINT"(+)="Z"."COINF")
108       53 - access("P"."ID_COPOR"(+)="H"."ID_COPOR")
109       54 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
110       56 - access(ROWID=ROWID)
111       59 - access("I"."COINT"(+)="I"."COINT")
112       64 - access("C"."COINT"(+)="X"."COINC" AND "C"."NUCPT"(+)="X"."NUCPT")
113       65 - access("I"."COSIE"="S"."COSIE"(+))
114      
115     Note
116     -----
117        - 'PLAN_TABLE' is old version
118        - dynamic sampling used for this statement
here is the GOODexplain plan:
ALTER session SET "_optimizer_cost_based_transformation" = OFF;

explain plan for
SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0 
FROM TOTO X  
WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')  
AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%' 
GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;

select * from table(dbms_xplan.display);

1     Plan hash value: 2919098668
2      
3     ----------------------------------------------------------------------------------------------------------------------------
4     | Id  | Operation                                                | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
5     ----------------------------------------------------------------------------------------------------------------------------
6     |   0 | SELECT STATEMENT                                         |         |    19 |  3173 |       | 36594   (2)| 00:07:20 |
7     |   1 |  SORT AGGREGATE                                          |         |     1 |    88 |       |            |          |
8     |*  2 |   VIEW                                                   |         |     1 |    88 |       |     7  (15)| 00:00:01 |
9     |*  3 |    WINDOW SORT PUSHED RANK                               |         |     1 |    51 |       |     7  (15)| 00:00:01 |
10     |*  4 |     TABLE ACCESS BY INDEX ROWID                          | HISMVC  |     1 |    51 |       |     6   (0)| 00:00:01 |
11     |*  5 |      INDEX RANGE SCAN                                    | HISMVC1 |     6 |       |       |     3   (0)| 00:00:01 |
12     |   6 |  SORT GROUP BY                                           |         |    19 |  3173 |       | 36594   (2)| 00:07:20 |
13     |   7 |   NESTED LOOPS OUTER                                     |         |    19 |  3173 |       | 36593   (2)| 00:07:20 |
14     |   8 |    NESTED LOOPS OUTER                                    |         |    19 |  3097 |       | 36593   (2)| 00:07:20 |
15     |*  9 |     HASH JOIN OUTER                                      |         |    19 |  2793 |       | 36593   (2)| 00:07:20 |
16     |* 10 |      HASH JOIN                                           |         |    19 |  2717 |       | 36592   (2)| 00:07:20 |
17     |* 11 |       HASH JOIN                                          |         |    19 |  2603 |       | 36584   (2)| 00:07:20 |
18     |* 12 |        HASH JOIN                                         |         |    19 |  2337 |       | 36102   (2)| 00:07:14 |
19     |  13 |         VIEW                                             |         |    37 |  4070 |       | 36097   (2)| 00:07:14 |
20     |* 14 |          FILTER                                          |         |       |       |       |            |          |
21     |  15 |           HASH GROUP BY                                  |         |    37 | 23791 |       | 36097   (2)| 00:07:14 |
22     |* 16 |            HASH JOIN RIGHT OUTER                         |         |   127 | 81661 |       | 36096   (2)| 00:07:14 |
23     |* 17 |             TABLE ACCESS FULL                            | RGCCAL  |    45 |  1755 |       |     5   (0)| 00:00:01 |
24     |* 18 |             HASH JOIN                                    |         |   127 | 76708 |       | 36090   (2)| 00:07:14 |
25     |  19 |              TABLE ACCESS FULL                           | PARMAR  |    72 |   648 |       |     5   (0)| 00:00:01 |
26     |* 20 |              HASH JOIN                                   |         |   127 | 75565 |       | 36085   (2)| 00:07:14 |
27     |* 21 |               TABLE ACCESS FULL                          | TYPSCR  |    14 |   112 |       |     5   (0)| 00:00:01 |
28     |* 22 |               HASH JOIN                                  |         |    91 | 53417 |       | 36079   (2)| 00:07:13 |
29     |  23 |                TABLE ACCESS FULL                         | DEVISE  |    44 |   308 |       |     5   (0)| 00:00:01 |
30     |* 24 |                TABLE ACCESS BY INDEX ROWID               | HISMVC  |     1 |    64 |       |     5   (0)| 00:00:01 |
31     |  25 |                 NESTED LOOPS                             |         |    91 | 52780 |       | 36074   (2)| 00:07:13 |
32     |* 26 |                  HASH JOIN RIGHT OUTER                   |         |   129 | 66564 |       | 35560   (2)| 00:07:07 |
33     |  27 |                   TABLE ACCESS FULL                      | INTERV  |   379 |  8717 |       |     5   (0)| 00:00:01 |
34     |* 28 |                   HASH JOIN RIGHT OUTER                  |         |   129 | 63597 |       | 35555   (2)| 00:07:07 |
35     |  29 |                    TABLE ACCESS FULL                     | INTERV  |   379 |  9475 |       |     5   (0)| 00:00:01 |
36     |* 30 |                    HASH JOIN RIGHT OUTER                 |         |   129 | 60372 |       | 35549   (2)| 00:07:07 |
37     |  31 |                     TABLE ACCESS FULL                    | COMPTE  |   308 |  7392 |       |     5   (0)| 00:00:01 |
38     |  32 |                     NESTED LOOPS                         |         |   129 | 57276 |       | 35544   (2)| 00:07:07 |
39     |* 33 |                      HASH JOIN RIGHT OUTER               |         |   129 | 55212 |       | 35544   (2)| 00:07:07 |
40     |  34 |                       TABLE ACCESS FULL                  | PORTEF  |    11 |   231 |       |     5   (0)| 00:00:01 |
41     |* 35 |                       HASH JOIN RIGHT OUTER              |         |   129 | 52503 |       | 35538   (2)| 00:07:07 |
42     |  36 |                        TABLE ACCESS FULL                 | INTERV  |   379 |  8717 |       |     5   (0)| 00:00:01 |
43     |  37 |                        NESTED LOOPS OUTER                |         |   129 | 49536 |       | 35533   (2)| 00:07:07 |
44     |  38 |                         NESTED LOOPS OUTER               |         |   129 | 46569 |       | 35533   (2)| 00:07:07 |
45     |* 39 |                          HASH JOIN                       |         |   129 | 43602 |  3112K| 35533   (2)| 00:07:07 |
46     |* 40 |                           HASH JOIN RIGHT OUTER          |         | 14478 |  2940K|       |  7235   (1)| 00:01:27 |
47     |  41 |                            TABLE ACCESS FULL             | INTERV  |   379 |  8717 |       |     5   (0)| 00:00:01 |
48     |* 42 |                            HASH JOIN RIGHT OUTER         |         | 14478 |  2615K|       |  7229   (1)| 00:01:27 |
49     |  43 |                             TABLE ACCESS FULL            | INTERV  |   379 |  8717 |       |     5   (0)| 00:00:01 |
50     |  44 |                             NESTED LOOPS OUTER           |         | 14478 |  2290K|       |  7224   (1)| 00:01:27 |
51     |  45 |                              NESTED LOOPS                |         | 14478 |  1965K|       |  7224   (1)| 00:01:27 |
52     |  46 |                               TABLE ACCESS FULL          | RENGEN  |     1 |     4 |       |     5   (0)| 00:00:01 |
53     |  47 |                               TABLE ACCESS BY INDEX ROWID| HISNEG  | 14478 |  1908K|       |  7219   (1)| 00:01:27 |
54     |* 48 |                                INDEX RANGE SCAN          | HISNEG4 | 14478 |       |       |    41   (0)| 00:00:01 |
55     |  49 |                              TABLE ACCESS BY INDEX ROWID | INTERV  |     1 |    23 |       |     0   (0)| 00:00:01 |
56     |* 50 |                               INDEX UNIQUE SCAN          | INTERV1 |     1 |       |       |     0   (0)| 00:00:01 |
57     |* 51 |                           TABLE ACCESS FULL              | IHSDEP  | 32994 |  4188K|       | 27925   (2)| 00:05:36 |
58     |  52 |                          TABLE ACCESS BY INDEX ROWID     | INTERV  |     1 |    23 |       |     0   (0)| 00:00:01 |
59     |* 53 |                           INDEX UNIQUE SCAN              | INTERV1 |     1 |       |       |     0   (0)| 00:00:01 |
60     |  54 |                         TABLE ACCESS BY INDEX ROWID      | INTERV  |     1 |    23 |       |     0   (0)| 00:00:01 |
61     |* 55 |                          INDEX UNIQUE SCAN               | INTERV1 |     1 |       |       |     0   (0)| 00:00:01 |
62     |* 56 |                      INDEX UNIQUE SCAN                   | COMPTE3 |     1 |    16 |       |     0   (0)| 00:00:01 |
63     |* 57 |                  INDEX RANGE SCAN                        | HISMVC1 |     6 |       |       |     2   (0)| 00:00:01 |
64     |  58 |         TABLE ACCESS FULL                                | INTERV  |   379 |  4927 |       |     5   (0)| 00:00:01 |
65     |  59 |        TABLE ACCESS FULL                                 | NATACF  | 62466 |   854K|       |   481   (1)| 00:00:06 |
66     |  60 |       INDEX FAST FULL SCAN                               | OBJCON1 | 11541 | 69246 |       |     7   (0)| 00:00:01 |
67     |  61 |      INDEX FULL SCAN                                     | MOIECH1 |    12 |    48 |       |     1   (0)| 00:00:01 |
68     |* 62 |     INDEX UNIQUE SCAN                                    | COMPTE1 |     1 |    16 |       |     0   (0)| 00:00:01 |
69     |* 63 |    INDEX UNIQUE SCAN                                     | SIEGE1  |     1 |     4 |       |     0   (0)| 00:00:01 |
70     ----------------------------------------------------------------------------------------------------------------------------
71      
72     Predicate Information (identified by operation id):
73     ---------------------------------------------------
74      
75        2 - filter("H"."RK"=1)
76        3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX" 
77                   ORDER BY "H"."CTCOF")<=1)
78        4 - filter("H"."DAVAL"=:B1 AND "H"."DATRA"=:B2 AND "H"."NCCOF"=11 AND "H"."CDCOF"=:B3)
79        5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
80        9 - access("M"."CMECH"(+)="X"."CMECH")
81       10 - access("O"."COBCN"="A"."COBCN")
82       11 - access("A"."CNACT"="X"."CNACT")
83       12 - access("I"."COINT"="X"."COINL")
84       14 - filter(SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF",DECODE("R"."NUSPF",825,0,2)),DECO
85                   DE("P"."ARRCO",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D
86                   "."FACAR"))))<>0 OR "H"."CTFDE"='N')
87       16 - access("R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR")
88       17 - filter("R"."COMAR"(+)='ICE')
89       18 - access("P"."COMAR"="X"."COMAR")
90       20 - access("S"."CTCOF"="X"."CTCOF")
91       21 - filter("S"."COLAN"='A')
92       22 - access("D"."CODEV"="X"."CDCOF")
93       24 - filter("X"."NCCOF"=11)
94       26 - access("I"."COINT"(+)="I"."COINT")
95       28 - access("I"."IDINT"(+)="H"."ID_COINC")
96       30 - access("C"."ID_NUCPT"(+)="H"."ID_NUCPT")
97       33 - access("P"."ID_COPOR"(+)="H"."ID_COPOR")
98       35 - access("L"."COINT"(+)="H"."COINI")
99       39 - access("Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA")
100       40 - access("J"."COINT"(+)="Z"."COINF")
101       42 - access("K"."COINT"(+)="Z"."COINN")
102       48 - access("Z"."DATOP">=TO_DATE(' 2009-11-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE(' 
103                   2009-11-08 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
104       50 - access("N"."COINT"(+)="Z"."COINK")
105       51 - filter("H"."COINL" IS NOT NULL AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("H"."CTFDE"='A' OR 
106                   "H"."CTFDE"='E' OR "H"."CTFDE"='L' OR "H"."CTFDE"='N' OR "H"."CTFDE"='S'))
107       53 - access("O"."COINT"(+)="H"."COINA")
108       55 - access("M"."COINT"(+)="H"."COINV")
109       56 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
110       57 - access("X"."NUBIX"="H"."NUBIX" AND "X"."NUFDP"="H"."NUFDP")
111       62 - access("C"."COINT"(+)="X"."COINC" AND "C"."NUCPT"(+)="X"."NUCPT")
112       63 - access("I"."COSIE"="S"."COSIE"(+))
113      
114     Note
115     -----
116        - 'PLAN_TABLE' is old version
117        - dynamic sampling used for this statement
here is the script of the TOTO view :
CREATE OR REPLACE VIEW toto
(coinl, nminl, adinl1, adinl2, adinl3, adinl4, adinl5, colal, cosie, lisie, nubnl, coinf, coinn, coope, coint, nucpt, copor, idety, coini, comar, comsc, cnact, ntact, lnact, cnacn, cnama, caini, caloc, cdvac, ctacf, cobcn, ctstr, cnach, datra, datop, daval, csens, qtfdp, mtneg, mvneg, csopt, cmech, nmech, caech, mtsna, nuver, nuins, nucon, nubix, nufdp, nuord, nutic, ctfne, ctfic, coglo, cnaop, cntra, codev, mthex, mtvex, mthgx, mtvgx, mttgl, copro, coinv, coina, couti, ctcot, cotsj, coink, ischc, daecb, tytrn, saima, corig, rgcod, rgcid, mtref, daeca, cotal, cotax, cotaf, cotan, cotai, cotav, cotak, cotaa, info1, guref, rgcof, rgcif)
AS
SELECT 
X.COINL,
I.NMINT,I.ADINT1,I.ADINT2,I.ADINT3,I.ADINT4,I.ADINT5,I.COLAN,I.COSIE,S.LISIE,I.NUBNK,
X.COINF,X.COINN,X.COOPE,X.COINC,X.NUCPT,X.COPOR,X.IDETY,X.COINI,
X.COMAR,A.COMSC,
X.CNACT,
DECODE(A.CNACN,'T',0,1),A.LNACT,A.CNACN,A.CNAMA,A.CAINI,A.CALOC,A.CODEV,A.CTACF,A.COBCN,
X.CTSTR,X.CNACE,
X.DATRA,X.DATOP,X.DAVAL,X.CSENS,X.QTCOF,X.MTNEG,X.MVNEG,
X.CSOPT,X.CMECH,M.NMECH,X.CAECH,X.MTSNA,X.NUVER,X.NUINS,
X.NUCON,X.NUBIX,X.NUFDP,X.NUORD,X.NUTIC,X.CTFNE,X.CTFIC,X.COGLO,X.CNAOP,X.CNTRA,
X.CODEV,
X.MTHEX,X.MTVEX,X.MTHGX,X.MTVGX,X.MTTGL,
O.COPRO,X.COINV,X.COINA,X.COUTI,
A.CTCOT,A.COTSJ,X.COINK,X.ISCHC,X.DAECB,X.TYTRN,
DECODE(X.CORIG,NULL,'O','F','F','REG','REG','N'),X.CORIG,C.RGCOD,C.RGCID,X.MTREF,X.DAECA, 
I.COTAX,X.COTAX,X.COTAF,X.COTAN,X.COTAI,X.COTAV,X.COTAK,X.COTAA,X.INFO1,X.GUREF,X.RGCOF,X.RGCIF
FROM
(SELECT 
 Y.COINL,
 Z.COINF,Z.COINN,Z.COOPE,Y.COINC,Y.NUCPT,Y.COPOR,Y.IDETY,Y.COINI,
 Y.COMAR,
 Z.CNACT,
 Y.CTSTR,Y.CNACE,
 V.DATRA,Z.DATOP,V.DAVAL,Z.CSENS,V.QTCOF,Z.MTNEG,Z.MVNEG,
 Z.CSOPT,Z.CMECH,Z.CAECH,Z.MTSNA,Z.NUVER,Z.NUINS,
 Z.NUCON,Y.NUBIX,Y.NUFDP,Y.NUORD,Y.NUTIC,Y.CTFNE,Y.CTFDE,Y.CTFIC,Z.COGLO,Y.CNAOP,Y.CNTRA,
 V.CODEV,
 V.MTHEX,V.MTVEX,V.MTHGX,V.MTVGX,V.MTTGL,
 Y.COINV,Y.COINA,Y.COUTI,
 Z.COINK,V.ISCHC,Z.DAECB,Z.TYTRN,Z.CORIG,Z.MTREF,Z.DAECA, 
 I.COTAX,J.COTAX COTAF,K.COTAX COTAN,L.COTAX COTAI,M.COTAX COTAV,N.COTAX COTAK,O.COTAX COTAA, 
 Y.INFO1,Y.GUREF,R.RGCOD RGCOF,R.RGCID RGCIF
 FROM
 HISDEP Y,
 HISNEG Z,
 VCRDAL V,
 INTERV I,
 INTERV J,
 INTERV K,
 INTERV L,
 INTERV M,
 INTERV N,
 INTERV O,
 RGCCAL R
 WHERE
 Y.CTFDE IN ('N','L','A','S','E') AND
 Y.COINL IS NOT NULL              AND
 Z.NUBIX=Y.NUBIX                  AND
 Z.DATRA=Y.DATRA                  AND -- perf
 V.NUBIX=Y.NUBIX                  AND
 V.NUFDP=Y.NUFDP                  AND
 I.COINT (+)= Y.COINC             AND
 J.COINT (+)= Z.COINF             AND
 K.COINT (+)= Z.COINN             AND
 L.COINT (+)= Y.COINI             AND
 M.COINT (+)= Y.COINV             AND
 N.COINT (+)= Z.COINK             AND
 O.COINT (+)= Y.COINA             AND
 R.COINT (+)= Y.COINL             AND
 R.COMAR (+)= Y.COMAR             
) X,
INTERV I,
NATACF A,
OBJCON O,
MOIECH M,
COMPTE C,
SIEGES S
WHERE
(X.MTHEX!=0 OR X.CTFDE='N') AND
I.COINT=X.COINL             AND
A.CNACT=X.CNACT             AND
O.COBCN=A.COBCN             AND
M.CMECH(+)=X.CMECH          AND
C.COINT(+)=X.COINC          AND
C.NUCPT(+)=X.NUCPT          AND
I.COSIE=S.COSIE(+);
Thanks for helping
  • 1. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    RobertGeier Oracle ACE
    Currently Being Moderated
    Why do you want to use this parameter ? It is better to NOT use understore parameters like "_OPTIMIZER_COST_BASED_TRANSFORMATION".

    You upgrade to 10g, you get better performance by without parameter, so what is the problem ? Why tune a query so you can use a parameter you don't need ?
  • 2. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    joc Oracle ACE Director
    Currently Being Moderated
    First just few observations:
    1) You have old version of the PLAN_TABLE. Run $ORACLE_HOME/rdbms/admin/utlxplan.sql to create the latest version
    2) Some of the tables are not analyzed and dynamic sampling is used by CBO to get basic statistics data about tables. I would suggest to gather statistics first.

    I would suggest you to run the statement in both configurations (with altered hideen parameter and without) with included hint /*+ gather_plan_statistics */
    SELECT /*+ gather_plan_statistics */  '02/11/09','08/11/09',X.COINL,X.CNACT...
    and then run the following:
    select * from table(dbms_xplan.display_cursor(NULL,NULL, 'allstats last'));
    Now you will have actual run-time execution plan with estimated and actual cardinality. Post the results and then we can talk.

    Regards, Joze

    Co-author of forthcoming OakTable book "Expert Oracle Practices"
    http://www.apress.com/book/view/9781430226680
  • 3. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    705249 Newbie
    Currently Being Moderated
    OPTMIZERCOST_BASED_TRANSFORMATION= LINEAR
    This is the default value.

    I modified this hidden parameter only after encountering this issue.
  • 4. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    RobertGeier Oracle ACE
    Currently Being Moderated
    Seems like this is a known issue, so a parameter change is easier than tuning the query.

    http://oradbatips.blogspot.com/2007/07/tip-51-performance-issue-after-upgrade.html
    http://www.dbasupport.com/forums/showthread.php?t=51342
    http://www.dba-oracle.com/t_optimizer_cost_based_transformation_parameter.htm
    _optimizer_cost_based_transformation parameter

    What version of 10g are you using ?
  • 5. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    CharlesHooper Expert
    Currently Being Moderated
    Farenheiit wrote:
    OPTMIZERCOST_BASED_TRANSFORMATION= LINEAR
    This is the default value.

    I modified this hidden parameter only after encountering this issue.
    In the slow executing plan you will notice a MERGE JOIN CARTESIAN operation involving the table RENGEN, which according to the statistics for the table, contains 1 row. When you generate the plan using Joze's suggestion, take a close look at the A-Rows column for the line containing RENGEN in the plan - it could be that the table contained 0 rows or 1 row when the table statistics were gathered.

    Based on the simplicity of the SQL statement compared to the explain plan, it appears that TOTO is a view which may reference several other statically defined views - there is a chance that this will also affect Oracle's ability to generate an efficient execution plan depending on how far into the views Oracle is able to push the predicates and eliminate unnecessary tables.

    I suggest not changing the hidden parameter. You could always hint the use of the 9.2 optimizer for the query, if nothing else works. But, I would first check the statistics for the tables and indexes.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 6. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    736085 Newbie
    Currently Being Moderated
    In our application we have some table tables without statistics. This is volunteer because on these tables the volume is changing every time. you can have 1000000 rows at one time and 0 rows one hour later. So we prefer using dynamic sampling (level 2) on these tables.
    But in my test case there is only one table without statistics: RGCCAL
    SQL> select owner, table_name,num_rows, last_analyzed  from all_tables
      2  where table_name in ('HISMVC','COMPTE','HISNEG','IHSDEP','TYPSCR','DEVISE','RENGEN','INTERV','NATACF','PARMAR','PORTEF','RGCCAL')
      3  order by 3;
    
    OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANAL
    ------------------------------ ------------------------------ ---------- ---------
    F362                           RENGEN                                  1 07-NOV-09
    F362                           PORTEF                                 11 07-NOV-09
    F362                           TYPSCR                                 42 07-NOV-09
    COMMON                         DEVISE                                 44 22-NOV-09
    F362                           PARMAR                                 72 07-NOV-09
    F362                           COMPTE                                308 07-NOV-09
    F362                           INTERV                                379 07-NOV-09
    COMMON                         NATACF                              62466 07-NOV-09
    F362                           HISNEG                            3711347 07-NOV-09
    F362                           IHSDEP                            3713726 07-NOV-09
    F362                           HISMVC                           20513644 07-NOV-09
    F362                           RGCCAL                        NULL      NULL     
    
    SQL> select count(1) from RGCCAL;
    
      COUNT(1)
    ----------
            63
            
    SQL> select count(1) from rengen;
    
      COUNT(1)
    ----------
             1
    there is only a single row into RENGEN table. So the MJC is justified.


    Execution plan with OPTMIZERCOST_BASED_TRANSFORMATION= LINEAR :
    SQL> alter session set statistics_level=all;
    
    Session altered.
    
    Elapsed: 00:00:00.00
    SQL> SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0
      2  FROM TOTO X
      3  WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DD
    MMYYYY HH24:MI:SS')
      4  AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%'
      5  GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;
    
    '02/11/0 '08/11/0 COINL    CNACT    SUM(X.QTFDP*X.ISCHC) COD SUM(X.MTHEX) SUM(X.MTVEX) SUM(X.MTTGL)       0
    -------- -------- -------- -------- -------------------- --- ------------ ------------ ------------ ----------
    02/11/09 08/11/09 VMLN AM  WBSFICE                   784 USD         1176            0         1176       0
    
    Elapsed: 00:10:14.08
    
    
    SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL, 'iostats last'));
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  98azu6fmrzpxa, child number 0
    -------------------------------------
    SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0 FROM TOTO X WHERE
    X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND
    X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%' GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4
    
    Plan hash value: 1719824681
    
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                               | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    |   1 |  SORT AGGREGATE                                         |                  |   1993K|      1 |   1993K|00:08:42.07 |      11M|   5176K|      0 |
    |*  2 |   VIEW                                                  |                  |   1993K|      1 |   2094K|00:08:34.54 |      11M|   5176K|      0 |
    |*  3 |    WINDOW SORT PUSHED RANK                              |                  |   1993K|      1 |   2094K|00:08:29.64 |      11M|   5176K|      0 |
    |*  4 |     TABLE ACCESS BY INDEX ROWID                         | HISMVC           |   1993K|      1 |   2094K|00:08:02.97 |      11M|   5176K|      0 |
    |*  5 |      INDEX RANGE SCAN                                   | HISMVC1          |   1993K|      6 |     10M|00:02:27.23 |    4017K|   1297K|      0 |
    |   6 |  SORT GROUP BY                                          |                  |      1 |     19 |      1 |00:10:00.01 |      11M|   5358K|  24986 |
    |   7 |   NESTED LOOPS OUTER                                    |                  |      1 |     19 |      3 |00:10:00.01 |      11M|   5358K|  24986 |
    |   8 |    NESTED LOOPS OUTER                                   |                  |      1 |     19 |      3 |00:10:00.01 |      11M|   5358K|  24986 |
    |*  9 |     HASH JOIN OUTER                                     |                  |      1 |     19 |      3 |00:10:00.01 |      11M|   5358K|  24986 |
    |* 10 |      HASH JOIN                                          |                  |      1 |     19 |      3 |00:10:00.01 |      11M|   5358K|  24986 |
    |* 11 |       HASH JOIN                                         |                  |      1 |     19 |      3 |00:09:59.98 |      11M|   5358K|  24986 |
    |* 12 |        HASH JOIN                                        |                  |      1 |     19 |      3 |00:09:59.90 |      11M|   5358K|  24986 |
    |  13 |         VIEW                                            |                  |      1 |     37 |      3 |00:09:59.89 |      11M|   5358K|  24986 |
    |* 14 |          HASH JOIN RIGHT OUTER                          |                  |      1 |     37 |      3 |00:09:59.89 |      11M|   5358K|  24986 |
    |* 15 |           INDEX FAST FULL SCAN                          | RGCCAL1          |      1 |     45 |     45 |00:00:00.01 |       3 |      1 |      0 |
    |  16 |           NESTED LOOPS OUTER                            |                  |      1 |     37 |      3 |00:09:59.89 |      11M|   5358K|  24986 |
    |* 17 |            HASH JOIN OUTER                              |                  |      1 |     37 |      3 |00:09:59.89 |      11M|   5358K|  24986 |
    |* 18 |             HASH JOIN RIGHT OUTER                       |                  |      1 |     37 |      3 |00:09:59.87 |      11M|   5358K|  24986 |
    |  19 |              TABLE ACCESS FULL                          | COMPTE           |      1 |    308 |    309 |00:00:00.02 |      15 |     14 |      0 |
    |  20 |              NESTED LOOPS                               |                  |      1 |     37 |      3 |00:09:59.85 |      11M|   5358K|  24986 |
    |  21 |               NESTED LOOPS OUTER                        |                  |      1 |     37 |      3 |00:09:59.84 |      11M|   5358K|  24986 |
    |  22 |                NESTED LOOPS OUTER                       |                  |      1 |     37 |      3 |00:09:59.84 |      11M|   5358K|  24986 |
    |  23 |                 NESTED LOOPS OUTER                      |                  |      1 |     37 |      3 |00:09:59.84 |      11M|   5358K|  24986 |
    |  24 |                  NESTED LOOPS OUTER                     |                  |      1 |     37 |      3 |00:09:59.84 |      11M|   5358K|  24986 |
    |  25 |                   NESTED LOOPS OUTER                    |                  |      1 |     37 |      3 |00:09:59.84 |      11M|   5358K|  24986 |
    |  26 |                    NESTED LOOPS OUTER                   |                  |      1 |     37 |      3 |00:09:59.83 |      11M|   5358K|  24986 |
    |  27 |                     NESTED LOOPS OUTER                  |                  |      1 |     37 |      3 |00:09:59.83 |      11M|   5358K|  24986 |
    |* 28 |                      HASH JOIN                          |                  |      1 |     37 |      3 |00:09:59.83 |      11M|   5358K|  24986 |
    |* 29 |                       HASH JOIN                         |                  |      1 |    129 |      3 |00:00:09.46 |     140K|    128K|      0 |
    |  30 |                        TABLE ACCESS BY INDEX ROWID      | HISNEG           |      1 |  14478 |  32487 |00:00:00.98 |   12877 |    897 |      0 |
    |* 31 |                         INDEX RANGE SCAN                | HISNEG4          |      1 |  14478 |  32487 |00:00:00.14 |     100 |    100 |      0 |
    |* 32 |                        TABLE ACCESS FULL                | IHSDEP           |      1 |  32994 |   4133 |00:00:08.40 |     127K|    127K|      0 |
    |  33 |                       VIEW                              | VCRDAL           |      1 |   3589K|   1993K|00:09:47.37 |      11M|   5230K|  24986 |
    |  34 |                        HASH GROUP BY                    |                  |      1 |   3589K|   1993K|00:00:57.08 |   29417 |  54385 |  24986 |
    |  35 |                         MERGE JOIN CARTESIAN            |                  |      1 |   3589K|   2094K|00:00:47.27 |   29417 |  48929 |  19530 |
    |* 36 |                          HASH JOIN                      |                  |      1 |   3589K|   2094K|00:00:40.96 |   29402 |  48915 |  19530 |
    |* 37 |                           TABLE ACCESS FULL             | TYPSCR           |      1 |     14 |     14 |00:00:00.02 |      15 |     14 |      0 |
    |* 38 |                           HASH JOIN                     |                  |      1 |   2564K|   2094K|00:00:37.57 |   29387 |  48901 |  19530 |
    |  39 |                            TABLE ACCESS FULL            | DEVISE           |      1 |     44 |     44 |00:00:00.01 |      15 |      0 |      0 |
    |* 40 |                            HASH JOIN                    |                  |      1 |   2564K|   2094K|00:00:31.24 |   29372 |  48901 |  19530 |
    |  41 |                             TABLE ACCESS BY INDEX ROWID | HISMVC           |      1 |   2564K|   2094K|00:00:25.17 |   29357 |  29357 |      0 |
    |  42 |                              BITMAP CONVERSION TO ROWIDS|                  |      1 |        |   2094K|00:00:00.02 |      57 |     57 |      0 |
    |* 43 |                               BITMAP INDEX SINGLE VALUE | HISMVC6_BMP      |      1 |        |    108 |00:00:00.06 |      57 |     57 |      0 |
    |  44 |                             TABLE ACCESS FULL           | PARMAR           |      1 |     72 |     72 |00:00:00.02 |      15 |     14 |      0 |
    |  45 |                          BUFFER SORT                    |                  |   2094K|      1 |   2094K|00:00:02.67 |      15 |     14 |      0 |
    |  46 |                           TABLE ACCESS FULL             | RENGEN           |      1 |      1 |      1 |00:00:00.02 |      15 |     14 |      0 |
    |* 47 |                      INDEX UNIQUE SCAN                  | INTERV1          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |
    |* 48 |                     INDEX UNIQUE SCAN                   | INTERV1          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |
    |* 49 |                    INDEX UNIQUE SCAN                    | INTERV1          |      3 |      1 |      3 |00:00:00.01 |       2 |      2 |      0 |
    |* 50 |                   INDEX UNIQUE SCAN                     | INTERV1          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |
    |* 51 |                  INDEX UNIQUE SCAN                      | INTERV1          |      3 |      1 |      3 |00:00:00.01 |       2 |      0 |      0 |
    |* 52 |                 INDEX UNIQUE SCAN                       | INTERV1          |      3 |      1 |      3 |00:00:00.01 |       2 |      0 |      0 |
    |* 53 |                INDEX UNIQUE SCAN                        | PORTEF2          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |
    |* 54 |               INDEX UNIQUE SCAN                         | COMPTE3          |      3 |      1 |      3 |00:00:00.01 |       2 |      2 |      0 |
    |  55 |             VIEW                                        | index$_join$_028 |      1 |    379 |    382 |00:00:00.02 |       6 |      3 |      0 |
    |* 56 |              HASH JOIN                                  |                  |      1 |        |    382 |00:00:00.02 |       6 |      3 |      0 |
    |  57 |               INDEX FAST FULL SCAN                      | INTERV1          |      1 |    379 |    382 |00:00:00.01 |       3 |      1 |      0 |
    |  58 |               INDEX FAST FULL SCAN                      | INTERV3          |      1 |    379 |    382 |00:00:00.01 |       3 |      2 |      0 |
    |* 59 |            INDEX UNIQUE SCAN                            | INTERV1          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |
    |  60 |         TABLE ACCESS FULL                               | INTERV           |      1 |    379 |    382 |00:00:00.01 |      15 |     14 |      0 |
    |  61 |        TABLE ACCESS FULL                                | NATACF           |      1 |  62466 |  62467 |00:00:00.01 |    2198 |      0 |      0 |
    |  62 |       INDEX FAST FULL SCAN                              | OBJCON1          |      1 |  11541 |  11541 |00:00:00.02 |      31 |     18 |      0 |
    |  63 |      INDEX FULL SCAN                                    | MOIECH1          |      1 |     12 |     12 |00:00:00.01 |       1 |      0 |      0 |
    |* 64 |     INDEX UNIQUE SCAN                                   | COMPTE1          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |
    |* 65 |    INDEX UNIQUE SCAN                                    | SIEGE1           |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("H"."RK"=1)
       3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX" ORDER BY "H"."CTCOF")<=1)
       4 - filter(("H"."DAVAL"=:B1 AND "H"."DATRA"=:B2 AND "H"."NCCOF"=11 AND "H"."CDCOF"=:B3))
       5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
       9 - access("M"."CMECH"="X"."CMECH")
      10 - access("O"."COBCN"="A"."COBCN")
      11 - access("A"."CNACT"="X"."CNACT")
      12 - access("I"."COINT"="X"."COINL")
      14 - access("R"."COINT"="H"."COINL" AND "R"."COMAR"="H"."COMAR")
      15 - filter("R"."COMAR"='ICE')
      17 - access("I"."IDINT"="H"."ID_COINC")
      18 - access("C"."ID_NUCPT"="H"."ID_NUCPT")
      28 - access("V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP")
           filter(("V"."MTHEX"<>0 OR "H"."CTFDE"='N'))
      29 - access("Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA")
      31 - access("Z"."DATOP">=TO_DATE(' 2009-11-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE(' 2009-11-08 23:59:59',
                  'syyyy-mm-dd hh24:mi:ss'))
      32 - filter(("H"."COINL" IS NOT NULL AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND INTERNAL_FUNCTION("H"."CTFDE")))
      36 - access("S"."CTCOF"="X"."CTCOF")
      37 - filter("S"."COLAN"='A')
      38 - access("D"."CODEV"="X"."CDCOF")
      40 - access("P"."COMAR"="X"."COMAR")
      43 - access("X"."NCCOF"=11)
      47 - access("O"."COINT"="H"."COINA")
      48 - access("M"."COINT"="H"."COINV")
      49 - access("L"."COINT"="H"."COINI")
      50 - access("N"."COINT"="Z"."COINK")
      51 - access("K"."COINT"="Z"."COINN")
      52 - access("J"."COINT"="Z"."COINF")
      53 - access("P"."ID_COPOR"="H"."ID_COPOR")
      54 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
      56 - access(ROWID=ROWID)
      59 - access("I"."COINT"="I"."COINT")
      64 - access("C"."COINT"="X"."COINC" AND "C"."NUCPT"="X"."NUCPT")
      65 - access("I"."COSIE"="S"."COSIE")
    
    Note
    -----
       - dynamic sampling used for this statement
  • 7. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    705249 Newbie
    Currently Being Moderated
    Execution plan with OPTMIZERCOST_BASED_TRANSFORMATION=OFF :
    SQL> alter session set "_optimizer_cost_based_transformation"=OFF;
    
    Session altered.
    
    Elapsed: 00:00:00.00
    SQL> SELECT
      2           '02/11/09', '08/11/09', x.coinl, x.cnact, SUM (x.qtfdp * x.ischc),
      3           x.codev, SUM (x.mthex), SUM (x.mtvex), SUM (x.mttgl), 0
      4      FROM TOTO x
      5     WHERE x.datop >= TO_DATE ('02112009 00:00:00', 'DDMMYYYY HH24:MI:SS')
      6       AND x.datop <= TO_DATE ('08112009 23:59:59', 'DDMMYYYY HH24:MI:SS')
      7       AND x.comar = 'ICE'
      8       AND x.nuord LIKE '%NY%'
      9  GROUP BY x.coinl, x.cnact, x.codev
     10  ORDER BY 3, 6, 4;
    
    '02/11/0 '08/11/0 COINL    CNACT    SUM(X.QTFDP*X.ISCHC) COD SUM(X.MTHEX) SUM(X.MTVEX) SUM(X.MTTGL)       0
    -------- -------- -------- -------- -------------------- --- ------------ ------------ ------------ ----------
    02/11/09 08/11/09 VMLN AM  WBSFICE                   784 USD         1176            0         1176       0
    
    Elapsed: 00:00:13.01
    
    SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL, 'iostats last'));
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  auk6xsywfw78g, child number 0
    -------------------------------------
    SELECT          '02/11/09', '08/11/09', x.coinl, x.cnact, SUM (x.qtfdp * x.ischc),          x.codev, SUM (x.mthex), SUM
    (x.mtvex), SUM (x.mttgl), 0     FROM TOTO x    WHERE x.datop >= TO_DATE ('02112009 00:00:00', 'DDMMYYYY HH24:MI:SS')      AND
    x.datop <= TO_DATE ('08112009 23:59:59', 'DDMMYYYY HH24:MI:SS')      AND x.comar = 'ICE'      AND x.nuord LIKE '%NY%' GROUP BY
    x.coinl, x.cnact, x.codev ORDER BY 3, 6, 4
    
    Plan hash value: 2919098668
    
    ---------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    ---------------------------------------------------------------------------------------------------------------------------------------
    |   1 |  SORT AGGREGATE                                          |         |      3 |      1 |      3 |00:00:00.01 |      17 |      0 |
    |*  2 |   VIEW                                                   |         |      3 |      1 |      3 |00:00:00.01 |      17 |      0 |
    |*  3 |    WINDOW SORT PUSHED RANK                               |         |      3 |      1 |      3 |00:00:00.01 |      17 |      0 |
    |*  4 |     TABLE ACCESS BY INDEX ROWID                          | HISMVC  |      3 |      1 |      3 |00:00:00.01 |      17 |      0 |
    |*  5 |      INDEX RANGE SCAN                                    | HISMVC1 |      3 |      6 |     15 |00:00:00.01 |       8 |      0 |
    |   6 |  SORT GROUP BY                                           |         |      1 |     19 |      1 |00:00:11.77 |     142K|    128K|
    |   7 |   NESTED LOOPS OUTER                                     |         |      1 |     19 |      3 |00:00:11.77 |     142K|    128K|
    |   8 |    NESTED LOOPS OUTER                                    |         |      1 |     19 |      3 |00:00:11.77 |     142K|    128K|
    |*  9 |     HASH JOIN OUTER                                      |         |      1 |     19 |      3 |00:00:11.77 |     142K|    128K|
    |* 10 |      HASH JOIN                                           |         |      1 |     19 |      3 |00:00:11.76 |     142K|    128K|
    |* 11 |       HASH JOIN                                          |         |      1 |     19 |      3 |00:00:11.73 |     142K|    128K|
    |* 12 |        HASH JOIN                                         |         |      1 |     19 |      3 |00:00:11.63 |     140K|    128K|
    |  13 |         VIEW                                             |         |      1 |     37 |      3 |00:00:11.62 |     140K|    128K|
    |* 14 |          FILTER                                          |         |      1 |        |      3 |00:00:11.62 |     140K|    128K|
    |  15 |           HASH GROUP BY                                  |         |      1 |     37 |      3 |00:00:11.62 |     140K|    128K|
    |* 16 |            HASH JOIN RIGHT OUTER                         |         |      1 |    127 |      3 |00:00:11.62 |     140K|    128K|
    |* 17 |             TABLE ACCESS FULL                            | RGCCAL  |      1 |     45 |     45 |00:00:00.01 |      15 |      0 |
    |* 18 |             HASH JOIN                                    |         |      1 |    127 |      3 |00:00:11.62 |     140K|    128K|
    |  19 |              TABLE ACCESS FULL                           | PARMAR  |      1 |     72 |     72 |00:00:00.02 |      15 |     14 |
    |* 20 |              HASH JOIN                                   |         |      1 |    127 |      3 |00:00:11.61 |     140K|    128K|
    |* 21 |               TABLE ACCESS FULL                          | TYPSCR  |      1 |     14 |     14 |00:00:00.01 |      15 |     14 |
    |* 22 |               HASH JOIN                                  |         |      1 |     91 |      3 |00:00:11.59 |     140K|    128K|
    |  23 |                TABLE ACCESS FULL                         | DEVISE  |      1 |     44 |     44 |00:00:00.01 |      15 |      0 |
    |* 24 |                TABLE ACCESS BY INDEX ROWID               | HISMVC  |      1 |      1 |      3 |00:00:11.59 |     140K|    128K|
    |  25 |                 NESTED LOOPS                             |         |      1 |     91 |     19 |00:00:11.58 |     140K|    128K|
    |* 26 |                  HASH JOIN RIGHT OUTER                   |         |      1 |    129 |      3 |00:00:11.56 |     140K|    128K|
    |  27 |                   TABLE ACCESS FULL                      | INTERV  |      1 |    379 |    382 |00:00:00.01 |      15 |     14 |
    |* 28 |                   HASH JOIN RIGHT OUTER                  |         |      1 |    129 |      3 |00:00:11.55 |     140K|    128K|
    |  29 |                    TABLE ACCESS FULL                     | INTERV  |      1 |    379 |    382 |00:00:00.01 |      15 |      0 |
    |* 30 |                    HASH JOIN RIGHT OUTER                 |         |      1 |    129 |      3 |00:00:11.54 |     140K|    128K|
    |  31 |                     TABLE ACCESS FULL                    | COMPTE  |      1 |    308 |    309 |00:00:00.01 |      15 |     14 |
    |  32 |                     NESTED LOOPS                         |         |      1 |    129 |      3 |00:00:11.53 |     140K|    128K|
    |* 33 |                      HASH JOIN RIGHT OUTER               |         |      1 |    129 |      3 |00:00:11.52 |     140K|    128K|
    |  34 |                       TABLE ACCESS FULL                  | PORTEF  |      1 |     11 |     11 |00:00:00.01 |      15 |     14 |
    |* 35 |                       HASH JOIN RIGHT OUTER              |         |      1 |    129 |      3 |00:00:11.50 |     140K|    128K|
    |  36 |                        TABLE ACCESS FULL                 | INTERV  |      1 |    379 |    382 |00:00:00.01 |      15 |      0 |
    |  37 |                        NESTED LOOPS OUTER                |         |      1 |    129 |      3 |00:00:11.50 |     140K|    128K|
    |  38 |                         NESTED LOOPS OUTER               |         |      1 |    129 |      3 |00:00:11.50 |     140K|    128K|
    |* 39 |                          HASH JOIN                       |         |      1 |    129 |      3 |00:00:11.50 |     140K|    128K|
    |* 40 |                           HASH JOIN RIGHT OUTER          |         |      1 |  14478 |  32487 |00:00:01.38 |   12922 |    911 |
    |  41 |                            TABLE ACCESS FULL             | INTERV  |      1 |    379 |    382 |00:00:00.01 |      15 |      0 |
    |* 42 |                            HASH JOIN RIGHT OUTER         |         |      1 |  14478 |  32487 |00:00:01.34 |   12907 |    911 |
    |  43 |                             TABLE ACCESS FULL            | INTERV  |      1 |    379 |    382 |00:00:00.01 |      15 |      0 |
    |  44 |                             NESTED LOOPS OUTER           |         |      1 |  14478 |  32487 |00:00:01.28 |   12892 |    911 |
    |  45 |                              NESTED LOOPS                |         |      1 |  14478 |  32487 |00:00:01.15 |   12892 |    911 |
    |  46 |                               TABLE ACCESS FULL          | RENGEN  |      1 |      1 |      1 |00:00:00.01 |      15 |     14 |
    |  47 |                               TABLE ACCESS BY INDEX ROWID| HISNEG  |      1 |  14478 |  32487 |00:00:01.10 |   12877 |    897 |
    |* 48 |                                INDEX RANGE SCAN          | HISNEG4 |      1 |  14478 |  32487 |00:00:00.13 |     100 |    100 |
    |  49 |                              TABLE ACCESS BY INDEX ROWID | INTERV  |  32487 |      1 |      0 |00:00:00.09 |       0 |      0 |
    |* 50 |                               INDEX UNIQUE SCAN          | INTERV1 |  32487 |      1 |      0 |00:00:00.04 |       0 |      0 |
    |* 51 |                           TABLE ACCESS FULL              | IHSDEP  |      1 |  32994 |   4133 |00:00:09.97 |     127K|    127K|
    |  52 |                          TABLE ACCESS BY INDEX ROWID     | INTERV  |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |* 53 |                           INDEX UNIQUE SCAN              | INTERV1 |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |  54 |                         TABLE ACCESS BY INDEX ROWID      | INTERV  |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |* 55 |                          INDEX UNIQUE SCAN               | INTERV1 |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |* 56 |                      INDEX UNIQUE SCAN                   | COMPTE3 |      3 |      1 |      3 |00:00:00.01 |       2 |      1 |
    |* 57 |                  INDEX RANGE SCAN                        | HISMVC1 |      3 |      6 |     15 |00:00:00.02 |       8 |      3 |
    |  58 |         TABLE ACCESS FULL                                | INTERV  |      1 |    379 |    382 |00:00:00.01 |      15 |      0 |
    |  59 |        TABLE ACCESS FULL                                 | NATACF  |      1 |  62466 |  62467 |00:00:00.01 |    2198 |      0 |
    |  60 |       INDEX FAST FULL SCAN                               | OBJCON1 |      1 |  11541 |  11541 |00:00:00.02 |      31 |     15 |
    |  61 |      INDEX FULL SCAN                                     | MOIECH1 |      1 |     12 |     12 |00:00:00.01 |       1 |      0 |
    |* 62 |     INDEX UNIQUE SCAN                                    | COMPTE1 |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |* 63 |    INDEX UNIQUE SCAN                                     | SIEGE1  |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |
    ---------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("H"."RK"=1)
       3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX" ORDER BY
                  "H"."CTCOF")<=1)
       4 - filter(("H"."DAVAL"=:B1 AND "H"."DATRA"=:B2 AND "H"."NCCOF"=11 AND "H"."CDCOF"=:B3))
       5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
       9 - access("M"."CMECH"="X"."CMECH")
      10 - access("O"."COBCN"="A"."COBCN")
      11 - access("A"."CNACT"="X"."CNACT")
      12 - access("I"."COINT"="X"."COINL")
      14 - filter((SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF",DECODE("R"."NUSPF",825,0,2)),DECODE("P"."AR
                  RCO",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"))))<>0 OR
                  "H"."CTFDE"='N'))
      16 - access("R"."COINT"="H"."COINL" AND "R"."COMAR"="H"."COMAR")
      17 - filter("R"."COMAR"='ICE')
      18 - access("P"."COMAR"="X"."COMAR")
      20 - access("S"."CTCOF"="X"."CTCOF")
      21 - filter("S"."COLAN"='A')
      22 - access("D"."CODEV"="X"."CDCOF")
      24 - filter("X"."NCCOF"=11)
      26 - access("I"."COINT"="I"."COINT")
      28 - access("I"."IDINT"="H"."ID_COINC")
      30 - access("C"."ID_NUCPT"="H"."ID_NUCPT")
      33 - access("P"."ID_COPOR"="H"."ID_COPOR")
      35 - access("L"."COINT"="H"."COINI")
      39 - access("Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA")
      40 - access("J"."COINT"="Z"."COINF")
      42 - access("K"."COINT"="Z"."COINN")
      48 - access("Z"."DATOP">=TO_DATE(' 2009-11-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE(' 2009-11-08
                  23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
      50 - access("N"."COINT"="Z"."COINK")
      51 - filter(("H"."COINL" IS NOT NULL AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND INTERNAL_FUNCTION("H"."CTFDE")))
      53 - access("O"."COINT"="H"."COINA")
      55 - access("M"."COINT"="H"."COINV")
      56 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
      57 - access("X"."NUBIX"="H"."NUBIX" AND "X"."NUFDP"="H"."NUFDP")
      62 - access("C"."COINT"="X"."COINC" AND "C"."NUCPT"="X"."NUCPT")
      63 - access("I"."COSIE"="S"."COSIE")
    
    Note
    -----
       - dynamic sampling used for this statement
    What I don't understand is: if the CBO chooses the cheapest Execution Plan according to the COST calculated, why Does it choose a more expensive plan when the COST BASED TRANSFORMATION feature is enabled ? If it's "COST_BASED" it would choose the cheapest plan, am I correct ?
  • 8. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    Dom Brooks Guru
    Currently Being Moderated
    Any chance of exposing the SQL query for TOTO?
  • 9. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    CharlesHooper Expert
    Currently Being Moderated
    DomBrooks wrote:
    Any chance of exposing the SQL query for TOTO?
    It appears that the OP did post the SQL for the TOTO view (I missed that also). However, the TOTO view seems to reference the VCRDAL view, whose definition was not provided (maybe this is what you are asking for?). Without STATISTICS_LEVEL set to ALL, we can't really trust the 09:47.37 time for the view access (it appears that the OP did set STATISTICS_LEVEL to ALL - so I can't explain why the time on that line of the plan exceeds the 08:42.07 for the entire plan, other than a bug). It is possibly interesting to note that the VCRDAL view name is not present in the fast execution plan, possibly due to view merging.

    To the OP, are you able to rewrite the TOTO view to directly include the necessary portion of the SQL statement for the VCRDAL view, and then not reference the VCRDAL view in the TOTO view?

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 10. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    CharlesHooper Expert
    Currently Being Moderated
    Farenheiit wrote:
    What I don't understand is: if the CBO chooses the cheapest Execution Plan according to the COST calculated, why Does it choose a more expensive plan when the COST BASED TRANSFORMATION feature is enabled ? If it's "COST_BASED" it would choose the cheapest plan, am I correct ?
    There are a couple issues with the above logic (yes, it is reasonable what you state, but there are issues):
    1. Enabling or disabling optimizer features by changing parameters could affect how costs are calculated - take for instance what happens when workload system statistics are available.
    2. The costs are essentially evaluated at each stage of the plan - the least expensive cost at each stage wins. But, the path used during the early stages of the plan could potentially cause an otherwise greater cost for later portions of the plan. Because the costs are essentially evaluated at each stage of the plan, it is typically not a good idea to compare the cost of two different plans for the same query.
    3. Query transformations (see a 10053 trace to see how it is transformed) may take place early in the plan development stage (before evaluating join order), and the options for what query transformations are available in part depends on the optimizer parameters. After query transformations, Oracle is actually executing an entirely different SQL statement which may have significantly different (greater or lower) calculated cost than the original SQL statement with no transformations, even if the time required for the transformed query does not match the associated change in the calculated cost.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 11. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Charles Hooper wrote:
    However, the TOTO view seems to reference the VCRDAL view, whose definition was not provided (maybe this is what you are asking for?). Without STATISTICS_LEVEL set to ALL, we can't really trust the 09:47.37 time for the view access (it appears that the OP did set STATISTICS_LEVEL to ALL - so I can't explain why the time on that line of the plan exceeds the 08:42.07 for the entire plan, other than a bug). It is possibly interesting to note that the VCRDAL view name is not present in the fast execution plan, possibly due to view merging.
    Charles,

    the plan looks a bit odd because the view VCRDAL obviously contains a nested subquery containing an analytic function to restrict the result to the top 1 result - in the bad execution plan this gets executed as part of the projection of the unmerged view and therefore needs to get executed almost 2 million times.

    That part is responsible for most of the time spent by the bad execution - it's still seems to be odd that the CBO doesn't find the plan that it is able to find with the CBQT feature turned off. In that case probably the merging of the view is done based on heuristics. The question is then why does the costing of the merged variant come to a cost result that obviously is higher than the 10M estimated for the plan selected with CBQT enabled.

    Note that the 10M cost of the CBQT plan is caused to a very large extent by the MERGE JOIN CARTESIAN operation, because its cost is simply number of rows from the driving row source times the cost of accessing the inner row source - but that clearly doesn't reflect in most cases the effect of the BUFFER (NO)SORT operation that attempts to read the inner row source into PGA performing the whole operation in memory (if it fits into memory) without any buffer handling overhead.

    Unfortunately the OP hasn't provided yet any detailed version information - if it's e.g. a 10.2.0.1 base release I could imagine that it is a CBO bug.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
    http://www.apress.com/book/view/1430226684
    http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
  • 12. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    705249 Newbie
    Currently Being Moderated
    The oracle version used is 10.2.0.4:
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for Linux: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    Here is the VCRDAL view script:
    SELECT /*+ ORDERED INDEX_COMBINE(X)*/
    X.COMAR,X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF,
    (SELECT ABS(SUM(DECODE(H.RK,1,DECODE(H.CTMVC,'N',0,'D',0,H.QTCOF*H.CSCOF),0)))
     FROM
     (SELECT H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,RANK() OVER(PARTITION BY H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,H.NTUBX ORDER BY H.CTCOF) RK,H.
    CTMVC,H.QTCOF,H.CSCOF
      FROM HISMVC H
      WHERE
      H.NCCOF=11
     ) H
     WHERE
     H.DATRA=X.DATRA AND
     H.DAVAL=X.DAVAL AND
     H.NUBIX=X.NUBIX AND
     H.NUFDP=X.NUFDP AND
     H.CDCOF=X.CDCOF AND
     H.RK=1
    ) QTCOF,
    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2))                  ,DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*
    X.CSCOF,D.FACAR)                         ,TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF,D.FACAR)))),
    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*X.QTC
    OF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
    0,0,
    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2))                  ,DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*
    X.CSCOF,D.FACAR)                         ,TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF,D.FACAR))))+
    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*X.QTC
    OF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
    MIN(DECODE(X.DATRA,X.DAORI,1,DECODE(X.CTMVC,'N',0,'D',0,1)))
    FROM
    HISMVC X,
    PARMAR P,
    DEVISE D,
    TYPSCR S,
    RENGEN R
    WHERE
    X.NCCOF=11      AND
    P.COMAR=X.COMAR AND
    D.CODEV=X.CDCOF AND
    S.CTCOF=X.CTCOF AND
    S.COLAN='A'
    GROUP BY
    X.COMAR,X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF;
    Let me know if you want other information
  • 13. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Here is the VCRDAL view script:
    Let me know if you want other information
    This looks like a typical "upgrade" issue: Your VCRDAL view contains an ORDERED hint (and an INDEX_COMBINE in addition). This very likely explains why Oracle 10g with CBQT enabled comes up with such costly plans - it looks like it obeys the ORDERED hint.

    With CBQT disabled it looks like that due to view merging taking place the hint is not obeyed and therefore the optimizer is able to come up with a more reasonable plan.

    So the bottom line is: Don't blame it on Oracle - in previous releases it might have simply ignored the hint and therefore came up with a better plan.

    When upgrading you should always check every SQL statement that contains a hint - one of the first things that are recommended to do is get rid of all hints and try with the new release to see what the new optimizer code works out without any hints - in particular strong hints like ORDERED and an upgrade from 9i to 10g due to the numerous changes in the optimizer introduced in 10g.

    So can you show us what you get with CBQT enabled and without the ORDERED (and any other) hint?

    Furthermore - as a general guideline - it might be useful to replace the nested subquery with a join - it doesn't looks like a problem in this particular case since the "good" transformation already reduced the result set to 3 rows before executing the nested subquery, but it could be worse if you get more rows.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
    http://www.apress.com/book/view/1430226684
    http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
  • 14. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    joc Oracle ACE Director
    Currently Being Moderated
    Randolf,

    Excellent analysis.
    I would just like to add a small comment one should keep in mind that using hints in views is not recommended. The reason behind is that people select from views and add new condtions and also join views with other tables or views. Using hints in a view therefore forces certain access path which is maybe siutable only for a particular usage (query) but for sure not for all.

    Regards, Joze

    Co-author of forthcoming OakTable book "Expert Oracle Practices"
    http://www.apress.com/book/view/9781430226680

    Home page: http://www.dbprof.com
    Oracle related blog: http://joze-senegacnik.blogspot.com/
    Blog about flying: http://jsenegacnik.blogspot.com/
1 2 Previous Next

Legend

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