DBMS_SQLTUNE Package in ORACLE to Perform Automatic SQL Tuning & SQL Profiles, SQL Tuning sets

Purpose:  Sql tuning advisor helps to tune SQL statements

Tuning of SQL Statements using DBMS_SQLTUNE involves following 4 steps:

1) Creating Tuning Task
2) Executing Tuning Task
3) Displaying results of tuning task
4) Implementing Recommendations

In Tuning mode optimizer performs the following analysis:

Statistics Analysis: The optimizer recommends the gathering of the statistics on object with missing or stale statistics.

The additional statistics for those objects are stored in an SQL profile.

Sql Profile: The optimizer may be able to improve performance by gathering additional statistics and altering session specific parameters such as the OPTIMIZER_MODE. If such improvements are possible the information is stored in an SQL profile

Access Path Analysis - The optimizer investigates the effect of new or modified indexes on the access path. Its index recommendations relate to a specific statement so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.

SQL Structure Analysis - The optimizer suggests alternatives for SQL statements that contain structures that may impact on performance. The implementation of these suggestions requires human intervention to check their validity.

The statements to be analyzed can be retrieved from the Automatic Workload Repository (AWR), the cursor cache, an SQL tuning set or specified manually.

Before starting with sql tuning, let me delete statistics of table EMP. This is to observe the execution of dbms sqltune.

exec dbms_stats.delete_table_stats (user, 'EMP')

The above statement will delete the statistics from EMP table.

Tuning Task can be created for the SQL statements retrieved from any of the following:

1.    Sql retrieved from AWR (Automatic Workload Repository)

2.    Sql retrieved from cursor cache

3.    Sql retrieved from SQL Sets

4.    Sql statements prepared manually

Step1: Creation of Tuning Task

declare

    v_sql varchar2(300);

    v_tuning_task varchar2(200);

begin

    v_sql := 'select e.* from emp e join dept d on (e.deptno = d.deptno ) where nvl(e.empno,0) = :emp_no ';

    v_tuning_task := dbms_sqltune.create_tuning_task ( sql_text => v_sql,

                                                                       bind_list => sql_binds anydata.convertnumber(100)),

                                                                                user_name => 'OMSOWN',

                                                            scope   =>  dbms_sqltune.scope_comprehensive,

                                                                                time_limit => 60,

                                                                                task_name   => 'my_fist_task_on_emp',

                                                                                description => 'to tune emp dept stmt ' );

dbms_output.put_line ( 'v_tuning_task ->' || v_tuning_task );

end;

After creation of the tuning task query the table dba_advisor_log to see the status.

select *

from dba_advisor_log

where task_name = 'my_fist_task_on_emp'

Note:

·         If task_name is not passed then System will generated returned like TASK_1478

·         If the SCOPE parameter is set to scope_limited then sql profiling analysis is omitted.

·         Time_limit optimizer simply restricts the time the optimizer can spend on compiling the recommendations.

  

Step2: Executing Tuning Task

exec dbms_sqltune.execute_tuning_task ( task_name => 'my_fist_task_on_emp');

Step3: Reporting Tuning Task Results

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('my_fist_task_on_emp') AS recommendations FROM dual;

SET PAGESIZE 24

Step4: We have the results on had now. Let’s proceed with implementation of these results to improve the performance of SQL.

Use the SCRIPT_TUNING_TASK Function to create a SQL*PLUS script which can then be executed to implement a set of Advisor recommendations.

Script_tuning_task function returns clob.

SET LINESIZE 140

-- Get a script for all actions recommended by the task.

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task) FROM DUAL;

-- Get a script of just the sql profiles we should create.

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'PROFILES') FROM DUAL;

-- get a script of just stale / missing stats

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'STATISTICS') FROM DUAL;

-- Get a script with recommendations about just one SQL statement when we have

-- tuned an entire STS.

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:sts_task, 'ALL', 5) FROM DUAL;

