<转>oracle性能调整读书笔记(2)

 

第三章 SQL 调整 3. 稳定执行计划 xV&c ) l>} !^{0vF WE > W fkWUb 改善应用程序性能 [{]/9E /& 包括两个方面：改进执行路径（稳定执行计划，物化视图）和最小化IO（索引，分区， 簇）； [D !-~ ]5 xLxXc!{J5 稳定执行计划 V}?*kx~T2C · 可以用存储在OUTLN方案的公共大纲或者存储在本方案的私有大纲来稳定某些SQL语句的执行计划，这样这些语句的执行计划就不会因为统计数据或者会话优化模式的改变而发生改变； Z>0a?=1[ · 创建大纲时可以指定类别，默认的类别是DEFAULT； I4 [ sf · 创建大纲时，是将SQL当前的执行计划和SQL文本一起存储起来； h( M_ K · 激活大纲有三种方式，SQL文本和大纲完全一致时才会使用大纲中存储的执行计划； fN)A`>iP Ø 在参数文件中加上USE_STORED_OUTLINES=TRUE )4nf={ iM Ø ALTER SYSTEM SET USE_STORED_OUTLINES={TRUE | FALSE | category_name}; #6fQ$x(F#j Ø ALTER SESSION SET USE_STORED_OUTLINES={TRUE | FALSE | category_name}; % A q t0e · 当SQL语句执行时，ORACLE确定执行计划是按下面的顺序进行的，检查共享池中是否有可用的大纲 à 检查字典中是否有可用的大纲à 检查共享池中是否有可共用的已解析SQL à 考查优化模式和相关统计产生并选择最优的执行计划，前面三种情形下都有现成的执行计划； kGz0`8U Ru · 相关的数据字典视图有：DBA_OUTLINES， DBA_OUTLINE_HINTS。 !?AgA sSmc bD2):U*Fzo O V" 5:){ 第三章 SQL 调整 4. 物化视图 *KMW 6dg; sKg IKYG}T \ GF 9;N}V · 物化视图用数据段存储预连接，预汇总的查询数据，物化视图可以有索引，也可以分区，物化视图主要应用于数据仓库和决策支持系统； _?}[7K! ~d · 创建物化视图时需考虑以下因素： Ii .0B ul 1. 确定视图语句，是何种连接，何种汇总；可以借助Summary Advisor来确定最佳的物化视图SQL语句，并检查已创建物化视图的使用情况； 2. 确定刷新方式：NEVER REFRESH（不刷新），REFREST FAST（借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）， REFRESH COMPLETE（先清除，再重装数据）， REFRESH FORCE（先试图用FAST方式刷新，如果失败再用COMPLETE方式刷新，这是默认的刷新方式）； J<9; Ix 8R 3. 确定刷新时机：ON COMMIT（事务提交时刷新）， ON DEMAND（用DBMS_MVIEW.REFRESH， DBMS_MVIEW.REFRESH_DEPENDENT， DBMS_MVIEW.REFRESH_ALL_MVIEWS来手工刷新）， By Time（用START WITH 和 NEXT 子句创建的job来定时自动刷新）； Fkv f[!Ci 4. 创建方式：BUILD IMMEDIATE（立即生成数据）， BUILD DEFERRED（下一次刷新时生新数据）， ON PREBUILD TABLE（不创建新的数据段，用已存在的含有当前物化视图数据的表来代替）； _{ C =d 3 5. ENABLE | DISABLE QUERY REWRITE指定是否启用当前物化视图用于查询重写，启用该选项时，系统会检查以保证查询的可确定性（不允许有如序列数，USER， DATE等不确定的返回值），DISABLE时物化视图照样可以被刷新； , hVDGif z +5% .^Re · 与物化视图生效相关的设置 A xAb U7m 1. 初始化参数JOB_QUEUE_PROCESSES设置大于零，物化的自动刷新操作需要JOB QUEUE进程来执行； PNp -/1 Cx 2. 初始化参数OPTIMIZER_MODE要设成某种CBO优化模式； Zs to8wuf# 3. 用户会话有QUERY_REWRITE（优化器能将查询重写到本方案物化视图）或GLOBAL_QUERY_REWRITE（优化器能将查询重写到其它方案的物化视图）系统权限； F @l d#O 4. 初始化参数QUERY_REWRITE_ENABLED 指示优化器是否动态重写查询来使用物化视图，这个参数可以在四个级别上进行设置（参数文件，ALTER SYSTEM， ALTER SESSION， HINTS）； o: c : hSV 5. 初始化参数QUERY_REWRITE_INTEGRITY 指示优化器在不同的数据一致性情况下决定是否使用物化视图来重写查询，ENFORCED（只有在能确保数据一致的前提下才使用物化视图）， TRUSTED（数据不一定一致，只要有用维度对象定义的关系存在，就可使用物化视图）， STALE_TOLERATED（数据不一致，也没有相关的维度定义时仍可使用物化视图），这个参数可以在三个级别上进行设置（参数文件，ALTER SYSTEM， ALTER SESSION）； 第三章 SQL 调整 5.索引 x0||' 0I0 |l Mc6C >)ed ha*W] 索引 @ ;7Ht Z` ORACLE 9i 中有六种索引：二叉树索引，压缩的二叉树索引，位图索引，基于函数的索引，反向索引，索引组织表； +c tJV> ,!QtVi A7 二叉树索引 b !T-{Ns6 · 二叉树索引将索引栏位值和ROWID一起保存树状结构中，适用于只存取表中总记录的5%以下的查询； \u*[mrX_B: · 出现在SQL的WHERE条件中，集势高的栏位适于当作索引栏位； ;v 5Jps2^] · 随着基表数据的不断增加，索引块会不断分裂以保持二叉树的平衡，树的层次（从根结点访问到叶结点要经过的数据块数，DBA_INDEXES.BLEVEL）也不断增加，层次大于4的索引宜重建； \ t]_UNGyW · 基表数据删除时，索引项也随之删除，但叶块上的空间并不能被重用，除非该叶块上所有的索引项都被删除，当删除项占所有项超过20%时，这个索引也需要重建（ANALYZE INDEX … VALIDATE STRUCTURE;分析索引后查看index_stats.del_lf_rows_len 和 index_stats.lf_rows_len 可以知道被删除的索引项占用的空间和所有叶行占用的空间）； W?ge lu ] · 有三种重建索引的方法： iCy $ rC 1. 先删除再重建；这种方法耗费最多的资源，是早期版本的唯一方法； q> m [vvt" 2. ALTER INDEX … REBUILD; 这种方法高效快速，但需要额外的磁盘空间；用这种方法可以指定许多选项如ONLINE（在线重建可减少锁争用）， TABLESPACE（移动段到其它表空间）， COMPUTE STATISTICS（统计）， PARALLEL（并行）， NOLOGGING（尽可能少地产生日志）； {j %7/T{ 3. ALTER INDEX … COALESCE; 这种方法快速，无需额外空间，锁争用也少，缺点是选项少。 ( H -kW T @ ; Xa&* 压缩的二叉树索引 vP ^ V 3 · 压缩的二叉树索引对重复的索引键值只保存一次，后跟所有的有这个键值行的ROWID； aK-- D2@}i · 有两种方法创建： s,~g| I\ 1. CREATE INDEX … COMPRESS; L/)Q 1Mm 2. 先创建普通索引，再更改：CREATE INDEX …; ALTER INDEX … COMPRESS; J#?z/3 v( BO'7c 1FU 位图索引 Sw` +4 4 · 位图索引适于创建在数据相对稳定的表的低集势栏位上； s/ Q8(sF5 · 位图索引针对每一个键值创建一个二进制位图，位图中的每位对应表中的一行，1表示这一行是当前键值，0表示这一行为其它键值； KdC '#$ · 与二叉树索引相比，位图索引占的空间很少，如果WHERE条件中出现同一表的多个位图索引栏位时，无论是AND或是OR操作，都能用位图合并操作快速地定位到ROWID； j>#ywh*A · 当基表上有DML操作时，整个位图都会被锁住，但是，每一个DML操作，位图只需更新一次； =2 1m| 8c · 相关参数： !wH' dsriD 1. SORT_AREA_SIZE 创建索引排序位图栏位和ROWID时会用到； _J>!K'Dz 2. PGA_AGGREGATE_TARGET 创建位图索引以及位图合并时会用到； z~# d@c \ 3. CREATE_BITMAP_AREA_SIZE， BITMAP_MERGE_AREA_SIZE 这是8i的两个与位图创建和合并有关参数，在9i建议不再使用。 ?7TuE!!M KU ;J2Kt 基于函数的索引 o T "7O 5v · 当栏位以函数表达式的形式出现在WHERE条件中，基于这些栏位的索引不会被优化器用到，除非是基于这些栏位的函数索引； = ,Z5F`d 4 · 要创建基于函数的索引需要QUERY REWRITE 或 GLOBAL QUERY REWRITE系统权限，有CREATE ANY INDEX权限是不行的； >9t+lr1 · 要想优化器使用基于函数的索引，OPTIMIZER_MODE必须设成某种CBO模式，QUERY_REWRITE_ENABLED须设成TRUE（这点从试验中无法证明，设成FALSE似乎也能用到函数索引）。 Q$ A;Fk}- l fqsoIn; 反向索引 Nh1 , w · 反向索引是建立在索引栏位值的反向值上的，可以使相临的记录在索引段里变得离散，可以减少某些DML操作引发的块竞争； X 1}U · 反向索引多建立在用序列号生成的栏位上，这样的栏位在普通索引中容易形成更多的层（设计过一些试验，但一直未观察到这种现象）； / @0 · 有两种产生方法：CREATE INDEX … REVERSE; ALTER INDEX … REVERSE; lV gin5 4Q · 反向索引适于WHERE中的等于或不等于的比较，对于范围查找（>,< between）是无能为力的。 6NG QU%H d Og?P5&C"9D 索引组织表 J/\^ 3r CB · 前面五种索引的基表数据存储是随机的，这种表称为堆表； spT I hZ · 索引组织表的数据存储在二叉树索引中，所以，如果通过主键来存取数据，索引组织表能更快地返回数据，因为不需要先取索引块再取数据块，可以直接从索引中返回数据； \{ff7_mLo · 创建索引组织表时必须指定一个主键栏位，用这个作为索引； C W?R7A/ · 索引组织表上不能建唯一约束，也不能将索引组织表建立在簇上面； )V9 wU1. · 索引组织表的语法要点： o Fp4* Ø ORGANIZATION INDEX 指明当前表是索引组织表； z W* Z Ø PCTTHRESHOD 后跟一个0到50的数，默认值为50，指明容纳一行数据可用使块空间的百分比； P F0AU T Ø INCLUDING 后跟一个字段，如果数据行的长度超过了PCTTHRESHOLD指定的可用空间，从这个字段之后将数据行分为两段，后面的部分放入溢出段中； rM)#}eZ K! Ø OVERFLOW TABLESPACE 指定溢出段所在的表空间； DVw 04ay% Ø MAPPING TABLE 当在索引组织表上创建位图索引时创建关联的映射表；堆表位图索引的每个位对应到表的一个ROWID，索引组织表的ROWID会随着索引的分裂而发生改变，如果和堆表一样处理，位图索引很容易就失效或者维护成本很高，映射表就是为解决这个问题而引入的，映射表存放索引组织表的ROWID和逻辑行间的对应关系，索引位图中的位对应到逻辑行；dba_indexes.pct_direct_access可以用来指示映射表GUESS的效率，这个值大于30时推荐重建位图索引；一个索引组织表只有一个映射表。 %( #kJZ · 通过dba_tables.iot_name， dba_tables.iot_type 可以查看到索引组织表的溢出表段，映射表段； ] ".SW5b_ · 索引组织表的相关段的段名都是由系统生成的，这些段名的共同特性是SYS_IOT_XXX_YYY， XXX in （TOP 索引段， OVER 溢出表段， MAP 映射表段），YYY是索引组织表的OBJECT_ID。 0E/ 16@ 6= oC|']r 6 标识未使用过的索引 il kN 3J · 索引创建后是否使用是由优化器来控制的，某些索引可能不会使用到，这样的索引不仅加重了DML操作的负担，也占用空间；可以用下面的方法找出这样的索引，然后删除； s d XZsQ w Ø 找出某个或某些被怀疑的索引； Kd Lj 1 T Ø ALTER INDEX index_name MONITORING USAGE; 9bu 1A x1M Ø 在数据库经历一定时间的活动后再执行: ALTER INDEX index_name NOMONITORING USAGE; ] u jXPK=t Ø 查询v$object_usage.index_name, v$object_usage.used，可以得知被监控的索引在这段时间内是否被使用到。 S= g E'"LT Mby V_A`r_ }W5 ~ 89" 第三章 SQL 调整 6.分区 f- ~ ] ~ A,(D - -\V;Gw8mD · 分区表是将大表的数据分成称为分区的许多小的子集，9i提供四种分区方法：范围分区，列表分区，哈希分区和混合分区； "rR$2`v" · 范围分区是根椐分区键的不同取值范围来划分子集的，关键字RANGE， VALUES LESS THAN； dW#?{n- H< · 列表分区是根椐分区键的一些离散的取值来划分子集的，关键字LIST， VALUES； 9[Xe|5?c · 哈希分区是应用哈希算法将分区键对应到某个子集中去，关键字HASH， PARTITIONS； 'Ad|* ~ · 混合分区只能有两层，第一层是范围分区，第二层可以是列表分区或者哈希分区； dyu T-. 2 · 范围分区和列表分区中，如果插入记录的分区键没有对应的容纳分区，会产生ORA-14400； GF gh{ '| · update操作如果会使记录从一个分区迁移到另一个分区，且分区表的ROW MOVEMENT属性是DISABLE，会产ORA-14402； M F s W · 分区表上的索引有两大类：普通的二叉树索引，分区索引，下面讲到的都是分区索引： 3Qu Ft~@ @ · 按索引分区和表分区间的对应关系可以分为局部索引和全局索引； UszR. Z Ø 局部索引的索引分区和表分区间是一一对应的，全局索引则相反； BnIZ+ fg= Ø 局部索引的分区方法可以用上面提到四种的任何一种，全局索引的分区方法只有范围分区（而且最高的分区必须用MAXVALUE来定义）； psB9~EU&Q Ø ORACLE自动维护局部索引的分区，当表分区被合并，分裂或删除时，关联的索引分区也会被合并，分裂或删除；对分区表执行管理操作时会使其上的全局索引失效； eLyaTOZadu Ø 建在分区表的位图索引必须是局部分区索引； r+;AE N48 Ø ORACLE推荐尽可能地使用局部索引； F+::UWK A · 按索引栏位和分区键间的关系分为前缀索引和非前缀索引； H & =3rkX Ø 前缀索引最前面的栏位是分区键栏位，非前缀索引相反； KfMaV U=4P · 在这两种分类方法的四种组合中，只有三种有效（局部前缀索引，局部非前缀索引，全局前缀索引），不存在全局非前缀索引； j GKasI` · 分区表给CBO带来很多选项，如分区排除，并行分区连接等。

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

转载于:http://blog.itpub.net/119501/viewspace-609376/