Oracle Optimizer Cost Based Transformations: Join View Factorization

Oracle Optimizer Cost Based Transformations

Join View Factorization

Recently, I got a complain regarding slowness in the Db where one of the query was spending lot of time and its was like never ending.

Looking at it closely it was Optimizer cost based transformation which was doing the trick.

Optimizer Cost based transformation<Default value Linear> is the one which allows Oracle Optimizer to rewrite the query so as to come up with best execution plan.

In my case, based on given facts Optimizer chose view join factorization <VW_JF_SET$BE5F108C see the execution plan>.

Join factorization is a cost-based transformation in which the branches of an UNION/UNION ALL query that join a common table are combined to reduce accesses to the common table. Meaning common table will be accessed just once instead of multiple times to gain on performance.

Now the issue with the Query plan is that due view join factorization< VW_JF_SET$BE5F108C>  one of the T3 table is pushed at the end of the execution and this caused all the mess.

Meaning instead of starting the execution of the query with joining of T1 and T3. Optimizer started with T1 and T2 and see the amount of volume difference.

T1 table is joined directly T2 table and is fetching huge no of records and such huge volume is not good for nested loop.

SELECT COUNT (*)

  FROM T1, T2

 WHERE     T2.CID = T1.CUN

       AND T1.P1_KEY = 61

       AND T1.P2_KEY = 107

       AND T1.GRID = 30

       AND T1.SEQ = 24844

  COUNT(*)

----------

841971446

Looking at the query and without any transformation. Execution of the query should start with joining T1 and T3. See the volume of rows reduced drastically.

SELECT COUNT (*)

  FROM T1, T3

 WHERE     T1.P1_KEY = 61

       AND T1.P2_KEY = 107

       AND T1.GRID = 30

       AND T1.SEQ = 24844

       AND T3.XN = T1.XN

       AND T3.CUN = T1.CUN

       AND T3.P1_KEY = T1.P1_KEY

       AND T3.P2_KEY = T1.P2_KEY

       AND T3.SEQ = T1.SEQ

       AND T3.IND IN ('Y', 'E')

       AND T3.SENO = T1.SENO

  COUNT(*)

----------

     28274

See the bad execution plan that query took and notice VW_JF_SET$BE5F108C and this shows that join factorization took place.

Bad Execution Plan:

----------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                    | Name                  | Rows  | Bytes | Cost  | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                             |                       |       |       |    29 |       |       |

|   1 |  NESTED LOOPS                                |                       |       |       |       |       |       |

|   2 |   NESTED LOOPS                               |                       |     1 |   282 |    29 |       |       |

|   3 |    NESTED LOOPS                              |                       |     1 |   205 |    28 |       |       |

|   4 |     VIEW                                     | VW_JF_SET$BE5F108C    |     2 |   382 |    28 |       |       |

|   5 |      UNION-ALL                               |                       |       |       |       |       |       |

|   6 |       NESTED LOOPS                           |                       |       |       |       |       |       |

|   7 |        NESTED LOOPS                          |                       |     1 |   687 |    14 |       |       |

|   8 |         NESTED LOOPS                         |                       |     1 |   114 |    13 |       |       |

|   9 |          NESTED LOOPS                        |                       |     1 |    82 |    12 |       |       |

|  10 |           NESTED LOOPS                       |                       |     3 |   162 |    11 |       |       |

|  11 |            PARTITION RANGE SINGLE            |                       |     1 |    42 |    10 |   KEY |   KEY |

|* 12 |             TABLE ACCESS BY LOCAL INDEX ROWID| T1                    |     1 |    42 |    10 |   KEY |   KEY |

|* 13 |              INDEX RANGE SCAN                | T1_3IX                |     2 |       |     2 |   KEY |   KEY |

|  14 |            TABLE ACCESS BY INDEX ROWID       | T2                    |     7 |    84 |     1 |       |       |

|* 15 |             INDEX RANGE SCAN                 | T2_3IX                |     7 |       |     1 |       |       |

|* 16 |           TABLE ACCESS BY INDEX ROWID        | T5                    |     1 |    28 |     1 |       |       |

|* 17 |            INDEX RANGE SCAN                  | T5_1IX                |     1 |       |     1 |       |       |

|  18 |          TABLE ACCESS BY INDEX ROWID         | T6                    |    10 |   320 |     1 |       |       |

|* 19 |           INDEX RANGE SCAN                   | T6_PK                 |    14 |       |     1 |       |       |

|* 20 |         INDEX RANGE SCAN                     | T7_1IX                |     1 |       |     1 |       |       |

|* 21 |        MAT_VIEW ACCESS BY INDEX ROWID        | T7                    |     1 |   573 |     1 |       |       |

