Oracle数据库中级培训执行计划第7讲.ppt

资源描述

Oracle数据库中级培训执行计划第7讲.ppt

《Oracle数据库中级培训执行计划第7讲.ppt》由会员分享，可在线阅读，更多相关《Oracle数据库中级培训执行计划第7讲.ppt（68页珍藏版）》请在冰点文库上搜索。

Oracle数据库中级培训执行计划第7讲.ppt

Oracle数据库中级培训（第7讲）,上海全成通信技术有限公司金刚2010-06,培训大纲,一.连接选择率及基数二.连接机制嵌套循环散列（哈希）连接归并连接三.优化提示四.理解并行执行计划,连接基数,关于连接需要了解的几个知识点：

1）Oracle一次只能连接两个表。

不管查询中有多少个表，Oracle在连接中一次仅能操作两个对象。

2）到目前为止，优化器没有长期策略，它只是在当前已经得到的结果上连接下一个可用的表，以确定接下来的情况。

3）当执行5个表的连接时，优化器从一个表开始，将它与另一个表连接；然后将中间结果与下一个表连接，以此类推，直到处理完5个表为止。

连接基数和选择率,连接选择率：

（t1.c1=t2.c2）Sel（Pj）=1/max（NDV（t1.c1）,NDV（t2.c2）（card（t1）-#t1.c1NULLS）/card（t1）（card（t2）-#t2.c2NULLS）/card（t2）连接基数：

Card（Pj）=card（t1）card（t2）Sel（Pj）补充：

GROUPBY分组基数Card（groupbyc1,c2,cn）=NDV（c1）NDV（c2）NDV（cn）2（n-1）,连接基数计算示例,连接选择率和基数计算示例：

selecta.city_name,a.city_id,b.use_countfromdms_areaajoinfct_use_daybona.city_id=b.dms_area_idwherea.city_name=上海andb.dms_time_id=20101001;-|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|Pstart|Pstop|-|0|SELECTSTATEMENT|37741|884K|5291（7）|00:

01:

36|*1|HASHJOIN|37741|884K|5291（7）|00:

01:

36|*2|TABLEACCESSFULL|DMS_AREA|1|10|3（0）|00:

00:

01|3|PARTITIONRANGESINGLE|13M|174M|5220（5）|00:

01:

34|574|574|*4|TABLEACCESSFULL|FCT_USE_DAY|13M|174M|5220（5）|00:

01:

34|574|574|-分析如下：

08:

04|1|HASHGROUPBY|3253K|58M|498M|26867（4）|00:

08:

04|2|PARTITIONRANGESINGLE|13M|236M|5166（4）|00:

01:

33|574|574|*3|TABLEACCESSFULL|FCT_USE_DAY|13M|236M|5166（4）|00:

01:

33|574|574|-NDV（dms_area_id）=346NDV（DMS_SERVICE_TYPE_ID）=6268NDV（DMS_ENTRANCE_ID）=3CARD（groupby）=346*6268*3/2（3-1）=3253092,培训大纲,一.连接选择率及基数二.连接机制嵌套循环散列（哈希）连接归并连接三.优化提示四.理解并行执行计划,嵌套循环连接（原理）,嵌套循环实现机制（伪代码）：

Forr1in（selectrowsfromtable_1wherecolx=value）loopforr2in（selectrowsfromtable_2thatmatchcurrentrowfromtable_1）loopoutputvaluesfromcurrentrowoftable_1andcurrentrowoftable_2;endloop;Endloop;伪代码解释：

这段代码由两个循环构成。

嵌套循环中的这两个表通常称为外部表（outertable）和内部表（innertable）。

在嵌套循环连接中,外部表又称为驱动表（drivertable）,嵌套循环连接（要点）,嵌套循环连接的相关知识点：

1）嵌套循环连接可以用两幅典型的图来表示。

作为解释嵌套循环连接机制的工具，每副图有各自的优点和不足。

2）嵌套循环通常用于从驱动表中过滤得到少数行中的每一行用于连接第二个表以便从从选取“少数行”。