Additional Info:

To interrupt and resume tuning task:

exec dbms_sqltune.interrupt_tuning_task (task_name =>'my_fist_task_on_emp');

exec dbms_sqltune.resume_tuning_task (task_name => 'my_fist_task_on_emp' );

To cancel tuning task:

exec dbms_sqltune.cancel_tuning_task (task_name => 'my_fist_task_on_emp' );

Reset a tuning task allowing it to be re-executed:

exec dbms_sqltune.reset_tuning_task ( task_name => 'my_fist_task_on_emp');

To drop tuning task:

exec dbms_sqltune.drop_tuning_task (task_name => 'my_fist_task_on_emp')

plsql block to create tuning  task for sql, display results:

declare

   v_tune_result      clob;

   v_sql              varchar2 (300) := 'select * from emp join dept on (emp.deptno = dept.deptno)';

   v_tuning_task_id   varchar2 (200);

begin

   v_tuning_task_id := dbms_sqltune.create_tuning_task (sql_text => v_sql);

   dbms_sqltune.execute_tuning_task (v_tuning_task_id);

   dbms_output.put_line ('Tuning Task Execution Completed-' || v_tuning_task_id);

   v_tune_result := dbms_sqltune.report_tuning_task (v_tuning_task_id);

   dbms_output.put_line ('Result Set :' || v_tune_result);

   dbms_sqltune.drop_tuning_task (v_tuning_task_id);

   dbms_output.put_line ('Tuning Task dropped');

exception

   when others    then

      dbms_output.put_line (dbms_utility.format_error_stack || ' ----- ' || dbms_utility.format_error_backtrace);

end;

SQL Profiles:

The SQL Tuning Advisor may recommend the creation of a SQL Profile to improve the performance of a statement. SQL Profiles consist of auxiliary statistics specific to the statement.

SQL Tuning Advisor can recommend a profile the following types of statements:

·         DML statements (SELECT, INSERT with a SELECT clause, UPDATE, and DELETE)

·         CREATE TABLE statements (only with the AS SELECT clause)

·         MERGE statements (the update or insert operations)

SQL Plan base lines are to preserve the performance of corresponding SQL statements, regardless of the changes in database.

Sql profiles were supposed to guide the optimizer to a better plan. They do not guarantee the same plan each time the statement is parsed.

Migrate SQL profiles in 10GR2 and beyond:

Step 1: create_stgtab_sqlprof = Create a SQL Profile storage table

Step 2: pack_stgtab_sqlprof = Populate the storage table with SQL Profiles

Step 3: expdp/impdb = Next, you migrate the table anywhere you like, using standard Oracle utilities or CTAS over a database link.

Step 4: unpack_stgtab_sqlset = Unpacks the SQL profiles on the destination database

How to accept the profiles recommended by Tuning Advisor:

SET SERVEROUTPUT ON

DECLARE

  l_sql_tune_task_id  VARCHAR2(20);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (

                          task_name => 'emp_dept_tuning_task',

                          name      => 'emp_dept_profile');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

How to alter the name, description, category, status of an SQL Profile:

BEGIN

  DBMS_SQLTUNE.alter_sql_profile (

    name            => 'emp_dept_profile',

    attribute_name  => 'STATUS',

    value           => 'DISABLED');

END;

How to Drop the SQL Profile:

BEGIN

  DBMS_SQLTUNE.drop_sql_profile (

    name   => 'emp_dept_profile',

    ignore => TRUE);

END;

DBMS_SQLTUNE.IMPORT_SQL_PROFILE:

To change the execution plan use import_sql_profile

DBA_SQL_PROFILES is the table in which Sql Profiles stores.

Note: dba_profiles  is the table which stores resource information like follows. No relation between dba_profiles and dba_sql_profiles tables.

select distinct resource_name  from dba_profiles;

SESSIONS_PER_USER

