Oracle固定SQL的执行计划(一)—SQL Profile

共计 12785 个字符，预计需要花费 32 分钟才能阅读完成。

我们都希望对于所有在 Oracle 数据库中执行的 SQL，CBO 都能产生出正确的执行计划，但实际情况却并非如此，由于各种各样的原因 (比如目标 SQL 所涉及的对象的统计信息的不准确，或者 CBO 内部一些成本计算公式的先天缺陷等)，导致有时 CBO 产生效率不高、甚至是错误的执行计划。特别是 CBO 对目标 SQL 所产生的初始执行计划是正确的，后来由于某种原因(比如统计信息的变更等) 而导致 CBO 重新对其产生了错误的执行计划，这种执行计划的改变往往会导致目标 SQL 执行时间呈数量级的递增，而且常常会让我们很困惑：这个 SQL 原先跑得好好的，为什么突然就慢得让人无法接受？其实这种 SQL 执行效率突然衰减往往是因为目标 SQL 执行计划的改变。

我们当然希望这样的改变永远不要发生，即在 Oracle 数据库中跑的所有 SQL 都能有正确的、稳定的执行计划，但实际上在 Oracle 11g 的 SPM(SQL Plan Management)出现之前，这一点是很难做到的。那么现在退而求其次，如果已经出现了执行坟墓的变更，即 CBO 已经产生了错误的执行计划，我们应该怎么纠正呢？

我种情况下，我们通常会重新收集一下统计信息或者修改目标 SQL(比如在目标 SQL 中加入 Hint 等)以纠正错误的执行计划。但有时候重新收集统计信息并不能解决问题，更糟糕的是，很多情况下是没有办法修改目标 SQL 的 SQL 文本的(比如第三方开发的系统，修改不了源码，或者目标 SQL 是前台框架动态生成的等等)，那么这种情况下我们该怎么办呢？

在 Oracle 10g/11g 及其以后的版本中，我们可以使用 SQL Profile 或 SPM(SQL Plan Management)来解决上述执行计划变更的问题，用它们来调整、稳定目标 SQL 的执行计划。

本文介绍使用 SQL Profile 来稳定执行计划：

Oracle 10g 中的 SQL Profile(直译为“SQL 概要”)可以说是 Oracle 9i 中的 Stored Outline(直译为“存储概要”)的进化。Stored Outline 能够实现的功能 SQL Profile 也完全能够实现。

与 Stored Outline 相比，SQL Profile 具备如下优点：

更容易生成、更改和控制
在 SQL 语句的支持上做得更好，也就是说适用范围更广。

使用 SQL Profile 可以很容易实现如下两个目的：

锁定或者说稳定执行计划
在不能修改目标 SQL 的 SQL 文本的情况下使目标 SQL 语句按指定的执行计划运行。

SQL Profile 有两种类型：一种是 Automatic 类型，另一种是 Manual 类型。下面分别介绍这两种类型：

1. Automatic 类型的 SQL Profile

Automatic 类型的 SQL Profile 其实就是针对目标 SQL 的一些额外的调整信息，这些信息存储在数据字典里。当有了 Automatic 类型的 SQL Profile 后，Oracle 在产生执行计划时就会根据它对目标 SQL 所涉及的统计信息等内容做相应的调整，因而能够在一定程度上避免产生错误的执行计划。你不用担心 Automatic 类型的 SQL Profile 的准确性，因为 Oracle 会使用类型于动态采用技术那样的手段来保证这些额外调整信息相对准确。

Automatic 类型的 SQL Profile 不会像 Stored Outline 那样锁定目标 SQL 的执行计划，因为 Automatic 类型的 SQL Profile 的本质就是针对目标 SQL 的一些额外的调整信息，这些额外的调整信息需要与原目标 SQL 的相关统计信息等内容一起作用才能得到新的执行计划，即原始 SQL 的统计信息等内容一旦发生变化，即使原有 Automatic 类型的 SQL Profile 并没有改变，该 SQL 的执行也可能会发生变化。从这个意义上讲，Automatic 类型的 SQL Profile 并不能完全起到稳定目标 SQL 的执行计划的作用，虽然它确实可以用来调整执行计划。

