`
安铁辉
  • 浏览: 244911 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

Oracle执行计划

阅读更多
最近做数据迁移,重拾Oracle,买了从来没看的Oracle书可以发挥价值了,学执行计划前先普及一点基本概念
一、基本概念
1、Rowid的概念,Oracle的一个虚拟列,用于命中索引后回表(根据rowid去文件块的某个位置读取数据),rowid在该行的生命周期内是唯一的,即即使该行产生行迁移,行的rowid也不会改变
2、Recursive SQL概念:用户的ddl,dml操作会带来一些隐藏操作,显而易见的就是会修改数据字典,数据字典信息存储在内存中
3、Row Source(行源) :通俗点说就是查询或连接时的摸个表经过where条件过滤后剩下的结果集
4、Driving Table(驱动表):该表又称为外层表(OUTER TABLE)。这个概念用于嵌套与HASH连接中。ps:读完后面的内容就对潜逃和hash链接有概念了,我第一次读也不是很清晰
5、Probed Table(被探查表) 与4相反
6、组合索引(concatenated index):由多个列构成的索引,如create index idx_emp on emp(col1, col2, col3, ……),则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念:引导列(leading column),在上面的例子中,col1列为引导列。当我们进行查询时可以使用”where col1 = ? ”,也可以使用”where col1 = ? and col2 = ?”,这样的限制条件都会使用索引,但是”where col2 = ? ”查询就不会使用该索引。所以限制条件中包含先导列时,该限制条件才会使用该组合索引。
7、可选择性(selectivity):其实就是 distinct 这列/count(1)
二、Oracle读取数据的方法
1、 全表扫描(Full Table Scans, FTS)
极端情况会全表扫面,也不会用到rowid,比如select * from xx 没了,任何条件都没,这种情况可以设置并发读取,提高效率
2、通过ROWID的表存取(Table Access by ROWID或rowid lookup)
通过rowid定位文件块位置
SQL> explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF';
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID DEPT [ANALYZED]

3、上面2中都很少见,或者说用到的很少,索引扫描(Index Scan或index lookup)是优化的主要对象
索引扫面分2部,可以想象第一部扫描索引(大部分在内存中)找到rowid,第二步通过rowid定位到文件块,读取,第二步的性能可能会成为瓶颈
SQL> explain plan for select empno, ename from emp where empno=10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED] 
INDEX UNIQUE SCAN EMP_I1

极端情况下返回的结果都在第一步,也就是索引中找到,那就不需要第二步了
SQL> explain plan for select empno from emp where empno=10;-- 只查询empno列值
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX UNIQUE SCAN EMP_I1

4、扫描索引类型:根据where条件不同,产生不同索引策略,其实就是索引和rowid比例的几种关系
  • 索引唯一扫描(index unique scan)
  • 索引范围扫描(index range scan)
  • 索引全扫描(index full scan)
  • 索引快速扫描(index fast full scan)

a、索引唯一扫描(index unique scan) 索引:rowid=1:1
INDEX UNIQUE SCAN EMP_I1

b、索引范围扫描(index range scan) :  <索引< 产生范围扫描的原因除了where中有范围查询,还有可能是查询组合索引,但是没有出现引导列
INDEX RANGE SCAN EMP_I1 [ANALYZED]

c、索引全扫描(index full scan):极端。与全表扫描类似,做全表扫描
INDEX FULL SCAN BE_IX [ANALYZED]

d、索引快速扫描(index fast full scan),与全扫描不同的是不对返回结果排序,可以并行

三.表之间的连接,先看一条sql,后续的例子多按照它讲解
SELECT A.COL1, B.COL2 
FROM A, B
WHERE A.COL3 = B.COL4;
为例进行说明,假设A表为Row Soruce1,则其对应的连接操作关联列为COL 3;B表为Row Soruce2,则其对应的连接操作关联列为COL 4;

目前为止,无论连接操作符如何,典型的连接类型共有3种:
  • 排序 - - 合并连接(Sort Merge Join (SMJ) )
  • 嵌套循环(Nested Loops (NL) )
  • 哈希连接(Hash Join
)这就是开头提到的后面会讲到的排序和潜逃,我们平时join所理解的方式都是循环嵌套,其实还有2种
排序 - - 合并连接(Sort Merge Join, SMJ)
内部连接过程:
1) 首先生成row source1需要的数据,然后对这些数据按照连接操作关联列(如A.col3)进行排序。
2) 随后生成row source2需要的数据,然后对这些数据按照与sort source1对应的连接操作关联列(如B.col4)进行排序。
3) 最后两边已排序的行被放在一起执行合并操作,即将2个row source按照连接条件连接起来
下面是连接步骤的图形表示:
MERGE
/\
SORTSORT
||
Row Source 1Row Source 2
排序的例子
SQL> explain plan for
select /*+ ordered */ e.deptno, d.deptno
from emp e, dept d
where e.deptno = d.deptno
order by e.deptno, d.deptno;


Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL EMP [ANALYZED]
SORT JOIN
TABLE ACCESS FULL DEPT [ANALYZED]

嵌套循环(Nested Loops, NL)
这个连接方法有驱动表(外部表)的概念。其实,该连接过程就是一个2层嵌套循环,所以外层循环的次数越少越好,这也就是我们为什么将小表或返回较小row source的表作为驱动表(用于外层循环)的理论依据。
内部连接过程:
Row source1的Row 1 ---------------- Probe ->Row source 2
Row source1的Row 2 ---------------- Probe ->Row source 2 
Row source1的Row 3 ---------------- Probe ->Row source 2
…….
Row source1的Row n ---------------- Probe ->Row source 2

从内部连接过程来看,需要用row source1中的每一行,去匹配row source2中的所有行,所以此时保持row source1尽可能的小与高效的访问row source2(一般通过索引实现)是影响这个连接效率的关键问题
NESTED LOOPS有其它连接方法没有的的一个优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。
例子:不加关键字的join应该都是循环的方式join,我个人理解
哈希连接(Hash Join, HJ)

这种连接是在oracle 7.3以后引入的,从理论上来说比NL与SMJ更高效,而且只用在CBO优化器中。
较小的row source被用来构建hash table与bitmap,第2个row source被用来被hansed,并与第一个row source生成的hash table进行匹配,以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法,来检查在hash table中是否有匹配的行。特别的,当hash table比较大而不能全部容纳在内存中时,这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念,被构建为hash table与bitmap的表为驱动表,当被构建的hash table与bitmap能被容纳在内存中时,这种连接方式的效率极高。
例子:
SQL> explain plan for
select /*+ use_hash(emp) */ empno 
from emp, dept
where emp.deptno = dept.deptno;
Query Plan
----------------------------
SELECT STATEMENT[CHOOSE] Cost=3
HASH JOIN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL EMP


总结一下,在哪种情况下用哪种连接方法比较好:

排序 - - 合并连接(Sort Merge Join, SMJ):
a) 对于非等值连接,这种连接方式的效率是比较高的。
b) 如果在关联的列上都有索引,效果更好。
c) 对于将2个较大的row source做连接,该连接方法比NL连接要好一些。
d) 但是如果sort merge返回的row source过大,则又会导致使用过多的rowid在表中查询数据时,数据库性能下降,因为过多的I/O。

嵌套循环(Nested Loops, NL):
a) 如果driving row source(外部表)比较小,并且在inner row source(内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。
b) NESTED LOOPS有其它连接方法没有的的一个优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。

哈希连接(Hash Join, HJ):
a) 这种方法是在oracle7后来引入的,使用了比较先进的连接理论,一般来说,其效率应该好于其它2种连接,但是这种连接只能用在CBO优化器中,而且需要设置合适的hash_area_size参数,才能取得较好的性能。
b) 在2个较大的row source之间连接时会取得相对较好的效率,在一个row source较小时则能取得更好的效率。
c) 只能用于等值连接中
分享到:
评论

相关推荐

    oracle 执行计划 详解

    "Oracle 执行计划详解" Oracle 执行计划是数据库性能优化的关键。为了更好地理解和优化 Oracle 数据库的执行计划,我们需要了解执行计划的生成过程、优化方法和执行计划的解读方法。 执行计划生成过程 执行计划的...

    Oracle执行计划参数解释

    Oracle执行计划参数解释,Oracle SQL优化的基础是看懂Oracle的执行计划,本文当系统整理了Oracle执行计划里面的各种参数。

    oracle执行计划解读

    Oracle 执行计划解读 Oracle 执行计划是一种查询执行路径的表示形式,它展示了 Oracle 数据库在执行查询时访问数据的路径。下面是 Oracle 执行计划的详细解读,包括执行计划的定义、访问数据的方式、执行计划层次...

    oracle执行计划建立与阅读

    Oracle执行计划是数据库管理系统在处理SQL语句时的预估工作流程,它是Oracle优化器根据当前数据分布、索引情况和系统资源等信息选择的最佳执行策略。了解和分析执行计划对于提升SQL语句的性能至关重要。 一、生成...

    Oracle 执行计划PPT

    Oracle 执行计划是数据库管理系统在处理SQL查询时制定的一系列步骤,用于高效地检索和处理数据。它是Oracle数据库优化器(Optimizer)根据统计信息、成本估算和已存在的索引等信息生成的。优化器有两种主要的工作...

    Oracle执行计划介绍与测试.pdf

    ### Oracle执行计划深入解析 #### 引言 在数据库领域,尤其对于大型企业级应用,Oracle数据库因其强大的功能和稳定性而被广泛采用。在Oracle中,执行计划是数据库优化器根据SQL语句特性生成的一系列步骤,用于指导...

    oracle执行计划详解

    Oracle 执行计划详解是数据库管理系统中一个非常重要的概念。本文将详细介绍 Oracle 执行计划的相关概念、访问数据的存取方法、表之间的连接等内容,并对总结和概述,以便于理解和记忆。 一、相关的概念 1. Rowid ...

    oracle执行计划文档

    除了以上概念,Oracle执行计划还包括其他访问方法,如索引扫描(Index Scan)、索引唯一扫描(Index Unique Scan)、索引快速全扫描(Index Fast Full Scan)以及各种类型的连接操作,如嵌套循环(Nested Loop)、...

    ORACLE执行计划和SQL调优.pptx

    ORACLE 执行计划和 SQL 调优 ORACLE 执行计划和 SQL 调优是关系数据库管理系统中非常重要的概念。执行计划是 Oracle 优化器生成的,用于描述如何访问数据库中的数据的计划。execute plan 中包括了访问路径、表扫描...

    Oracle执行计划详解

    Oracle执行计划详解,包括oracle执行顺序和索引详细介绍

    Oracle执行计划与SQL优化实例.pptx

    《Oracle执行计划与SQL优化实例》这一主题深入探讨了数据库管理与优化的关键方面,尤其针对Oracle数据库环境。本文旨在解析并扩展此PPTX文件中提及的重要知识点,涵盖执行计划的概念、获取方法、解读技巧以及SQL优化...

    ORACLE执行计划和SQL调优

    ORACLE执行计划和SQL调优

    oracle执行计划详细解释

    oracle执行计划详细解释

    Oracle执行计划.ppt

    Oracle 执行计划 Oracle 执行计划是 Oracle 数据库中的一种机制,用于确定如何访问存储器,得到需要的结果集。执行计划的主要内容包括访问方式和访问顺序。下面是 Oracle 执行计划的详细知识点: 一、执行计划的...

    关于Oracle中执行计划稳定性深入研究

    总之,Oracle执行计划的稳定性与数据库性能紧密相关,理解并掌握优化器的工作原理以及如何控制执行计划的选择,是优化数据库性能的关键。在基于代价的优化器模式下,虽然存在执行计划不稳定的可能,但通过合理管理和...

    Oracle 执行计划稳定性

    总之,Oracle执行计划的稳定性对于数据库的高效运行具有重要意义。通过合理管理统计信息、选择合适的优化器模式、利用SQL提示和监控工具,可以有效控制执行计划的稳定性,从而提升数据库的整体性能。

    Oracle执行计划1.ppt

    "Oracle 执行计划" Oracle 执行计划是 Oracle 数据库中的一种机制,用于优化 SQL 语句的执行过程。它决定了如何访问存储器,得到需要的结果集。执行计划的主要内容包括访问方式和访问顺序。 Oracle 执行计划的生成...

Global site tag (gtag.js) - Google Analytics