9i Performance Tuning Guide 读书笔记一

转自: http://www.heysky.net/archives/2006/08/9iperformancetuningguide.html[@more@]

Introduction to the Optimizer

Overview of SQL Processing
1. parser 进行语意和语法分析
2. Optimizer 利用RBO,CBO等方法决定产生查询结果的最有效路径
3. Row Source Generator 从2中接受优化后的方案，并产生SQL的Execution Plan
4. SQL Execution Engine运行该执行计划，并产生查询结果

Features that Require the CBO
1. Partitioned tables and indexes
2. Index-organized tables
3. Reverse key indexes
4. Function-based indexes
5. SAMPLE clauses in a SELECT statement
6. Parallel query and parallel DML
7. Star transformations and star joins
8. Extensible optimizer
9. Query rewrite with materialized views
10. Enterprise Manager progress meter
11. Hash joins
12. Bitmap indexes and bitmap join indexes
13. Index skip scans
即使OPTIMIZER_MODE＝rule，这些features仍然会使用CBO

Components of the CBO Query Transformer
数据来自于parse后的SQL,是一系列的query block。其目标是测定SQL的形式是否有利于产生好的query plan。有以下4种：
1. View Merging
SQL 中的view 被扩展到单独的query block中。Optimizer会单独分析view query block，这样通常会导致在整体优化上得不到最优的结果。因此query transformer会将大部分view和其他的query block 合并，这样可以在整体上统一优化。
2. Predicate Pushing
针对没有被merge的view, push the relevant predicates from the containing query block into the view query block, which can be used either to access indexes or to act as filters
3. Subquery Unnesting
子查询是nested在主查询中的，这样很难得到好的优化结果。所以将他们unnested，变成join
4. Query Rewrite with Materialized Views
如果查询与某个物化视图符合的化，则会按照物化视图重写这个查询，因为物化视图的结果都是事先计算好的。

Estimator
产生 3 种度量标准：
1. Selectivity
表示有多少 rows 可以通过谓词被选择出来，大小介于 0.0~1.0，0 表示没有 row 被选择出来。
如果没有 statistics，estimator 会使用一个默认的 selectivity 值，这个值根据谓词的不同而异。比如 '=' 的 selectivity 小于 ' 如果有 statistics，比如对于 last_name = 'Smith'，estimator 使用 last_name 列的 distinct 值的倒数（注：是指表中所有 last_name 的 distinct 值），作为 selectivity。
如果 last_name 列上有 histogram，则使用 histogram 根据 last_name 值的分布情况产生的 selectivity 作为 selectivity。Histogram 在当列有数据倾斜时可以大大帮助 CBO 产生好的 selectivity。

2. Cardinality
表示一个 row set 的行数。
Base cardinality：base table 的行数。如果表分析过了，则直接使用分析的统计信息。如果没有，则使用表 extents 的数量来估计。
Effective cardinality：有效行集，指从基表中选择出来的行数。是 Base cardinality 和表上所有谓词的组合 Selectivity 的乘积。如果表上没有谓词，那么 Effective cardinality = Base cardinality。
Join cardinality：两表 join 后产生的行数。是两表 cardinality 的乘积(Cartesian)乘以 Join 谓词的 selectivity。
Distinct cardinality：列上 distinct 值的行数。
Group cardinality：GROUP BY 操作之后 row set 的行数。由 grouping columns 的 distinct cardinality 和整个 row set 的行数决定。
group cardinality lies between max ( dist. card. colx , dist. card. coly )
and min ( (dist. card. colx * dist. card. coly) ,
num rows in row set )

3. Cost
Cost 表现了 Disk I/O, CPU usage, Memory usage 资源单位的使用数量（units of work or resource used）。
Access path 决定从 base table 获得数据所需的 units of work 的数量。也就是说Access path 决定 Cost 的值。Access path 可以是 table scan, fast full index scan, index scan。

