oracle sql优化
目录 oracle sql 解析步骤 oracle优化器 表的访问方式 表的连接方式 干预执行计划 其他sql书写技巧 oracle sql 解析步骤1、语法检查:检查sql语句的拼写是否正确。 2、语义检查:检查sql语句的访问对象是否存在,及该用户是否具备相应的 权限。 3、计算sql的hash值并与共享池中现有语句的hash值进行对比。如果命中则 跳过4,直接跳到7；如果没有命中则往下执行4。 4、视图转换,将涉及视图的查询语句转换为相应的对基表的查询； 5、表达式转换,将复杂的sql表达式转换为较简单的等效表达式； 6、选择执行计划,这是解析sql最耗时间的步骤。这里又分: 1)选择优化器,不同的优化器一般会产生不同的执行计划； 2)选择连接方式,oracle有三种连接方式； 3)选择连接顺序,多表连接的时候,选择哪个表作为驱动表； 4)选择数据的搜索路径,是用全表扫描还是索引扫描。 7、绑定变量。 8、执行sql,返回结果。 oracle优化器rbo 在10g的时候oracle废除了rbo优化器,但发现11g的rbo优化器还是可以用的。rbo优化器遵循oracle内部预定的一些规则。比如:where条件的某一列建立了索引是就会走这个索引。 cbo 这里就是看执行语句的代价。这里的代价主要是指cpu代价、io代价。使用cbo优化器,需要事先用analyse对表和索引做分析,收集到这些表和索引的统计信息(这些统计信息包括表大小,行数,行大小等信息)。cbo就是根据这些统计信息计算跟所有可行的执行计划需要的代价。从中选择一个代价最小的。 表的访问方式全表扫描 顾名思义,就是oracle读取表中所有的行,并检查每一行是否满足语句的where限制条件(读取hwm下所有的数据块,这里还包括delete语句删除的数据行所在数据块)。db_block_m ltiblock_read_co nt参数可以设置多块读,就可以极大的减少了io总次数,提高了系统的吞吐量。 根据rowid定位到行 行的rowid指出了该行所在的数据文件、数据块以及行在该块中的位置,所以通过rowid来存取数据可以快速定位到目标数据上,是oracle存取单行数据的最快方法。这种存取方法不会用到多块读操作,一次io只能读取一个数据块。我们会经常在执行计划中看到该存取方法,如通过索引查询数据。 rowid定位行 - 索引扫描方式唯一索引扫描 通过唯一索引查找一个数值返回单个rowid。如果该唯一索引是一个组合索引,需要指定这个组合索引所有列的值才可会使用唯一索引扫描。 索引范围扫描 通过normal索引查找一个数值返回多个或一个rowid。建立normal索引是要关注索引的选择性。对选择性高的列建立normal索引。 全索引扫描 索引全扫描(index f ll scan),跟全表扫描一样,索引也存在全扫描。全索引扫描只在cbo下 有效,当优化器认为全索引扫描比全表扫描更有效时,才使用全索引扫描。 索引快速扫描 索引快速扫描(index fast f ll scan)与索引全扫描一样,只在cbo下有效。 区别在于:索引快速可以多块读,需要排序操作。 表的连接方式sort merge join 排序合并连接:行源1和行源2的数据分别排序,然后将两个排序的源表合并,符合连接条件的记录放到结果集中。由于排序需要内存空间,sort merge join对内存有比较大的消耗,如果内存空间不足,则会使用临时表空间,这样会降低排序合并连接的效率。非等值连接用sort merge join能获得较好的性能。 hash join 哈希连接:将行源1计算成一张基于连接键的hash表,行源2的每条记录依次扫描这张hash表,找到匹配的记录放到结果集。计算hash表需要内存空间,hash join同样对于内存有比较大的消耗,如果内存空间不足,则会使用临时表空间,这样会降低哈希连接的效率。 nested loop 循环嵌套连接:行源1的每一条记录,依次去匹配行源2的每条记录,将符合连接条件的记录放在结果集中,直到行源1的所有记录都完成这个操作。循环嵌套连接是最基本也是最古老的表连接方式。 干预执行计划 - 通过hints干预执行计划 选择优化器的hints r le: 选择rbo优化器 choose: 默认的情况下oracle用的便是这种方式。指当一个表或索引有统计信息,则走cbo的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走rbo的方式。 all rows: 也就是我们所说的cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走rbo的方式。 first_rows: 当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。没有统计信息则走rbo的方式。 选择表访问方式的hints full : 全表扫描。 rowid: 根据rowid进行访问。 index: 通过索引对表进行访问 表的并行度 parallel: 并行模式下不能使用该表的索引。 通过parallel(table n)指定表的并行度。 选择表连接方式的hints use_nl : 使用nested loop方式关联两个表。当被驱动表的关联字段有建索引时,能获得较好的性能。 use_hash: 将指定的表与其他行源通过哈希连接方式连接起来。 use_merge : 将指定的表与其他行源通过合并排序连接方式连接起来。 其他 leading: 将指定的表作为连接次序中的首表。 append: 直接插入到表的最后,可以提高速度。 ordered: 根据表出现在from中的顺序,ordered使oracle依此顺序对其连接。 其他sql书写技巧1、selct语句避免使用"*"号。 2、删除重复数据使用rowid的方式: delete from tab t where rowid  (select min(rowid) from tab tt where  = ) 避免索引列上使用计算(看清一些隐式类型转换) 尽可能不要使用 is n ll、is not n ll、、not = 之类的运算符。 尽量用existnot exist来替代innot in。 尽量避免使用 nion,min s,intersect,order by,distinct,gro p by之类的操作,这些操作都会引发oracle的排序操作。