What is SQL Profile, How to create and accept it and how it works in Oracle

SQL profiles are optionally generated corrections and improvements to statistics. The recommendation (and code) to implement a SQL profile is manifested through the output of the SQL Tuning Advisor. You can manually enable SQL profiles or configure them to be automatically accepted. SQL profiles help the optimizer derive better execution plans.

SQL Profiles store statement statistics in data dictionary in the form of hints as key inputs to the optimizer.

Creating and Accepting a SQL Profile

You have a poorly performing query, and you want to get advice from the SQL Tuning Advisor. You realize that the SQL Tuning Advisor may recommend that a SQL profile be applied to the problem query as part of the tuning recommendation.

So here is the solution, run the SQL Tuning Advisor for the problem query. Keep in mind that the SQL Tuning Advisor may or may not recommend a SQL profile as a solution for performance issues. To run the SQL Tuning Advisor manually, perform the following steps:

1. Use DBMS_SQLTUNE to create a tuning task.
2. Execute the tuning task.
3. Generate the tuning advice report.
4. If SQL profile is part of the tuning advice output, then create and accept.

The following example follows the prior steps. In this scenario, the SQL Tuning Advisor
recommends that a SQL profile be applied to the given query.

Step 1: Use DBMS_SQLTUNE to Create a Tuning Task

The first step is to create a tuning task that is associated with the problem SQL statement. In the following code, the SQL text is hard-coded as input to the tune_sql variable:

DECLARE
tune_sql CLOB;
tune_task VARCHAR2(30);
BEGIN
tune_sql := 'select count(*) from mgmt_db_feature_usage_ecm2';
tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => tune_sql
 ,user_name => 'STAGING'
,scope => 'COMPREHENSIVE'
,time_limit => 60
,task_name => 'TUNE1'
,description => 'Calling SQL Tuning Advisor for one statement'
);
END;
/

The prior code is placed in a file named sqltune.sql, and executed as follows:

SQL> @sqltune.sql

If you need to later drop the tuning task, you can use the DBMS_SQLTUNE.DROP_TUNING_TASK procedure. Obviously don’t drop the tuning task at this point because you’ll need it for the next several steps.

Step 2: Execute the Tuning Task

This step runs the SQL Tuning Advisor to generate advice regarding any queries associated with the tuning task (created in step 1):

SQL> exec dbms_sqltune.execute_tuning_task(task_name=>'TUNE1');

Step 3: Run Tuning Advice Report

Now use DBMS_SQLTUNE to extract any tuning advice generated in step 2:

sql>set long 10000
sql>set longchunksize 10000
sql>set lines 132
sql>set pages 200
sql>select dbms_sqltune.report_tuning_task('TUNE1') from dual;

For this example, the SQL Tuning Advisor recommends creating a SQL profile. Here is a snippet from the output that contains the recommendation and the code required to create the SQL profile:

Recommendation (estimated benefit: 86.11%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution for this statement.

execute dbms_sqltune.accept_sql_profile(task_name => 'TUNE1', task_owner
=> 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
-------------------------------------------
Executing this query parallel with DOP 8 will improve its response time
86.11% over the original plan. However, there is some cost in enabling
parallel execution...

Step 4: Create and Accept SQL Profile

To actually create the SQL profile, you need to run the code recommended by the SQL Tuning Advisor (from step 3)—for example:

begin
-- This is the code from the SQL Tuning Advisor
dbms_sqltune.accept_sql_profile(
task_name => 'TUNE1',
task_owner => 'SYS',
replace => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
--
end;
/

When the prior code is run, it creates and enables the SQL profile. Now whenever the associated SQL query is executed, the SQL profile will be considered by the optimizer when formulating an execution plan.

How It Works

The only Oracle-supported method for creating a SQL profile is to run the SQL Tuning Advisor and if recommended, create a SQL profile using the Tuning Advisor’s output. In other words, the SQL Tuning Advisor determines if a SQL profile will help, and if so generates the code required to create a SQL profile for a given query.

The “Solution” section detailed how to manually run the SQL Tuning Advisor. Keep in mind that as of Oracle Database 11g, this tuning task job automatically runs on a regularly scheduled basis.

You can easily review the output of the automatic tuning job via this query:

SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

We recommend that you review the output of the automatic tuning job on a regular basis. The SQL Tuning Advisor will provide the code to create and accept SQL profiles as part of the output.