oracle固定执行计划3-sql profile manual续-Query Block

最新推荐文章于 2023-03-01 17:11:21 发布

P10ZHUO

最新推荐文章于 2023-03-01 17:11:21 发布

阅读量491

点赞数

分类专栏： sql优化文章标签： oracle

本文链接：https://blog.csdn.net/fanzhuozhuo/article/details/114626897

版权

sql优化专栏收录该内容

4 篇文章 0 订阅

订阅专栏

oracle固定执行计划3-sql profile manual续-Query Block

Query Block
manual绑定最佳实践
总结

在上一篇中，说明使用manual sql profile的方法绑定执行计划，但是有要求：虽然执行计划的输出Note部分显示已经使用到了SQL Profile，但是执行计划并没有如我们预期一样被改变，依然是全表扫描，查看存储hint的基表也显示索引扫描的hint已经被绑定到了这个SQL上，那么问题出哪了？
这是由于SQL Profile对于hint是非常挑剔的，SQL Profile里接受的hint需要提供Query Block Name(初始化参数类的hint不需要提供Query Block Name），否则优化器会忽略掉这些hint，我们重新设置SQL Profile的Hints，在Hints中加上Query Block Name。
本文就说明下啥是Query Block Name?到底怎样来手动构造hint，来手动利用sql profile绑定执行计划？

Query Block

Query Block Name

可能很多人用手工创建SQL Profile的一个最大的难处是不知道该如何写出让SQL Profile接受的hint，因为传统的hint是不包含Query Block Name的，把Query Block Name增加到hint里会显得这个技术有点复杂。其实Query Block Name并不是复杂的技术，我们搞清楚了它的由来、它的技术细节后，就会豁然开朗。

Oracle数据库中的Query Block是指一个语义上完整的查询语句，它可以是一个子查询对应的SQL语句，也可以是一个视图所对应的视图定义语句。目标SQL可以包含一个或者多个Query Block，当目标SQL只包含一个Query Block时，这时Query Block就是目标SQL本身。
Oracle数据库中Hint生效的范围仅限于它本身所在的Query Block，如果想将Hint生效的范围扩展到它所在的Query Block之外而又没有在该Hint中指定其生效的Query Block名称的话，则Oracle会忽略该Hint。
对于目标SQLselect t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = ‘CHICAGO’)
而言，很明显这里有两个Query Block，一个是外部查询"select t1.ename, t1.deptno from emp t1 where t1.deptno in(…)",一个是子查询"select t2.deptno from dept t2 where t2.loc = ‘CHICAGO’"。
如果我们想对此目标SQL使用全表扫描hint，让该SQL的执行几哈走对表EMP和表DEPT的全表扫描，则根据上述原则，这里加入全表扫描Hint后的正确形式应该是：
select /*+ full(t1)*/ t1.ename, t1.deptno from emp t1 where t1.deptno in(select /+ full(t2)/ t2.deptno from dept t2 where t2.loc = ‘CHICAGO’)
如果违背了上述原则，比如全表扫描表T2的hint在使用时跨了Query Block，改写了这样的形式：
select /*+ full(t1) full(t2)*/ t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = ‘CHICAGO’)

从如下执行结果可以看到，oracle会忽略该hint(即full(t2)),此时的执行计划在访问表DEPT时还是走对索引PK_DEPT的索引全扫描：

