挑战题

jimn1982

zergduan 发表于 2016-3-9 09:55
我认为Nested loop的执行计划分为6#中的三种~

第一种 性能最差，因为要对每一个DEPT中过滤后的值，都要执 ...

但是这种情况下，实际的反馈时间跟这个BUG无关吧

Yong Huang

> 第二种 性能适中，因为在Nested loop中会先得到DEPT表过滤后的结果集，对于结果中的每一行都与EMP_IX进行连接，最终得到连接结果在EMP_IX的leaf node block id（仅仅读取到banch noed，不会访问leaf node）

I agree with the first part. But I think Oracle does visit leaf nodes of the index (but not the table). Otherwise how can it gather all the rowid's needed later? Branch nodes have the starting and ending rowid's  for each leaf node (assume 3 layers of index), but not *all* rowid's. It's true that Oracle inspects the rowid's and groups them based on the table's block ID's. But individual rowid's are still needed.

Your summary of the three NL joins is pretty good. Honestly, I think Oracle's official explanation of the pre-11g and 11g plans (link in msg #4) is poorly written. I'd rather read Jonathan Lewis's classic book "Cost Based Oracle" Chapter 11. He also has a summary of "NL History" at https://jonathanlewis.wordpress.com/2014/04/23/nl-history/

To answer jimn1982's original question: 为什么1和2 两个步骤的nest loop汇总的损耗会这里高? As I said, most of the cost comes from TBL_XLM_RXC. If you can further limit the number of rows needed (currently 4421), your cost will come down.

I'm curious about the bug zergduan mentions. If we can't find the bug number, I hope an SR can be opened with Oracle Support. I'm not sure whether the bug is related to the performance of the query. Probably not.

zergduan

Yong Huang 发表于 2016-3-12 01:25
> 第二种 性能适中，因为在Nested loop中会先得到DEPT表过滤后的结果集，对于结果中的每一行都与EMP_IX进行 ...

I agree with the first part. But I think Oracle does visit leaf nodes of the index (but not the table). Otherwise how can it gather all the rowid's needed later? Branch nodes have the starting and ending rowid's  for each leaf node (assume 3 layers of index), but not *all* rowid's. It's true that Oracle inspects the rowid's and groups them based on the table's block ID's. But individual rowid's are still needed.

Huang 先生，
您可能没有看清，我在7#原文写的是 “然后在 TABLE ACCESS BY INDEX ROWID (EMP) 中，先将前面一步重复的leaf node block id合并，然后以一个叶子节点为单位，来读取叶子节点，并用节点中的rowid返表查询~” ，并没有说不访问leaf node，只不过leaf node的读取发生在NL连接之后，而不是在NL连接之内~

假如是一个三层索引，从上到下是一个树状结构，为了方便我们简称最上层唯一一个block是root block，假设第二层有2个block，我们简称branch block，第三层有4个block，我们简称为leaf block ~ （我们可以通过增大pctfree 来构造出这个索引）

在每一个branch block中，都保存着其下面“连接”的2个leaf block的信息，这些信息包括其下面“连接”的每一个leaf block的block id， 以及每一个leaf block中index entry键值最大值和最小值，所以我们通过branch block的读取，就可以知道与连接条件相符的键值到存储在哪一个leaf block中，也能得到这个leaf block的block id~

所以，我说的第二种NL连接方式
-----------------------------------------------
| Id  | Operation                    | Name   |
-----------------------------------------------
|   0 | SELECT STATEMENT             |        |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMP    |
|   2 |   NESTED LOOPS               |        |
|*  3 |    TABLE ACCESS FULL         | DEPT   |
|*  4 |    INDEX RANGE SCAN          | EMP_IX |
-----------------------------------------------

执行过程如下：
第一步
|*  3 |    TABLE ACCESS FULL         | DEPT   |
|*  4 |    INDEX RANGE SCAN          | EMP_IX
的过程，无需去访问leaf block，只要访问到banch block，就可以知道我们需要的leaf block id列表，也就是说对于DEPT中的每一行进行后面的INDEX RANGE SCAN （EMP_IX）并不涉及读取索引的leaf block，而是得到leaf block列表，此时leaf block 列表中有可能存在重复的leaf block id~

第二步
|   1 |  TABLE ACCESS BY INDEX ROWID | EMP    |
此时，我们根据前面得到的leaf block id列表，先处理列表中的重复leaf block id，将重复的leaf block id合并，然后重新组建一个unique leaf block id列表，然后才开始返表查询操作，过程如下：

读取unique leaf block id 列表中的第一个leaf block id所对应的索引leaf block，并将连接条件带入，找到对应的index entry，得到table中的rowid，然后用这个rowid访问table，得到结果；然后在读取unique leaf block id列表中下一个leaf block id所对应的索引leaf block，继续带入连接条件，找到对应的index entry，得到table的rowid，通过rowid访问table~
相当于将 “读取index leaf block，得到table rwoid” 延迟到NL连接后再执行，这样可以提前合并重复的 index leaf block，减少访问次数~


这样的好处是，减少了索引leaf block的逻辑读次数，也就是第二种NL连接比第一种NL连接性能提高的关键




这个结论是我好久之前通过10046得到的，实验过程就是我前面说的先通过增大pctfree创建一个三层的索引，并且让branch block是2个，leaf block是4个~  
然后构造一个查询，让这个查询结果的数据分布在两个branch下面的leaf block中，且让每个leaf block存在多个符合连接条件的index entry~
清空buffer cache后执行查询~

