General question in execution plan
Dear Experts,
We are on 12cr1 database version. We have an issue last week and during our monitoring,we have seen multiple occurrences of same sql id.We are in pressure and when we check the execution plan
Plan hash value: 3986924973 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT ORDER BY | | 1 | 307 | 2 (50)| 00:00:01 | | 2 | FILTER | | | | | | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| SEVA_DIP_REGISTRATIONS | 1 | 307 | 1 (0)| 00:00:01 | | 4 | INDEX RANGE SCAN | INDX_DIP_SCH | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------
We ran gather stats on table SEVA_DIP_REGISTRATIONS and flushed the share pool for this specific sql id.After that the plan changed as below
Plan hash value: 2076832513
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | SORT ORDER BY | | 2 | 448 | 10 (10)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| SEVA_DIP_REGISTRATIONS | 2 | 448 | 9 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | INDX_USER_ID | 189 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:3>=:2)
3 - filter(("SEVADIPREG0_"."CHANNEL_TYPE_ID"=:1 AND "SEVADIPREG0_"."SEVA_DATE">=:2 AND
"SEVADIPREG0_"."SEVA_DATE"<=:3 AND "SEVADIPREG0_"."SEVA_DIP_SCHEDULE_ID"=:5))
4 - access("SEVADIPREG0_"."USER_ID"=:4)
0