opt_estimate5

原创张程 2020-08-26

876

原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2019/07/12/opt_estimate-5/
译文如下：

如果您想知道为什么我在opt_estimate（）提示上重新起草笔记，那么几周前我收到一封电子邮件，其中包含一个查询示例，其中几个opt_estimate（）提示根本不起作用。这个例子的关键特性是查询的基本结构是我以前没有研究过的类型。这实际上是一个常见的问题，当你试图从陌生领域中研究任何一个Oracle特性时，你可以花上几天的时间来考虑你应该建模的所有可能的场景，然后当你第一次需要将你的知识应用到一个生产系统时，实际应用需求不在你所考察的每一个模型之中。
不过，在你进一步阅读这篇文章之前，我应该提醒你，这篇文章以失败告终，因为我没有找到解决问题的方法——可能是因为没有解决办法，也可能是因为我不够努力。
所以这里有一个简化版本的问题-它涉及到将一个谓词推入到union all视图中。首先是一些数据和基线查询：

rem
rem     Script:         opt_estimate_3a.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2019
rem
 
create table t1
as
select
        rownum                          id,
        100 * trunc(rownum/100)-1       id2,
        mod(rownum,1e3)                 n1,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        dual
connect by
        rownum <= 1e4   -- > comment to avoid WordPress format issue
;
 
create table t2a pctfree 75 as select * from t1;
create table t2b pctfree 75 as select * from t1;
 
create index t2ai on t2a(id);
create index t2bi on t2b(id);
 
explain plan for
select
        t1.v1,
        t2.flag,
        t2.v1
from
        t1,
        (select 'a' flag, t2a.* from t2a
         union all
         select 'b', t2b.* from t2b
        )       t2
where
        t2.id = t1.n1
and     t1.id = 99
/
 
select * from table(dbms_xplan.display(null,null,'outline alias'))
/

有一行t1.id=99，我希望优化器使用索引访问路径从union all视图中的两个表中的每一个表中选择一个匹配的行。聪明的执行计划将是一个使用“连接谓词推入”的嵌套循环，这正是默认情况下使用此数据集得到的结果：