看一个在不更改目标 SQL 的 SQL 文本的情况下使用 Automatic 类型的 SQL Profile 来调整执行计划的实例：

创建测试表及相关操作：

zx@MYDB>create table t1 (n number);
 
Table created.
 
zx@MYDB>declare
  2  begin
  3  for i in 1..10000 loop
  4  insert into t1 values(i);
  5  end loop;
  6  commit;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
zx@MYDB>select count(*) from t1;
 
  COUNT(*)
----------
     10000
 
zx@MYDB>create index idx_t1 on t1(n);
 
Index created.
 
zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T1',method_opt=>'for all columns size 1',cascade=>true);
 
PL/SQL procedure successfully completed.
 
zx@MYDB>select /*+no_index(t1 idx_t1) */ * from t1 where n=1;
 
         N
----------
         1

Oracle 固定 SQL 的执行计划(一)---SQL Profile

从上述显示内容可以看出，目标 SQL 走的是对表 T1 的全表扫描(Table Access Full)，这个执行计划显然是错误，这里正确的执行坟墓应该是走索引 IDX_T1 的索引范围扫描(Index Range Scan)。下面使用 SQL Tuning Advisor 对这条 SQL 生成 Automatic 类型的 SQL Profile。

a. 先创建一个名为 my_sql_tuning_task_2 的自动调整任务：

zx@MYDB>declare
  2  my_task_name varchar2(30);
  3  my_sqltext clob;
  4  begin
  5  my_sqltext:='select /*+no_index(t1 idx_t1) */ * from t1 where n=1';
  6  my_task_name:=dbms_sqltune.create_tuning_task(
  7  sql_text=>my_sqltext,
  8  user_name=>USER,
  9  scope=>'COMPREHENSIVE',
 10  time_limit=>60,
 11  task_name=>'my_sql_tuning_task_1',
 12  description=>'Task to tune a query on table t1');
 13  end;
 14  /
  
 PL/SQL procedure successfully completed.
  
zx@MYDB>select task_name,status,execution_start,execution_end from user_advisor_log;
 
TASK_NAME                      STATUS                            EXECUTION_START     EXECUTION_END
------------------------------ --------------------------------- ------------------- -------------------
my_sql_tuning_task_1           INITIAL

注：创建任务时可以使用 SQL 来创建，可以适用于 SQL 文本长的情况。详情参考官方文档。

b. 执行上述自动调整任务

zx@MYDB>begin
  2  dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_1');
  3  end;
  4  /
   
zx@MYDB>zx@MYDB>select task_name,status,execution_start,execution_end from user_advisor_log;
 
TASK_NAME                      STATUS                            EXECUTION_START     EXECUTION_END
------------------------------ --------------------------------- ------------------- -------------------
my_sql_tuning_task_1           COMPLETED                         2017-02-28 10:59:43 2017-02-28 10:59:44
 
PL/SQL procedure successfully completed.

c. 查看上述自动任务的调整结果

zx@MYDB>set long 9000
zx@MYDB>set longchunksize 1000
zx@MYDB>set linesize 800
zx@MYDB>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_1') from dual;

Oracle 固定 SQL 的执行计划(一)---SQL Profile

从上述调整结果可以看到，Oracle 现在告诉我们：它已经为目标 SQL 找到了更好的执行计划，并且已经创建了针对该 SQL 的 Automatic 类型的 SQL Profile。如果我们使用 accecp_sql_profile 接受了这个 SQL Profile，则目标 SQL 的响应时间将会有 86.24% 的提升，逻辑读将会有 95% 的提升，并且接受了该 SQL Profile 后目标 SQL 的执行计划将会由原来的全表扫描变为索引范围扫描。

上面 Automatic 类型的 SQL Profile 所产生的调整结果就是我们想要的，所以现在只需按 Oracle 的提示接受这个 SQL Profile 即可：