SQL> select t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = 'CHICAGO');

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |   100 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |     5 |   100 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T2"."LOC"='CHICAGO')
   4 - access("T1"."DEPTNO"="T2"."DEPTNO")
       filter("T1"."DEPTNO"="T2"."DEPTNO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        714  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL> select /*+ full(t1) full(t2)*/ t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = 'CHICAGO');

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |   100 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |     5 |   100 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T2"."LOC"='CHICAGO')
   4 - access("T1"."DEPTNO"="T2"."DEPTNO")
       filter("T1"."DEPTNO"="T2"."DEPTNO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        714  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed

这里对标DEPT的全表扫描hint(即full(t2))失效是因为该hint出现的位置不对，/*+ full(t1) full(t2)*/此时出现在外部查询所对应的Query Block中，这表示它们生效的范围
均是针对外部查询select t1.ename, t1.deptno from emp t1 where t1.deptno in(…)"的,对于full(t1)而言，这是没问题的；但是对于full(t2)而言，它出现的位置就不对了，很明显
它是针对子查询"select t2.deptno from dept t2 where t2.loc = ‘CHICAGO’"的，所以full(t2)应该出现在子查询所对应的Query Block中，而不是出现在外部查询中。

现在将上述全表扫描hint(即full(t2))从外部查询所对应的Query Block以到子查询所对应的Query Block中，即改写如下，并查看执行计划，
这种没有跨Query Block的全表扫描hint确实生效了，此时的执行计划在访问表DEPT时走的就是对标DEPT的全表扫描

SQL> select /*+ full(t1)*/ t1.ename, t1.deptno from emp t1 where t1.deptno in(select /*+ full(t2)*/ t2.deptno from dept t2 where t2.loc = 'CHICAGO');

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   100 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     5 |   100 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   2 - filter("T2"."LOC"='CHICAGO')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        714  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

对于目标SQL中的Query Block，Oracle会对其中没有被我们自定义的所有Query Block自动命名**。自动命名的规则就是"类型$数字"，如果同样类型的Query Block不止一个，则这里的数字就从1开始往上递增。**

查询语句的Query Block类型所对应的关键字就是"SEL",“SEL"显然是单词"SELECT"的缩写，对于SQL语句而言：select t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = ‘CHICAGO’);
其外部查询select t1.ename, t1.deptno from emp t1 where t1.deptno in(…) 所对应的Query Block自动命名就是"SEL$1”,其子查询"select t2.deptno from dept t2 where t2.loc = ‘CHICAGO’“所对应的Query Block自动命名就是"SEL$2”
在这里插入图片描述
除了自动命名之外，我们还可以使用QB_NAME Hint来为一个Query Block指定一个自定义的名称，格式如下所示： /*+ QB_NAME(自定义的Query Block名称)*/
对于上述SQL中的子查询"select t2.deptno from dept t2 where t2.loc = ‘CHICAGO’“而言，我们可以对其使用QB_NAME Hint，如改为select /*+ QB_NAME(zhuozhuo)*/ t2.deptno from dept t2 where t2.loc = ‘CHICAGO’
后就将其所在的Query Block的名称指定为了"zhuozhuo”.

Hint生效的范围仅限于它本身所在的Query Block，不能跨Query Block。但oracle提供了一种方法，只要在写Hint的时候指定了该Hint生效的Query Block名称，那么就不必非得把Hint写在其生效的Query Block中，比如完全可以把目标SQL中所有的Hint都写在一起，只要你指定了这些Hint各自生效的Query Block名称。
在Hint中出现的Query Block其格式必须是"@Query Block名称"。oracle数据库中基本上所有的Hint都可以指定该Hint生效的Query Block名称。指定Query Block有如下两种方式：
方法一：在Hint的目标对象前额外加上该Hint生效的Query Block名称，它们之间用空格分隔。比如：

/*+ full(@sel$1 t1) full(@sel$2 t2)*/

这表示对T1的全表扫描的生效范围是在名为"SEL$1"的Query Block中，对T2的全表扫描的生效范围是在名为"SEL$2"的Query Block中.
方法二：在Hint的目标对象后额外加上该Hint生效的Query Block名称，他们之间是连在一起的，并不做任何分割，比如：

/*+ full(t1@sel$1) full(t2@sel$2)*/

这同样表示对T1的全表扫描的生效范围是在名为"SEL$1"的Query Block中，对T2的全表扫描的生效范围是在名为"SEL$2"的Query Block中.

另外需要注意的是，如果在Hint中不指定该Hint生效的Query Block名称，则oracle会默认认为该Hint生效的范围是该Hint所处的Query Block。
对于SQL语句：
select t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = ‘CHICAGO’);
如下的三种方法都可以实现即将所有的Hint都写在一起，同时又对表EMP和DEPT走全表扫描。
方法一：在全表扫描Hint中以第一种方式指定系统自动命名的Query Block名称，即将上述SQL改写为如下这样的形式：
select /*+ full(@sel$1 t1) full(@sel$2 t2)*/ t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = ‘CHICAGO’);

从如下执行结果可以看到，这种指定系统自动命名的Query Block名称的全表扫描Hint确实生效了，此时的执行计划在访问表DEPT时确实走的是对表DEPT的全表扫描：

