Fixing bad plan using baseline
Hi Team,
During online registrations,where multiple users login and book their registrations,we faced issue with below query with high cpu consumption. We didnt face this issue previously.
Below is the plan during the issue.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 117547mmcm5dj, child number 0
-------------------------------------
select unique(a.SEVA_DIP_FORM_ID) from SEVA_DIP_PILGRIM
b,SEVA_DIP_REGISTRATIONS a where upper(b.PILIGRIM_ID_PROOF_NUMBER) =
:1 and (a.SEVA_DATE between :2 and :3 ) and a.SEVA_DIP_FORM_ID =
b.SEVA_DIP_FORM_ID and a.seva_dip_schedule_id = :4
Plan hash value: 3282040072
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | HASH UNIQUE | | 1 | 59 | 9 (12)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS SEMI | | 1 | 59 | 8 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| SEVA_DIP_REGISTRATIONS | 1 | 29 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | INDX_DIP_SCH | 1 | | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| SEVA_DIP_PILGRIM | 2516 | 75480 | 4 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | INDX_SEVA_DIP_FORM_ID | 2 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:3)>=TO_DATE(:2))
4 - filter(("A"."SEVA_DATE">=:2 AND "A"."SEVA_DATE"<=:3))
5 0