zx@MYDB>execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_1', task_owner => 'ZX', replace => TRUE,force_match=>true);
 
PL/SQL procedure successfully completed.

接受此 SQL Profile 后我们来看一下效果，再次执行目标 SQL:

zx@MYDB>select /*+no_index(t1 idx_t1) */ * from t1 where n=1;
 
         N
----------
         1

Oracle 固定 SQL 的执行计划(一)---SQL Profile

注意到 Note 部分有这样的内容“SQL profile SYS_SQLPROF_015a82b353490000 used for this statement”这说明我们刚才接受的 SQL Profile 已经起了作用，该 SQL Profile 的名字为 SYS_SQLPROF_015a82b353490000。从执行计划中也可以看到，执行计划确实已经改变了。

另外，DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 的输入参数 force_match 的默认值为 FALSE，表示只有在 SQL 文本完全匹配的情况下才会应用 SQL Profile，这种情况下只要目标 SQL 的 SQL 文本发生一点变动，原有的 SQL Profile 将会失去作用，如果设置为 TRUE，即使 SQL 有变动 SQL Profile 也会强制生效。

删除 SQL Profile

zx@MYDB>exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_015a82b353490000');
 
PL/SQL procedure successfully completed.

2. Manual 类型的 SQL Profile

Manual 类型的 SQL Profile 本质上就是一堆 Hint 的组合，这一堆 Hint 的组合实际上来源于执行计划中的 Outline Data 部分的 Hint 组合。Manual 类型的 SQL Profile 同样可以在不更改目标 SQL 的 SQL 文本的情况下，调整其执行计划，而且更为重要的是，Manual 类型的 SQL Profile 可以起到很好稳定目标 SQL 的执行计划的作用，这一点是 Automatic 类型的 SQL Profile 所不具备的。

看一个使用 Manual 类型的 SQL Profile 实例固定执行计划的实例，使用上面的 t1 表，删除上面的 SQL Profile，再次执行 SQL

zx@MYDB>select /*+no_index(t1 idx_t1) */ * from t1 where n=1;
 
         N
----------
         1

Oracle 固定 SQL 的执行计划(一)---SQL Profile

从上述输出可以看出执行计划仍然走全表扫描。

现在来创建 Manual 类型的 SQL Profile。这里使用了 MOS 上的一个脚本 coe_xfr_sql_profile.sql。这个脚本用于从 Shared Pool、AWR Repository 中指定 SQL 的指定执行计划的 Outline Data 部分的 Hint 组合，来创建 Manual 类型的 SQL Profile。

使用 coe_xfr_sql_profile.sql 脚本的步骤为

针对目标 SQL 使用 coe_xfr_sql_profile.sql 产生能生成其 Manual 类型的 SQL Profile 的脚本 A。
改写目标 SQL 的文本，在其中使用合适的 Hint，直到加入 Hint 后的 SQL 能走出我们想要的执行计划。然后对加入合适 Hint 后的 SQL 使用脚本 coe_xfr_sql_profile.sql，产生能生成其 Manual 类型的 SQL Profile 的脚本 B。
用脚本 B 中的 Outline Data 部分的 Hint 组合替换掉脚本 A 的 Outline Data 部分的 Hint 组合。
执行脚本 A 生成针对原目标 SQL 的 Manual 类型的 SQL Profile。

现在改写上面的 SQL，强制走索引：

zx@MYDB>select /*+index(t1 idx_t1) */ * from t1 where n=1;
 
         N
----------
         1

Oracle 固定 SQL 的执行计划(一)---SQL Profile

从执行计划中可以看出 SQL Id 和对应的 Plan hash value。

全表扫描的 SQL Id：6chcc0pvvhqqm Plan hash value：3617692013

索引扫描的 SQL Id：2ufquy7xs5nm5 Plan hash value：1369807930

a. 先使用 coe_xfr_sql_profile.sql 生成全表扫描 SQL 对应的脚本