Clustering Factor：
Index 的一种属性，表示被索引的行在数据块中的分布情况，表征表中数据的存储顺序和某索引字段顺序的符合程度。直接影响使用 rowid 找到 row 的cost。大小介于 block 数和 rownum 之间。
（以下来自biti_rainy http://blog.itpub.net/post/330/2970 ）
Oracle 按照索引块中存储的 rowid 来识别相临的索引中记录在表 block 中是否为相同块，如果索引中存在记录 rowid a,b,c,d……,若b 和 a 是同一个block，则比较 c 和 b，若这时不是同一个block，则 clustering_factor + 1 ,然后比较 d 和 c，若还不是同一个 block，则clustering_factor + 1……
若 clustering_factor 接近表 block 数量，则说明表中数据具有比较好的跟索引字段一样排序顺序的存储，通过索引进行 range scan 的代价比较小（需要读取的表块可能比较少）,若 clustering_factor 接近 row 数量，则说明表中数据和索引字段排序顺序差异很大，杂乱无张。则通过索引进行 range scan 的代价比较大（需要读取的表块可能更多）。
当然，在 oracle 920 开始，对于cluster_factor 比较接近表块数量的根据索引的大范围查询做了特别的处理，不再是读一个索引记录去搜索一个表记录了，而是成批处理（通过索引块一批 rowid 一次去表块中获得一批记录），这样就大大节约了读的成本（consistent gets）。