嵌套循环连接（机制一）,该图简单的将一个表中的行与另一个表中的行连接起来，用箭头表示活动的方向，这种表示使我们很容易看出一个表中的行与另一个表中对应行之间的联系。

很明显,内表在连接列上不存在对应的索引或存在但没有使用。

我们要尽可能规避这种连接实现机制。

嵌套循环连接（机制二）,该图表示利用了第二个表的索引，因为当存在嵌套循环连接时，通常都以这种方式涉及到索引。

该图的优点是可以直观显示出嵌套循环的新旧两种机制。

嵌套循环连接（机制二：

旧机制）,在旧机制中，首先在外部（驱动）表中找到第一行，然后遍历索引，依次访问内部表中每个匹配的行；接下来对外部表中的第二行和第三行重复上述过程，其结果是按照（a,a,a,b,b,b,c,c,c）的顺序取出第二个表中的行，可以使后面的orderby子句完成SORT（ORDERBY）NOSORT操作。

嵌套循环连接（机制二：

新机制）,在新机制中，首先在外部表中找到第一行，然后遍历索引，但停止在叶块上，只为内部表选取相关的rowid;接下来对外部表中的第二行和第三行重复上述过程。

当找到全部目标rowid之后对其进行排序，然后只对内部表按顺序访问一遍，按照其rowid的顺序选取出相应的行，该例中的顺序是（a,b,b,a,c,b,c,a,c）.,嵌套循环连接（COST）,嵌套循环连接成本主要涉及到下列要素：

1）从第一个表中取出全部所需的行的成本是多少。

2）第一个表中返回多少行3）根据从第一个表中当前行获得的新的可用信息，在第二个表中查找一次相关行通常的成本是多少结论：

NESTLOOPJOINCOST=从第一个表取得数据的成本+从第一个表得到结果的基数对第二个表访问一次的成本,嵌套循环连接示例,嵌套循环连接成本计算示例：

00:

01|1|NESTEDLOOPS|1|49|3（0）|00:

00:

01|2|PARTITIONRANGESINGLE|1|39|2（0）|00:

00:

01|574|574|*3|TABLEACCESSFULL|FCT_USE_DAY|1|39|2（0）|00:

00:

01|574|574|*4|TABLEACCESSBYINDEXROWID|DMS_AREA|1|10|1（0）|00:

00:

01|*5|INDEXUNIQUESCAN|DMS_AREA_PK|1|0（0）|00:

00:

01|-,哈希连接,哈希连接又叫散列连接。

散列连接可以在3种不同层次上执行，分别为：

1）Optimal（最优散列连接）2）Onepass（一遍散列连接）3）Multipass（多遍散列连接）,最优散列连接

（1）,执行步骤如下：

1）Oracle读取第一个数据集并在内存中建立一个“散列桶（hashbucket）”的数组。

散列表中桶的数目一般是2的偶数次幂（对于小的散列连接，桶的数目通常为1024或4096）。

虽然整个结构是一个固定数组和链表的复杂集合，但我们可以简单的将散列表想象为一个方阵。

第一个表（构建表）中的行随机分布在方阵之中。

最优散列连接

（2）,执行步骤如下：

2）Oracle开始读取第二个表（探查表,probetable），采用最适合获取行的访问机制，并且在连接列上使用同样的散列函数计算相关散列桶的号码。

然后Oracle查看该桶中是否有行-这称为探查散列表（Probingthehashtable）.,最优散列连接（3）,执行步骤如下：

3）如果相应的桶中没有行，则Oracle会立即丢弃从探查表中取出的这一行。

如果相应的桶中有一些行，则Oracle会精确地检查连接列以判断是否存在合适的匹配。

由于连接列上具有不同值的行有可能在同一个散列桶中，因此必须执行精确检查。

对于通过精确检验的行，就可以立即报告了。

Oracle为散列分配内存的方式与为排序分配内存的方式有着重要的区别。