SQL> select /*+ full(@sel$1 t1) full(@sel$2 t2)*/ t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = 'CHICAGO');

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   100 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     5 |   100 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   2 - filter("T2"."LOC"='CHICAGO')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        714  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

方法二：在目标SQL中使用QB_NAME Hint将该SQL的子查询select t2.deptno from dept t2 where t2.loc = 'CHICAGO’所在的Query Block名称自定义为"zhuozhuo",同时在全表扫描hint中以第一种方式指定自定义的Query Block，即将SQL改写为：
select /*+ full(t1) full(@zhuozhuo t2)*/ t1.ename, t1.deptno from emp t1 where t1.deptno in(select /+ QB_NAME(zhuozhuo)/t2.deptno from dept t2 where t2.loc = ‘CHICAGO’);
从如下执行结果可以看到，以第一种方式指定我们自定义的Query Block名称的全表扫描Hint确实生效了，此时的执行计划在访问表DEPT时确实走的是对表DEPT的全表扫描：

SQL> select /*+ full(t1) full(@zhuozhuo t2)*/ t1.ename, t1.deptno from emp t1 where t1.deptno in(select /*+ QB_NAME(zhuozhuo)*/t2.deptno from dept t2 where t2.loc = 'CHICAGO');

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   100 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     5 |   100 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   2 - filter("T2"."LOC"='CHICAGO')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        714  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

这里对表T1(即表EMP)使用full(t1)时并没有指定该全表扫描Hint生效的Query Block名称，于是oracle会默认认为该Hint生效的范围是其所处的Query Block。现在该hint处于上述sql的外部查询select t1.ename, t1.deptno from emp t1 where t1.deptno in(…)中，所以默认情况下full(t1)生效的Query Block就是指这个外部查询。

方法三：在目标SQL中使用QB_NAME Hint将该SQL的子查询select t2.deptno from dept t2 where t2.loc = 'CHICAGO’所在的Query Block名称自定义为"zhuozhuo",同时在全表扫描hint中以第二种方式指定自定义的Query Block，即将SQL改写为：
select /*+ full(t1@SEL$1) full(t2@zhuozhuo)*/ t1.ename, t1.deptno from emp t1 where t1.deptno in(select /+ QB_NAME(zhuozhuo)/t2.deptno from dept t2 where t2.loc = ‘CHICAGO’);
从如下执行结果可以看到，以第二种方式指定我们自定义的Query Block名称的全表扫描Hint确实生效了，此时的执行计划在访问表DEPT时确实走的是对表DEPT的全表扫描：

SQL> select /*+ full(t1@SEL$1) full(t2@zhuozhuo)*/ t1.ename, t1.deptno from emp t1 where t1.deptno in(select /*+ QB_NAME(zhuozhuo)*/t2.deptno from dept t2 where t2.loc = 'CHICAGO');

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   100 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     5 |   100 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   2 - filter("T2"."LOC"='CHICAGO')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        714  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

将autotrace开关关闭后重新执行上述SQL：

SQL> set autot off
SQL> select t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = 'CHICAGO');

