执行计划7：查询块和内联视图

沃趣技术 2020-02-19

1433

译者林锦森 · 沃趣科技数据库技术专家

出品沃趣科技

当我们获取执行计划时，我们的目的一般都是想弄清楚Oracle执行了哪些基本的步骤来生成结果集。值得注意的是，我们原始的文本与Oracle优化后的文本有可能并不是一致的。Oracle会先将语句进行转换，之后再传递给优化引擎。有些时候我们很容易发现这些转换，像执行计划中可能会出现“VIEW”这个操作，实际上语句并不涉及视图。我们需要注意“Query Block”中的细节。

QueryBlocks

Query Blcok是优化的单元，从10g开始Oracle就能很容易的识别执行计划中的查询块。每次你看到语句中的select，insert，update，delete或者merge等关键字时，这些就是查询块的开头，我们可以通过qb_name hint来详细查看。

select /*+ qb_name(main) */
       outer.*
from
       emp outer
where
       outer.sal > (
             select  /*+ qb_name(avg_subq) */
                   avg(inner.sal)
               from
                       emp inner
               where
                       inner.dept_no = outer.dept_no
       )
;
复制

语句中我将查询主体命名为“main”，相关联的子查询为“avg_subq”。如果显式声明名称的情况下，Oracle会生成sel$1，sel$2这种格式的名称，其他类型的会生成del$1，ins$1等。下面是这个查询的执行计划，使用dbms_xplan.display()并且格式选项选择‘+alias+outline’输出的结果，语句并没有任何其他hint。

----------------------------------------------------------------
| Id | Operation  | Name         | Rows  | Bytes | Cost  |
----------------------------------------------------------------
|  0 | SELECT STATEMENT     |         |  1000 | 98000 |   120 |
|* 1 |  HASH JOIN           |         |  1000 | 98000 |   120 |
|  2 |   VIEW               | VW_SQ_1 |     6 |   156 |    84 |
|  3 |    HASH GROUP BY     |         |     6 |    48 |    84 |
|  4 |     TABLE ACCESS FULL| EMP     | 20000 |   156K|    35 |
|  5 |   TABLE ACCESS FULL  | EMP     | 20000 |  1406K|    35 |
----------------------------------------------------------------

QueryBlock Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1- SEL$C7CDAD1E
   2- SEL$11FCF3E2 / VW_SQ_1@SEL$EF633D71
   3- SEL$11FCF3E2
   4- SEL$11FCF3E2 / INNER@AVG_SUBQ
   5- SEL$C7CDAD1E / OUTER@MAIN

OutlineData
-------------
 /*+
     BEGIN_OUTLINE_DATA
     USE_HASH_AGGREGATION(@"SEL$11FCF3E2")
     FULL(@"SEL$11FCF3E2" "INNER"@"AVG_SUBQ")
     USE_HASH(@"SEL$C7CDAD1E" "OUTER"@"MAIN")
     LEADING(@"SEL$C7CDAD1E" "VW_SQ_1"@"SEL$EF633D71""OUTER"@"MAIN")
     FULL(@"SEL$C7CDAD1E" "OUTER"@"MAIN")
     NO_ACCESS(@"SEL$C7CDAD1E""VW_SQ_1"@"SEL$EF633D71")
     OUTLINE(@"MAIN")
     OUTLINE(@"SEL$EF633D71")
     OUTLINE(@"AVG_SUBQ")
     UNNEST(@"AVG_SUBQ")
     OUTLINE_LEAF(@"SEL$C7CDAD1E")
     OUTLINE_LEAF(@"SEL$11FCF3E2")
     ALL_ROWS
     OPT_PARAM('_optimizer_cost_model' 'io')
     DB_VERSION('11.2.0.4')
     OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
     IGNORE_OPTIM_EMBEDDED_HINTS
     END_OUTLINE_DATA
  */

复制

这个例子中我们可以发现以下几点：第1行告诉我们第2行和第5行之间使用hash join，但是第2行是VIEW操作，语句中并没有引用这个VIEW，事实上这个视图根本不存在数据库中。这是个在查询转换过程中Oracle生成内部视图之后使用非合并视图优化的例子。实际上优化器使用以下文本重写了这个查询：

