Oracle version: 19.3.0.0.0
Good day all,
I'd like to have your feedback about my use-case where it looks like a bad execution plan is still computed even though statistics are updated.
The test case is the following:
- at the beginning of the test there's no data in the schema
- We insert data (trades) in the system
- those data reach a ‘location’ in the application where a SELECT statement is executed. At some the ‘statistics’ a diverging too much compared to the data in the system and my SELECT statement is taking ages (~1000s) and I have a huge backlog of entries waiting to be processed
During the test, I decided to ‘pause’ the application so I can can gather the statistics on the guilty tables and flush the plan from memory to force a computation of a new plan that I would expect to be better. Unfortunately the same plan is generated and my sql are taking ages again.
Therefore I don't understand why the same plan is computed even with updated statistics. Am I misunderstanding that a better plan should be computed?
Below is what I did:
SQL ID under execution is 9b5x2yx7js94h. We can see there's on child being executed 20 times and the plan is valid
Looking at the plan we can see the cost is important, same for the estimated elapsed time
From here, I pause my application and kill all sessions executing that statement. I purge the plan and make sure it's not there anymore
Then I update the statistics for the 4 tables
From there, I resume my application and i'd expect a new plan --and better – to be computed as statistics are up-to-date.
I can see a new plan is computed (child #3) but the plan is identical
Am I missing anything to have a better plan generated, I probably misunderstand something and I'll be happy to learn from you.
Thanks
Simon