oracle数据库调优描述（五）.txt

性能优化的第二部分是sql语句的优化。

输入给oracle的代码，oracle并不会直接执行。
通常来说，oracle定期自动进行统计信息的收集，10g以后的版本通常是每天晚上对数据变化量超过10%的表进行数据收集（避免统计信息过旧造成的执行计划不够优化）。如果新建的表没来得及收集统计信息就执行了，则oracle采用动态采样的方法，尽量保证统计较少的数据而得到较准确的统计结果。
执行一条语句时，首先用查询优化器对语句进行修改，生成尽可能多的执行计划。其目的是提高得到最优执行计划的概率。最简单的例子，比如将对视图的操作修改为对基表的操作，将由union连接的表上的条件修改为对及表上的条件限制，将可以修改的嵌套操作改为非嵌套连接，对可以通过修改提高性能的物化视图进行修改。

在9i及之前版本中普遍采用的RBO优化方式采用的就是一套固定的规则，有十六种可能的路径。oracle认为全表扫描的效率最低，而通过rowid扫描效率最高。
10g后普遍采用的CBO优化方式下，oracle对各个执行计划的成本（cost）进行比较，使用成本最低的执行计划完成sql语句的执行。oracle对执行计划的评估参考如下结果：待查询的结果distinct值占到该列所有distinct值的比例（selectivity）、待查询的条件产生的结果集占待查表的比例（cardinality）、完成此工作所需要花费的i/o、cpu、内存等资源的综合成本（cost）。cost和执行路径的选择有很大关系，完成同样操作可以既可以选择全表扫描、也可以走索引，而索引并不一定是最好的方式。

前面提到为提高得到最优执行计划的概率，oracle会产生尽可能多的计划。这个工作主要是plan generation完成的。多表连接时，一般选择数据量少的表作为驱动表，保证每步操作都产生及可能少的结果集（并行执行时选择大表为驱动表）；多层嵌套的环境下，oracle从内到外生成子计划；比较各个执行计划的cost值时，如果发现当前待比较的计划已经超过了已知最优计划的cost，就不再继续比较此计划了。

最简单的统计信息包括了行数、范围、最大值、最小值等信息，如果数据严重倾斜，自动或手工指定收集柱状图信息有助于生成最优执行计划。对于数据分布严重倾斜的列，统计信息中包含了柱状图。重复执行包含对该列查询的语句时，会根据查询的值selectivity、柱状图等信息来决定采用的执行计划，该语句被标记为bind-sensitive cursors；由于查询值的不同而产生了多个执行计划，该语句还会被标记为bind-aware cursors，每次执行时根据新的变量值生成新的计划；bind-aware cursors不断生成新的计划，则以前用过的相同执行计划就需要被删除，删除后，与其相同的新计划被标记为不可共享，称为cursor merging。当使用绑定变量时，生成执行计划过程中无法确定变量值，也就不可能知道列是否倾斜，无法使用柱状图。因此使用绑定变量有时会生成不够优化的执行计划。

执行计划对于语句的性能影响巨大，出现问题时就需要首先查看执行计划是否符合预期。通过explain plan语句可以得到评估出来的执行计划，再从plan_table输出；而对语句进行自动跟踪（autotrace），再提取跟踪结果的方式查看执行计划则更加准确。

为了提高性能，应该根据需要使用索引、簇。
基于函数的索引是对某些列处理后的结果加索引，可以认为是在数据插入时生成的不可见的列，一般是在维护阶段不能改变应用的情况下采用的，开发阶段应该通过更好的设计来避免；
分区索引类似于分区表，分为全局索引和本地索引，根据业务合理分区或指定hash分区；
索引组织表适合于存在父子关系的数据，按照索引列将所有数据排列起来，按照范围查询时可能更方便；
应用位图索引一般在可选择性较低的列上，且结果集较大的olap系统；
位图连接索引bitmap join index是对多个表上的位图索引；
域索引是以用户自定义的索引类型来索引数据的，比较复杂。
表簇是由于共享相同的行并且通常同时出现在sql语句中的一系列表，创建表簇可以提高性能和空间使用率。
hash簇是将表的列分开存储，一般将一起查询的列放在一起。

hint可以直接指定使用执行计划访问数据的路径、方法等信息，而baselines则存储这些信息。
应该使用sql 计划管理器来管理sql执行计划。计划管理器避免了系统环境、版本、优化参数的突然改变造成的执行计划更改，可以对语句进行记录、演化，为语句创建一系列运行良好的计划，即sql plan baseline。对于索引删除等情况，baseline则无能为力。本质上讲，baseline是一系列的hint、plan hase value、plan-related information的结合。
用sql tuning advisor调优时，若发现当前的计划比plan baseline中的某个计划性能好，将自动将其加入到baseline中；可以手动修改baseline或显示baseline内容，将baseline导出并导入到其他数据库。sql plan baseline与语句日志、计划历史信息、sql profiles共同存储在sql management base中，占用sysaux的空间。可以设定该占用范围。

对oracle的调优包括定位top sql语句、查看其是否可接受、为性能差的语句调优三方面。目标是减少用户响应时间或减少完成相同工作消耗的资源。
定位top sql可以通过addm报告、自动sql调优、查看v$sql视图、运行awr报告、sql trace跟踪等方式。
在开发时，应该关注执行计划、重构性能差的语句、用hint控制访问路径和连接方式，删除不必要的索引并合理使用，减少触发器和约束的应用，重构数据，尽可能减少数据访问次数。

[sql access advisor的相关内容还需要仔细研究]

在自动调优的同时oracle提供了hint来进行人工干预或性能比对测试。hints分为单表、多表、查询块、语句几种，也可以分为优化目标类（all_rows）、开启优化特性类（optimizer_features_enable（'11.1.0.6'））、访问路径类（FULL、INDEX）、连接顺序类（leading）、连接操作类型类（use_nl、use_merge）、在线应用升级类（change_dupkey_error_index）、并行执行类（parallel、no_parallel）、查询事务类（use_concat、rewrite）、其他（append、qb_name）。

使用plan stability可以避免环境变化对应用性能的影响。

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