Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)

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.  注意：在下面的图像和/或文档内容中，使用的用户信息和数据表示来自Oracle示例产品或Oracle数据库产品随附的公共文档中的虚拟数据。如有雷同，纯属巧合。

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. 
SQL概要文件是10g中引入的一项功能，作为自动SQL调整过程的一部分，可以通过DBMS_SQLTUNE软件包或Oracle Enterprise Manager进行管理。
This FAQ answers basic questions on using SQL Profiles and provides an example using DBMS_SQLTUNE package.  该常见问题解答回答了有关使用SQL配置文件的基本问题，并提供了使用DBMS_SQLTUNE包的示例。

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?   什么是自动SQL调整？

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.
由于缺少信息，查询优化器有时可能会生成关于语句属性的不正确估计，从而导致执行计划不佳。传统上，用户必须通过向应用程序代码中手动添加提示(Hint)来纠正此问题，以指导优化器做出正确的决定。对于打包的应用程序，不能选择更改应用程序代码，唯一的选择是向应用程序供应商记录错误并等待修复。
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.
Automatic SQL Tuning通过其SQL性能分析功能来解决此问题。自动调整优化器会创建SQL语句的配置文件，称为SQL Profile，其中包含特定于该语句的辅助统计信息。正常模式下的查询优化器对基数，选择性和成本进行估算，这些估算有时可能相差很大，从而导致执行计划不佳。SQL Profile通过使用采样和部分执行技术收集更多信息来验证并在必要时调整这些估计值，从而解决了此问题。
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.
在进行SQL分析时，Automatic Tuning优化器还会使用SQL语句的执行历史记录信息来适当地设置优化器参数设置，例如将该SQL语句的OPTIMIZER_MODE初始化参数设置从ALL_ROWS更改为FIRST_ROWS。
The output of this type of analysis is a recommendation to accept the SQL Profile.   建议您接受这种SQL分析配置文件。
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. SQL Profile一经接受，便会永久存储在数据字典中。SQL配置文件特定于特定查询。如果接受，则正常模式下的优化器在生成执行计划时将使用SQL Profile中的信息以及常规数据库统计信息。附加信息的可用性使得可以为相应的SQL语句生成经过调整的计划，而无需更改应用程序代码。

The following documentation provides more information about the SQL Tuning Advisor:  以下文档提供了有关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？

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.
SQL配置文件是存储在数据字典中的信息的集合，使查询优化器可以为SQL语句创建最佳执行计划。SQL配置文件包含对自动SQL调整期间发现的优化器估计不正确的更正。该信息可以改善优化程序的基数和选择性估计，从而使优化程序选择更好的计划。
The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following sources of information when choosing plans:  SQL配置文件不包含有关单个执行计划的信息。相反，选择计划时，优化器具有以下信息来源：

• The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on   该环境包含数据库配置，绑定变量值，优化器统计信息，数据集等
• The supplemental statistics in the SQL profile    SQL配置文件中的补充统计信息

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.   重要的是要注意，SQL Profile不会像存储的大纲那样冻结SQL语句的执行计划。随着表的增长或索引的创建或删除，执行计划可以使用相同的SQL Profile进行更改。即使相应语句的数据分配或访问路径发生更改，存储在其中的信息仍将保持相关性。但是，在很长一段时间内，其内容可能会过时，必须重新生成。这可以通过在同一条语句上再次运行自动SQL调整来重新生成SQL配置文件来完成。

How can the scope of the SQL Profile be controlled?   如何控制SQL Profile的范围？

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.  SQL配置文件的范围可以由CATEGORY配置文件属性控制。此属性确定哪些用户会话可以应用配置文件。您可以在DBA_SQL_PROFILES视图的CATEGORY列中查看SQL Profile的CATEGORY属性。

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.
默认情况下，所有配置文件都在DEFAULT类别中创建。这意味着将SQLTUNE_CATEGORY初始化参数设置为DEFAULT的所有用户会话都可以使用配置文件。
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.   通过更改SQL配置文件的类别，可以确定哪些会话受配置文件的创建影响。例如，通过将SQL配置文件的类别设置为DEV，只有将SQLTUNE_CATEGORY初始化参数设置为DEV的那些用户会话才能使用该配置文件。所有其他会话均无权访问SQL Profile，并且SQL语句不会影响SQL语句的执行计划。此技术使您可以在受限的环境中测试SQL配置文件，然后再将其提供给其他用户会话使用。

To what statements can a SQL Profile be applied?  SQL Profile可以应用于哪些语句？

SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)  （仅带有SELECT子句）
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)  （仅带有AS SELECT子句）
MERGE statements (the update or insert operations)

How can SQL Profiles be managed?  如何管理SQL Profile？

SQL Profiles can be handled by Oracle Enterprise Manager as part of the Automatic SQL Tuning process or manually by using DBMS_SQLTUNE package. Oracle企业管理器可以将SQL配置文件作为自动SQL调整过程的一部分进行处理，也可以使用DBMS_SQLTUNE包进行手动处理。

Using Enterprise Manager

1. On the Performance page, click Top Activity.
   The Top Activity page appears.
2. Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.
   The SQL Details page appears.
3. Click the Plan Control tab.
   A list of SQL profiles is displayed under SQL Profiles and Outlines.
4. 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.
5. 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.  要使用SQL Profiles APIs，您需要CREATE ANY SQL_PROFILE，DROP ANY SQL_PROFILE和ALTER ANY SQL_PROFILE系统特权。

Accepting a SQL Profile   接受 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.  使用DBMS_SQLTUNE.ACCEPT_SQL_PROFILE过程接受SQL Tuning Advisor建议的SQL Profile。这将创建一个SQL配置文件并将其存储在数据库中。

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.  my_sql_tuning_task是SQL调整任务的名称。您可以在DBA_SQL_PROFILES视图中查看有关SQL Profile的信息。

Altering a SQL Profile   修改SQL Profile

Alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of  an existing SQL Profile with the ALTER_SQL_PROFILE procedure.  使用ALTER_SQL_PROFILE过程更改现有SQL Profile的STATUS，NAME，DESCRIPTION和CATEGORY属性。

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.  在此示例中，my_sql_profile是您要更改的SQL配置文件的名称。
The status attribute is changed to disabled which means the SQL Profile will not be used during SQL compilation.  status属性更改为Disabled，这意味着在SQL编译期间将不使用SQL Profile。

Dropping a SQL Profile   删除SQL配置文件

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   即使使用no_index Hint，也使用索引
Note: In Explain Plan we see "SQL profile "my_sql_profile" used for this statement  注意：在Explain Plan中，我们看到“ SQL profile“ my_sql_profile”用于该语句

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

 

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)