Team,
We have a report that gives output very late. I checked the sql generated from its background log table and got the explain plan. I found that all available required indexes are being used. So, Hint is not required to force any unused index. Then I ran the sql in Prod and the execution time was around 5 mins. I ran the sql again and got the result in 30 secs. I understand here is that first time, it went for Hard parse and later soft parse. And both times, the plan and cost are same.
Obviously, when a user uses the report, he will click for first time only. So, how we can tune it so that, it will be faster all the time? We are using Oracle 19c.
Thanks