What is SQL Profile and how it works

SQL Profile: Conceptually a SQL profile is to a SQL statement what statistics are to a table or index.
The database can use the auxiliary information to improve execution plans.
A SQL profile contain correction for poor optimizer estimates discovered by the SQL tuning advisor.

To check all the SQL profile related history we can query dba_sql_profiles view.

To what statement a SQL profile can be applied

SELECT statements 
UPDATE statements 
INSERT statements (only with a SELECT clause) 
DELETE statements 
CREATE TABLE statements (only with the AS SELECT clause) 
MERGE statements (the update or insert operations)

Accepting a SQL profile

Use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.

DECLARE

my_sqlprofile_name VARCHAR2(30);

BEGIN

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

task_name => 'my_sql_tuning_task',

name => 'my_sql_profile');

END;

my_sql_tuning_task is the name of the SQL tuning task. You can view information about a SQL Profile in the DBA_SQL_PROFILES view.

Altering a SQL Profile

Alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of  an existing SQL Profile with the ALTER_SQL_PROFILE procedure.

BEGIN

DBMS_SQLTUNE.ALTER_SQL_PROFILE(

name => 'my_sql_profile',

attribute_name => 'STATUS',

value => 'DISABLED');

END;

/

In this example, my_sql_profile is the name of the SQL Profile that you want to alter. 

The status attribute is changed to disabled which means the SQL Profile will not be used during SQL compilation.

Dropping a SQL Profile

A SQL Profile can be dropped using the DROP_SQL_PROFILE procedure.

begin

DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');

end;

/

Example

SESSION 1 -- SCOTT

Create table, populate, create index and gather statistics
Execute query with no_index hint
Full Table Scan used

SQL> create table test (n number );

Table created.

SQL> declare

          begin

           for i in 1 .. 10000 loop

               insert into test values(i);

               commit;

           end loop;

          end;

/

PL/SQL procedure successfully completed.

SQL> create index test_idx on test(n);

Index created.

SQL> exec dbms_stats.gather_table_stats('','TEST');

PL/SQL procedure successfully completed.

set autotrace on

select /*+ no_index(test test_idx) */ * from test where n=1;

Plan hash value: 217508114

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     4 |     4   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     4   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("N"=1)

SESSION 2 -- SYS

Create and execute tuning task and run report tuning task.
Accept recommended SQL Profile

SQL> create table test (n number );

Table created.

declare

  my_task_name VARCHAR2(30);

  my_sqltext CLOB;

  begin

     my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';

     my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

     sql_text => my_sqltext,

     user_name => 'SCOTT',

     scope => 'COMPREHENSIVE',

     time_limit => 60,

     task_name => 'my_sql_tuning_task_2',

     description => 'Task to tune a query on a specified table');

end;

/

PL/SQL procedure successfully completed.

begin

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');

end;

/

PL/SQL procedure successfully completed.

set long 10000

set longchunksize 1000

set linesize 100

set heading off

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;

set heading on

Output:

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : my_sql_tuning_task_2

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 09/24/2012 12:36:44

Completed at       : 09/24/2012 12:36:49

-------------------------------------------------------------------------------

Schema Name: SCOTT

SQL ID     : d4wgpc5g0s0vu

SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1

-------------------------------------------------------------------------------

FINDINGS SECTION (1 finding)

-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

--------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

--------------------------------------------------------

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 90.95%)

  ------------------------------------------

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name =>

            'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);

  Validation results

  ------------------

  The SQL profile was tested by executing both its plan and the original plan

  and measuring their respective execution statistics. A plan may have been

  only partially executed if the other could be run to completion in less time.

Accept the profile as suggested above:

DECLARE

my_sqlprofile_name VARCHAR2(30);

begin

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

task_name => 'my_sql_tuning_task_2',

name => 'my_sql_profile');

end;

/

PL/SQL procedure successfully completed  

SESSION 1 -- SCOTT

Run query again
Even with no_index hint , index is used
Note: In Explain Plan we see "SQL profile "my_sql_profile" used for this statement

SQL> set autotrace on

SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

Execution Plan                                                              

-------------------------------------------------------------------------   

Plan hash value: 1416057887                                                 

                                                                             

-----------------------------------------------------------------------------

| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------

                                                                            

Predicate Information (identified by operation id):                         

---------------------------------------------------                         

                                                                             

   1 - access("N"=1)                                                         

---------------                                                             

Note                                                                         

-----                                                                        

   - SQL profile "my_sql_profile" used for this statement