SQL Profile

Creating and Accepting a SQL Profile

Problem

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.

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.

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

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:

set long 10000

set longchunksize 10000

set lines 132

set pages 200

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,

FORCE_MATCH=>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.

The FORCE_MATCH parameter of ACCEPT_SQL_PROFILE requires further explanation. Recall that a SQL profile is associated with a SQL statement. The SQL statement is identified via a hash function (SQL signature). The hash function is generated after converting the SQL text and removing extra white space.

When setting FORCE_MATCH to TRUE, this additionally normalizes literal values into bind values. This is similar to the algorithm generated via the FORCE option of the CURSOR_SHARING database initialization parameter.

For example, with FORCE_MATCH set to TRUE, the following two SQL statements will generate the same SQL signature:

SQL> select value from my_table where value = 'AA';

SQL> select value from my_table where value = 'bb';

This allows SQL statements that use literal values to share the same SQL profile. If there is a combination of literal values and bind variables in a SQL statement, then literal values are not normalized.

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;

/

Displaying SQL Profile Information

Use the DBA_SQL_PROFILES view to display information about SQL profiles. Here’s an example that selects the most interesting columns:

SQL> select name, type, status, sql_text from dba_sql_profiles;

Disabling a SQL Profile

First verify the name of the SQL profile that you want to disable:

SQL> select name, status from dba_sql_profiles;

Here’s a partial snippet of the output:

NAME STATUS

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

SYS_SQLPROF_012eda58a1be0001 ENABLED

Now use the DBMS_SQLTUNE.ALTER_SQL_PROFILE procedure to modify the status of the profile to disabled:

BEGIN

DBMS_SQLTUNE.ALTER_SQL_PROFILE(

name => 'SYS_SQLPROF_012eda58a1be0001',

attribute_name => 'STATUS',

value => 'DISABLED');

END;

Dropping a SQL Profile

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_012edef0d0a70002');

Moving a SQL Profile

Problem

You have a test database and want to extract all of the SQL profiles from the test database and move them to a production database.

Solution

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.

Step 1: 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;

/

Step 2: Populate the Staging Table

Use the DBMS_SQLTUNE.PACK_STGTAB_SQLPROF procedure to populate the table created in step 1 with SQL

profile information. This example populates the table with information regarding a specific SQL profile:

BEGIN

dbms_sqltune.pack_stgtab_sqlprof(

profile_name => 'SYS_SQLPROF_012edf84806e0004',

staging_table_name => 'PROF_STAGE',

staging_schema_owner => 'MV_MAINT' );

END;

/

Step 3: Copy the Staging Table to the Destination Database

You can copy the table from one database to the other via Data Pump, the old exp/imp utilities, or by using a database link. This example creates a database link in the destination database and then copies the table from the source database:

create database link source_db connect to mv_maint identified by foo using 'source_db';

Once the database link has been created, the table can be copied directly from the source with the CREATE TABLE...AS SELECT statement:

SQL> create table PROF_STAGE as select * from PROF_STAGE@source_db;

Step 4: Load the Contents of the Staging Table into the Destination Database

Now in the destination database, unpack the table to load profile information into the database:

BEGIN

DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(

replace => TRUE,

staging_table_name => 'PROF_STAGE');

END;

/

If no profile name is specified, the default is the % wildcard character (meaning all profiles in the table will be loaded into the destination database).