How oracle plan baselines saved our day!!

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

pic1

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

  1. Ask developers to rewrite the code and deploy it.
  2. Add a hint and direct oracle to use the correct index
  3. 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.

pic2

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

http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#BABEAFGG

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s