SQL 自动调优以及 SQL Profile (Doc ID 2331566.1)

2017/04/12 by Leave a Comment

适用于:

 

Oracle Database – Enterprise Edition – 版本 10.1.0.2 和更高版本
本文档所含信息适用于所有平台

用途

 

SQL Profiles 是10g 引入的新特性，它通过 DBMS_SQLTUNE 包来管理，您也可以通过 Oracle Enterprise Manager 的自动 SQL 调优流程的功能来管理。这个 FAQ 回答关于 SQL profile 基本的问题，并且提供了一个使用 DBMS_SQLTUNE 包的例子。

 

提问，获得帮助，并分享您对于这篇文档的经验。

 

您是否希望与其他 Oracle 客户、Oracle 员工和业内专家进一步探讨此主题？

 

请点击这里 进入 Oracle 社区（中文）.
请点击这里进入My Oracle Support 社区的数据库安装/升级（英文）主页发现更多的话题和讨论。

问题和答案

 

什么是自动 SQL 调优？

 

有时候，因为缺乏相关的统计信息，会导致 SQL 优化器不能产生精准的评估，导致产生了差的执行计划。
一般的办法是用户不得不以手工在程序代码里面添加一些 hint 来指导优化器产生好的执行计划。对于已经封装的应用程序来说，修改代码是不可行的，唯一的办法是给应用开发商提 bug 并等其修改。

 

自动 SQL 调优正好是为这种场景而设计的。自动调优优化器（Automatic Tuning Optimizer）可以为 SQL 文本创建一个 Profile，叫SQL profile，它是由和 sql 语句相关的辅助统计信息所组成。在正常模式下的查询优化器在评估数据量（cardinality），选择率（selectivity）和成本（cost）时，可能因为缺乏足够数量的信息而导致不准，SQL profile 可以通过收集附加的信息，如采样、部分执行等技术来验证评估是否准确，必要时会调整优化器的评估。

在 SQL Profile 工作时，自动调优优化器（Automatic Tuning Optimizer）也会使用 SQL 语句的历史执行信息来设置合适的优化器参数，例如把 OPTIMIZER_MODE 从ALL_ROWS 改成 FIRST_ROWS。

 

此类型的分析输出通常是推荐您接受这个 SQL Profile。

对于一个 SQL Profile，一旦被接受，就会持续的存在数据字典中。SQL Profile 是针对某个特定 SQL 有效的，如果被接受，工作在正常模式下的优化器就会连同 SQL Profile 中的信息和其他正常统计信息一起来生成执行计划。这些可用的附加信息使得针对某个特定 SQL 产生更优的执行计划成为可能，而这无需修改任何应用程序代码。

下面的文档提供更多关于 SQL 调优顾问（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

 

 

什么是 SQL Profile?

 

SQL Profile 是一些信息的集合体，存储在数据字典中，使得 SQL 优化器可以为 SQL 语句产生最优的执行计划。SQL Profile 包含自动 SQL 调优期间发现的对产生错误执行计划的评估的纠正信息。这些信息能改善优化器的数据量（cardinality），选择率（selectivity）评估，而这些信息会指引优化器找到更好的执行计划。

一个 SQL profile 不包含某个特定执行计划的相关联信息，相反，优化器当选择执行计划的时候参考如下信息数据：

  • 环境信息，包括数据库配置，绑定变量值，统计信息，数据集等等
  • 存储在 SQL profile 中的补充信息

需要提醒的是，SQL Profile 不会像存储纲要（stored outlines）那样固定一个 SQL 的执行计划,当表的数据量增长或者有索引创建或删除时，使用了 SQL Profile 的执行计划仍会及时调整，也就是说，当数据分布或访问路径变化后，存储在 SQL Profile 中的相关信息仍然被参考。当然，经过相当长时间后，它的内容可能变得陈旧过时，需要重新生成。您可以通过再次运行自动 SQL 调优（Automatic SQL Tuning）来重新生成这个 SQL 的 Profile。

 

SQL Profile 的范围如何控制?

 