select /*+ qb_name(main) */
       outer.*
from
       (
         select /*+ qb_name(avg_subq) */
                inner.dept_no, avg(inner.sal) avg_sal
         from
               emp inner
         group by
               inner.dept_no
       )      vw_sq_1,
       emp     outer
where
       outer.sal     > vw_sq_1.avg_sal
and    outer.dept_no = vw_sq_1.dept_no
;
复制

这个重写与原来的计划完全相同，只是有一点不同，即我显式给出的内联视图的名称没有出现在执行计划中。

优化器决定不将内联视图(无论是生成的还是显式声明的)合并到主查询中以生成单个连接，因此它分别对其进行了优化。除了VIEW操作之外我们还能通过两个地方找到线索—第一个是”Query Block Name/Object Alias”部分，它告诉我们第2行是查询块SEL$11FCF3E2的一个部分，之后我们看“Outline Data”部分，可以看到查询块SEL$11FCF3E2是一个“outline_lead”，换句话说，它是一个“最终”查询块，实际上已经被独立优化了。

另一个可以从计划中看到的细节就是，表EMP的两种不同的标识。第4行和第5行都表示对表EMP的扫描，但是哪个EMP来自查询的哪个部分呢？”Query Block Name/Object Alias”部分告诉我们第4行中的EMP表在查询块”avg_subq”中的别名为”inner”，而第5行中的EMP在查询块“main”中的别名为”outer”。在本例中，我们可能很快就猜到了这一点，但是当您查看包含对fnd_code_table的多个引用的Oracle财务报表时，情况就变得更加复杂了!

还有一个我们可以想到的问题就是Oracle生成的复杂的查询块的名字是每次都相同的吗？在不同的版本下，相同的查询可能也会生成相同的执行计划，但是查询块的名称却不是。Oracle会使用hash函数生成查询块的名称。

MultipleTransformations

我将进一步推动这个查询，通过hint使计划执行另一个路径。将子查询展开到内联聚合视图之后，优化器可能会决定在聚合之前使用“复杂视图合并”来连接EMP的两个副本。我可以通过在代码中添加/*+ merge */ hint来模拟这一点。子查询中添加提示来获得：

select/*+ qb_name(avg_subq) merge */
复制

或者在查询主体中添加hint：

select/*+ qb_name(main) merge(@avg_subq)*/
复制

特别要注意，我前面使用“@”符号将hint指向特定的查询块。

---------------------------------------------------------------------
| Id | Operation            | Name | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT     |      |  3333K|   254M|       |   953K|
|* 1 |  FILTER              |      |       |       |       |       |
|  2 |   HASH GROUP BY      |      |  3333K|   254M|  6127M|   953K|
|* 3 |    HASH JOIN         |      |    66M|  5086M|       |  6749 |
|  4 |     TABLE ACCESS FULL| EMP  | 20000 |   156K|       |    35 |
|  5 |     TABLE ACCESS FULL| EMP  | 20000 |  1406K|       |    35 |
---------------------------------------------------------------------

QueryBlock Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1- SEL$A36D7A23
   4- SEL$A36D7A23 / INNER@AVG_SUBQ
   5- SEL$A36D7A23 / OUTER@MAIN

PredicateInformation (identified by operation id):
---------------------------------------------------
   1-filter("OUTER"."SAL">AVG("INNER"."SAL"))
   3- access("INNER"."DEPT_NO"="OUTER"."DEPT_NO")

复制

值得注意的是，由于计划的变化，结果集的期望基数也发生了变化。这是不应该发生的，这也是优化器的一个缺陷，原理上来说，不同的路径通过优化器应该产生相同的基数估算（即使cost可能改变）。

还有几个需要指出的点是（a）最后的查询块的名称改变了，（b）执行计划中没有VIEW操作，优化器已经将整个查询压缩为一个查询块并进行了优化，（c）由于使用了查询块名，我们仍然可以看到两个EMP表的来源。