zx@MYDB>@scripts/coe_xfr_sql_profile.sql
 
Parameter 1:
SQL_ID (required)
 
Enter value for 1: 6chcc0pvvhqqm
 
 
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3617692013        .002
 
Parameter 2:
PLAN_HASH_VALUE (required)
 
Enter value for 2: 3617692013
 
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "6chcc0pvvhqqm"
PLAN_HASH_VALUE: "3617692013"
 
SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3     RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3     RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
 
Execute coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql
on TARGET system in order to create a custom SQL Profile
with plan 3617692013 linked to adjusted sql_text.
 
 
COE_XFR_SQL_PROFILE completed.

从输出可以看出，生成一个名为 coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql 的脚本。

b. 用 coe_xfr_sql_profile.sql 生成索引扫描 SQL 对应的脚本

SQL>@scripts/coe_xfr_sql_profile.sql
 
Parameter 1:
SQL_ID (required)
 
Enter value for 1: 2ufquy7xs5nm5
 
 
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1369807930        .001
 
Parameter 2:
PLAN_HASH_VALUE (required)
 
Enter value for 2: 1369807930
 
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "2ufquy7xs5nm5"
PLAN_HASH_VALUE: "1369807930"
 
SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3     RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3     RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
 
Execute coe_xfr_sql_profile_2ufquy7xs5nm5_1369807930.sql
on TARGET system in order to create a custom SQL Profile
with plan 1369807930 linked to adjusted sql_text.
 
 
COE_XFR_SQL_PROFILE completed.

从输出可以看出，生成一个名为 coe_xfr_sql_profile_2ufquy7xs5nm5_1369807930.sql 的脚本。

c. 把后生成的脚本里的 Outline Data 部分的 Hint 组合替换到先生成的脚本里，即下图红框部分内容

Oracle 固定 SQL 的执行计划(一)---SQL Profile

d. 执行 coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql 脚本

