这个系列的开头,我就强调过规则不是适用于所有的场景,下一部分(第5部分)里我们会来看几个我们需要特别注意的场景。但是在这部分中,我们将继续使用更简单的例子,来了解应用规则时谓词的选择时机和使用的一些细节。
这里列出上个部分文章中两表hash join的执行计划:
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 300 | 22 (0)| 00:00:01 | |* 1 | HASH JOIN | | 10 | 300 | 22 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 150 | 11 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_I1 | 10 | | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 150 | 11 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | T2_I1 | 10 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T2"."ID"="T1"."ID") 3 - access("T1"."N_1000"=1) 5 - access("T2"."N_1000"=100)
通过上述过程,我们可以有以下描述:Oracle在处理Hash Join的两个子操作的方式上是不一样的。第二个子操作(访问T2)只有在第一个子操作(访问T1)完成后才能开始---hash join是一个"阻塞"操作的例子。只有内存里的hash表建立之后,Oracle才能调用第2个子操作每次返回一行去探测hash表,之后将匹配的行传递给父操作,从这个时刻开始就有了分段的数据流。
我偶尔也会看到这样的说法,因为hash join属于阻塞操作,所以当优化器处于first_rows(n)模式下时,不能进行hash join。这是不对的,如果优化器认为能很迅速的建立起hash表,并且从第2张表返回前N行的代价很低,那么hash join可能仍然会是返回前N列代价最低的路径。
我们可以通过hash join和使用hint让优化器使用merge join这两种不同执行计划,来比较阻塞的效果。这里是merge join的计划:
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 300 | 24 (9)| 00:00:01 | | 1 | MERGE JOIN | | 10 | 300 | 24 (9)| 00:00:01 | | 2 | SORT JOIN | | 10 | 150 | 12 (9)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 150 | 11 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | T1_I1 | 10 | | 1 (0)| 00:00:01 | |* 5 | SORT JOIN | | 10 | 150 | 12 (9)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 150 | 11 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | T2_I1 | 10 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."N_1000"=1) 5 - access("T2"."ID"="T1"."ID") filter("T2"."ID"="T1"."ID") 7 - access("T2"."N_1000"=100)
这个计划里我们可以看到第1行的merge join操作有两个子操作,分别是第2行的sort join(第1个子操作)和第5行的sort join(第2个子操作)。运用"子操作按先后顺序执行"的规则,我们可以知道Oracle从范围扫描索引t1_i1开始,从t1中获取可能需要的数据并且在第2行中对它们进行排序(对id列进行排序,因为它们是连接列)。如果运气好的话,第1行中排序后的数据集会在内存中(在会话的PGA中):第一个子操作是一个阻塞操作,所以在排序完成前我们不能调用第二个子操作。
之后开始调用第二个子操作,同样的规则,从范围扫描索引t2_i1开始,访问t2表,对结果集排序:第二个子操作仍然是一个阻塞操作,排序完成前,merge join操作自身并不会进行。
当准备好两个完成排序的rowsource时,merge join从第一个rowsource一次取一条,去探测第二个rowsource,如果匹配上就构造结果行并向上传递给父操作。由于对第二个rowsource进行了排序,所以Oracle查找每个匹配行的最坏情况是o(log(N))—其中N是第二个rowsource中的行数;Oracle使用二分法(使用 log2(N)检查)来找到第一个匹配的行,之后按照顺序从该行向下扫描。实际上代码会更灵活,因为探测的行也是从一个排好序的结果集得来的,所以可以通过"记住"上次探测开始的行的位置来降低工作量。
实际上,这种修改连接为merge join的方法也为我们提供了一个执行计划可能不是如展示的那样执行的例子,我们可以从"rowsource执行统计信息"中发现更多信息。现在我们开始执行查询并使用对dbms_xplan更复杂的调用来查看计划中调用不同步骤的次数。
alter session set statistics_level = all; set linesize 156 set trimspool on set pagesize 60 set serveroutput off select /*+ leading(t1, t2) use_merge(t2) */ t1.v1, t2.v1 from t1, t2 where t1.n_1000 = 1 and t2.id = t1.id and t2.n_1000 = 100 ; select * from table(dbms_xplan.display_cursor(null,null,"iostats last")); -------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 | | 1 | MERGE JOIN | | 1 | 10 | 0 | | 2 | SORT JOIN | | 1 | 10 | 10 | | 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 10 | |* 4 | INDEX RANGE SCAN | T1_I1 | 1 | 10 | 10 | |* 5 | SORT JOIN | | 10 | 10 | 0 | | 6 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 10 | 10 | |* 7 | INDEX RANGE SCAN | T2_I1 | 1 | 10 | 10 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."N_1000"=1) 5 - access("T2"."ID"="T1"."ID") filter("T2"."ID"="T1"."ID") 7 - access("T2"."N_1000"=100)输出中我们应该关注Starts列,第5行的starts列的值可能是会引起问题的一个小细节(第二个排序操作)。显然Oracle把第二个表中的数据排序了10次,但由于Oracle的开发人员是相当聪明的,所以我们有理由认为这不是真实发生的,我们需要对sort join操作有更好的解释,并更清楚的理解它是如何在执行计划中展示的。
第二行的A-rows的值是10(这个行数是从第一个子操作返回的),解释了为什么Oracle需要调用10次第二个子操作,就像是nested loop的操作一样,第一个子操作返回多少行就需要调用相应次数的第二个子操作。我们获取并排序整个数据集一次,之后重用排序后的数据,最终总共探测10次。
在这里我们可以对谓词信息的解释做一个初步介绍。第5行中同时使用了"access"和"filter"谓词,而且这两个谓词使用完全一样的表达式。
简单来说,这两种类型的谓词的区别在于,access谓词告诉我们怎么找到数据行,filter谓词告诉我们怎么在找到数据行后检查这些数据行是否是我们所需要的。
以第二个sort join操作为例子,access谓词告诉我们怎么在排好序的数据集中找到第一条匹配的行,filter谓词告诉我们,当我们按照顺序遍历已排序的数据集时,如何检查每一行,以便在不符合filter表达式条件的行前停止。
平常存在这样一种情况,你认为你已经通过正确的索引正确的顺序访问所有的表,但是执行查询时似乎工作量还是很大,这种情况有可能时访问了很多的数据但使用filter谓词过滤了很大一部分。
如果我们有合适的索引,特别是在连接列上的索引,我们可以进一步研究merge join以及阻塞和计时的问题。这里有个例子(还是通过hint,因为优化器对于merge join的选择性不高),执行计划如下:
-------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 | | 1 | MERGE JOIN | | 1 | 10 | 0 | |* 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 10 | 10 | | 3 | INDEX FULL SCAN | T1_PK | 1 | 10000 | 10000 | |* 4 | SORT JOIN | | 10 | 10 | 0 | |* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 10 | 10 | | 6 | INDEX FULL SCAN | T2_PK | 1 | 10000 | 10000 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T1"."N_1000"=1) 4 - access("T2"."ID"="T1"."ID") filter("T2"."ID"="T1"."ID") 5 - filter("T2"."N_1000"=100)
该计划显示了阻塞操作可能出现的另一种情况。我们访问第一张表的操作并不是一个阻塞操作,只有访问第二张表的操作才是阻塞操作(排序)。这个计划的步骤如下:
1、在表t1上通过index full scan查找第一条符合连接条件的行。
2、在表t2上通过index full scan查找所有符合条件的行,把它们传输到私有工作区;探测工作区的第一条匹配行并将后续匹配行进行合并。
3、从t1中获取第二条匹配的行(遍历索引访问表,并过滤不需要的数据)。
4、探测私有工作区寻找匹配第二行的数据。
5、从第三步开始重复。
这篇文章的关键点是:时机很重要;执行计划中的操作并不总是准确地描述正在发生的事情;谓词部分对于理解优化正在执行的工作是一个重要的帮助,而rowsource(运行时)的统计信息对于查看实际发生的情况是一个非常大的帮助。
原文链接:https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-4-precision-and-timing/