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.
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.
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:
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.