一旦散列连接开始Oracle就会要求hash_area_size内存量的一大部分，以便创建大量的散列桶，这有助于将散列冲突减到最少。

最优散列连接（4）,更深一层次理解：

最优散列连接实际上只是对动态创建的单表散列簇的从后到前的嵌套循环。

我们利用从构建表中选取出的行，在本地内存中建立一个单表散列簇，然后对于从探查表中选取出的每一行，利用散列键检查该散列簇。

最忧散列连接的主要好处是构建表被转移到本地内存中，而不是作为缓冲存储器中真正的单表散列簇。

这意味着通常存在于表访问中的锁存、缓冲和读一致性成本，在探查散列时候不会出现。

最优散列连接的近似估计成本=从构建中获取数据的成本+从探查表中获取数据的成本,一遍散列连接

（1）,执行步骤如下：

1）Oracle读取第一个数据集并将其分布到散列表中。

一旦使用某个桶，则设置该桶在位图中的对应位。

一遍散列连接

（2）,执行步骤如下：

2）随着内存的使用，当可用内存用完时，簇被转储到磁盘上，转储时采用一种谨慎的策略，试图将尽可能多的完整分区在内存中保留尽可能长的时间。

当构建表处理完毕时，有可能一些分区仍完整的保留在内存中，而其它分区只有一些（但至少要有一个）簇保留在内存中。

位图总是完整的保留在内存中。

此时,Oracle将散列表整理完毕，将尽可能多的完整分区装入内存，但其余分区中超出的部分转储到磁盘。

作为重建的一部分，Oracle会保留一些簇（每个分区至少一个）用于处理探查表。

一遍散列连接（3）,执行步骤如下：

3）一旦散列表整理完毕，Oracle就开始获取第二个数据集中的行，在每个行的连接列上应用同样的散列函数。

散列函数的结果用于位图中相关的位。

（最优散列连接中忽略了这个细节）,一遍散列连接（4a）,执行步骤如下：

4）Oracle执行下面几种可能的操作之一，这取决于位图测试的结果。

4a）事件：

位被清除（0）操作：

没有匹配，将行丢弃。

一遍散列连接（4b）,执行步骤如下：

4）Oracle执行下面几种可能的操作之一，这取决于位图测试的结果。

4b）事件：

位被设置

（1），而且相关的散列桶所在的分区在内存中。

操作：

检查散列桶-如果探查行与构建行匹配则报告；否则将其丢弃。

一遍散列连接（4c）,执行步骤如下：

4）Oracle执行下面几种可能的操作之一，这取决于位图测试的结果。

4c）事件：

位被设置

（1），而且相关的散列桶所在的分区在磁盘上。

操作：

将探查行暂时搁置。

它有可能匹配了磁盘上的一个构建行，但此时若为了检查该探查行而重新读取相关的构建分区，则这种操作方式成本太大。

暂时搁置的探查行被收集到与先前转储至磁盘的散列表分区匹配的集合中。

当处理到探查表的末尾时，我们得到磁盘上构建表和探查表匹配的分区对。

一遍散列连接（5）,执行步骤如下：

5）将第2步中转储至磁盘的构建散列表（未匹配的构建散列表）重新读入内存。

6）将未匹配的转储至磁盘的探查表读入，根据连接列计算出哈希值和对应的桶匹配。

重复步骤4。

若将第2步中转储至磁盘的构建散列表（未匹配的构建散列表）能够一次全部重新读入磁盘，则称为一次遍历（onepass）,否则成为multipass（多遍遍历）。

一遍散列连接,对于大容量的散列连接，散列表可能会向磁盘传输，紧随其后的是探查表向磁盘传输。

连接的成本应考虑到将超出的部分转储至磁盘以及为第二阶段的连接重新将其读入的IO成本。

因为仅从磁盘重新读取一次探查数据集，所以这类散列连接称为一遍（onepass）工作区执行。

散列连接相关术语,术语解释：

簇：

散列表扇入/出内存的原子单位。

分区：

分区这个词在oracle中表示不同的内容。

分区由簇构成，每个分区包含的簇数目不一定相同。

