We had an interesting performance issue in our project yesterday. Our senior performance DBA Joe was able to fix it very quickly. I will explain what the issue was and how Joe analyzed it step by step and resolved it using Oracle plan baselines.
It all happened when we noticed one of our batch job is using too much of CPU and time, a quick OEM analysis proved that we had considerably high resource usage while the batch job was running. Most of the resource was consumed by a SQL query
The plan control told us that we have two execution plans and oracle is picking the cheaper plan.
Oracle used the plan with least cost but it was using the wrong index to scan the tables. And once he found the problems it is about devising the solutions.
He had 3 options
- Ask developers to rewrite the code and deploy it.
- Add a hint and direct oracle to use the correct index
- Use plan baseline to make oracle use the right plan
The first two options were ruled out because and code change has to follow a deployment lifecycle and might get weeks to hit production. So he immediately chose the third option.
So he just asked oracle to choose the second plan using the SQL plan baseline, and Bingo!! The query started running smoothly.
You can read more about the plan baselines below