LOGICAL_READS_PER_SESSION

PASSWORD_GRACE_TIME

CPU_PER_CALL

CONNECT_TIME

PASSWORD_REUSE_MAX

COMPOSITE_LIMIT

CPU_PER_SESSION

PASSWORD_VERIFY_FUNCTION

PRIVATE_SGA

PASSWORD_LIFE_TIME

LOGICAL_READS_PER_CALL

IDLE_TIME

PASSWORD_REUSE_TIME

FAILED_LOGIN_ATTEMPTS

PASSWORD_LOCK_TIME

SQL Tuning Sets:

To tune more than one sql statement, sql tuning set is required to create.

Create SQL Tuning Set:

BEGIN

  DBMS_SQLTUNE.create_sqlset (

    sqlset_name  => 'test_sql_tuning_set',

    description  => 'A test SQL tuning set.');

END;

select * from

dba_sqlset

where name = 'test_sql_tuning_set'

Statements are added to the set using the LOAD_SQLSET procedure which accepts a REF CURSOR of statements retrieved using one of the following pipelined functions:

• SELECT_WORKLOAD_REPOSITORY - Retrieves statements from the Automatic Workload Repository (AWR).
• SELECT_CURSOR_CACHE - Retrieves statements from the cursor cache.
• SELECT_SQLSET - Retrieves statements from another SQL tuning set.

Let see how we can tune SQL statements of SQL set.

Step1: create table MAH_OBJECTS as select * from all_objects

            exec dbms_stats.gather_table_stats (user, 'MAH_OBJECTS')

Step2:  Execute the following statements. This will help to keep the SQL statements in shared pool

SELECT COUNT(*) FROM MAH_OBJECTS WHERE object_id <= 100;

SELECT object_name FROM MAH_OBJECTS WHERE object_id = 100;

SELECT COUNT(*) FROM MAH_OBJECTS WHERE object_id <= 1000;

SELECT object_name FROM MAH_OBJECTS WHERE object_id = 1000;

SELECT COUNT(*) FROM MAH_OBJECTS WHERE object_id BETWEEN 100 AND 1000;

Step3: Create SQL Tuning Set. The below statement execution makes an entry into table dba_sqlset.

BEGIN

  DBMS_SQLTUNE.create_sqlset (

    sqlset_name  => 'test_sql_tuning_set',

    description  => 'A test SQL tuning set.');

END;

OR

EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'test_sql_tuning_set');

Step4: Adding statements to SQLSET.

Statements are added to the set using the LOAD_SQLSET procedure which accepts a REF CURSOR of statements retrieved using one of the following pipelined functions:

• SELECT_WORKLOAD_REPOSITORY - Retrieves statements from the Automatic Workload Repository (AWR).
• SELECT_CURSOR_CACHE - Retrieves statements from the cursor cache.
• SELECT_SQLSET - Retrieves statements from another SQL tuning set.

DECLARE

   l_cursor   DBMS_SQLTUNE.sqlset_cursor;

BEGIN

   OPEN l_cursor FOR

SELECT VALUE(p)

    FROM   TABLE (DBMS_SQLTUNE.select_cursor_cache (

                     q'[sql_text LIKE '%mah_objects%']' , -- basic_filter

                    NULL, -- object_filter

                    NULL, -- ranking_measure1

                    NULL, -- ranking_measure2

                    NULL, -- ranking_measure3

                    NULL, -- result_percentage

                    10)    -- result_limit

                  ) p;

   DBMS_SQLTUNE.load_sqlset (sqlset_name       => 'test_sql_tuning_set',

                populate_cursor   => l_cursor);

END;

select * from

DBA_SQLSET_STATEMENTS

where sqlset_name = 'test_sql_tuning_set'

This sql gives the SQL statements added to SQL SET.

The SQL_TEXT column above is CLOB column and it contains SQL statement.

Note: The SQL Set contains four statements in it.