ENAME          DEPTNO
---------- ----------
WARD               30
TURNER             30
ALLEN              30
JAMES              30
BLAKE              30
MARTIN             30

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5qkuad5rv1kt4, child number 0
-------------------------------------
select t1.ename, t1.deptno from emp t1 where t1.deptno in(select
t2.deptno from dept t2 where t2.loc = 'CHICAGO')

Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     6 (100)|          |
|   1 |  MERGE JOIN                  |         |     5 |   100 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T2@SEL$2
   3 - SEL$5DA710D3 / T2@SEL$2
   5 - SEL$5DA710D3 / T1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX(@"SEL$5DA710D3" "T2"@"SEL$2" ("DEPT"."DEPTNO"))
      FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
      USE_MERGE(@"SEL$5DA710D3" "T1"@"SEL$1")
      PX_JOIN_FILTER(@"SEL$5DA710D3" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T2"."LOC"='CHICAGO')
   4 - access("T1"."DEPTNO"="T2"."DEPTNO")
       filter("T1"."DEPTNO"="T2"."DEPTNO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T1"."DEPTNO"[NUMBER,22], "T1"."ENAME"[VARCHAR2,10]
   2 - "T2"."DEPTNO"[NUMBER,22]
   3 - "T2".ROWID[ROWID,10], "T2"."DEPTNO"[NUMBER,22]
   4 - (#keys=1) "T1"."DEPTNO"[NUMBER,22], "T1"."ENAME"[VARCHAR2,10]
   5 - "T1"."ENAME"[VARCHAR2,10], "T1"."DEPTNO"[NUMBER,22]


63 rows selected.

从上述执行计划中可以看到，被自动命名的Query Block的命名规则其实可以从执行计划的"Query Block Name"和"Outline Data"部分得到验证。比如可以从"Query Block Name"中看到关键字"T1@SEL$1"和"T2@SEL$2",这就表明表T1(即表EMP)是在名为SEL$1的Query Block中，表T2(即表DEPT)是在名为SEL$2的Query Block中。注意，上述执行计划中还可以看到名为"SEL$5DA710D3"的Query Block，这个Query Block是oracle在对上述子查询select t2.deptno from dept t2 where t2.loc = 'CHICAGO’做子查询展开后形成的Query Block，像这种由查询转换而形成的新Query Block，oracle都会自动产生一个新名称，此时这个新名称中关键字"$"后将会是一个字符串，而不再是我们之前所熟悉的数字。

Query Block–Oracle中的Query Block是指一个语义上完整的查询语句。
Query Block Name–给查询语句定义了一个名字，相当于别名。这个别名我们可以让oracle自动命名(没有加)，自动命名的规则就是"类型$数字"，如果同样类型的Query Block不止一个，则这里的数字就从1开始往上递增。也可以加hint /*+ QB_NAME(zhuozhuo)*/，我们人为自己命名。在Hint中出现的Query Block其格式必须是"@Query Block名称"

执行计划Outline Data部分

执行计划中的Outline Data部分实际上是oracle用来固定执行计划的内部hint组合，它是非常全面的hint组合，考虑到了各个方面的内容，是可以用来完整的固定目标SQL的执行计划的。而之前我们用到的hint组合(/*+ full(t1@SEL$1) full(t2@zhuozhuo)*/等)被称为部分hont组合(partial hint)，使用了这些partial hint组合后，并不能保证目标SQL每次执行时执行计划都相同。所以，如果要完全的固定一个SQL的执行计划，最好的方式就是使用其执行计划中Outline Data"部分的hint组合。Outline Data"部分的hint组合可以直接加到目标SQL的SQL文本中，比如，将上述执行计划中Outline Data"部分的hint组合抓出来加到目标SQL的文本中，
在这里插入图片描述
改写后的目标SQL如下所示：

select 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX(@"SEL$5DA710D3" "T2"@"SEL$2" ("DEPT"."DEPTNO"))
      FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
      USE_MERGE(@"SEL$5DA710D3" "T1"@"SEL$1")
      PX_JOIN_FILTER(@"SEL$5DA710D3" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = 'CHICAGO');

这样一改写，目标SQL的执行计划就被固定下来了。现在我们来验证以下，在表EMP的字段empno上创建一个索引idx_emp_deptno.
将autotrace开关重新以traceonly方式打开后再次执行上述SQL：

SQL> create index idx_emp_deptno on emp(deptno);

Index created.

SQL> set autot trace
SQL> select t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = 'CHICAGO');

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1221474500

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     5 |   100 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                |     5 |   100 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |                |     5 |   100 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPT           |     1 |    11 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_EMP_DEPTNO |     5 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    45 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T2"."LOC"='CHICAGO')
   4 - access("T1"."DEPTNO"="T2"."DEPTNO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        714  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

可以看到，上述SQL执行计划已经变为对dept的全扫描，而emp表是对索引idx_emp_deptno的范围扫描，已经改变。
现在我们把他修改回原来的执行计划，对表emp全扫描，不用索引idx_emp_deptno的范围扫描，dept表的索引PK_DEPT扫描。现在只需要将原执行计划中的Outline Data"部分的hint组合加到上述SQL的SQL文本中，重新执行下即可：

SQL> select 
  2    /*+
  3        BEGIN_OUTLINE_DATA
  4        IGNORE_OPTIM_EMBEDDED_HINTS
  5        OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
  6        DB_VERSION('11.2.0.4')
  7        ALL_ROWS
  8        OUTLINE_LEAF(@"SEL$5DA710D3")
  9        UNNEST(@"SEL$2")
 10        OUTLINE(@"SEL$1")
 11        OUTLINE(@"SEL$2")
 12        INDEX(@"SEL$5DA710D3" "T2"@"SEL$2" ("DEPT"."DEPTNO"))
 13        FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
 14        LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
 15        USE_MERGE(@"SEL$5DA710D3" "T1"@"SEL$1")
 16        PX_JOIN_FILTER(@"SEL$5DA710D3" "T1"@"SEL$1")
 17        END_OUTLINE_DATA
 18    */
 19  t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = 'CHICAGO');

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |   100 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |     5 |   100 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T2"."LOC"='CHICAGO')
   4 - access("T1"."DEPTNO"="T2"."DEPTNO")
       filter("T1"."DEPTNO"="T2"."DEPTNO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        714  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed

通过dbms_xplan.display_cursor增加outline参数来获得SQL PLAN的outline data数据是一种可以快速获得查询块、获得提示的方式，用这些hint可以非常容易获得、构造出我们需要的hint。其实这些hint都是存储在v$sql_plan的other_xml字段中，我们也可以通过转换函数直接从这个字段中查询到这些hint。

SQL> col OUTLINE_HINTS for a100
SQL> select extractvalue(value(d), '/hint') as outline_hints
  from xmltable('/*/outline_data/hint' passing
                (select xmltype(other_xml) as xmlval
                   from v$sql_plan
                  where sql_id = '&sqlid'
                    and child_number = 0
                    and other_xml is not null)) d;

OUTLINE_HINTS
----------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX(@"SEL$5DA710D3" "T2"@"SEL$2" ("DEPT"."DEPTNO"))
FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
USE_MERGE(@"SEL$5DA710D3" "T1"@"SEL$1")
PX_JOIN_FILTER(@"SEL$5DA710D3" "T1"@"SEL$1")

13 rows selected.

可以看出，和上面dbms_xplan.display_cursor增加outline参数获得的效果一样。

manual sql profile

现在的执行计划：

SQL> set autot trace
SQL> select t1.ename, t1.deptno from emp t1 where t1.deptno in(select t2.deptno from dept t2 where t2.loc = 'CHICAGO');

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1221474500

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     5 |   100 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                |     5 |   100 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |                |     5 |   100 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPT           |     1 |    11 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_EMP_DEPTNO |     5 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    45 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T2"."LOC"='CHICAGO')
   4 - access("T1"."DEPTNO"="T2"."DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        714  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

manual绑定sql profile：

SQL> declare
  2    v_hints        sys.sqlprof_attr;
  3    hint           varchar2(100);
  4    v_sql_id       varchar2(100);
  5    v_sql_fulltext varchar2(32767);
  6  begin
  7    v_sql_id := '&请输入SQL_ID';
  8    hint     := '&请输入HINT';
  9    select sql_fulltext
 10      into v_sql_fulltext
 11      from v$sql
 12     where sql_id = v_sql_id
 13       and rownum = 1;
 14    v_hints := sys.sqlprof_attr(hint); 
 15    dbms_sqltune.import_sql_profile(v_sql_fulltext, 
 16                                    v_hints,
 17                                    v_sql_id, ---SQL PROFILE 名字，这里我用sql_id来命名
 18                                    force_match => true);
 19  end;
 20  /
Enter value for 请输入sql_id: 5qkuad5rv1kt4
old   7:   v_sql_id := '&请输入SQL_ID';
new   7:   v_sql_id := '5qkuad5rv1kt4';
Enter value for 请输入hint: full(t1@SEL$1) index(t2@SEL$2 PK_DEPT)
old   8:   hint     := '&请输入HINT';
new   8:   hint     := 'full(t1@SEL$1) index(t2@SEL$2 PK_DEPT)';

PL/SQL procedure successfully completed.

注意这里的hint: full(t1@SEL$1) index(t2@SEL$2 PK_DEPT)。这就是加入Query Block Name的hint
session 1，查看执行计划是否发生变化：

SQL> /

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2622742753

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |   100 |     5   (0)| 00:00:01 |
|*  1 |  HASH JOIN                   |         |     5 |   100 |     5   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   2 - filter("T2"."LOC"='CHICAGO')

Note
-----
   - SQL profile "5qkuad5rv1kt4" used for this statement


Statistics
----------------------------------------------------------
        645  recursive calls
          0  db block gets
        374  consistent gets
          1  physical reads
          0  redo size
        714  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed

已经绑定了SQL profile “5qkuad5rv1kt4”，而且执行计划按我们加的hint执行了。
此profile文件，查看出来，就是我们刚才加的带有Query Block Name的hint。

SQL> col HINT for a100
SQL> SELECT extractValue(value(h), '.') AS hint
  2    FROM sys.sqlobj$data od,
  3         sys.sqlobj$ so,
  4         table(xmlsequence(extract(xmltype(od.comp_data),
  5                                   '/outline_data/hint'))) h
  6   WHERE so.name = '&profile_name'
  7     AND so.signature = od.signature
  8     AND so.category = od.category
  9     AND so.obj_type = od.obj_type
 10     AND so.plan_id = od.plan_id;
Enter value for profile_name: 5qkuad5rv1kt4
old   6:  WHERE so.name = '&profile_name'
new   6:  WHERE so.name = '5qkuad5rv1kt4'

HINT
----------------------------------------------------------------------------------------------------
full(t1@SEL$1) index(t2@SEL$2 PK_DEPT)

manual绑定最佳实践

默认通过SQL Tuning Advisor创建的SQL Profile，是通过一些修正因子来达到修正执行计划的目的，但是随着数据的变化，时间的推移，这些SQL Profile里包含的修正因子可能也已经不再准确，因此在某些使用了SQL Profile的SQL上可能会发现刚开始这些SQL Profile工作的很好，但是不久就会产生一些问题。虽然SQL Tuning Advisor创建的SQL Profile有着这些缺点，但是用它来为一个SQL产生比优化器更好的执行计划也是一个非常好的事，我本人非常喜欢用SQL Tuning Advisor来优化一个SQL，然后会去查看SQL Profile到底提供了什么建议，然后测试这些建议，如果性能确实有提升，我会考虑接受这些SQL Profile，然后通过本文后面介绍飞方式来锁定这个SQL的执行计划，让SQL Profile达到锁定执行计划的目的。既然SQL Profile也可以使用常见的hint，例如index、full等，那么我们就可以想个办法以常见的hint替换掉修正因子类的hint(OPT_ESTIMATE(@SEL$1, TABLE, TEST@SEL$1, SCALE_ROWS=10)）来达到锁定执行计划的目的。如何把通过SQL Tuning Advisor方式创建的SQL Profile转变为可以直接锁定执行计划的SQL Profile？我们再倒回到本章的第一节：使用SQL Tuning Advisor创建了一个SQL Profile，我们看看如何来锁定这个执行计划。10GR2后，任何SQL解析后，都会在v$sql_plan的other_xml中存储outline需要的hint信息。
主要是manual书写带Query Block Name的hint太复杂了，容易出错，特别是一些复杂的sql。我们刚好可以用autotune的方式来偷梁换柱。
还是用之前的例子。

按照前面介绍的auto的步骤，进行aviser绑定：

SQL> var tuning_task varchar2(100);  
DECLARE  
SQL>   2    l_sql_id v$session.prev_sql_id%TYPE;  
  3    l_tuning_task VARCHAR2(30);  
  4  BEGIN  
  5    l_sql_id:='4zbqykx89yc8v';  
  6    l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
  7    :tuning_task:=l_tuning_task;  
  8    dbms_sqltune.execute_tuning_task(l_tuning_task);  
  9    dbms_output.put_line(l_tuning_task);  
 10  END;  
 11  / 

PL/SQL procedure successfully completed.

SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> set heading off
SQL> print tuning_task;  
SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual; 
TASK_104

SQL> 

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_104
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 03/09/2021 10:05:45
Completed at       : 03/09/2021 10:05:45

-------------------------------------------------------------------------------
Schema Name: SCOTT

SQL ID     : 4zbqykx89yc8v
SQL Text   : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
             and t1.object_id=t2.object_id

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 78.58%)

  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_104',
            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):             .028004           .019622      29.93 %
  CPU Time (s):                 .025571           .019199      24.91 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                     1393               297      78.67 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                    36                36
  Fetches:                           36                36
  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.

2- Alternative Plan Finding
---------------------------
  Some alternative execution plans for this statement were found by searching
  the system's real-time and historical performance data.

  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.


  id plan hash  last seen            elapsed (s)  origin          note
  -- ---------- -------------------- ------------ --------------- ----------------
   1 3787413387  2021-03-09/09:00:36        0.053 AWR
   2 1838229974  2021-03-09/10:01:30        0.186 Cursor Cache    original plan

  Recommendation
  --------------
  - Consider creating a SQL plan baseline for the plan with the best average
    elapsed time.
    execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_104',
            owner_name => 'SYS', plan_hash_value => 3787413387);
................................
...............................
  
 SQL> SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true);

PL/SQL procedure successfully completed.

session 1:

SQL> /

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3787413387

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |    36 |  1476 |   142   (0)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL        | T1     |    36 |  1080 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_017814c013f90002" used for this statement


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        311  consistent gets
          1  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

执行已经变化，sql profile已经绑定。
10GR2后，任何SQL解析后，都会在v$sql_plan的other_xml中存储outline需要的hint信息。执行计划的输出Outline Data部分的数据就是从v$sql_plan的other_xml里取出来的，我们也可以直接通过一些转换函数来把v$sql_plan的other_xml中的值变为我们可以阅读的方式：

查看绑定后sql用到的hint：
SQL> col OUTLINE_HINTS for a100
select extractvalue(value(d), '/hint') as outline_hints
SQL>   2    from xmltable('/*/outline_data/hint' passing
  3                  (select xmltype(other_xml) as xmlval
  4                     from v$sql_plan
  5                    where sql_id = '&sqlid'
  6                      and child_number = 0
  7                      and other_xml is not null)) d;
Enter value for sqlid: 4zbqykx89yc8v
old   5:                   where sql_id = '&sqlid'
new   5:                   where sql_id = '4zbqykx89yc8v'

IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.1.0.5')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
NLJ_PREFETCH(@"SEL$1" "T2"@"SEL$1")

10 rows selected.
查看通过SQL Tuning Advisor创建的SQL Profile使用到的hint
SQL> col HINT for a100
SQL> SELECT extractValue(value(h), '.') AS hint
  2    FROM sys.sqlobj$data od,
  3         sys.sqlobj$ so,
  4         table(xmlsequence(extract(xmltype(od.comp_data),
  5                                   '/outline_data/hint'))) h
  6   WHERE so.name = '&profile_name'
  7     AND so.signature = od.signature
  8     AND so.category = od.category
  9     AND so.obj_type = od.obj_type
 10     AND so.plan_id = od.plan_id;
Enter value for profile_name: SYS_SQLPROF_017814c013f90002
old   6:  WHERE so.name = '&profile_name'
new   6:  WHERE so.name = 'SYS_SQLPROF_017814c013f90002'

HINT
----------------------------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.1.0.5')

因此我们可以在通过SQL Tuning Advisor方式使用SQL Profile 后，通过把other_xml中的hint取出来，replace参数设置为true，然后通过dbms_sqltune.import_sql_profile包把这些hint植入，替换之前由SQL Tuning Advisor产生的SQL Profile，这样就达到了锁定执行计划的目的。

执行如下替换脚本：

declare
  l_profile_name   varchar2(30);
  cl_sql_text      clob;
  ar_profile_hints sys.sqlprof_attr;
begin
  select -----从other_xml里取出hint
   extractvalue(value(d), '/hint') as outline_hints bulk collect
    into ar_profile_hints
    from xmltable('/*/outline_data/hint' passing
                  (select xmltype(other_xml) as xmlval
                     from v$sql_plan
                    where sql_id = 'c37q7z5qjnwwf'
                      and child_number = 0
                      and other_xml is not null)) d;
  select -----取出sql文本
   sql_fulltext
    into cl_sql_text
    from v$sqlarea
   where sql_id = 'c37q7z5qjnwwf';

  select 'profile_' || 'c37q7z5qjnwwf' || '_dwrose'
    into l_profile_name -----构造profile的name
    from dual;

  dbms_sqltune.import_sql_profile(sql_text    => cl_sql_text,
                                  profile     => ar_profile_hints,
                                  category    => '',
                                  name        => l_profile_name,
                                  force_match => FALSE,
                                  replace     => true ------取代之前由SQL Tuning Advisor产生的Profile
                                  );
  dbms_output.put_line(' ');
  dbms_output.put_line('Profile ' || l_profile_name || ' created.');
  dbms_output.put_line(' ');
end;
/

PL/SQL procedure successfully completed.

上面的代码通过包dbms_sqltune的函数import_sql_profile创建了一个SQL Profile，接受的hint为ar_profile_hints对象，这个对象是从other_xml里解析出来的，replace参数设置为了true，代表替换之前由SQL Tuning Advisor产生的SQL Profile。我们看看新创建的SQL Profile是否起作用了：

session1：
SQL> /

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3787413387

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  2500 |   100K|  5071   (1)| 00:01:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |  2500 |   100K|  5071   (1)| 00:01:01 |
|*  3 |    TABLE ACCESS FULL        | T1     |  2500 | 75000 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "profile_4zbqykx89yc8v_dwrose" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        306  consistent gets
          0  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

根据执行计划的Note部分显示，刚才创建的SQL Profile已经起作用了，而且SQL Profile的名字也是按照我们命名的格式。我们来看看后台存储的hint是什么样子的，如果符合预期的话，应该跟other_xml中的hint一致。

SQL> col HINT for a100
SQL> SELECT extractValue(value(h), '.') AS hint
  2    FROM sys.sqlobj$data od,
  3         sys.sqlobj$ so,
  4         table(xmlsequence(extract(xmltype(od.comp_data),
  5                                   '/outline_data/hint'))) h
  6   WHERE so.name = '&profile_name'
  7     AND so.signature = od.signature
  8     AND so.category = od.category
  9     AND so.obj_type = od.obj_type
 10     AND so.plan_id = od.plan_id;
SQL> /
Enter value for profile_name: profile_4zbqykx89yc8v_dwrose
old   6:  WHERE so.name = '&profile_name'
new   6:  WHERE so.name = 'profile_4zbqykx89yc8v_dwrose'

HINT
----------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.1.0.5')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
NLJ_PREFETCH(@"SEL$1" "T2"@"SEL$1")

10 rows selected.

不出所料，SQL Profile里存储的hint与当时我们查询v$sql_plan的other_xml里的hint一致。

这种方式非常棒，即使用了SQL Tuning Advisor的优点，可以为SQL提供非常好的优化建议，又使用到了SQL Profile来锁定执行计划，不用担心修正因子过时导致执行计划改变的问题了。

总结

Query Block–Oracle中的Query Block是指一个语义上完整的查询语句。
Query Block Name–给查询语句定义了一个名字，相当于别名。这个别名我们可以让oracle自动命名(没有加)，自动命名的规则就是"类型$数字"，如果同样类型的Query Block不止一个，则这里的数字就从1开始往上递增。也可以加hint /*+ QB_NAME(zhuozhuo)*/，我们人为自己命名。在Hint中出现的Query Block其格式必须是"@Query Block名称"
Query Block Name专为手工绑定sql profile而生，手工绑定的时候，注意hint的内容，要加入Query Block Name。
执行计划中的Outline Data部分实际上是oracle用来固定执行计划的内部hint组合，它是非常全面的hint组合，考虑到了各个方面的内容，是可以用来完整的固定目标SQL的执行计划的
manual方式sql profile最佳绑定实践：

1）按照前面第一章介绍的，按照SQL Tuning Advisor来创建SQL Profile；
2）执行替换脚本，把不能锁定执行计划的修正因子类的hint，替换为执行计划中outline data里面常见的hint，自动从v$sql_plan里面查找，替换
3）验证执行计划，验证profile里面的hint是否已经被替换
5. 参考
Query Block Name

P10ZHUO

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
oracle固定执行计划3-sql profile manual续-Query Block

oracle固定执行计划3-sql profile manual续-Query BlockQuery BlockQuery Block Name执行计划Outline Data部分manual sql profilemanual绑定最佳实践总结在上一篇中，说明使用manual sql profile的方法绑定执行计划，但是有要求：虽然执行计划的输出Note部分显示已经使用到了SQL Profile，但是执行计划并没有如我们预期一样被改变，依然是全表扫描，查看存储hint的基表也显示索引扫描的hint已经被
复制链接

扫一扫