如果按照第一种方式NL连接，查看100046的报告（不要tkprof），db file sequential read 等待事件后面的内容显示file#和block#，通过这个可以判断访问的是什么block，可以看到branch block -> leaf block -> table block -> branch block -> leaf block -> table bloc...  这样的访问顺序~
如果按照第二种方式NL连接，可以看到branch block （第一个）-> branch block (第二个）-> leaf block -> table block,table block,table block... -> leaf block -> table block,table block,table block... 这样的访问顺序~



我在家里没有环境，没法重新做这个实验，我到公司后会在做一次~

您也可以尝试一下，看看我的理解是否正确

zergduan

Yong Huang 发表于 2016-3-12 01:25
> 第二种 性能适中，因为在Nested loop中会先得到DEPT表过滤后的结果集，对于结果中的每一行都与EMP_IX进行 ...

我之所以说是bug，是因为我觉得当使用11g的NL方式执行jimn1982的SQL的时候，TABLE ACCESS BY GLOBAL INDEX ROWID （BL_WAXHEER_HELL_REST11） 的执行次数不应该由NL中TABLE ACCESS FULL （BL_XLM_RXC）的行数所决定~


----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Rows | Bytes   | Cost  | Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |   40 |   66920 | 22518 | 00:04:31 |
| * 1 |   COUNT STOPKEY                          |                         |      |         |       |          |
|   2 |    VIEW                                  |                         | 4445 | 7436485 | 22518 | 00:04:31 |
| * 3 |     SORT ORDER BY STOPKEY                |                         | 4445 | 1724660 | 22518 | 00:04:31 |
|   4 |      NESTED LOOPS                        |                         | 4445 | 1724660 | 22149 | 00:04:26 |
|   5 |       NESTED LOOPS                       |                         | 4445 | 1724660 | 22149 | 00:04:26 |
|   6 |        PARTITION RANGE ITERATOR          |                         | 4421 | 1299774 |    39 | 00:00:01 |
| * 7 |         TABLE ACCESS FULL                | TBL_XLM_RXC             | 4421 | 1299774 |    39 | 00:00:01 |
| * 8 |        INDEX RANGE SCAN                  | IDX1                    |    1 |         |     3 | 00:00:01 |
| * 9 |       TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_WAXHEER_HELL_REST11 |    1 |      94 |     5 | 00:00:01 |
----------------------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        | 19735 |   732K|   856   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                |        | 19735 |   732K|   856   (1)| 00:00:01 |
|   2 |   NESTED LOOPS               |        | 19922 |   732K|   856   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPT   |    2 |    32 |    3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_IX |  9961 |       |    28   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP    |  9868 |   212K|   824   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


的确，现在无法判断到底是cost方法出错，还是执行计划出错，反正cost计算和执行计划对应不上~
我觉得jimn1982也可以通过10046种的物理读，来确定到底真正的执行计划是什么~ 方法按照我前面的实验原理就可以

Yong Huang

I think the method you described in msg #13 is a very good one. It can clearly show the actual mechanism Oracle works in fetching the data. Instead of 10046 event trace, 10202 event for "consistent read block header" may also be used and you don't have to flush buffer cache.

Apparently what you described is inconsistent with Jonathan Lewis's statement. On p.311 of his "Cost Based Oracle", he says

"The new mechanism finds the first row in the outer table, traverses the index, and stops in the leaf block, picking up just the relevant rowids for the inner table; then repeats for the second and third rows in the outer table. When all the target rowids have been found, the engine can sort them and then visit the inner table in a single pass, working along the length of the table just once, picking the rows in whatever order they happen to appear"

The new mechanism in this book published in 2006 refers to your second plan (see msg #6). The outer table is the one immediately below "NESTED LOOPS", such as DEPT or TBL_XLM_RXC in our case. Note he says "stops in the leaf block", not e.g. "stops before visiting the leaf block". Maybe over the years Oracle has changed the behavior even within the second plan (his "new mechanism"). You're sure your observation is on the second not third plan?

jimn1982

zergduan 发表于 2016-03-09 09:55

我认为Nested loop的执行计划分为6#中的三种~

第一种 性能最差，因为要对每一个DEPT中过滤后的值，都要执行一遍INDEX RANGE SCAN (EMP_IX) 和 TABLE ACCESS BY INDEX ROWID (EMP)，DEPT的结果集增大，整个sql 的cost 会因为INDEX RANGE SCAN (EMP_IX) 和 TABLE ACCESS BY INDEX ROWID (EMP)执行次数增多而变大~

第二种 性能适中，因为在Nested loop中会先得到DEPT表过滤后的结果集，对于结果中的每一行都与EMP_IX进行连接，最终得到连接结果在EMP_IX的leaf node block id（仅仅读取到banch noed，不会访问leaf node），然后在 TABLE ACCESS BY INDEX ROWID (EMP) 中，先将前面一步重复的leaf node block id合并，然后以一个叶子节点为单位，来读取叶子节点，并用节点中的rowid返表查询~
这样相对于第一种执行计划，减少了TABLE ACCESS BY INDEX ROWID (EMP)的次数~所以nested loop中的得到的unique leaf node block id越少，相对于第一种执行计划，提高的性能就越大

第三种 性能最好，11g种新增强的Nested loop，这种执行计划中和第二种差不多，也是在DEPT和EMP_IX连接时的到leaf node block id，但是在后面返表查询前做了一个类似批量的事情，先读取一个leaf node中的rowid，然后用一个IO请求来完成这些rowid的返表查询

根据上面的3种执行计划的真正执行方式，可以看出LZ的问题是，命名执行计划是第三种，但是cost计算的时候确实第一种，这肯定是错误的，应该是一个bug，建议你想MOS开tar来确定

你好，我想说的是就算这里的cost计算的有问题，但是这个语句的返回时间的确很慢呢。理论上就算估算得不对很实际执行应该关联不大吧

jimn1982

4000多条记录，每条记录0.38k要15秒