E-rows和A-rows
原文作者：Jonathan lewis
原文地址： https://jonathanlewis.wordpress.com/2019/12/04/e-rows-a-rows-2/
初稿2019年12月
这个提示是由我昨天在UKOUG TechFest19上犯的一个错误引起的。众所周知，当您读取一个包含rowsource执行统计信息的执行计划时，您将得到报告的E-rows(估计值)和A-rows(实际值)，然后，对优化器的计算质量的一个明智的检查是比较估计和实际情况，考虑到E-rows是“每个开始”，而A-rows是“累积的”，所以A-rows = E-rows * Starts.
我昨天犯的错误是忘记了这种关系并不总是正确的。特别是，分区和并行查询需要在读取数字时具有一定的灵活性，我将用运行在12.2.0.1下的几个简单示例来说明这一点。
rem rem Script: estimate_actual.sql rem Author: Jonathan Lewis rem Dated: Nov 2019 rem create table pt_composite_1 ( id, grp, small_vc, padding ) nologging partition by range(id) subpartition by hash (grp) subpartitions 4 ( partition p1 values less than ( 4000), partition p2 values less than ( 8000), partition p3 values less than ( 16000), partition p4 values less than ( 32000), partition p5 values less than ( 64000), partition p6 values less than (128000) ) as with generator as ( select rownum id from dual connect by level <= 1e4 -- > comment to avoid WordPress format issue ) select rownum id, trunc(rownum/100) grp, cast(to_char(trunc(rownum/20)) as varchar2(10)) small_vc, cast(rpad('x',100) as varchar2(100)) padding from generator g1, generator g2 where rownum <= 1e5 -- > comment to avoid WordPress format issue / create table t3 nologging pctfree 80 storage (initial 1M next 1M) as select * from pt_composite_1 /复制
我所做的就是创建两个表，每个表有100,000行——现在我要计算行数，看看我从启用了rowsource执行统计的执行计划中得到了什么:
set serveroutput off alter session set statistics_level = all; prompt ================= prompt Partition effects prompt ================= select count(id) from pt_composite_1; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); prompt ================ prompt Parallel effects prompt ================ select /*+ parallel (t3 4) */ count(id) from t3; select * from table(dbms_xplan.display_cursor(null,null,'allstats'))复制
稍微整理一下，下面是两个执行计划(注意，我在报告并行计划时没有使用“last”格式选项:
================= Partition effects ================= select count(id) from pt_composite_1 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 1866 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 1866 | | 2 | PARTITION RANGE ALL| | 1 | 100K| 100K|00:00:00.04 | 1866 | | 3 | PARTITION HASH ALL| | 6 | 100K| 100K|00:00:00.04 | 1866 | | 4 | TABLE ACCESS FULL| PT_COMPOSITE_1 | 24 | 100K| 100K|00:00:00.04 | 1866 | ------------------------------------------------------------------------------------------------- ================ Parallel effects ================ select /*+ parallel (t3 4) */ count(id) from t3 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 20 | 0 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 20 | 0 | | 2 | PX COORDINATOR | | 1 | | 4 |00:00:00.04 | 20 | 0 | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | 4 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:00.11 | 8424 | 7692 | | 5 | PX BLOCK ITERATOR | | 4 | 100K| 100K|00:00:00.11 | 8424 | 7692 | |* 6 | TABLE ACCESS FULL| T3 | 61 | 100K| 100K|00:00:00.06 | 8424 | 7692 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 6 - access(:Z>=:Z AND :Z<=:Z)复制
如您所见，指定分区选择的行报告整个表的E-Rows，而不是任何分区级别的近似值，因此对于操作3和4，我们不应该将E-Rows与A-rows相乘（对于操作3，Starts= 6，因为我们有6个分区;对于操作4，Starts= 24，因为在最低级别上，我们总共有24个数据段）
对于并行查询，我们看到了相同的模式——每个并行的从属服务器报告预期的总行数，每个“块迭代器”(rowid范围)报告预期的总行数。我们再次看到，使用乘法来比较E-Rows与A-rows是无效的。
事实上，不仅仅是分区和并行会引起混淆。即使是像串行嵌套循环联接这样简单的事情也会带来一些惊喜(很大程度上要感谢机制的发展——不需要对执行计划进行匹配调整)。下面是生成两个表的脚本，然后我们将联接这些表，以提示嵌套循环的各种机制。
create table t1 as select rownum id, mod(rownum,100) n1, cast(lpad(rownum,20) as varchar2(20)) v1 from dual connect by level <= 1000 -- > comment to avoid WordPress format issue ; create table t2 as select * from t1 union all select * from t1 union all select * from t1 ; create index t2_i1 on t2(id);复制
这不是一个微妙的测试——正如你所看到的，表t2中的3行对应t1中的每一行。我们选一些t1表的行，id连接到t2，同样是12.2.0.1：
set serveroutput off alter session set statistics_level = all; prompt ============================== prompt Nested loop join (traditional) prompt ============================== select /*+ leading(t1 t2) use_nl_with_index(t2 t2_i1) opt_param('_nlj_batching_enabled', 0) no_nlj_prefetch(t2) */ t1.v1, t2.v1 from t1, t2 where t1.n1 = 4 and t2.id = t1.id ; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); prompt ============================== prompt Nested loop join with prefetch prompt ============================== select /*+ leading(t1 t2) use_nl_with_index(t2 t2_i1) nlj_prefetch(t2) */ t1.v1, t2.v1 from t1, t2 where t1.n1 = 4 and t2.id = t1.id ; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); prompt ============================== prompt Nested loop join with batching prompt ============================== select /*+ leading(t1 t2) use_nl_with_index(t2 t2_i1) nlj_batching(t2) */ t1.v1, t2.v1 from t1, t2 where t1.n1 = 4 and t2.id = t1.id ; select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));复制
以下是三个计划，每个计划后面都有一些注释——所有三个查询都返回相同的30 * — 10*3 行。
============================== Nested loop join (traditional) ============================== -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 60 | | 1 | NESTED LOOPS | | 1 | 30 | 30 |00:00:00.01 | 60 | |* 2 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 15 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 10 | 3 | 30 |00:00:00.01 | 45 | |* 4 | INDEX RANGE SCAN | T2_I1 | 10 | 3 | 30 |00:00:00.01 | 15 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T1"."N1"=4) 4 - access("T2"."ID"="T1"."ID")复制
这是最初的嵌套循环结构(除了12c中出现的“batched”选项外)，遵循以下规则/指导方针:
·操作2操作一次并返回预测的10行。
·操作3由操作1启动10次，每次启动预测3行—实际结果为30行。
·操作4被启动10次(每次启动操作3一次)，每次启动都有3个行id——实际的行数是30行
============================== Nested loop join with prefetch ============================== ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 3 | 30 |00:00:00.01 | 60 | | 2 | NESTED LOOPS | | 1 | 30 | 30 |00:00:00.01 | 30 | |* 3 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 15 | |* 4 | INDEX RANGE SCAN | T2_I1 | 10 | 3 | 30 |00:00:00.01 | 15 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."N1"=4) 4 - access("T2"."ID"="T1"."ID")复制
同样是按行源生成的顺序
·操作3只启动一次，预测10行，规则生效。
·操作4由操作2启动10次，每次启动预测3行(rowids)，规则生效。
·操作2由操作1启动一次，并带有30行(rowids)的预测，规则生效。
·操作1只启动一次，但是预测被报告为您将从原始NLJ形状得到的值，并且打破了规则。
============================== Nested loop join with batching ============================== ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 60 | | 1 | NESTED LOOPS | | 1 | 30 | 30 |00:00:00.01 | 60 | | 2 | NESTED LOOPS | | 1 | 30 | 30 |00:00:00.01 | 30 | |* 3 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 15 | |* 4 | INDEX RANGE SCAN | T2_I1 | 10 | 3 | 30 |00:00:00.01 | 15 | | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 30 | 3 | 30 |00:00:00.01 | 30 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."N1"=4) 4 - access("T2"."ID"="T1"."ID")复制
按创建行源的顺序
·操作3只启动一次，预测10行—并且a行与规则匹配
·操作4由opreation 2启动10次，每次启动预测3行，并且a行符合规则。
·操作5由操作1启动30次，每次启动预测3行—再次报告您将从NLJ的原始表示中看到的值，预测显然应该是1，因此规则再次被打破
结论：
重要的是要记住，“A-rows = started * E-rows”的基本规则不适用于分区和并行执行计划的分区相关行或与PX相关的行。
您可能还会发现其他一些情况，在没有仔细考虑计划的形式所告诉您的机制之前，您需要对信任规则保持谨慎。
原文：
Oracle Scratchpad
December 4, 2019
E-rows / A-rows
Filed under: Execution plans,Oracle — Jonathan Lewis @ 1:17 pm GMT Dec 4,2019
This note was prompted by an error I made at the UKOUG TechFest19 yesterday. It’s fairly well-known that when you read an execution plan that includes the rowsource execution stats – so you get the E-rows (estimated) and A-rows (Actual) reported – then a sensible check of the quality of the optimizer’s calculations is to compare the estimates and actuals allowing for the fact that the E-rows is “per start” and the A-rows is “cumulative”, so A-rows = E-rows * Starts.
The error I made yesterday was to forget that this relationship isn’t always true. In particular partitioning and parallel query introduced the need to be a little flexibility in reading the numbers – which I’ll demonstrate with a coupld of simple examples running under 12.2.0.1
rem rem Script: estimate_actual.sql rem Author: Jonathan Lewis rem Dated: Nov 2019 rem create table pt_composite_1 ( id, grp, small_vc, padding ) nologging partition by range(id) subpartition by hash (grp) subpartitions 4 ( partition p1 values less than ( 4000), partition p2 values less than ( 8000), partition p3 values less than ( 16000), partition p4 values less than ( 32000), partition p5 values less than ( 64000), partition p6 values less than (128000) ) as with generator as ( select rownum id from dual connect by level <= 1e4 -- > comment to avoid WordPress format issue ) select rownum id, trunc(rownum/100) grp, cast(to_char(trunc(rownum/20)) as varchar2(10)) small_vc, cast(rpad('x',100) as varchar2(100)) padding from generator g1, generator g2 where rownum <= 1e5 -- > comment to avoid WordPress format issue / create table t3 nologging pctfree 80 storage (initial 1M next 1M) as select * from pt_composite_1 /复制
All I’ve done is create a couple of tables with 100,000 rows each – and now I’m going to count the rows and see what I get from the execution plans with rowsource execution stats enabled:
set serveroutput off alter session set statistics_level = all; prompt ================= prompt Partition effects prompt ================= select count(id) from pt_composite_1; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); prompt ================ prompt Parallel effects prompt ================ select /*+ parallel (t3 4) */ count(id) from t3; select * from table(dbms_xplan.display_cursor(null,null,'allstats'));复制
With a little cosmetic tidying, here are the two execution plans (note that I haven’t used the “last” format option when reporting the parallel plan:
================= Partition effects ================= select count(id) from pt_composite_1 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 1866 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 1866 | | 2 | PARTITION RANGE ALL| | 1 | 100K| 100K|00:00:00.04 | 1866 | | 3 | PARTITION HASH ALL| | 6 | 100K| 100K|00:00:00.04 | 1866 | | 4 | TABLE ACCESS FULL| PT_COMPOSITE_1 | 24 | 100K| 100K|00:00:00.04 | 1866 | ------------------------------------------------------------------------------------------------- ================ Parallel effects ================ select /*+ parallel (t3 4) */ count(id) from t3 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 20 | 0 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 20 | 0 | | 2 | PX COORDINATOR | | 1 | | 4 |00:00:00.04 | 20 | 0 | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | 4 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:00.11 | 8424 | 7692 | | 5 | PX BLOCK ITERATOR | | 4 | 100K| 100K|00:00:00.11 | 8424 | 7692 | |* 6 | TABLE ACCESS FULL| T3 | 61 | 100K| 100K|00:00:00.06 | 8424 | 7692 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 6 - access(:Z>=:Z AND :Z<=:Z)复制
As you can see, the lines specifying partition selection report E-Rows for the whole table, not for any partition-level approximation, so for operations 3 and 4 we shouldn’t multiply Starts by E-rows to compare with A-row. (Starts = 6 for operation 3 because we have 6 partitions, and Start = 24 for operation 4 because at the lowest level we have a total of 24 data segments).
For the parallel query we see the same pattern – every parallel slave reports the expected total number of rows, and every “block iterator” (rowid range) reports the expected total number of rows. Again we see that using multiplication to compare E-rows and A-rows would not be valid.
In fact it’s not just partitioning and parallelism that can cause confusion. Even something as simple as a serial nested loop join has a couple of surprises (largely thanks to the evolution of the mechanics – without a matching adjustment to the execution plans – over time). Here’s a script to generate a couple of tables, which we will then join – hinting various mechanisms for the nested loop.
create table t1 as select rownum id, mod(rownum,100) n1, cast(lpad(rownum,20) as varchar2(20)) v1 from dual connect by level <= 1000 -- > comment to avoid WordPress format issue ; create table t2 as select * from t1 union all select * from t1 union all select * from t1 ; create index t2_i1 on t2(id);复制
It’s not a subtle test – as you can see we have 3 rows in table t2 for every row in t1. So let’s pick some t1 rows and join to t2 on id. Again it’s 12.2.0.1:
set serveroutput off alter session set statistics_level = all; prompt ============================== prompt Nested loop join (traditional) prompt ============================== select /*+ leading(t1 t2) use_nl_with_index(t2 t2_i1) opt_param('_nlj_batching_enabled', 0) no_nlj_prefetch(t2) */ t1.v1, t2.v1 from t1, t2 where t1.n1 = 4 and t2.id = t1.id ; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); prompt ============================== prompt Nested loop join with prefetch prompt ============================== select /*+ leading(t1 t2) use_nl_with_index(t2 t2_i1) nlj_prefetch(t2) */ t1.v1, t2.v1 from t1, t2 where t1.n1 = 4 and t2.id = t1.id ; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); prompt ============================== prompt Nested loop join with batching prompt ============================== select /*+ leading(t1 t2) use_nl_with_index(t2 t2_i1) nlj_batching(t2) */ t1.v1, t2.v1 from t1, t2 where t1.n1 = 4 and t2.id = t1.id ; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); Here are the three plans, with a couple of comments after each – all three queries returned the same 30 *- 10 * 3) rows. ============================== Nested loop join (traditional) ============================== -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 60 | | 1 | NESTED LOOPS | | 1 | 30 | 30 |00:00:00.01 | 60 | |* 2 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 15 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 10 | 3 | 30 |00:00:00.01 | 45 | |* 4 | INDEX RANGE SCAN | T2_I1 | 10 | 3 | 30 |00:00:00.01 | 15 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T1"."N1"=4) 4 - access("T2"."ID"="T1"."ID")复制
This is the original nested loop structured (apart from the “batched” option that appeared in 12c) and follows the rule/guideline:
• Operation 2 operates once and returns the 10 rows predicted.
• Operation 3 is started 10 times by operation 1, with a prediction of 3 rows per start – and the actual comes out at 30 rows.
• Operation 4 is started 10 times (once for each start of operation 3), with a predication of 3 rowids per start – and the actual comes out at 30 rows
============================== Nested loop join with prefetch ============================== ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 3 | 30 |00:00:00.01 | 60 | | 2 | NESTED LOOPS | | 1 | 30 | 30 |00:00:00.01 | 30 | |* 3 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 15 | |* 4 | INDEX RANGE SCAN | T2_I1 | 10 | 3 | 30 |00:00:00.01 | 15 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."N1"=4) 4 - access("T2"."ID"="T1"."ID")复制
Again in the order of rowsource generation
• Operation 3 starts once with a prediction of 10 rows and the rule works.
• Operation 4 is started 10 times by operation 2, with a prediction of 3 rows (rowids) per start, and the rule works.
• Operation 2 was started once by operation 1, with a predication of 30 rows (rowids), and the rule works.
• Operation 1 starts once, but the prediction is reported as the value you would have got from the original NLJ shape – and breaks the rule.
============================== Nested loop join with batching ============================== ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 60 | | 1 | NESTED LOOPS | | 1 | 30 | 30 |00:00:00.01 | 60 | | 2 | NESTED LOOPS | | 1 | 30 | 30 |00:00:00.01 | 30 | |* 3 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 15 | |* 4 | INDEX RANGE SCAN | T2_I1 | 10 | 3 | 30 |00:00:00.01 | 15 | | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 30 | 3 | 30 |00:00:00.01 | 30 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."N1"=4) 4 - access("T2"."ID"="T1"."ID")复制
In the order in which rowsources are created
• Operation 3 starts once with a prediction of 10 rows – and the A-rows matches the rule
• Operation 4 is started 10 times by opreation 2, with a prediction of 3 rows per start – and the A-rows matches the rule.
• Operation 5 is started 30 times by operation 1, with a prediction of 3 rows per start – again reporting the value that you would have seen from the original representation of the NLJ, the prediction obviously should be 1 – so the rule is broken again
tl;dr
It is important to remember that the basic rule of “A-rows = starts * E-rows” does not hold for the partition-related lines or the PX related lines of partitioned and parallel execution plans.
You may also find a few other cases where you need be a little cautious about trusting the rule without first thinking carefully about the mechanics of what the shape of the plan is telling you.