Join Cost：
表征了做 join 的两个 row sets 分别 cost 的组合。
Nested loop join：
outer row set 的每行遍历 inner row set 的所有行，寻找匹配的行。
cost = outer access cost + (inner access cost * outer cardinality)
Sort merge join：
做 join 的两个 row sets 根据 join keys 进行排序，如果他们不是按照 join keys 的顺序的话。
cost = outer access cost + inner access cost + sort costs (outer and inner, if sort is used)
Hash join：
cost = (outer access cost * # of hash partitions) + inner access cost
首先理解 hash table 的数据结构：
可以把 hash table 看做一个 2 维数组 a[200][100]，现有 1000 个无序数字用来被查询。我们考虑把这 1000 个数字除以 200，根据其余数放在 a[200][100] 中，余数就是数组的第一维下标。这样平均一个 a[i] 只放5个数字。当查询的时候，对数字除以 200（这就是一个简单的 hash 算法），根据余数 i 去 a[i] 中查找，大约遍历 5 次就能找到。
Inner row（小表）被 hash 在内存中，并且通过 join key 建立 hash table（作为第一个下标），然后 scan outer table，到 hash table 中查找 joined rows（通过 hash 算法）。
hash table 会按照 multiblock_IO 决定分成几个 partitions。如果 hash table 太大超出了 hash_area_size，则将超出部分的 partitions 放到 temporary segments 中。
可以通过 10104 events 查看 hash join 的 statistics：
ALTER SESSION SET EVENTS '10104 trace name context forever, level 10'; 比如：
Total number of partitions:
Number of partitions which could fit in memory:
如果后者大于前者，则说明一些 partitions 因为超出了 hash_area_size，要被放置到临时表空间中。

Plan Generator
作用是尝试各种可能的执行计划，选择 cost 最低的一种。
Plan Generator 会先为 nested subqueries and nonmerged views 产生 subplans，并且从 innermost query block 开始往外优化。
Plan Generator 会使用 internal cutoff 来减少 plan 的尝试数量。internal cutoff 基于现有的最优计划的 cost。如果很大，那么会尝试较多的计划；如果很小，那么会很快结束尝试。
如果 plan generator 从一个接近最优的 initial join order 开始，那么 internal cutoff 可以很好的工作。Plan Generator 根据 join items 的 effective cardinality 来确定 initial join order，小的在前面，大的在后面。

Understanding Execution Plans
Execution Plan：
为了执行 SQL 语句，Oracle 会执行很多步骤，这些步骤的综合叫做 execution plan，包括 access path 和 join order。

Understanding Access Paths for the CBO
Access paths：
从数据库获得数据的方式。

Full Table Scans
表中所有在 HWM 以下的 blocks 都被扫描一遍，确定符合 where 条件的行。
块的读取范围从 1 到 DB_FILE_MULTIBLOCK_READ_COUNT。Multiblock reads 可以提高执行效率，当访问表中大量块时 full table scans 比 index range scans 效率高。
优化器选择 Full Table Scans 的情况：
Lack of Index
Large Amount of Data
Small Table --大小表的区分由 _small_table_threshold 隐含参数决定，默认为 db_cache_size 的 2%。 http://www.eygle.com/archives/2006/05/oracle_long_short_table.html
High Degree of Parallelism
Full Table Scan Hints：/*+ FULL(table alias) */

Rowid Scans
获得一行数据的最快方法。
一般要先通过 index scan 获得 Rowid，如果需要的列不在 index 中，再进行 Rowid Scans 获得相应的行，如果在 index 中，则不需要 Rowid Scans。

Index Scans
Index Unique Scans
最多返回一个 rowid，用于 Unique Index 且 index cols 在条件中使用“等于”。
Index Range Scans
返回的数据按照 index columns 升序排列，index column 为同一个值的多行按照行 rowid 的升序排列。如果 order by/group by 的顺序和 Index Range Scans 返回的 row set 的顺序相同就不需要再 sort 了，否则还需要再对 row set 进行 sort。
Unique index 中的 < > 条件，以及 nonunique indexe 的 < = > 条件，都会引起 Index Range Scans。如果进行 wild-card searches，% 不能放最前面，否则不会进行 Index Range Scans。
如果某列上有索引，该列有 skewed distribution，且具有 histograms，但当时用 bind variable 时，Oracle 不知道该变量具体是什么值，而无法使用 histograms，导致选择 full table scan，这种情况下可以通过使用 hints 进行调整。
Index Range Scans Descending
和 Index Range Scans 相同，只是用于降序返回结果，或者返回小于某特定值的结果。
HINT：INDEX_DESC(table_alias index_name)
Index Skip Scans
用于前导列没有出现在查询中（skiped）时使用索引。它将 composite index 拆分成若干个小的逻辑子索引。子索引的个数由前导列的 distinct 值决定。适用于前导列 distinct 值很少（子索引就少了），非前导列 distinct 值很多的情况。
Full Scans
适用于：
1.A predicate references one of the columns in the index. The predicate does not need to be an index driver.
2.No predicate，并且：
A.查询中引用的列都在 index 中
B.只少有一个索引列不为空。
它是先定位到索引的 root block，然后到 branch block（如果有的话），再定位到第一个 leaf block，然后根据 leaf block 的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。
Fast Full Index Scans
和 Full Scans 适用于：查询的所有列都在索引中出现，且至少有一个索引列具有 NOT NULL 约束。区别在于 它是从段头开始，读取包含位图块，root block，所有的branch block，leaf block，读取的顺序完全有物理存储位置决定，并采取多块读，没次读取db_file_multiblock_read_count个块。（更详细的说明参见汪海的《Index Full Scan vs Index Fast Full Scan 》 http://www.dbanotes.net/Oracle/Index_full_scan_vs_index_fast_full_scan.htm）
Fast Full Index Scans 可以利用多块读和并行读，只能用于 CBO，不能在 bitmap indexes 上使用。
当需要排序时，Oracle 会使用 Full Index Scans，因为他的结果已经排好序；当不排序时，会使用 Fast Full Index Scans，因为能使用多块读，速度更快。
在 rebuild index 时使用的就是 Fast Full Index Scans，所以 rebuild index 需要排序。（参见汪海的 《Rebuild Index与 Sort》http://www.dbanotes.net/Oracle/Rebuild_Index_vs_Sort.htm）
HINT：INDEX_FFS(table_alias index_name)
Fast Full Index Scan 限制：
索引列中至少有一列有 NOT NULL 约束
如果要用并行 fast full index scan 必须在创建索引时单独指定 parallel clause，不能从索引所在的表上继承
必须分析索引，否则优化器可能不会使用它
Index Joins
他是一个有若干个 indexes 组成的 hash join，包含查询中需要的所有列。
他无法 eliminate a sort operation，必须在 CBO 中使用。
HINT：INDEX_JOIN
Bitmap Joins
A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions.
只能使用于 CBO，且需要企业版。

Cluster Scans 用于从存放于 indexed cluster 中的表中获得相同 cluster key 值的数据。具有相同 cluster key 值的所有数据存放于同一个 BLOCK。通过扫描 cluster index 获得相应的 rowid，再通过 rowid 定位到所需的行。

Hash Scans
用于从存放于 hash cluster 中的表中获得相同 hash value 值的数据。具有相同 hash value 值的所有数据存放于同一个 BLOCK。通过将 hash function 应用于 cluster key 值上，获得 hash value，再通过 hash value 定位到所需的行上。

Sample Table Scans
从表中获得 a random sample of data。
SAMPLE clause：从表中随机获得指定百分比的行数据。
SAMPLE BLOCK clause：从表中随机获得指定百分比的块数据。
限制：
查询不能包含 a join or a remote table
需要使用 CBO

How the CBO Chooses an Access Path
1.语句所有可用的 access paths
2.使用每种 access paths 或者 combination of paths 时，估计的 cost 值
影响 CBO 的因素：
1.Optimizer Hints
hints 会覆盖可用的 access paths，除非包含 SAMPLE or SAMPLE BLOCK
2.Old Statistics
影响 cost 的估计


Understanding Joins
How the CBO Executes Join Statements
决定一下几个参数：
Access Paths
Join Method（nested loop, sort merge, cartesian, and hash joins）
Join Order

How the CBO Chooses the Join Method
估计每个 join method 的 cost，选择 cost 最少的那种 join method。
如果 join 返回大量行（一般来说，大于 10000 行），考虑以下因素：
1.nested loop join 是低效的，优化器不会使用它
nested loop join cost= access cost of A + (access cost of B * number of rows from A)
2.CBO 中，hash join 是最高效的
hash join cost= (access cost of A * number of hash partitions of B) + access cost of B
3.RBO 中，merge join 是最高效的
merge join cost= access cost of A + access cost of B +(sort cost of A + sort cost of B)
（如果数据是预先排序好的，sort cost 为 0）

How the CBO Chooses Execution Plans for Join Types
CBO，RBO 都适用的：
1.优化器通过 UNIQUE and PRIMARY KEY 约束找到最多返回一行的表，如果这样的表存在，就把它放在连接顺序的第一位，再处理连接中的其他表。
2.For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule.
CBO：
估计各种 join orders, join methods, and available access paths 的 cost，选择 cost 最低的
其他因素：
1.较小的 sort area size 会增加 sort merge join 的 cost，因为需要更多的 CPU time and I/O
2.较大的多块读相对于 nested loop join 来说，会减少 sort merge join 的 cost。
CBO 中 join orders 的选择会被 ORDERED hint 覆盖，但如果 HINT 和 outer join 的规则冲突，那么 HINT 会被忽略。

Nested Loop Joins
当 small subsets of data 被连接，并且 join condition 对于 inner table 来说是一种高效的访问方式时，nested loop joins 是非常有用的。
必须保证 inner table is driven from (dependent on) the outer table，否则性能很差，这种情况适用 hash joins 更好。
HINT：USE_NL(table1_alias table2_alias)

Hash Joins
Hash joins 适用于连接 large data sets。优化器选择较小的 data source 通过 join key 建立一个 hash table 存放在内存中。通过扫描大表，查询 hash table 找到所需的数据。
当 hash table 太大无法放到内存中，他会被拆分，一部分放到 temporary segments 中，这时 temporary extent sizes 影响 I/O 性能。这种情况下，性能很差。
当连接使用 equijoin，并且以下任何一种情况下，会使用 hash join：
1. A large amount of data needs to be joined.
2. A large fraction of the table needs to be joined.
Execution Expain 中在上面的是先被扫描的小表，用作建立 hash table，在下的是大表。
HINT：USE_HASH(table1_alias table2_alias)
当使用 Hash Joins 时碰到问题时，应该注意 HASH_AREA_SIZE and HASH_JOIN_ENABLED 参数。

Sort Merge Joins
Sort Merge Join 没有驱动表的概念，他的执行步骤：
1.Sort join operation: 两个表分别按照 join key 排序 （如果已经按照 join key 排序，这步省略）
2.Merge join operation: 将排好序的结果 merge together.
性能比较：
Sort Merge Join 适用于两个 independent sources。一般来说 Hash join 比 Sort Merge Join 性能好，除非以下两个条件同时满足
1.row sources 已经被排序
2.排序操作不需要再进行
如果 Sort Merge Join 选择了 slower access method，比如全表扫描，他的优势就失去了。
当两个表通过 , or >= 连接时，Sort merge join 是很有用的，在进行两个大 data sets 连接时，他比 nested loop join 性能好，hash join 适合用 = 连接。
当两个大数据量的 data sets 连接时，优化器何时选择 sort merge join 而不选择 hash join：
1.连接条件不是 equi-join，而是 , or >= (but not a nonequality)
2.OPTIMIZER_MODE is set to RULE，hash join 需要 CBO
3.HASH_JOIN_ENABLED is false.
4.连接的表已经排好序
5.评估 HASH_AREA_SIZE and SORT_AREA_SIZE 大小之后，优化器认为 hash join 的 cost 较高
HINT：USE_MERGE(table1_alias table2_alias) 可能还需要指定相应的 access path

Cartesian Joins
当两个表连接时，没有指定连接条件，就会导致 Cartesian Join，一般是由于 poor SQL 造成的。
HINT：ORDERED，By specifying a table before its join table is specified, the optimizer does a Cartesian join.

Outer Joins
返回一个表满足连接条件的所有行以及另一个表的全部或者部分行，不管这些行符不符合连接条件。

Nested Loop Outer Joins
返回 outer (preserved) table 的所有行，即使 inner (optional) table 没有符合条件的行。
Nested Loop Outer Join 时，外部表永远是驱动表，不根据 cost 来决定哪个是驱动表。
在以下条件下，优化器选择 Nested Loop Outer Join
1.外部表驱动内部表是可能的
2.数据量小到使得 nested loop 效率较高

Hash Join Outer Joins
当连接的表的数据量大到使用 hash join 效率更高，或者外部表无法驱动内部表时，优化器选择 Hash Join Outer Joins。
表的连接顺序也不按照 cost 来决定，外部表先进行处理，用它构建 hash table。

Sort Merge Outer Joins
当外表不能驱动内表，无法使用 hash join or nested loop joins 时，那么使用 Sort Merge Outer Joins。
由于数据量或者表已经经过排序操作，使得 Sort Merge Outer Joins 效率更高时，优化器选择 Sort Merge Outer Joins。

Full Outer Joins
Left and right outer joins 的联合。


Setting Cost-Based Optimizer Parameters
Enabling CBO Features
OPTIMIZER_FEATURES_ENABLE
后面跟版本号，设置 Oracle 允许哪些 CBO 相关的特征被使用，只允许被设置了的版本的 CBO 特征，其他的不允许，在升级版本之后，为了执行计划的稳定性和向后兼容可以使用，否则不需要设置。
Peeking of User-Defined Bind Variables
在第一次 invocation of a cursor 的时候，Oracle peeks at 用户定义绑定变量的值，来决定所有 where 条件的 selectivity，即使没有使用绑定变量。在这之后的 invocations of the cursor 就不用再 peek 了，cursor 被共享，即使绑定变量的值不同。
使用绑定变量假设 cursor sharing 是故意的，并且假设不同的 invocations 使用相同的执行计划，如果没有使用相同的执行计划，那么绑定变量的使用可能不正确。

Controlling the Behavior of the CBO
CURSOR_SHARING
将语句中的 literal values 转为绑定变量，提高 cursor sharing 并且影响语句的执行计划，生成的执行计划将基于绑定变量而不是 actual literal values。
DB_FILE_MULTIBLOCK_READ_COUNT
Full table scan or index fast full scan 时，一次 I/O 读取的块数。用于估计 full table scans and index fast full scans 的 cost。大的 DB_FILE_MULTIBLOCK_READ_COUNT 值使得 full table scans 的 cost 较低，从而选择 full table scans 而不是 index scan。
HASH_AREA_SIZE
用于 hash joins 的内存大小（bytes），越大 hash join 的 cost 越低。
HASH_JOIN_ENABLED
是否使用 hash joins
OPTIMIZER_INDEX_CACHING
控制 an index probe in conjunction with a nested loop 的 cost。范围 0~100，表明索引块在 buffer cache 中的百分比。他影响优化器对 index caching for nested loops and IN-list iterators 的假设。100 表示 100% 的索引块在 buffer cache 中能找到，这将影响优化器对 an index probe or nested loop cost 的调整。
OPTIMIZER_INDEX_COST_ADJ
用于调整 index probe 的 cost。范围 0~10000，默认值 100，表示 indexe 是基于 normal costing model 的 access path；如果设为 10，表示 index 的 cost 是一般 index access path 的 cost 的 1/10。
OPTIMIZER_MAX_PERMUTATIONS
用于控制 CBO 对带有连接的 SQL 语句产生的执行计划数。范围 4 to 80000，80000 相当于无限。当将他设置成小于 1000 可以保证 parse times 降到几秒甚者更少。这个参数可以用来减少多表连接语句的 parse times，但是可能会丢失最优的执行计划。
OPTIMIZER_MODE
设置优化器的模式：RULE, CHOOSE, ALL_ROWS, FIRST_ROWS_n, and FIRST_ROWS
PARTITION_VIEW_ENABLED
是否使用 partition view pruning。如果设置成 true，CBO 只扫描需要的 partitions，根据 view predicates or filters。
QUERY_REWRITE_ENABLED
是否使用 query rewrite 的特性。Query rewrite 是和 materialized views 一起工作的。如果设置为 true，Oracle 会考虑使用 query rewrite 来查询 materialized views 而不是原始的大表（参见 D.C.B.A 对 query rewrite 的解释 http://www.anysql.net/2005/12/queryrewrite01.html）。另外该参数还用来控制是否使用 function-based indexes。
SORT_AREA_SIZE
执行 sort 所使用的内存大小（bytes）。如果 sort 的数据超过该值，那么超过的部分会放到 temporary tablespace 中。CBO 用该值估量 sort 的 cost，包括 sort merge joins。
STAR_TRANSFORMATION_ENABLED
This parameter, if set to true, enables the CBO to cost a star transformation for star queries. The star transformation combines the bitmap indexes on the various fact table columns rather than using a Cartesian approach.（D.C.B.A 对该功能的测试：http://www.anysql.net/2006/03/dw_star_transform.html）

Overview of the Extensible Optimizer
The extensible optimizer is part of the CBO. It allows the authors of user-defined functions and domain indexes to control the three main components that the CBO uses to select an execution plan: statistics, selectivity, and cost evaluation.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271283/viewspace-1008614/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271283/viewspace-1008614/