You might have heard of SQL profile and SQL baselines in performance tuning chapters. Lets today go through details about sql profile.

What is SQL profile:

SQL profile is a collection of additional statistical information stored in the data dictionary that helps the optimizer to generate the best plan for the query. SQL profile is managed through SQL_TUNE package of SQL tuning advisor. i.e when we run SQL tuning advisor against a query, The tuning optimizer creates a profile for an SQL statement called SQL profile which consists additional statistical information about that statement, and it gives the recommendation to accept that profile.

NOTE – It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change.

SQL PROFILE can be applied to below statements.

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)

EXAMPLE:

In this example, first we will run SQL tuning advisor against an sql_id , and then will do different operations on sql profile.

1. Run sql tuning advisor for sql_id=5dkrnbx1z8gcb

set long 1000000000
Col recommendations for a200
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '5dkrnbx1z8gcb',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => '5dkrnbx1z8gcb_tuning_task_1',
                          description => 'Tuning task for statement 5dkrnbx1z8gcb');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5dkrnbx1z8gcb_tuning_task_1');


SET LONG 10000000;
SET PAGESIZE 100000000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('5dkrnbx1z8gcb_tuning_task_1') AS recommendations FROM dual;
SET PAGESIZE 24

2. Go through the profile recommendation part of the report:

DBMS_SQLTUNE.report_tuning_task will generate the completed output of the advisory. If you go through the profile recommendation part, it will be as below.

  Recommendation (estimated benefit: 96.57%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
 
    execute dbms_sqltune.accept_sql_profile(task_name => '5dkrnbx1z8gcb_tuning_task_1', task_owner => 'SYS', replace =>TRUE);

2. Accept profile:

    execute dbms_sqltune.accept_sql_profile(task_name => '5dkrnbx1z8gcb_tuning_task_1', task_owner => 'SYS', replace =>TRUE);

3. Find the name of the sql_profile:

in the accept_sql_profile package we didn’t specify a sql_profile name, So we need to find the sql_profile from the task_name.
Use the below query.

select name,status,force_matching from dba_sql_profiles  where task_id in ( select task_id from DBA_ADVISOR_TASKS where task_name ='5dkrnbx1z8gcb_tuning_task_1')

set pagesize 299
set lines 299
col name for a45
NAME                               STATUS   FOR
---------------------------------- -------- ---
SYS_SQLPROF_01601b64332e0000       ENABLED  NO

4. Alter a profile,

You can disable a sql_profile using steps.

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_01601b64332e0000', 
attribute_name => 'STATUS', 
value => 'DISABLED');
END;
/

To enable to changed the value from DISABLED to ENABLED.

5. Dropping an SQL profile:

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_01601b64332e0000');
end;
/