zx@MYDB>@coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql
zx@MYDB>REM
zx@MYDB>REM $Header: 215187.1 coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql 11.4.4.4 2017/02/28 carlos.sierra $
zx@MYDB>REM
zx@MYDB>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
zx@MYDB>REM
zx@MYDB>REM AUTHOR
zx@MYDB>REM   carlos.sierra@oracle.com
zx@MYDB>REM
zx@MYDB>REM SCRIPT
zx@MYDB>REM   coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql
zx@MYDB>REM
zx@MYDB>REM DESCRIPTION
zx@MYDB>REM   This script is generated by coe_xfr_sql_profile.sql
zx@MYDB>REM   It contains the SQL*Plus commands to create a custom
zx@MYDB>REM   SQL Profile for SQL_ID 6chcc0pvvhqqm based on plan hash
zx@MYDB>REM   value 3617692013.
zx@MYDB>REM   The custom SQL Profile to be created by this script
zx@MYDB>REM   will affect plans for SQL commands with signature
zx@MYDB>REM   matching the one for SQL Text below.
zx@MYDB>REM   Review SQL Text and adjust accordingly.
zx@MYDB>REM
zx@MYDB>REM PARAMETERS
zx@MYDB>REM   None.
zx@MYDB>REM
zx@MYDB>REM EXAMPLE
zx@MYDB>REM   SQL> START coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql;
zx@MYDB>REM
zx@MYDB>REM NOTES
zx@MYDB>REM   1. Should be run as SYSTEM or SYSDBA.
zx@MYDB>REM   2. User must have CREATE ANY SQL PROFILE privilege.
zx@MYDB>REM   3. SOURCE and TARGET systems can be the same or similar.
zx@MYDB>REM   4. To drop this custom SQL Profile after it has been created:
zx@MYDB>REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_6chcc0pvvhqqm_3617692013');
zx@MYDB>REM   5. Be aware that using DBMS_SQLTUNE requires a license
zx@MYDB>REM      for the Oracle Tuning Pack.
zx@MYDB>REM   6. If you modified a SQL putting Hints in order to produce a desired
zx@MYDB>REM      Plan, you can remove the artifical Hints from SQL Text pieces below.
zx@MYDB>REM      By doing so you can create a custom SQL Profile for the original
zx@MYDB>REM      SQL but with the Plan captured from the modified SQL (with Hints).
zx@MYDB>REM
zx@MYDB>WHENEVER SQLERROR EXIT SQL.SQLCODE;
zx@MYDB>REM
zx@MYDB>VAR signature NUMBER;
zx@MYDB>VAR signaturef NUMBER;
zx@MYDB>REM
zx@MYDB>DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  PROCEDURE wa (p_line IN VARCHAR2) IS
  5  BEGIN
  6  DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
  7  END wa;
  8  BEGIN
  9  DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
 10  DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
 11  -- SQL Text pieces below do not have to be of same length.
 12  -- So if you edit SQL Text (i.e. removing temporary Hints),
 13  -- there is no need to edit or re-align unmodified pieces.
 14  wa(q'[select /*+no_index(t1 idx_t1) */ * from t1 where n=1 ]');
 15  DBMS_LOB.CLOSE(sql_txt);
 16  h := SYS.SQLPROF_ATTR(
 17  q'[BEGIN_OUTLINE_DATA]',
 18  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 19  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
 20  q'[DB_VERSION('11.2.0.1')]',
 21  q'[ALL_ROWS]',
 22  q'[OUTLINE_LEAF(@"SEL$1")]',
 23  q'[INDEX(@"SEL$1""T1"@"SEL$1" ("T1"."N"))]',
 24  q'[END_OUTLINE_DATA]');
 25  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 26  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
 27  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 28  sql_text    => sql_txt,
 29  profile     => h,
 30  name        => 'coe_6chcc0pvvhqqm_3617692013',
 31  description => 'coe 6chcc0pvvhqqm 3617692013'||:signature||''||:signaturef||'',
 32  category    => 'DEFAULT',
 33  validate    => TRUE,
 34  replace     => TRUE,
 35  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 36  DBMS_LOB.FREETEMPORARY(sql_txt);
 37  END;
 38  /
 
PL/SQL procedure successfully completed.
 
zx@MYDB>WHENEVER SQLERROR CONTINUE
zx@MYDB>SET ECHO OFF;
 
            SIGNATURE
---------------------
  3589138201450662673
 
 
           SIGNATUREF
---------------------
  8068435081012723673
 
 
... manual custom SQL Profile has been created
 
 
COE_XFR_SQL_PROFILE_6chcc0pvvhqqm_3617692013 completed

e. 执行完成后再次查看目标 SQL 的执行计划

zx@MYDB>select /*+no_index(t1 idx_t1) */ * from t1 where n=1;
 
         N
----------
         1

Oracle 固定 SQL 的执行计划(一)---SQL Profile

从执行计划中可以看出已经走了 INDEX RANGE SCAN，而且 note 部分提示 SQL profile coe_6chcc0pvvhqqm_3617692013 used for this statement，说明执行 sql 时使用了该 SQL Profile。

如果想在目标 SQL 的 SQL 文本发生变动时 SQL Profile 依然生效，则需要修改生成的脚本里的 force_match=>true。

参考：《基于 Oracle 的 SQL 优化》PDF 下载见 http://www.linuxidc.com/Linux/2017-02/140521.htm

官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm#PFGRF94854

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sqltun.htm#CHDGAJCI

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-03/141194.htm

Oracle固定SQL的执行计划(一)—SQL Profile

基于开源MaxKB构建大语言模型的本地知识库系统

获取各大人工智能AI工具通过API和KEY调用的方法

安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

给你的NAS无限可能，安装小晓雅全家桶影音库

Python历史

ansible用法之ansible-playbook简单使用

案例：教你迁移MySQL数据库

HTTP协议简介

基于 Markdown 的幻灯片生成器

linux下使用tree命令以树形结构显示文件目录结构