|  22 |       NESTED LOOPS                           |                       |       |       |       |       |       |

|  23 |        NESTED LOOPS                          |                       |     1 |   687 |    14 |       |       |

|  24 |         NESTED LOOPS                         |                       |     1 |   114 |    13 |       |       |

|  25 |          NESTED LOOPS                        |                       |     1 |    82 |    12 |       |       |

|  26 |           NESTED LOOPS                       |                       |     3 |   162 |    11 |       |       |

|  27 |            PARTITION RANGE SINGLE            |                       |     1 |    42 |    10 |   KEY |   KEY |

|* 28 |             TABLE ACCESS BY LOCAL INDEX ROWID| T1                    |     1 |    42 |    10 |   KEY |   KEY |

|* 29 |              INDEX RANGE SCAN                | T1_3IX                |     2 |       |     2 |   KEY |   KEY |

|  30 |            TABLE ACCESS BY INDEX ROWID       | T2                    |     7 |    84 |     1 |       |       |

|* 31 |             INDEX RANGE SCAN                 | T2_3IX                |     7 |       |     1 |       |       |

|* 32 |           TABLE ACCESS BY INDEX ROWID        | T5                    |     1 |    28 |     1 |       |       |

|* 33 |            INDEX RANGE SCAN                  | T5_1IX                |     1 |       |     1 |       |       |

|  34 |          TABLE ACCESS BY INDEX ROWID         | T6                    |    10 |   320 |     1 |       |       |

|* 35 |           INDEX RANGE SCAN                   | T6_PK                 |    14 |       |     1 |       |       |

|* 36 |         INDEX RANGE SCAN                     | T7_1IX                |     1 |       |     1 |       |       |

|* 37 |        MAT_VIEW ACCESS BY INDEX ROWID        | T7                    |     1 |   573 |     1 |       |       |

|* 38 |     TABLE ACCESS BY INDEX ROWID              | T4                    |     1 |    14 |     1 |       |       |

|* 39 |      INDEX UNIQUE SCAN                       | T4_PK                 |     1 |       |     1 |       |       |

|  40 |    PARTITION RANGE ITERATOR                  |                       |    21 |       |     1 |   KEY |   KEY |

|* 41 |     INDEX RANGE SCAN                         | T3_1IX                |    21 |       |     1 |   KEY |   KEY |

|* 42 |   TABLE ACCESS BY LOCAL INDEX ROWID          | T3                    |     1 |    77 |     2 |     1 |     1 |

---------------------------------------------------------------------------------------------------------------------- 

To mitigate this i pushed to put the cost based transformation as OFF instead of linear as part of Hint. Or you can also _optimizer_join_factorization as FALSE.

With Oracle cost based optimization as off(meaning I am forcing Oracle not to transform and don’t use view join factorization).

