原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2019/06/25/opt_estimate-2/
译文如下：
这是一个初始示例的后续说明，该示例使用opt_estimate（）HINT来操纵优化器对它将访问多少数据的统计理解以及（隐式地）这将对资源使用产生多大影响。相反，两年后，这里是第二部分-关于使用opt_estimate（）和嵌套循环连接。像往常一样，我将从一些数据集开始：
rem
rem Script: opt_est_nlj.sql
rem Author: Jonathan Lewis
rem Dated: Aug 2017
rem
create table t1
as
select
trunc((rownum-1)/15) n1,
trunc((rownum-1)/15) n2,
rpad(rownum,180) v1
from dual
connect by
level <= 3000 --> hint to avoid wordpress format issue
;
create table t2
pctfree 75
as
select
mod(rownum,200) n1,
mod(rownum,200) n2,
rpad(rownum,180) v1
from dual
connect by
level <= 3000 --> hint to avoid wordpress format issue
;
create index t1_i1 on t1(n1);
create index t2_i1 on t2(n1);
每个表中有3000行，n1和n2列中的每个列都有200个不同的值。不过，两个表之间有一个重要的区别，因为给定值的行在t1中被很好地分组聚集，在t2中被广泛的分散。我将在这两个表之间执行一个连接查询，最终强制使用一个非常糟糕的访问路径，这样我就可以显示一些opt_estimate（）提示，这将对COST和CARDINALITY计算产生影响。以下是我的起始查询，带有未连接的执行计划（除了查询块名提示）：
select
/*+ qb_name(main) */
t1.v1, t2.v1
from t1, t2
where
t1.n1 = 15
and t2.n1 = t1.n2
;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 225 | 83700 | 44 (3)| 00:00:01 |
|* 1 | HASH JOIN | | 225 | 83700 | 44 (3)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15 | 2805 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 3000 | 541K| 42 (3)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."N1"="T1"."N2")
3 - access("T1"."N1"=15)
您会注意到，全表扫描和HASH连接使用t2作为探测（第二个）表，总成本开销为44，这主要是由于t2的全表扫描开销（我特意用pctfree75定义了这个值，以使全表扫描有点昂贵）。让我们提示查询执行从t1到t2的嵌套循环，以了解为什么哈希联接优于嵌套循环：
alter session set "_nlj_batching_enabled"=0;
select
/*+
qb_name(main)
leading(t1 t2)
use_nl(t2)
index(t2)
no_nlj_prefetch(t2)
*/
t1.v1, t2.v1
from t1, t2
where
t1.n1 = 15
and t2.n1 = t1.n2
;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 225 | 83700 | 242 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 225 | 83700 | 242 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15 | 2805 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 15 | 2775 | 16 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 15 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N1"=15)
5 - access("T2"."N1"="T1"."N2")
我在这里做了两件奇怪的事情——我设置了一个隐藏参数来禁用nlj batching，并且使用了一个HINT来阻止nlj prefetching。这并没有改变优化器使用的算法，但它确实意味着嵌套循环的表示回到了9i之前的原始形式，这使得在计划中更容易看到COST和CARDINALITY的增加。我不在生产系统中这样做。
如您所见，此计划的总成本为242，而大部分成本是由于对t2的索引访问。优化器正确估计t2的每次探查连接将获得15行，这15行将分散在15个块中，因此连接CARDINALITY为15*15=255，成本为：2（t1成本）+（15（t1行）*16（t2单位成本））=242。
所以让我们告诉优化器，它对索引范围扫描的估计基数是错误的。
select
/*+
qb_name(main)
leading(t1 t2)
use_nl(t2)
index(t2)
no_nlj_prefetch(t2)
opt_estimate(@main nlj_index_scan, t2@main (t1), t2_i1, scale_rows=0.06)
*/
t1.v1, t2.v1
from t1, t2
where
t1.n1 = 15
and t2.n1 = t1.n2
;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 225 | 83700 | 32 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 225 | 83700 | 32 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15 | 2805 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 15 | 2775 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N1"=15)
5 - access("T2"."N1"="T1"."N2")
我使用如下 hint opt_estimate(@main nlj_index_scan, t2@main (t1), t2_i1, scale_rows=0.06).
形式为: (@qb_name nlj_index_scan, target_table_alias (list of possible driving tables), target_index, numeric_adjustment).
numeric_adjustment可以是rows=nnn，也可以是scale_rows=nnn；目标索引必须按名称而不是列列表指定，并且可能的驱动表列表应该是完全限定的表别名的逗号分隔列表。有一个类似的nlj_index_filter选项，在这篇文章中我无法演示，因为它可能需要至少两个列的索引才能使用。
在这个计划中需要注意的是：操作5的索引范围扫描现在的基数（行）估计值为1（即0.06*原来的15）。这并没有改变范围扫描的成本（因为在我们应用opt_estimate（）提示之前，这个成本已经是1），但是，由于表访问的成本取决于索引选择性，表访问的成本降低到2（从16）。另一方面，表的基数没有下降，所以现在它与索引范围扫描预测的rowid数量不一致。
不过，查询的总开销降到了32，即：2（t1成本）+（15（t1行）*2（t2单位成本）。
让我们尝试调整优化器对我们从表中获取的行数的预测。与其一直保持与索引范围扫描的一致性，我将指定一个缩放因子，以便更容易地看到效果–让我们告诉优化器，我们将得到最初预期行的五分之一（即3）。
select
/*+
qb_name(main)
leading(t1 t2)
use_nl(t2)
index(t2)
no_nlj_prefetch(t2)
opt_estimate(@main nlj_index_scan, t2@main (t1), t2_i1, scale_rows=0.06)
opt_estimate(@main table , t2@main , scale_rows=0.20)
*/
t1.v1, t2.v1
from t1, t2
where
t1.n1 = 15
and t2.n1 = t1.n2
;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47 | 17484 | 32 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 47 | 17484 | 32 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15 | 2805 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 3 | 555 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N1"=15)
5 - access("T2"."N1"="T1"."N2")
通过添加提示opt_estimate(@main table, t2@main, scale_rows=0.20)我们已经告诉优化器，它应该将估计的行计数从它计算的任何值中减少5倍。请记住，在更复杂的查询中，优化器可能决定遵循我们预期的路径，并且每当访问t2时，都将应用系数0.2。
请注意，在这个计划中，操作1处的联接基数也从225降到47—如果优化器被告知其基数（或选择性）计算对于表是错误的，则参与选择性的数字将在整个计划中继续执行，从而为联接基数计算生成一个不同的“调整后的NDV”。
但是请注意，查询的总开销没有改变。成本由优化器对索引范围扫描后要访问的表块数量的估计决定。表块的估计数量没有改变，只是我们现在正在对行数做调整。
为了完成这一点，让我们做最后一个更改（同样，在更复杂的查询中可能需要这样做），让我们修复连接基数：
select
/*+
qb_name(main)
leading(t1 t2)
use_nl(t2)
index(t2)
no_nlj_prefetch(t2)
opt_estimate(@main nlj_index_scan, t2@main (t1), t2_i1, scale_rows=0.06)
opt_estimate(@main table , t2@main , scale_rows=0.20)
opt_estimate(@main join(t2 t1) , scale_rows=0.5)
*/
t1.v1, t2.v1
from t1, t2
where
t1.n1 = 15
and t2.n1 = t1.n2
;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 8556 | 32 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 23 | 8556 | 32 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15 | 2805 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 2 | 370 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N1"=15)
5 - access("T2"."N1"="T1"."N2")
我使用了提示opt_-estimate（@main join（t2 t1），scale_rows=0.5）告诉优化器将其对t1和t2之间的连接基数的估计值减半（无论它们以什么顺序出现）。有了先前的提示，估计值已经下降到47（这肯定是46，而且是一个很大的数字），有了这个最后的提示，现在已经下降到23。有趣的是，对t2的表访问的基数估计值同时下降了（几乎就像优化器通过调整联接中第二个表的选择性来“合理化”联接基数一样，这是我将来可能会考虑的问题，但可能需要读取10053跟踪事件，我倾向于避免这样做）。
另外：如果您有权访问MoS，您会发现Doc ID:2402821.1“How to Use Optimizer Hints to Specify Cardinality For Join Operation”（如何使用优化器提示来指定联接操作的基数），似乎暗示Cardinality（）提示用于单表基数，并暗示opt_estimate（Join）选项用于两个表联接。实际上，这两个提示都可以用来设置多表联接的基数（其中“multi”可以大于2）。
最后，让我们消除强制join order和join方法的提示，看看如果只包含opt_estimate（）提示（以及qb_name（）和no_nlj_prefetch提示，请记住我们禁用了“nlj batching”）。
select
/*+
qb_name(main)
no_nlj_prefetch(t2)
opt_estimate(@main nlj_index_scan, t2@main (t1), t2_i1, scale_rows=0.06)
opt_estimate(@main table , t2@main , scale_rows=0.20)
opt_estimate(@main join(t2 t1) , scale_rows=0.5)
*/
t1.v1, t2.v1
from t1, t2
where
t1.n1 = 15
and t2.n1 = t1.n2
;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 8556 | 32 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 23 | 8556 | 32 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15 | 2805 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 2 | 370 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N1"=15)
5 - access("T2"."N1"="T1"."N2")
Note
-----
- this is an adaptive plan
通过对优化器估计值进行一些工程设计，我们成功地说服Oracle使用了与默认选择不同的执行路径。不过，请注意结束注释部分（在所有其他示例中都没有出现）：我让Oracle可以选择在查询运行时检查实际的统计信息，因此，如果我运行查询两次，Oracle可能会发现算法都是错误的，并抛出一些SQL计划指令—这只是另一个opt_estimate（）提示的加载。
事实上，在这个例子中，当我们应用nlj_ind_scan修复后，我们想要的计划就变得很理想了，因为这使得t2中的索引探测的估计成本足够低（尽管它为表行留下了不一致的基数），Oracle仅在这个基础上就可以从默认的HASH连接切换到嵌套循环。
结束语:
正如我在上一篇文章中指出的，这只是触及opt_estimate（）提示如何工作的表面，即使使用非常简单的查询，也很难判断我们看到的任何行为是否真的在做我们认为它在做的事情。在第三篇文章中，我将研究最近收到的一封关于opt_estimate（）的电子邮件所提示的内容，即在存在内联视图和转换（如合并或推送谓词）时，它可能（或可能不会）的行为。我尽量不花2年时间出版。
