Working with SQL Tuning profiles

Run the SQL Tuning Advisor for the problem query. Keep in mind that the SQL Tuning Advisor may ormay 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.

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 * from FARHAT.FIN_CALC_TEST WHERE STUDENT_ID=106096';
tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => tune_sql
,user_name => 'FARHAT'
,scope => 'COMPREHENSIVE'
,time_limit => 60
,task_name => 'TUNE1'
,description => 'Calling SQL Tuning Advisor for one statement'
);
END;
/

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):

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

Run Tuning Advice Report

set long 10000
set longchunksize 10000
set lines 132
set pages 200

select dbms_sqltune.report_tuning_task('TUNE1') from dual;


Implement Recommendations


create index FARHAT.IDX$$_30A700001 on
    FARHAT.FIN_CALC_TEST("STUDENT_ID_NUM");
    

Run statement again and check difference

select * from FARHAT.FIN_CALC_TEST WHERE STUDENT_ID_NUM=1060964325;
 
Create and Accept SQL Profile

 execute dbms_sqltune.accept_sql_profile(
task_name  =>     'TUNE1', 
task_owner =>     'SYS', 
replace    =>     TRUE, 
profile_type =>   DBMS_SQLTUNE.PX_PROFILE);
--
end;

/


SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

Automatically Accepting SQL Profiles

Use the DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER procedure to enable the automatic
acceptance of SQL profiles recommended by the Automatic SQL Tuning task—for example:

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/

If you want to disable the automatic acceptance of SQL profiles, then do so as follows (using the
FALSE parameter):

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');
END;
/

You can report on the details of the automatic tuning task configuration via this query:

SELECT
parameter_name
,parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name
IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES',
'EXECUTION_DAYS_TO_EXPIRE');

Displaying SQL Profile Information

select name, type, status, sql_text from dba_sql_profiles;

Recall that a SQL profile contains improvements to existing statistics. The DBA_SQL_PROFILES view is the best source for viewing the SQL profile name, attributes, and associated SQL text.
To view the internal SQL profile hint-related information, you can additionally query the DBMSHSXP_SQL_PROFILE_ATTR view—for example:

SELECT
a.name
,b.comp_data
FROM dba_sql_profiles a
,dbmshsxp_sql_profile_attr b

WHERE a.name = b.profile_name;

You can also view this internal SQL profile information by querying the SQLOBJ$ and SQLOBJ$DATA views. The data in these views is in XML format, and therefore you must format the output with Oracle XML functions when querying—for example:

SELECT
extractvalue(value(a), '.') sqlprofile_hints
FROM sqlobj$ o
,sqlobj$data d
,table(xmlsequence(extract(xmltype(d.comp_data),'/outline_data/hint'))) a
WHERE o.name = '&&profile_name'
AND o. plan_id = d.plan_id
AND o.signature = d.signature

AND o.category = d.category
AND o.obj_type = d.obj_type;

Disabling a SQL Profile

select name, status from dba_sql_profiles;

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

/

Dropping a SQL Profile

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_012edef0d0a70002');

If you want to drop all profiles in a database, you can use PL/SQL to loop through all profiles and drop them:

declare
cursor c1 is select name from dba_sql_profiles;
begin
for r1 in c1 loop
dbms_sqltune.drop_sql_profile(r1.name);
end loop;
end;

/

Moving a SQL Profile

Listed next are the steps involved with transporting a SQL profile from one database to another:

1. Create a staging table.
2. Populate the staging table.
3. Move the table from the source database to the destination database (Data
Pump or database link).
4. On the destination database, extract information from the staging table to

populate the data dictionary with SQL profile information.

Create a Staging Table

Use the DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF procedure to create the staging table. This example creates a table named PROF_STAGE owned by the MV_MAINT user:

BEGIN
dbms_sqltune.create_stgtab_sqlprof(
table_name => 'PROF_STAGE',
schema_name => 'MV_MAINT' );
END;

/

Copy the Staging Table to the Destination Database

create database link source_db
connect to mv_maint
identified by foo

using 'source_db';

create table PROF_STAGE as select * from PROF_STAGE@source_db;

Load the Contents of the Staging Table into the Destination Database

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => TRUE,
staging_table_name => 'PROF_STAGE');
END;

/

Automatically Adding Plan Baselines

Listed next are the steps for automatically creating plan baselines for SQL statements that execute morethan once:

1. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE (either at the session or system level).
2. Execute two times or more the queries for which you want plan baselines captured.
3. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE.

This next example illustrates the process for adding a plan baseline (for a query) using the prior
steps. First set the specified initialization parameter at the session level:

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Now a query is executed twice. Oracle will automatically create a plan baseline for a query that is
run two or more times while the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE:

SQL> select emp_id from emp where emp_id=3000;
SQL> select emp_id from emp where emp_id=3000;

Now set the initialization parameter back to FALSE.

SQL> alter session set optimizer_capture_sql_plan_baselines=false;

The query now should have an entry in the DBA_SQL_PLAN_BASELINES view showing that it has an enabled plan baseline associated with it—for example:

SELECT
sql_handle, plan_name, enabled, accepted,
created, optimizer_cost, sql_text

FROM dba_sql_plan_baselines;

SQL> alter system set optimizer_capture_sql_plan_baselines=true;

Creating a Plan Baseline for One SQL Statement

The procedure for manually associating a plan baseline with a SQL statement is as follows:

1. Identify the SQL statement(s) for which you want plan baselines.
2. Provide an identifier such as the SQL_ID as input to the DBMS_SPM package to create a plan  baseline for the SQL statement.

For example, suppose you have a SQL statement you’ve been working with such as the following:

SQL> select emp_id from emp where emp_id = 100;

Now query the V$SQL view to determine the SQL_ID for the query:

select
sql_id
,sql_text
from v$sql
where sql_text
like 'select emp_id from emp where emp_id = 100';
Here is a snippet of the output:
SQL_ID SQL_TEXT
------------- ------------------------------------------------------------
0qgmjf9krq285 select emp_id from emp where emp_id = 100

Now that the SQL_ID has been identified, use it as input to the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function to create a plan baseline for the given query—for example:

DECLARE
plan1 PLS_INTEGER;
BEGIN
plan1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '0qgmjf9krq285');
END;
/

The query now should have an entry in the DBA_SQL_PLAN_BASELINES view showing that it has an enabled plan baseline associated with it—for example:


SQL> select sql_handle, plan_name, sql_text from dba_sql_plan_baselines;