Hi
version 11.2.0.3
We have a sql which some days is returning in approx 30 mins but other days taking several times longer - database workload same and volumes comparable.
This sql uses 2 parameters in format &prm1 @prm2 as called via script on one of parameters is name and other the a date
e.g . 'JOE BLOGGS' '15-FEB-2021' passed in when run son 15th feb
'JOE BLOGGS' '16-FEB-2021' when passed in when run for 16th feb
The dba has advised they can't pin a good execution plan as the sqlid is changing.
I assume this is due to different sql executing each day.
Is there a way we can ensure sqlid same each time runs such taht on day runs with good eexcution plan he coudl caprture that and pin that.
e.g if used 'JOE BLOGGS' and trunc(sysdate) hardcoded in sql rather than them passing in 'JOE BLOGGS' and '15-FEB-2021' via script woudl that keep sql eame each fday ran.
at runtime it would translate trunc(sysdate) to real date as would run every day but woudl that keep the sqlid consitent.
Woudl bind variables keep sqlid same each time runs.?
Basically trying to ensure sqlid is same and is it the change in sql which causes this to change.
How can this best be achieved?
Thanks