Oracle ERP最佳技术实践 E-BUSINESS SUITE
ORACLE 性能优化之 执行计划
Author:
E-Mail:
Creation Date:
Last Updated:
Document Ref:
Version:
Approvals:
<Approver 1>
<Approver 2> 苏南生 nansheng.su#hand-china.com October 19, 2014 October 19, 2014 <Document Reference Number> DRAFT 1A Copy Number _____
Oracle ERP最佳技术实践
Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 Document Control
Change Record
Reviewers
Distribution
Oracle 性能优化-执行计划
Note To Holders: name on the equivalent of the cover page, for document control purposes. cover, for document control purposes. Document Control ii ompany Confidential - For internal use only
Oracle ERP最佳技术实践
Oracle 性能优化-执行计划 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 Contents Document Control .................................................................................................................. ii 1. 概要 ............................................................................................................................. 2 1.1 SQL性能优化概要 .............................................................................................................. 2 1.2 SQL语句缓存 ..................................................................................................................... 2 1.3 驱动表 ................................................................................................................................. 2 1.4 组合索引 ............................................................................................................................. 3 2. 执行计划 ..................................................................................................................... 5 2.1 执行计划概要 ..................................................................................................................... 5 2.2 执行计划案例一个 ............................................................................................................. 5 2.3 执行计划步骤 ..................................................................................................................... 6 2.4 全表扫描 ............................................................................................................................. 7 2.5 通过ROWID的表存取 ....................................................................................................... 8 2.6 索引扫描 ............................................................................................................................. 8 2.7 4种类型的索引扫描 ........................................................................................................... 9 3.未完待续 .............................................................................................................................. 11 3. Open and Closed Issues for this Deliverable ...................................................... 12 Open Issues ..................................................................................................................... 12 Closed Issues .................................................................................................................. 12 Document Control iii ompany Confidential - For internal use only
Oracle ERP最佳技术实践
1. 概要
Oracle 性能优化-执行计划 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 1.1 SQL性能优化概要 在Oracle数据库应用系统中几乎有80%的性能问题是有糟糕的SQL语句引起的。优化是选择最有效的执行计划来执行SQL语句的过程，这是在处理任何数据的语句中的一个重要步骤。对Oracle来说，执行这样的语句有许多不同的方法，譬如说，将随着以什么顺序访问哪些表或索引的不同而不同。所使用的执行计划可以决定语句能执行得有多快。Oracle中称之为优化器（Optimizer）的组件用来选择这种它认为最有效的执行计划。 1.2 SQL语句缓存 为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则ORACLE就不需要再进行分析，直接得到该语句的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用。使用这个功能的关键是将执行过的语句尽可能放到内存中，所以这要求有大的共享池(通过设置shared buffer pool参数值)和尽可能的使用绑定变量的方法执行SQL语句。
共享池过小，会导致数据库性能降低，通过如下语句修改共享池的大小。 1.3 驱动表 驱动表(Driving Table )。这个概念用于嵌套与HASH连接中。如果该表返回较多的行数据，则对所有的后续操作有负面影响。一般说来，是应用查询的限制条件后，返回较少行的表作为驱动表，所以如果一个大表在WHERE条件有限制条件(如等值限制)，概要 2 of 14
ompany Confidential - For internal use only
Oracle ERP最佳技术实践
Oracle 性能优化-执行计划 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行的表作为驱动表。在执行计划中，应该为靠上的那个表。 例如： 表A: cux_shipment_headers_all 头表 表B: cux_shp_deliveries_all 行表 情况一：
表cux_shipment_headers_all数据比较少，作为驱动表。 情况二：
表cux_shp_deliveries_all限定查询条件，返回数据少，作为驱动表。 1.4 组合索引 由多个列构成的索引，如create index idx_emp on emp(col1, col2, col3, ??)，则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1列为引导列。当我们进行查询时可以使用where col1 = ? ，也可以使用where col1 = ? and col2 = ?，这样的限制条件都会使用索引，但是where col2 = ? 查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引。 概要 3 of 14
ompany Confidential - For internal use only
Oracle ERP最佳技术实践
Oracle 性能优化-执行计划 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 在Oracle中可以创建组合索引，即同时包含两个或两个以上列的索引。在组合索引的使用方面，Oracle有以下特点： 1. 当使用基于规则的优化器（RBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引； 2. 在使用Oracle9i之前的基于成本的优化器（CBO）时， 只有当组合索引的前导列出现在SQL语句的where子句中时，才可能会使用到该索引，这取决于优化器计算的使用索引的成本和使用全表扫描的成本，Oracle会自动选择成本低的访问路径； 3. 从Oracle9i起，Oracle引入了一种新的索引扫描方式——索引跳跃扫描（index skip scan），这种扫描方式只有基于成本的优化器（CBO）才能使用。这样，当SQL语句的where子句中即使没有组合索引的前导列，并且索引跳跃扫描的成本低于其他扫描方式的成本时，Oracle就会使用该方式扫描组合索引； 4. Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择。 上面4个例子的测试，可以参考网络教程： /rootq/archive/2008/10/19/1314669.html 概要 4 of 14
ompany Confidential - For internal use only
Oracle ERP最佳技术实践
2. 执行计划 Oracle 性能优化-执行计划 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 2.1 执行计划概要 为了执行语句，Oracle可能必须实现许多步骤。这些步骤中的每一步可能是从数据库中物理检索数据行，或者用某种方法准备数据行，供发出语句的用户使用。Oracle用来执行语句的这些步骤的组合被称之为执行计划。执行计划是SQL优化中最为复杂也是最为关键的部分，只有知道了ORACLE在内部到底是如何执行该SQL语句后，才能知道优化器选择的执行计划是否为最优的。 2.2 执行计划案例一个 SQL代码：
执行计划： 执行顺序： 执行顺序的原则是：由上至下，从右向左 由上至下：在执行计划中一般含有多个节点，相同级别(或并列)的节点，靠上的优先执行，靠下的后执行 从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行。 执行计划 5 of 14
ompany Confidential - For internal use only
Oracle ERP最佳技术实践
Oracle 性能优化-执行计划 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 存取方法： 1 全表扫描（Full Table Scans, FTS 或 TABLE ACESS FULL） 2 通过ROWID的表存取（Table Access by ROWID或rowid lookup） 3 索引扫描（Index Scan或index lookup） 2.3 执行计划步骤
执行计划解析： 1) 第３步和第６步分别的从EMP表和SALGRADE表读所有行。 2) 第５步在PK_DEPTNO索引中查找由步骤３返回的每个DEPTNO值。它找出与DEPT表中相关联的那些行的ROWID。 3) 第４步从DEPT表中检索出ROWID为第５步返回的那些行。 4) 由黑色字框指出的步骤在行源上操作，如做2表之间的关联，排序，或过滤等操作。 5) 第２步实现嵌套的循环操作(相当于C语句中的嵌套循环)，接收从第３步和第４步来的行源，把来自第３步源的每一行与它第４步中相应的行连接在一起，返回结果行到第１步。 执行计划 6 of 14
ompany Confidential - For internal use only
Oracle ERP最佳技术实践
Oracle 性能优化-执行计划 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 6) 第１步完成一个过滤器操作。它接收来自第２步和第６步的行源，消除掉第２步中来的，在第６步有相应行的那些行，并将来自第２步的剩下的行返回给发出语句的用户或应用。 执行计划步骤： 1) 首先，Oracle实现步骤３，并一行一行地将结果行返回给第２步。 2) 对第３步返回的每一行，Oracle实现这些步骤： 3) Oracle实现步骤５，并将结果ROWID返回给第４步。 4) Oracle实现步骤４，并将结果行返回给第２步。 5) Oracle实现步骤２，将接受来自第３步的一行和来自第４步的一行，并返回给第１步一行。 6) Oracle实现步骤６，如果有结果行的话，将它返回给第１步。 7) Oracle实现步骤１，如果从步骤６返回行，Oracle将来自第２步的行返回给发出SQL语句的用户。 2.4 全表扫描 为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序读取分配给表的每个数据块，直到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块(db_block_multiblock_read_count参数设定)，而非只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以高效实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。由于HWM标识最后一块被读入的数据，而delete操作不影响HWM值，所以一个表的所有数据被delete后，其全表扫描的时间不会有改善，一般我们需要使用truncate命令来使HWM值归为0。幸运的是oracle 10G后，可以人工收缩HWM的值。 由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部，这样可以使其快速交换出内存，从而不使内存重要的数据被交换出内存。 使用FTS的前提：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询功能时。 执行计划 7 of 14
ompany Confidential - For internal use only
Oracle ERP最佳技术实践
Oracle 性能优化-执行计划 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 2.5 通过ROWID的表存取 行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。 为通过ROWID存取表，Oracle 首先要获取被选择行的ROWID，或从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。 此存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们经常在执行计划中看到该存取方法，如通过索引查询数据。
2.6 索引扫描 通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据。 一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，该次i/o只会读取一个数据库块。 在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。索引扫由2步组成： (1) 扫描索引得到对应的rowid值。 (2) 通过找到的rowid从表中读出具体的数据。 执行计划
8 of 14
ompany Confidential - For internal use only
Oracle ERP最佳技术实践
Oracle 性能优化-执行计划 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 每步都是单独的一次I/O，但对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第1步的I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，则其I/O很有可能是物理I/O，这是一个机械操作，相对逻辑I/O来说，极其费时间。所以如果对大表进行索引扫描，取出的数据如果大于总量的5% -- 10%，使用索引扫描会效率下降很多。 2.7 4种类型的索引扫描 索引唯一扫描(index unique scan) 索引范围扫描(index range scan) 索引全扫描(index full scan) 索引快速扫描(index fast full scan) 1) 索引唯一扫描(index unique scan) 通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中，如创建一个索引：create index idx_test on emp(ename, deptno, loc)。则select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。而select ename from emp where deptno = ‘DEV’语句则不会使用该索引，因为where子句种没有引导列。如果存在UNIQUE 或PRIMARY KEY 约束（它保证了语句只存取单行），Oracle经常实现唯一性扫描。 参考上面的执行计划截图。 2) 索引范围扫描(index range scan) 使用索引存取多行数据，如果索引是组合索引，如索引唯一扫描所示，且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句返回多行数据，虽然该语句还是使用该组合索引进行查询，可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between)。 执行计划 9 of 14
ompany Confidential - For internal use only
Oracle ERP最佳技术实践
Oracle 性能优化-执行计划 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。 3) 索引全扫描(index full scan) 与全表扫描对应，也有相应的全索引扫描。在某些情况下，可能进行全索引扫描而不是范围扫描，需要注意的是全索引扫描只在CBO模式(基于成本的优化方式)下才有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时，才进行全索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。
4) 索引快速扫描(index fast full scan) 扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。 执行计划
10 of 14
ompany Confidential - For internal use only
Oracle ERP最佳技术实践
Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。
3.未完待续
Oracle 性能优化-执行计划
ompany Confidential - For internal use only 3.未完待续 11 of 14
Oracle ERP最佳技术实践 Doc Ref: <Document Reference Number> October 19, 2014错误！未找到引用源。
3. Open and Closed Issues for this Deliverable
Add open issues that you identify while writing or reviewing this document to the open issues section. As you resolve issues, move them to the closed issues section and keep the issue ID the same. Include an explanation of the resolution.
When this deliverable is complete, any open issues should be transferred to the project- or process-level Risk and Issue Log (PJM.CR.040) and managed using a project level Risk and Issue Form (PJM.CR.040). In addition, the open items should remain in the open issues section of this deliverable, but flagged in the resolution column as being transferred.
Open Issues
Closed Issues
Oracle 性能优化-执行计划
Open and Closed Issues for this Deliverable 12 of 14
ompany Confidential - For internal use only