原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2019/07/01/opt_estimate-4/
译文如下：
在本系列的上一篇文章中，关于opt_estimate（）提示，我提到了提示的“ query_block”选项。如果您可以在执行计划中识别出一个指定的查询块，成为一个“ outline_leaf”（也许是因为您故意将一个查询块名称赋予一个内联子查询，并对其应用了no_merge（）提示），则可以使用opt_estimate （）提示，告诉优化器该查询块将产生多少行（每次启动时）。提示的语法非常简单：
opt_estimate(@{query block name} query_block rows={number of rows})
与其他提示选项一样，您可以使用scale_rows =，min =，max =作为替代（最后一个似乎在Oracle生成的代码中用于实体化视图刷新），但是最常用的“ rows = N”可能会最受欢迎。实际上，它的作用与cardinality（）提示的“非特定”版本相同-我不时建议将其作为告知优化程序实例化CTE中数据集大小的一种方式（用“子查询”），例如
set serveroutput off
with demo as (
select /*+
qb_name(mat_cte)
materialize
cardinality(@mat_cte 11)
-- opt_estimate(@mat_cte query_block rows=11)
*/
distinct trunc(created) date_list
from all_objects
)
select * from demo
;
select * from table(dbms_xplan.display_cursor);
无论您使用上面的opt_estimate（）还是cardinality（）提示，物化临时表都将被告知返回11行。（请注意，在这种情况下，如果提示位于查询块中，则不必将其应用“ @mat_cte”）。
在前面的文章中，我产生一个脚本调用的一些数据opt_est_gby.sql向您展示的效果GROUP_BY和具有该选项opt_estimate（）提示，并指出有情况下，你可能还需要包括query_block选项，好。这是显示效果的最后一个示例示例，在创建表t2作为t1的副本但设置pctfree 75（使表扫描更昂贵）并在t2（id）上创建索引之后，具有scale_rows功能：
create table t2 pctfree 75 as select * from t1;
create index t2_i1 on t2(id);
select
t2.n1, t1ct
from
t2,
(
select /*+
qb_name(main)
opt_estimate(@main group_by scale_rows=4)
opt_estimate(@main having scale_rows=0.4)
opt_estimate(@main query_block scale_rows=0.5)
*/
mod(n1,10), count(*) t1ct
from t1
group by
mod(n1,10)
having
count(*) > 100
) v1
where
t2.id = v1.t1ct
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 168 | 27 (8)| 00:00:01 |
| 1 | NESTED LOOPS | | 8 | 168 | 27 (8)| 00:00:01 |
| 2 | NESTED LOOPS | | 8 | 168 | 27 (8)| 00:00:01 |
| 3 | VIEW | | 8 | 104 | 10 (10)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | HASH GROUP BY | | 8 | 32 | 10 (10)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 3000 | 12000 | 9 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T2_I1 | 1 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(COUNT(*)>100)
7 - access("T2"."ID"="V1"."T1CT")
我内联了上一篇文章中使用的最后一个查询（带有两个opt_estimate（）提示），并向该内联视图添加了第三个opt_estimate（）提示。在这种情况下，我不必添加no_merge（）提示，因为数字对我有利，但在生产环境中是安全的，这暗示了我应该包括在内。
您可能还记得，hash group by本身导致了200行的预测，而使用having子句时，该预测下降到了10行（标准5％）。有了我的三个opt_estimate（）提示，我应该看到以下算术的效果：
group by 200 * 4 = 800
having 5% of 800 * 0.4 = 16
query block 16 * 0.5 = 8
如您所见，VIEW 操作的基数预测的确为8 –因此，提示的组合已经奏效。遗憾的是，我们在执行计划时看不到算术中的三个独立的步骤。
一个警告
一如既往，我只能重复一遍– HINT并不容易的；通常，“不容易”翻译为“不稳定/不安全”（多亏了弗洛伊德在打字时的一个失误，您可能不知道如何正确地执行它，除非是在最简单的情况下，而且我们也不知道Oracle是如何解释这些提示（尤其是未记录的提示）。下面是一个例子，说明即使是opt_estimate（query_block）提示也会令人费解–通常从一些数据开始：
rem
rem Script: opt_estimate_2.sql
rem Author: Jonathan Lewis
rem Dated: Aug 2017
rem
create table t1
as
select * from all_objects;
create table t2
as
select * from all_objects;
如您所见，我对这个示例（几年前写的）有点懒，它使用all_objects作为方便的数据源。不幸的是，这意味着您不一定能够完全重现我要向您显示的结果，这是我在12.2.2.1的一个小实例上所做的。我将研究一个简单查询的四个版本，
限制从t1开始的行，
在t1的那个子集中找到唯一的object_types集
然后通过object_type与t2连接
select
/*+
qb_name(main)
*/
t2.object_id, t2.object_name, created
from (
select /*+ qb_name(inline) */
distinct object_type
from t1
where
created >= date'2017-03-01'
) v1,
t2
where
t2.object_type = v1.object_type
;
select
/*+
qb_name(main)
merge(@inline)
*/
t2.object_id, t2.object_name, created
from (
select /*+ qb_name(inline) */
distinct object_type
from t1
where
created >= date'2017-03-01'
) v1,
t2
where
t2.object_type = v1.object_type
;
select
/*+
qb_name(main)
opt_estimate(@inline query_block rows=14)
*/
t2.object_id, t2.object_name, created
from (
select /*+ qb_name(inline) */
distinct object_type
from t1
where
created >= date'2017-03-01'
) v1,
t2
where
t2.object_type = v1.object_type
;
select
/*+
qb_name(main)
merge(@inline)
opt_estimate(@inline query_block rows=14)
*/
t2.object_id, t2.object_name, created
from (
select /*+ qb_name(inline) */
distinct object_type
from t1
where
created >= date'2017-03-01'
) v1,
t2
where
t2.object_type = v1.object_type
;
第一个版本是我未公开的基准（在我的情况下，Oracle不使用复杂的视图合并），第二个版本强制内联聚合视图合并复杂的视图，然后查询3和4重复查询1和2，但告诉优化器不重复的object_type值的数量为14（在可能的情况下大约是实际值的一半）。但是在最后一个查询中有一个奇怪之处–我已经告诉优化器应该为内联视图估计多少行，但是我也告诉它摆脱内联视图并将其合并到外部查询块中；那会产生什么影响？我希望该提示必须被忽略，因为它将应用于最终计划中不存在的查询块，从而使其不相关且无法使用。这是四个执行计划：
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61776 | 4464K| 338 (7)| 00:00:01 |
|* 1 | HASH JOIN | | 61776 | 4464K| 338 (7)| 00:00:01 |
| 2 | VIEW | | 27 | 351 | 173 (9)| 00:00:01 |
| 3 | HASH UNIQUE | | 27 | 486 | 173 (9)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 59458 | 1045K| 164 (4)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 61776 | 3680K| 163 (4)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_TYPE"="V1"."OBJECT_TYPE")
4 - filter("CREATED">=TO_DATE(' 2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61776 | 5308K| | 1492 (2)| 00:00:01 |
| 1 | VIEW | VM_NWVW_1 | 61776 | 5308K| | 1492 (2)| 00:00:01 |
| 2 | HASH UNIQUE | | 61776 | 5489K| 6112K| 1492 (2)| 00:00:01 |
|* 3 | HASH JOIN RIGHT SEMI| | 61776 | 5489K| | 330 (5)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T1 | 59458 | 1045K| | 164 (4)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 61776 | 4403K| | 163 (4)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."OBJECT_TYPE"="OBJECT_TYPE")
4 - filter("CREATED">=TO_DATE(' 2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32032 | 2314K| 338 (7)| 00:00:01 |
|* 1 | HASH JOIN | | 32032 | 2314K| 338 (7)| 00:00:01 |
| 2 | VIEW | | 14 | 182 | 173 (9)| 00:00:01 |
| 3 | HASH UNIQUE | | 14 | 252 | 173 (9)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 59458 | 1045K| 164 (4)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 61776 | 3680K| 163 (4)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_TYPE"="V1"."OBJECT_TYPE")
4 - filter("CREATED">=TO_DATE(' 2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1232 | | 1492 (2)| 00:00:01 |
| 1 | VIEW | VM_NWVW_1 | 14 | 1232 | | 1492 (2)| 00:00:01 |
| 2 | HASH UNIQUE | | 14 | 1274 | 6112K| 1492 (2)| 00:00:01 |
|* 3 | HASH JOIN RIGHT SEMI| | 61776 | 5489K| | 330 (5)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T1 | 59458 | 1045K| | 164 (4)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 61776 | 4403K| | 163 (4)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."OBJECT_TYPE"="OBJECT_TYPE")
4 - filter("CREATED">=TO_DATE(' 2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
第一个计划告诉我们，t1中的大多数行都满足created > 1st March 2017，并且object_type有（估计）27个不同的值；t2中有61,776行（与t1基本相同），并且没有任何行被内联视图中的object_type联接所消除。
第二方案（被迫做了复杂视图合并）所示的Oracle由于唯一值数量的影响，改变了执行计划与视图的连接方式，采用半联接T2和T1形成内部视图名称VM_NWVW_1 ，和基数是正确的。
第三个计划表明，我告诉优化器的假设是假定原始内联视图产生14行，并且毫不奇怪，当我们声称我们拥有大约object_type值数量的一半时，联接中行的最终估计值大约是减半。
那么，当我们的提示适用于不再存在的视图时，在第四个计划中会发生什么？我认为优化器应该在合并视图的那一刻就将其视为无关紧要。不幸的是，它似乎已经将提示带入了合并的视图中，并用它对最终基数产生了极其不准确的估计。如果这是一个三表联接，则这是一种错误，可能会使对第三个表的合理的哈希联接变得难以置信的愚蠢的嵌套循环联接。如果您以为自己正在使用opt_estimate（）HINT做一件非常聪明的事情，可能是统计数据的微小变化导致优化器使用了以前从未见过的视图合并策略，并在（例如）一整夜的批处理中生成了灾难性的执行计划，然后“永远”运行。
提示很困难，实际上您的提示必须非常透彻，并确保涵盖所有可能出现的选项。然后，您仍然可能会遇到看起来像是错误的东西（像这样）。
脚注
这是一个封闭的想法：即使您设法告诉优化器确切的内容是查询块中将有多少行要连接到查询中的下一个表，除非您也可以告诉优化器，该数据集中有多少个不同的连接列值。否则您可能会得到一个非常糟糕的计划这意味着您可能还必须学习有关（甚至更多未记录的）column_stats() 提示的所有信息。