SQL Profile 的范围可以通过 CATEGORY 属性来控制，这个属性决定了哪个 sessoin 可以使用这个 profile，您可以通过查看 DBA_SQL_PROFILES 视图的 CATEGORY 字段来了解相关信息。

select category,name from dba_sql_profiles;

默认情况下，所有的 profiles 都被指定为 DEFAULT category，这就意味着所有 session 当初始化参数 SQLTUNE_CATEGORY 设置 DEFAULT 的时候都可以用这个 profile。

通过修改 SQL profile 的 category 属性，你可以指定哪个 session 受 profile 影响。例如，设置 category 为 DEV，那么 session 级设置了初始化参数 SQLTUNE_CATEGORY 为 DEV 的那些 session 可以使用这个 profile，其他 session 则不能使用这个 SQL profile，执行计划当然也不会受这个 SQL profile 影响。这个特性可以让您用来在某个限制环境上提前测试某个 SQL profile 而避免影响其他 session。

 

SQL Profile 适用什么语句？?

 

SELECT 语句
UPDATE 语句
INSERT 语句（必须包含select语句）
DELETE 语句
CREATE TABLE 语句（必须包含 AS SELECT 子句）
MERGE 语句（update 或者 insert 操作）

 

如何管理 SQL Profile？

SQL Profiles 可以通过 Oracle 企业管理器（Enterprise Manager）的自动 SQL 调优流程部分来管理它，或者通过 DBMS_SQLTUNE 包。

 

使用企业管理器：

  1. 在 Performance 页面, 点击 Top Activity。
    Top Activity 页面出现。
  2. 在 Top SQL 下, 点击使用 SQL profile 的 SQL 语句的 SQL ID 链接。
    SQL Details 页面显示出来。
  3. 点击 Plan Control 栏。
    SQL Profiles 和 Outlines 部分会显示 SQL Profile 的列表。
  4. 选择想要管理的 SQL profile。
    执行下面的任意操作 one of the following：

    • 要启用一个当前被禁用的 SQL profile，点击 Disable/Enable。
    • 要禁用一个当前被启用的 SQL profile，点击 Disable/Enable。
    • 要删除一个 SQL profile，点击 Delete.
  5. Confirmation 页面显示出来。
    点击 Yes 来确认，或者 No 取消刚才的操作。

 

使用 DBMS_SQLTUNE 包：

若是用 SQL Profiles APIs，您需要有 CREATE ANY SQL_PROFILE，DROP ANY SQL_PROFILE，ALTER ANY SQL_PROFILE 等系统权限。

 

接受 SQL Profile

使用 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 存储过程来接受一个 SQL Tuning Advisor 推荐的 SQL Profile。这会创建 SQL Profile 并存储在数据字典中。

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 是一个 SQL 调优任务的名字，您可以通过 DBA_SQL_PROFILES 来查看一个 SQL Profile 的相关信息。

 

修改 SQL Profile

您可以通过 ALTER_SQL_PROFILE 来修改一个已知 SQL Profile 的状态，名字，描述信息和 CATEGORY 属性。

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => ‘my_sql_profile’,
attribute_name => ‘STATUS’,
value => ‘DISABLED’);
END;
/

这个例子中，my_sql_profile 是您想要修改的 SQL Profile 名字。
状态被设置成 disabled，意思是 SQL Profile 将不会被 SQL 使用。

 

删除 SQL Profile

可以通过 DROP_SQL_PROFILE 来删除 SQL Profile。

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => ‘my_sql_profile’);
end;
/

 

 

样例：

SESSION 1 — SCOTT

创建表，插入数据，创建索引并收集统计信息。
执行带有 no_index hint 的 SQL，全表扫描将会被使用：

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

创建并执行调优任务并查询它的输出结果。
然后接受一个推荐的 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 => ‘SCOTT’,
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 — SCOTT

再次运行 SQL
可以看到即便是有 no_index 暗示，索引仍然被使用了
注意：执行计划里面可以看到使用了 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

 

如何获取每个调优集的报告？

 

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.
Filed Under: Oracle, Oracle SQL性能调优

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号