-----------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |     2 |    96 |    30   (4)| 00:00:01 |
|   1 |  NESTED LOOPS                          |      |     2 |    96 |    30   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                    | T1   |     1 |    19 |    26   (4)| 00:00:01 |
|   3 |   VIEW                                 |      |     1 |    29 |     4   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE          |      |       |       |            |          |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2A  |     1 |    15 |     2   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | T2AI |     1 |       |     1   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2B  |     1 |    15 |     2   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN                  | T2BI |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1        / T1@SEL$1
   3 - SET$5715CE2E / T2@SEL$1
   4 - SET$5715CE2E
   5 - SEL$639F1A6F / T2A@SEL$2
   6 - SEL$639F1A6F / T2A@SEL$2
   7 - SEL$B01C6807 / T2B@SEL$3
   8 - SEL$B01C6807 / T2B@SEL$3
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$639F1A6F" "T2A"@"SEL$2")
      INDEX_RS_ASC(@"SEL$639F1A6F" "T2A"@"SEL$2" ("T2A"."ID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B01C6807" "T2B"@"SEL$3")
      INDEX_RS_ASC(@"SEL$B01C6807" "T2B"@"SEL$3" ("T2B"."ID"))
      USE_NL(@"SEL$1" "T2"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      NO_ACCESS(@"SEL$1" "T2"@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      PUSH_PRED(@"SEL$1" "T2"@"SEL$1" 1)
      OUTLINE_LEAF(@"SET$5715CE2E")
      OUTLINE_LEAF(@"SEL$B01C6807")
      OUTLINE_LEAF(@"SEL$639F1A6F")
      ALL_ROWS
      OPT_PARAM('_nlj_batching_enabled' 0)
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."ID"=99)
   6 - access("T2A"."ID"="T1"."N1")
   8 - access("T2B"."ID"="T1"."N1")

这样做很好——步骤2对t1的表扫描预估为一行，使用嵌套循环连接和UNION ALL连接谓词推入，其中，索引范围扫描t2a_i1和t2b_i1将为每个表提供一行。“Predicate Information”告诉我们，t1.n1连接谓词已被推送到视图中的两个子查询中，因此我们可以看到“t2a.id=t1.n1”和“t2b.id=t1.n1”。

所以，如果我想告诉Oracle，它实际上会在t2a范围扫描和回表后找到5行，在t2b范围扫描和回表后找到7行（可能在一个更复杂的视图中，它会说服Oracle使用两个不同的索引进入该视图，并为接下来访问的几个表更改连接顺序和访问方式）。由于我最近刚写了opt_estimate（）的nlj_index_scan选项，您可能会认为这是我们需要使用的选项–可能类似于：

opt_estimate(@sel$639f1a6f nlj_index_scan,t2a@sel$2 (t1),t2a_i1,scale_rows=5)
opt_estimate(@sel$b01c6807 nlj_index_scan,t2b@sel$3(t1), t2b_i1, scale_rows=7)

您会注意到，通过查看执行计划中的“Query Block Name / Object Alias”部分，我已经非常小心地找到了t2a和t2b的完全限定的别名（如果视图是Oracle使用Concatenation or OR-Expansion的结果出现的，您会发现有两个查询块名看起来相似，但后缀为“_1”和“_2”）。但这不值得努力，也没用。摆弄我能想到的所有可能的变化都没有帮助（也许我应该使用set$5715ce2e作为两个提示的查询块目标–不；如果我…）。

当然，如果我们看一下“Outline Data” ，我们会注意到Outline中的use_nl（）hint是：“USE_NL(@SEL$1 T2@SEL$1)”，所以我们没有在t2a和t2b中做嵌套循环，而是在视图t2中进行了嵌套循环。因此，我决定忘记嵌套循环的主意，只去寻找具有以下提示的索引（您会注意到，在我的实验过程中，我将自己的查询块名称添加到初始查询块中–因此生成的查询块名称发生了更改）：

explain plan for
select
        /*+
                qb_name(main)
                opt_estimate(@sel$f2bf1101, index_scan, t2a@subq_a, t2ai, scale_rows=5)
                opt_estimate(@sel$f2bf1101, table,      t2a@subq_a,       scale_rows=5)
                opt_estimate(@sel$f4e7a233, index_scan, t2b@subq_b, t2bi, scale_rows=7)
                opt_estimate(@sel$f4e7a233, table,      t2b@subq_b,       scale_rows=7)
        */
        t1.v1,
        t2.flag,
        t2.v1
from
        t1,
        (select /*+ qb_name(subq_a) */ 'a' flag, t2a.* from t2a
         union all
         select /*+ qb_name(subq_b) */ 'b', t2b.* from t2b
        )       t2
where
        t2.id = t1.n1
and     t1.id = 99
;
 
select * from table(dbms_xplan.display(null,null,'outline alias'));
 
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |     2 |    96 |    30   (4)| 00:00:01 |
|   1 |  NESTED LOOPS                          |      |     2 |    96 |    30   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                    | T1   |     1 |    19 |    26   (4)| 00:00:01 |
|   3 |   VIEW                                 |      |     1 |    29 |     4   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE          |      |       |       |            |          |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2A  |     5 |    75 |     2   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | T2AI |     5 |       |     1   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2B  |     7 |   105 |     2   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN                  | T2BI |     7 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

好极了——我们得到了我们想要的表的基数——除了view操作符没有保存表基数的总和，并且join也没有将估计值相乘。我找不到一种让视图显示12行的方法（即使是在猜测的情况下，但可能没有实现opt_estimate（view…）提示！）然而，在我的实验过程中，我尝试了一个提示：“opt_estimate(@main, table, t2@main, scale_rows=15)”。这在计划中没有任何可见的效果，但在搜索10053跟踪文件时，我发现了以下几行：

Table Stats::
  Table: from$_subquery$_002  Alias: T2  (NOT ANALYZED)
  #Rows: 20000  SSZ: 0  LGR: 0  #Blks:  37  AvgRowLen:  15.00  NEB: 0  ChainCnt:  0.00  ScanRate:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 9
 
Access path analysis for from$_subquery$_002
    >> Single Tab Card adjusted from 20000.000000 to 300000.000000 due to opt_estimate hint
 
Access path analysis for from$_subquery$_002
    >> Single Tab Card adjusted from 12.000000 to 180.000000 due to opt_estimate hint

所以在代码路径的某个时刻，优化器知道5+7=12，而12*15=180。但这并没有出现在最终的执行计划中。我顺便说一下，您可能会注意到scale_rows = 15不仅被应用于我定位的地方，它还被用于将估计在union all中的20,000行按比例缩放到全部 300,000。对两个表进行tablescan。

可能如果我花更多的时间来处理10053跟踪文件（正如我之前所说，我尽量避免这样做），我可能会准确地找到Oracle所遵循的实现计划的代码路径，并设法调整一些提示以获得我想要的数据。可能优化器已经在跟踪实际生成我想要的数字的代码路径，然后“忘记”使用它们。也许有一天，我会讲述另一个关于这个问题的观点，但是由于我没有试图为客户解决一个问题（并且考虑到还有其他解决方法），我关闭了10053跟踪文件，并将模型放在一边。

脚注

我想到了一个想法，作为一种找出是否有真正解决方案的方法——我为任何想玩游戏的人提供了这个方法：创建第二个数据集，真正生成我想要看到的5和7（并检查视图是否报告了这两个组件的总和）；然后对原始数据运行原始查询，这样就可以得到内存中的执行计划，用新的数据集覆盖原始数据（不改变原始数据的统计信息）。然后使用SQL调优顾问查看它是否为捕获的SQL_ID生成一个SQL概要文件，该概要文件将为第二个数据集重新生成正确的计划，并检查它使用的opt_estimate（）提示。（警告-这可能会成为令人沮丧的时间浪费。）

更新2019年10月

多年来，我一直在说我不喜欢从内存中的执行计划中提取Outline Information并将其作为SQL Profile存储在数据库中的技巧，因为这样可以有效地将SQL Plan Baseline作为SQL Profile存储在那里滥用这两种机制可能会产生微妙的（可能）误导性副作用。在论点的后面，我还观察到，虽然两种机制都存储提示，但SQL Profile的提示与统计信息有关，而SQL Plan Baseline的提示与转换，联接和其他机制有关。

但是。
现在，我已经结束了上面脚注中描述的测试–创建了一个包含数据的表，该表使Oracle为t2a和t2b表选择完整的表扫描，然后更改数据（不更改对象统计信息）并运行SQL Tuning tool，以查看优化器是否会建议我想要的计划并提供profile以生成该计划。

我很成功– Oracle提供了profile，当我查看它（接受之前）时，它看起来像这样：

1 OPT_ESTIMATE(@"SEL$1", TABLE, "T2"@"SEL$1", SCALE_ROWS=200)
1 OPT_ESTIMATE(@"SEL$1", JOIN, ("T2"@"SEL$1", "T1"@"SEL$1"), SCALE_ROWS=15)
1 OPTIMIZER_FEATURES_ENABLE(default)
1 IGNORE_OPTIM_EMBEDDED_HINTS

但是当我接受它并再次查看它时，它看起来像这样：

BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$639F1A6F" "T2A"@"SEL$2")
IGNORE_OPTIM_EMBEDDED_HINTS
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B01C6807" "T2B"@"SEL$3")
INDEX_RS_ASC(@"SEL$B01C6807" "T2B"@"SEL$3" ("T2B"."ID"))
USE_NL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
NO_ACCESS(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "T2"@"SEL$1" 1)
OUTLINE_LEAF(@"SET$5715CE2E")
OUTLINE_LEAF(@"SEL$B01C6807")
OUTLINE_LEAF(@"SEL$639F1A6F")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
INDEX_RS_ASC(@"SEL$639F1A6F" "T2A"@"SEL$2" ("T2A"."ID"))

换句话说，Oracle记录了类似于SQL Plan Baseline的内容，并将其称为SQL Profile。

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

opt_estimate5

脚注

更新2019年10月

评论