桶：

对于小的连接，桶的数目通常为1024或4096。

和位图中的位一一对应。

每个桶对应一个哈希值。

位图：

位图常驻内存。

位图中的每一位表示散列表中的每一个桶。

当某个构建行散列到一个特定的桶时，就在位图中置该桶对应的位。

位图中的位和散列桶是一一对应的。

若桶中存在数据则置1。

散列连接（成本）,散列连接成本近似计算公式：

COST=（探查遍数+1）cost（探查表）+cost（构建表）从成本计算公式可以看出，优化散列连接的关键在于:

1）降低探查遍数2）正确选择构建表3）及时收集统计信息，优化内存,散列连接（示例）,selecta.city_name,a.city_id,b.use_countfromdms_areaajoinfct_use_daybona.city_id=b.dms_area_idwherea.city_name=上海andb.dms_time_id=20101001;-|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|Pstart|Pstop|-|0|SELECTSTATEMENT|37741|884K|5291（7）|00:

01:

36|*1|HASHJOIN|37741|884K|5291（7）|00:

01:

36|*2|TABLEACCESSFULL|DMS_AREA|1|10|3（0）|00:

00:

01|3|PARTITIONRANGESINGLE|13M|174M|5220（5）|00:

01:

34|574|574|*4|TABLEACCESSFULL|FCT_USE_DAY|13M|174M|5220（5）|00:

01:

34|574|574|-,排序（操作）,排序是一种非常特殊且很常见的操作，但清楚其实际机制和资源需求的人仍然不多。

涉及到排序的常见操作：

Orderbygroupbydistinctsortmergejoinconnectbyb-treeTobitmap的变换分析函数集合运算索引创建等等。

排序（级别）,和散列连接一样，排序的执行效率也有三种级别：

1）最优2）一遍3）多遍,最优排序,最优排序（optimalsort）执行时数据全部在内存中。

我们只是读取一个数据流并将其排序，执行时全部数据都在内存中。

在内存消耗完之前，数据已经读取完毕，因此不需要使用磁盘空间作为临时中转区。

与通常的认识不同，内存是随着数据的读入逐步分配的，而不是在开始排序时就分配全部sort_area_size（或由pga_aggregate_target规定的上限）大小的内存。

一遍排序,一遍排序（onepasssort）适用于数据量太大而无法一次全部读入内存的情况。

我们将一次能够处理的数据量读入内存并对其排序，当达到内存上限时，就将已排序数据转储到磁盘。

然后读取更多数据并对其排序，当内存再一次用尽时，就将下一个已排序数据集转储到磁盘，如此重复直至处理完整个输入数据流。

此时磁盘上有一些经过排序的数据集，接下来必须将它们归并成一个集合。

如果内存足够容纳从每个这样的已排序数据集中各读取一块，那么这就属于一遍排序。

一遍排序可以支持很大容量的数据排序。

根据经验，G大小的数据排序，只需要11M内存就可以实现一遍排序。

多遍排序,多遍排序（multipasssort）开始和一遍排序相似，但其针对的情况是所有数据都处理完并写至磁盘，且当前会话发现内存不足以容纳从每个已排序的数据集中各读取一块。

在这种情况下，必须归并一些数据流，把一个较大的数据流写至磁盘，然后再归并几个数据流，把另一个较大的数据流写回至磁盘。

最后，所有最初的数据流都处理完毕，接下来再将一些较大的数据流读回内存然后归并它们-有可能把更大的数据流写回至磁盘。

最终可以归并磁盘上的全部数据流（可能非常大）。

我们必须读取数据的次数称为归并的遍数。

排序（示例）,一遍排序示例：

22,43,63,12,98,6,15,210,13,9,54,73,29,91,55步骤1，每次取4个数据进行排序22,43,63,1212,22,43,6398,6,15,2106,15,98,21013,9,54,739,13,54,7329,91,5529,55,91步骤2，从每个已排序好的数据的第一个值取回进行排序6,（12,9,29）步骤3，然后将6的后一个数据15读回内存进行排序，得到6,9,（12,15,29）,然后将9的后一个数据13读回内存进行排序，得到6，9，12,（15,13,29）,依次类推，最后得到如下的结果：

