Fixing Execution Plan by SQL Profile

After reading Kerry Osborne’s presentation Controlling Execution Plans, I decide to sum up what I learned about SQL Profile and to make some experiments of my own.

To tune a sql in case that we CAN NOT change the code, some tuning methods are available, but keep in mind they’re not specific solution comparing with sql profile:

  • gathering or updating objects’ statistics
  • adding or removing accessing paths (e.g. indexes, mv, etc.)
  • adjust init parameters (e.g. in session level)

SQL Profile will take advantage of other tuning methods in such conditions:

  • to fix a bad execution plan of one particular SQL (it is not a general systematic problem affecting a lot of others)
  • we can’t change the code ( we don’t own the code; we’re in emergency and have no time to change the code; we need to follow a company rule to make changes in production, etc.)
  • the same sql executes well in test environment but has very bad performance in production
  • the same sql executes well in the past but has very bad performance today

Description about SQL Profile by Tom Kyte:

So, a SQL profile is sort of like gathering statistics on A QUERY which involves many tables, columns and the like….

In fact – it is just like gathering statistics for a query, it stores additional information in the dictionary which the optimizer uses at optimization time to determine the correct plan. The SQL Profile is not “locking a plan in place”, but rather giving the optimizer yet more bits of information it can use to get the right plan.

There are several ways to create SQL Profiles

  • Using SQL Tuning Adviser from EM or via DBMS_SQLTUNE package
  • Attaching a SQL Profile to a different SQL (e.g. update existing execution plan and attach it to a different SQL using KO’s scripts, see demo)
  • Moving a SQL Profile from one database to another database ( using script )

Create SQL Profile by SQL Tuning Adviser

1. find sql with


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: