Oracle优化常用概念

1、Oracle优化常用概念解析常见概念 CBO/RBO 表连接方式 执行计划 统计信息和柱状图 索引 分区表 AWR 表扫描方式 hintCBO/RBO 什么是CBO/RBO，工作原理是什么 应该使用哪种模式 如何查看是哪种模式 如何修改优化器模式什么是CBO/RBO，工作原理是什么 CBO：cost-based optimizer，基于成本的优化器 RBO：rule-based optimizer，基于规则的优化器 CBO需要使用统计信息，据此计算最佳的执行计划；而RBO根据oracle设定好的规则生成执行计划。由于不能窥视到表中数据，RBO往往不能得到合理的执行计划应该使用哪种模式 Orac

2、le 10g的CBO已经很成熟，推荐使用。Oracle公司也将使用CBO代替RBO，而且RBO对开发人员的要求比较高，因此，CBO是不二选择。如何查看是哪种模式 show parameter optimizer_mode optimizer_mode的取值包括： rule：RBO choose：有统计信息时是CBO，否则是RBO， 但使用use_hash,all_rows等提示时都是CBO all_rows,first_rows：CBO如何修改优化器模式 alter system set optimizer_mode = all_rows scope=both; scope的值：both、sp

3、file、memory，默认是both表连接方式 有哪几种连接方式 每种方式的特点、工作原理有哪几种连接方式 hash join nested loop merge join每种方式的特点、工作原理nested loop：使用条件：任何连接优点：当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果缺点：当索引丢失或者查询条件限制不够时，效率很低；当表的纪录数多时，效率低。sort merge：使用条件：主要用于不等价连接，但不包括；相关资源：内存、临时空间优点：当缺乏索引或者索引条件模糊时，排序合并连接比嵌套循环有效。缺点：所有的表都需要排序。它为最优化的吞吐量而设计，并

4、且在结果没有全部找到前不返回数据。hash join：使用条件：仅用于等价连接；相关资源：内存、临时空间优点：当缺乏索引或者索引条件模糊时，哈希连接连接比嵌套循环有效。通常比排序合并连接快。在数据仓库环境下，如果表的纪录数多，效率高。缺点：为建立哈希表，需要大量内存。第一次的结果返回较慢。执行计划 什么是执行计划 如何生成执行计划 如何看懂执行计划什么是执行计划 Oracle执行每一条SQL语句，都必须经过Oracle优化器的评估，选择最佳的执行路径，包括索引的使用、表的访问、表之间的连接等等。如何生成执行计划 Pl/sql中按F5，最简单快捷的方式 Explain plan Sql trac

5、e，最准确 Sql/plus autotrace，包含更多信息如何看懂执行计划 没有捷径，多看执行计划，网上查一下具体含义 计划开个专题讨论统计信息和柱状图 为什么要收集统计信息 统计信息包括哪些内容 如何查看是否存在统计信息 如何收集统计信息 什么是柱状图 为什么要做柱状图 如何生成柱状图为什么要收集统计信息 cbo模式下计算cost所需要的信息，统计信息越准确，oracle生成的执行计划越高效，所以要定期收集统计信息统计信息包括哪些内容 表：行数、块数、空块数、块的平均剩余空间等 表的列：唯一值数、null值数等 索引：级数、叶子块数、唯一值数等如何查看是否存在统计信息 User_tab_

6、statistics（user_tables也存在相应列） User_ind_statistics（user_indexes也存在相应列） User_tab_col_statistics如何收集统计信息 analyze table tb_name compute|estimate statistics; 该方式用于向后兼容 exec dbms_stats.gather_* oracle建议使用的方式什么是柱状图 Oracle的柱状图和web页面展示的柱状图属同一个概念，只是已数据的形式存在而没有直观展示出来而已。每个柱被称为bucket（桶）。柱状图包含了列上的数据分布，大致可理解为列上每个值

7、的记录数，即数据倾斜度。为什么要做柱状图 Oracle在选择索引时会检查索引的效率从而确定是否使用索引，而柱状图正是提供了这种信息。如果不做柱状图，oracle就确定不了索引的效率，会倾向于使用索引，可能反而不如做全表扫描来得快如何生成柱状图 analyze table tb_name compute|estimate statistics for all indexed columns; 针对索引列 analyze table tb_name compute|estimate statistics for columns col_name size ; 针对某一列 analyze table

8、 tb_name compute|estimate statistics for all columns; 针对表所有列 使用dbms_stats包提供的过程，如：dbms_stats.gather_table_stats(ownname = ,tabname = ,method_opt = for all columns);索引 创建索引的目的 索引为什么会加快查询 有哪些类型的索引 索引的限制创建索引的目的 从根本来讲就是为了加快查询速度 Oracle出于对效率的考虑，某些约束会关联到索引，从而可以更快的检查约束，主键约束就是一个典型的例子。索引为什么会加快查询 索引的结构使得搜索索引的键

9、值很快。oracle索引的结构类似于平衡二叉树，但每个非叶子节点存储了多个值，降低了树的高度。 每个索引键都存储了索引列和rowid，通过rowid访问表是最快的方式。Rowid相当于指针。 如果取表中少量数据，先索引搜索再通过rowid访问表显然代价比全表扫描小，但如果取表中大量数据，索引搜索几乎就成了多余的一步，因此，全表扫描更好，走索引反而效率低。这就是索引的效率问题，也是做柱状图的目的。有哪些类型的索引 唯一索引、非唯一索引：关键字unique 函数索引 反转索引：关键字reverse用于平衡索引树，使之不会朝一侧倾斜 位图索引：关键字bitmap列的基数比较小时非常适合，但dml操作

10、的代价很高，不适合oltp系统索引的限制 不等（、!=、=）、is null操作符 对索引列使用函数或者索引列存在隐身转换 索引效率不高，适合做全表扫描 错误的统计信息 数据倾斜而未做柱状图，或使用了绑定变量而oracle未窥视到 使用hint强制做full scan 以%开头的like操作符 并行查询 小表，oracle认为不需要走索引分区表 有哪些类型的分区表 索引的创建有哪些类型的分区表 range hash list complex索引的创建 global or local？建议使用local索引AWR 什么是awr 如何生成awr Awr包含哪些内容什么是awr 全称automati

11、c workload repository（自动工作负荷存储）。对于运行中的数据库，oracle会定时对运行状态抓取快照，包括内存的占用、磁盘的读写、语句执行情况等信息。Awr保存了时间最长的快照信息，默认是7天。与awr对应的概念还有addm和ash，是时间较短的快照。如何生成awr 登陆到数据库服务器，执行脚本awrrpt Pl/sql和sql/plus中也可以。独家抓取脚本，无须登陆服务器，使用更方便，每license 10元，收费公道，童叟无欺，欢迎订购使用表扫描 表扫描原理 如何查看hwm 如何降低hwm表扫描原理 从水线（hwm）向下扫描 hwm （hight water mark

12、）：oracle使用的块的最大高度，或者说是使用到的最大块号如何查看hwm 查询user_tables.blocks，需要先对表进行分析 其他一些左道旁门的方法如何降低hwm alter table tb_name shrink space; 该方法需要前提alter table tb_name enable row movement; alter table tb_name move; 该方法需要在move完后重建索引alter index ind_name rebuild; truncatehint 使用hint的两种方式 有哪些hint使用hint的两种方式 -+，需要注意该方式会注释掉

13、其后同行的内容 /*+ */有哪些hint use_nl、use_hash、use_merge 表连接方式 leading 表连接顺序 parallel 并行查询 full、index全表扫描、索引扫描 all_rows、first_rows、choose、rule优化器模式 Append、noappend直接路径插入，慎用语句慢怎么办？ 检查语句书写是否规范、业务逻辑是否可调 一句话，死盯执行计划，语句慢一般是走了错误的执行计划。以下原因都可能是罪魁祸首：1、优化器选择了RBO2、统计信息缺失或错误3、有hint进行了错误的强制4、未做柱状图，oracle窥视不到值分布5、索引建的不合理6、隐式转换导致索引使用不上（经常会遇到）7、某些时候oracle判断不了数据多少，选择了错误的表连接方式动态性能视图和数据字典视图 动态性能视图：v$开头 数据字典视图：dba_*、all_*、user_*动态性能视图 v$session,v$session_wait,v$session_event, v$sql v$parameter数据字典视图 dba_data_files user_tables user_indexes user_objects user_source user_segments：对象实际占用空间，非实际使用空间 user_users,