How to identify what caused the change in join order?
A couple of SQL statements which executed perfectly last Friday has taken more time today ( two folds increase in elapsed time). When I checked details - SQL_PLAN_HASH_VALUE is different - And, the difference is due to join order(refer attached screenshot).
Question:
1- How to identify what caused the change in join order?
2- And, how to fix it?
Plan hash value: 1745054540 - Last week - Good Plan || Elapsed time is less
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9292 (100)| | | |
| 1 | HASH GROUP BY | | 1 | 463 | 9292 (1)| 00:01:52 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 463 | 9286 (1)| 00:01:52 | | |
| 3 | NESTED LOOPS | | 1 | 443 | 9281 (1)| 00:01:52 | | |
| 4 | NESTED LOOPS | | 1 | 429 | 9280 (1)| 00:01:52 | | |
| 5 | NESTED LOOPS | | 1 | 409 | 9279 (1)| 00:01:52 | | |