6,9,12,13,15,22,29,43,54,55,63,73,91,98,210,排序（COST）,排序的近似成本计算：

COST（SORT）=（BlockstoSort+IOcostperpassMergepasses）,归并连接（原理）,归并连接非常简单，和散列连接一样，首先将连接分解为两个独立的查询，在联合独立的结果集之前：

1）获取第一个数据集，使用任何可能有关的访问和过滤谓词，并按连接列对其排序。

1）获取第二个数据集，使用任何可能有关的访问和过滤谓词，并按连接列对其排序。

3）对第一个数据集中的每一行，在第二个数据集中找到起始点，然后对其扫描直到发现无法连接的那一行（此时可以停止了，因为第二个数据集是已排序的）。

归并连接（原理图）,归并连接的实质就是从一个索引组织表进入另一个索引组织表的嵌套循环连接。

（虽然已排序数据集实际上并未建立索引，并且oracle可能在每一遍中利用二分检查索引找到第二个数据集中的起始点）。

与散列连接相似的是，归并连接将一个查询分解为两个。

首先获取数据的独立查询；与散列连接不同的是，第二个查询不必等待第一个查询完成就可以开始。

归并连接原理图：

Merge,Source1SetSort,Source2SetSort,归并连接（五种变体）,归并连接存在五种变体，如上图所示：

1）一对一2）由等值条件确定的一对多（例如：

父/子连接）3）由范围条件确定的一对多（例如：

t2.dtbetweent1.dt-3andt1.dt+3）4）由等值条件确定的多对多5）由范围条件确定的多对多,归并连接（COST）,我们也可以简单的认为归并连接只有一种策略-对于第一个输入中的每一行，从第二个输入中找到首个合法的连接行，然后按顺序遍历第二个输入，直到刚好超过最后一个合法的连接行。

归并连接的成本近似估计：

09:

25|1|MERGEJOIN|37741|884K|31346（4）|00:

09:

25|*2|TABLEACCESSBYINDEXROWID|DMS_AREA|1|10|3（0）|00:

00:

01|3|INDEXFULLSCAN|DMS_AREA_PK|346|1（0）|00:

00:

01|*4|SORTJOIN|13M|174M|598M|31343（4）|00:

09:

25|5|PARTITIONRANGESINGLE|13M|174M|5220（5）|00:

01:

34|574|574|*6|TABLEACCESSFULL|FCT_USE_DAY|13M|174M|5220（5）|00:

01:

34|574|574|-,不同连接方法的相对速度,嵌套循环连接,散列连接,排序合并连接,连接速度,数据量,小,大,慢,快,不同连接方法的相对速度（示例）,-|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|Pstart|Pstop|-|0|SELECTSTATEMENT|37741|884K|5223（5）|00:

01:

35|1|NESTEDLOOPS|37741|884K|5223（5）|00:

01:

35|*2|TABLEACCESSFULL|DMS_AREA|1|10|3（0）|00:

00:

01|3|PARTITIONRANGESINGLE|37741|515K|5220（5）|00:

01:

34|574|574|*4|TABLEACCESSFULL|FCT_USE_DAY|37741|515K|5220（5）|00:

01:

34|574|574|-|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|Pstart|Pstop|-|0|SELECTSTATEMENT|37741|884K|5291（7）|00:

01:

36|*1|HASHJOIN|37741|884K|5291（7）|00:

01:

36|*2|TABLEACCESSFULL|DMS_AREA|1|10|3（0）|00:

00:

01|3|PARTITIONRANGESINGLE|13M|174M|5220（5）|00:

01:

34|574|574|*4|TABLEACCESSFULL|FCT_USE_DAY|13M|174M|5220（5）|00:

01:

34|574|574|-|Id|Operation|Name|Rows|Bytes|TempSpc|Co

展开阅读全文