Below is the good plan and query finished in no time.

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                       |     2 |  1606 |    14  (50)| 00:00:01 |       |       |
|   1 |  UNION-ALL                                 |                       |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                             |                       |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                            |                       |     1 |   803 |     7   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                           |                       |     1 |   230 |     6   (0)| 00:00:01 |       |       |
|   5 |      NESTED LOOPS                          |                       |     1 |   198 |     5   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                         |                       |     1 |   186 |     4   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS                        |                       |     1 |   158 |     3   (0)| 00:00:01 |       |       |
|   8 |         NESTED LOOPS                       |                       |     1 |   144 |     2   (0)| 00:00:01 |       |       |
|   9 |          PARTITION RANGE SINGLE            |                       |     1 |    41 |     1   (0)| 00:00:01 |  1174 |  1174 |
|* 10 |           TABLE ACCESS BY LOCAL INDEX ROWID| T1                    |     1 |    41 |     1   (0)| 00:00:01 |  1174 |  1174 |
|* 11 |            INDEX RANGE SCAN                | T1_3IX                |     1 |       |     1   (0)| 00:00:01 |  1174 |  1174 |
|  12 |          PARTITION RANGE SINGLE            |                       |     1 |   103 |     1   (0)| 00:00:01 |  1174 |  1174 |
|* 13 |           TABLE ACCESS BY LOCAL INDEX ROWID| T3                    |     1 |   103 |     1   (0)| 00:00:01 |  1174 |  1174 |
|* 14 |            INDEX RANGE SCAN                | T3_1IX                |     1 |       |     1   (0)| 00:00:01 |  1174 |  1174 |
|  15 |         TABLE ACCESS BY INDEX ROWID        | T4                    |     1 |    14 |     1   (0)| 00:00:01 |       |       |
|* 16 |          INDEX RANGE SCAN                  | T4_1IX                |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 17 |        TABLE ACCESS BY INDEX ROWID         | T5                    |     1 |    28 |     1   (0)| 00:00:01 |       |       |
|* 18 |         INDEX RANGE SCAN                   | T5_3IX                |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 19 |       TABLE ACCESS BY INDEX ROWID          | T2                    |     1 |    12 |     1   (0)| 00:00:01 |       |       |
|* 20 |        INDEX UNIQUE SCAN                   | T2_PK                 |     1 |       |     1   (0)| 00:00:01 |       |       |
|  21 |      TABLE ACCESS BY INDEX ROWID           | T6                    |    14 |   448 |     1   (0)| 00:00:01 |       |       |
|* 22 |       INDEX RANGE SCAN                     | T6_PK                 |    14 |       |     1   (0)| 00:00:01 |       |       |
|* 23 |     INDEX RANGE SCAN                       | T7_1IX                |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 24 |    MAT_VIEW ACCESS BY INDEX ROWID          | T7                    |     1 |   573 |     1   (0)| 00:00:01 |       |       |
|  25 |   NESTED LOOPS                             |                       |       |       |            |          |       |       |
|  26 |    NESTED LOOPS                            |                       |     1 |   803 |     7   (0)| 00:00:01 |       |       |
|  27 |     NESTED LOOPS                           |                       |     1 |   230 |     6   (0)| 00:00:01 |       |       |
|  28 |      NESTED LOOPS                          |                       |     1 |   198 |     5   (0)| 00:00:01 |       |       |
|  29 |       NESTED LOOPS                         |                       |     1 |   186 |     4   (0)| 00:00:01 |       |       |
|  30 |        NESTED LOOPS                        |                       |     1 |   158 |     3   (0)| 00:00:01 |       |       |
|  31 |         NESTED LOOPS                       |                       |     1 |   144 |     2   (0)| 00:00:01 |       |       |
|  32 |          PARTITION RANGE SINGLE            |                       |     1 |    41 |     1   (0)| 00:00:01 |  1174 |  1174 |
|* 33 |           TABLE ACCESS BY LOCAL INDEX ROWID| T1                    |     1 |    41 |     1   (0)| 00:00:01 |  1174 |  1174 |
|* 34 |            INDEX RANGE SCAN                | T1_3IX                |     1 |       |     1   (0)| 00:00:01 |  1174 |  1174 |
|  35 |          PARTITION RANGE SINGLE            |                       |     1 |   103 |     1   (0)| 00:00:01 |  1174 |  1174 |
|* 36 |           TABLE ACCESS BY LOCAL INDEX ROWID| T3                    |     1 |   103 |     1   (0)| 00:00:01 |  1174 |  1174 |
|* 37 |            INDEX RANGE SCAN                | T3_1IX                |     1 |       |     1   (0)| 00:00:01 |  1174 |  1174 |
|  38 |         TABLE ACCESS BY INDEX ROWID        | T4                    |     1 |    14 |     1   (0)| 00:00:01 |       |       |
|* 39 |          INDEX RANGE SCAN                  | T4_1IX                |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 40 |        TABLE ACCESS BY INDEX ROWID         | T5                    |     1 |    28 |     1   (0)| 00:00:01 |       |       |
|* 41 |         INDEX RANGE SCAN                   | T5_3IX                |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 42 |       TABLE ACCESS BY INDEX ROWID          | T2                    |     1 |    12 |     1   (0)| 00:00:01 |       |       |
|* 43 |        INDEX UNIQUE SCAN                   | T2_PK                 |     1 |       |     1   (0)| 00:00:01 |       |       |
|  44 |      TABLE ACCESS BY INDEX ROWID           | T6                    |    14 |   448 |     1   (0)| 00:00:01 |       |       |
|* 45 |       INDEX RANGE SCAN                     | T6_PK                 |    14 |       |     1   (0)| 00:00:01 |       |       |
|* 46 |     INDEX RANGE SCAN                       | T7_1IX                |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 47 |    MAT_VIEW ACCESS BY INDEX ROWID          | T7                    |     1 |   573 |     1   (0)| 00:00:01 |       |       |

------------------------------------------------------------------------------------------------------------------------------------

Hints I suggested:

/*+ opt_param('_optimizer_cost_based_transformation','off') LEADING(T1,T3,p,CD,T2,s,o) USE_NL(T1,T3,p,CD,T2,s,o) INDEX(T3 T3_1IX) (T2,T2_PK) */

Note: Join view factorization transformation is a good feature; but in this case due to prevailing tables stats and other factors it didn't came out well.

Beware while doing any change at session level or DB level if you don’t know about any Hidden parameter as doing so may cause issue at your end.

There are lot of documents available on the internet about cost based transformation. So please go through.

 Enjoy Learning!!!!