Step5: Tuning Task creation for SQL Set

declare

    v_tuning_task varchar2(100);

begin

-- Define/Create a tuning task for Sql set

    v_tuning_task := dbms_sqltune.create_tuning_task (sqlset_name => 'test_sql_tuning_set');

    dbms_output.put_line ('v_tuning_task is ->' || v_tuning_task );

-- Execute Tuning Task for SQL Set now

    dbms_sqltune.execute_tuning_task (task_name => v_tuning_task );

exception

    when others then

        dbms_output.put_line ( dbms_utility.format_error_stack || dbms_utility.format_error_backtrace );

end;

output: v_tuning_task is ->TASK_34655

Now the tuning task is create for SQL Tuning set and execution of tuning task is completed.

Step6: Review the results of Tuning Task

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('TASK_34655') AS recommendations FROM dual;

SET PAGESIZE 24

If we examine the log , Tuning advisor suggests a SQL Profile for better performance to one of the SQL in the tuning set.

Step7: Congratulations…! Tuning of SQL Tuning set completed.

How to add references to SQL set?

References help to identify the use of sql set by a client

DECLARE

  l_ref_id  NUMBER;

BEGIN

  -- Add a reference to a set.

  l_ref_id := DBMS_SQLTUNE.add_sqlset_reference (

    sqlset_name => 'test_sql_tuning_set',

    reference   => 'Used for manual tuning by SQL*Plus.');

  -- Delete the reference.

  DBMS_SQLTUNE.remove_sqlset_reference (

    sqlset_name  => 'test_sql_tuning_set',

    reference_id => l_ref_id);

END;

How to update Sql set?

BEGIN

  DBMS_SQLTUNE.update_sqlset (

    sqlset_name     => 'test_sql_tuning_set',

    sql_id          => '19v5guvsgcd1v',

    attribute_name  => 'ACTION',

    attribute_value => 'INSERT');

END;

How to delete sql set?

BEGIN

  -- Delete statements with less than 50 executions.

  DBMS_SQLTUNE.delete_sqlset (

    sqlset_name  => 'test_sql_tuning_set',

    basic_filter => 'executions < 50');

  -- Delete all statements.

  DBMS_SQLTUNE.delete_sqlset (

    sqlset_name  => 'test_sql_tuning_set');

END;

How to drop sql set?

BEGIN

  DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'test_sql_tuning_set');

  DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'test_sql_tuning_set_2');

END;

SQL Tuning Set Data Dictionary:

·         DBA_SQLSET

·         DBA_SQLSET_BINDS

·         DBA_SQLSET_PLANS

·         DBA_SQLSET_REFERENCES

·         DBA_SQLSET_STATEMENTS

·         ALL_SQLSET

·         ALL_SQLSET_BINDS

·         ALL_SQLSET_PLANS

·         ALL_SQLSET_REFERENCES

·         ALL_SQLSET_STATEMENTS

·         USER_SQLSET

·         USER_SQLSET_BINDS

·         USER_SQLSET_PLANS

·         USER_SQLSET_REFERENCES

·         USER_SQLSET_STATEMENTS

·         DBA_SQLSET_DEFINITIONS

·         USER_SQLSET_DEFINITIONS

Useful views related to automatic SQL tuning include:

• DBA_ADVISOR_TASKS
• DBA_ADVISOR_FINDINGS
• DBA_ADVISOR_RECOMMENDATIONS
• DBA_ADVISOR_RATIONALE
• DBA_SQLTUNE_STATISTICS
• DBA_SQLTUNE_BINDS
• DBA_SQLTUNE_PLANS
• DBA_SQLSET
• DBA_SQLSET_BINDS
• DBA_SQLSET_STATEMENTS
• DBA_SQLSET_REFERENCES
• DBA_SQL_PROFILES
• V$SQL
• V$SQLAREA
• V$ACTIVE_SESSION_HISTORY