ANSI Headache

最后，我不得不提到Oracle中ANSI SQL的一个烦人的特性。说的简单点，优化器并不喜欢ANSI，并且会在优化前将ANSI转换为同等的Oracle的格式。这使得使用查询块名称变得复杂。下面是个例子，一个很简单的查询：

select
    /*+ qb_name(main) */
     *
from
    t1
join
    t2
on  t2.t2_n1 = t1.t1_n2
join
    t3
on  t3.t3_n1 = t2.t2_n2
join
    t4
on  t4.t4_n1 = t3.t3_n2
;

-----------------------------------------------------------------------------
| Id | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0| SELECT STATEMENT     |      |   192K|   140M|    56  (15)| 00:00:01 |
|*  1|  HASH JOIN           |      |   192K|   140M|    56  (15)| 00:00:01 |
|   2|   TABLE ACCESS FULL  | T4   |  3000 |   562K|    12   (0)| 00:00:01 |
|*  3|   HASH JOIN          |      | 48000 |    26M|    39   (8)| 00:00:01 |
|   4|    TABLE ACCESS FULL | T3   |  3000 |   562K|    12   (0)| 00:00:01 |
|*  5|    HASH JOIN         |      | 12000 |  4488K|    25   (4)| 00:00:01 |
|   6|     TABLE ACCESS FULL| T1   |  3000 |   562K|    12   (0)| 00:00:01 |
|   7|     TABLE ACCESS FULL| T2   |  3000 |   559K|    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------
复制

执行计划中的步骤都在意料之中，四张表的连接都是合理的，但是”Query Block Name/Object Alias”部分存在异常。我们在原始文本中有一个查询块，所有4个表都在这个查询块中，检查一下语法，确认hint qb_name()的使用是正确的，但是Oracle是这样看的：

QueryBlock Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1- SEL$43767242
   2- SEL$43767242 / T4@SEL$3
   4- SEL$43767242 / T3@SEL$2
   6- SEL$43767242 / T1@SEL$1
   7- SEL$43767242 / T2@SEL$1
复制

我们并没有一个名称为”main”的查询块，我们只有3个独立的查询块，缺省的名称为sel$1，sel$2和sel$3，而且我们并不是四张表一起连接，而是3次两张表之间的连接。事实上在”outline”部分可以看到，“main”查询块是在合并后被使用的。Oracle解析ANSI并将它转换为如下的查询：

select    /*+ qb_name(main) */
    *
from(
    select    /*+ qb_name(sel$3) */
          *
    from
          (
          select    /*+ qb_name(sel$2) */
               *
          from (
               select
                    /*+ qb_name(sel$1) */
                    *
               from
                    t1,
                    t2
               where t2.t2_n1 = t1.t1_n2
               ) v1,
               t3
         where t3.t3_n1 = v1.t2_n2
         )    v2,
          t4
    where t4.t4_n1 = v2.t3_n2
    )
;
复制

在复杂的情况下，这种类型的初步重写会使您在尝试强制执行某个查询的特定执行计划时，更加难以确定如何注入所需的hint。

Summary notes

在整个查询中使用qb_name hint来为组成查询的查询块提供显式名称是一个好主意。当查询在内部被优化器转换时，这种命名使得将转换后的计划中的表的位置与它们在原始文本中的位置关联起来变得更加容易——如果同一个表在一个查询中使用了多次，那么这种命名就特别有用。但是有时候即使是最简单的ANSI SQL语句，Oracle会生成一些自定义名称的查询块使结果变得混淆。

如果在执行计划中看到VIEW操作，这代表SQL语句中有多个各自优化过的查询块，有时这也代表SQL的结果集会在查询计划发生前在内存中已经完全构建好了。你也可能在执行计划中的VIEW操作与输出中OUTLINE_LEAF()部分关联起来。

| 译者简介

执行计划7：查询块和内联视图

林锦森·沃趣科技数据库技术专家

沃趣科技数据库工程师，多年从事Oracle数据库，较丰富的故障处理、性能调优、数据迁移及备份恢复经验。

评论

相关阅读