SQL Profile

Abstract
A SQL statement’s execution plan can change. The lack of a guarantee that a changed plan will always better lead some customers to several ways to manage this issue:

• lock the optimizer statistics
• lock the execution plans, using stored outlines

Starting with Oracle 10g, we have several options to respond to plan changing. One of these is SQL Profile, or more correctly SQL Tuning Advisor.



Introduction
From manual’s page:

SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack. It is a mechanism for resolving problems related to suboptimally performing SQL statements. Use SQL Tuning Advisor to obtain recommendations for improving performance. Tuning recommendations include also the creation of SQL profiles.

Here, I want to explain how to generate a SQL Profile both using and not using SQL Tuning Advisor.

There are 5 scenarios on which we can work

1. The statement is never been run. In this case, we obviously know the sql text.
2. The statement is in SQL Area but have a wrong execution plan
3. The statement is in AWR repository 
4. The statement is in SQL Area and it have two or more execution plan 
5. The statement is neither in SQL Area nor in AWR 

With the first three, we use the SQL Tuning Advisor. With the last two, we create manually the SQL Profile.

I think that those scenarios cover all possible cases. In the next posts, I'll discuss all 5 cases.



Create SQL Profile using SQL Tuning Advisor (cases 1-3)
You can interact with SQL Tuning Advisor through DBMS_SQLTUNE package.

When you invoice SQL Tuning Advisor you have to follow 4 steps:
(a) Create a tuning task: This step create a task
(b) Execute the tuning task: This step analyze the issue and provide recommendations
(c) Report the tuning task: This step show the recommendations
(d) Accept the SQL Profile: This step accept the recommendations



Create SQL Profile without using SQL Tuning Advisor (cases 4-5) 
With respect to the previous case, when you run SQL Tuning Advisor manually, you need only the step (d). This is because in this case you already know all information (HINTs) in order to create a SQL Profile.


SQL Profile: The statement is never been run Part 1/Part 2
SQL Profile: The statement is in SQL Area but have a wrong execution plan (under construction)
SQL Profile: The statement is in AWR repository (under construction)
SQL Profile: The statement is in SQL Area and it have two or more execution plan (under construction)
SQL Profile: The statement is neither in SQL Area nor in AWR (under construction)