OracleAppsDBAKK1: Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.

PURPOSE

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

SQL Profiles was a feature introduced in 10g and managed through the DBMS_SQLTUNE package or through Oracle Enterprise Manager as part of the Automatic SQL Tuning process.

This FAQ answers basic questions on using SQL Profiles and provides an example using DBMS_SQLTUNE package.

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for SQL Performance.

QUESTIONS AND ANSWERS

What is Automatic SQL Tuning?

The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information,leading to poor execution plans. Traditionally, users have had to correct this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.

Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.

During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.

The output of this type of analysis is a recommendation to accept the SQL Profile.
An SQL Profile, once accepted, is stored persistently in the data dictionary. An SQL Profile is specific to a particular query. If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan. The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code.

The following documentation provides more information about the SQL Tuning Advisor:

Oracle® Database 2 Day DBA
12c Release 1 (12.1)
E17643-13
10 Monitoring and Tuning the Database

About the SQL Tuning Advisor

What is a SQL Profile?

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.

The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following sources of information when choosing plans:

The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on
The supplemental statistics in the SQL profile

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. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.

How can the scope of the SQL Profile be controlled?

The scope of a SQL Profile can be controlled by the CATEGORY profile attribute. This attribute determines which user sessions can apply the profile. You can view the CATEGORY attribute for a SQL Profile in CATEGORY column of the DBA_SQL_PROFILES view.

select category,name from dba_sql_profiles;

By default, all profiles are created in the DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.

By altering the category of a SQL profile, you can determine which sessions are affected by the creation of a profile. For example, by setting the category of a SQL Profile to DEV, only those users sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. All other sessions do not have access to the SQL Profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a SQL Profile in a restricted environment before making it available to other user sessions.

To what statements can a SQL Profile 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)

How can SQL Profiles be managed?

SQL Profiles can be handled by Oracle Enterprise Manager as part of the Automatic SQL Tuning process or manually by using DBMS_SQLTUNE package.

Using Enterprise Manager

On the Performance page, click Top Activity.
The Top Activity page appears.
Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.
The SQL Details page appears.
Click the Plan Control tab.
A list of SQL profiles is displayed under SQL Profiles and Outlines.
Select the SQL profile you want to manage.
Do one of the following:
- To enable a SQL profile that is disabled, click Disable/Enable.
- To disable a SQL profile that is enabled, click Disable/Enable.
- To remove a SQL profile, click Delete.
A confirmation page appears.
Click Yes to continue, or No to cancel the action.

Using DBMS_SQLTUNE package.

To use the SQL Profiles APIs, you need the CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY SQL_PROFILE system privileges.

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 -- <Username>

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

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 => '<User Name>',
     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.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .001004           .000331      67.03 %
  CPU Time (s):                    .001                 0        100 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       22                 2       90.9 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
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)

2- Using SQL Profile
--------------------
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)

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

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 -- <User Name>

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

How do I produce a report of every tuning set?

SELECT 'SELECT d.id , d.owner , d.description , d.created , d.last_modified , d.statement_count, ss.*
FROM TABLE(DBMS_SQLTUNE.select_sqlset ('''||name||''')) ss, dba_sqlset d WHERE d.name='''||name||''';'
FROM dba_sqlset d
ORDER BY d.last_modified DESC

Discuss SQL Profiles

The window below is a live discussion of this article (not a screenshot). We encourage you to join the discussion by clicking the "Reply" link below for the entry you would like to provide feedback on. If you have questions or implementation issues with the information in the article above, please share that below.

REFERENCES

NOTE:262687.1 - Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor
NOTE:1253696.1 - How To Use SQL Profiles for Queries Using Different Literals Using the Force_Match Parameter of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
NOTE:457531.1 - How to Move SQL Profiles from One Database to Another (Including to Higher Versions)

OracleAppsDBAKK1

Friday, June 11, 2021

Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1) | SQL Tuning