基于Oracle的sql优化方案简介.

基于Oracle的SQL优化简介编写人万承书Oracle优化器•Oracle的优化器有两种方式，一种是基于规则的优化方式(RBO)，另外一种是基于代价的优化方式(CBO),Oracle推荐在oracle8以后的版本强烈推荐使用CBO的方式。•CBO方式，看语句的代价(CBO)，这里的代价一般指CPU和内存，优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因此应及时更新这些信息。Oracle优化器•Rule：基于规则的方式。•Choose：默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。•FirstRows：它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。•AllRows:也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走RBO的方式。Oracle优化器设定选用哪种优化模式：Instance级别我们可以通过在initSID.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS，如果没设定OPTIMIZER_MODE参数则默认用的是Choose方式。Sessions级别通过ALTERSESSIONSETOPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。语句级别用Hint（/*+...*/）来设定诊断工具Statspack/AWRReportExplainPlanSQLTrace和TKPROFPLSQLDeveloper工具Statspack/AWRReport•SQLOrderedbygets•SQLOrderedbyreads•SQLOrderedbyexecutions•SQLOrderedbyparsecalls执行计划•utlxplan.sql创建plan_tableexplainplanforselectlast_namefromper_all_peoples_all;然后查询表plan_table或select*fromtable(dbms_xplan.display);•检查V$sql_planPLSQL工具SQLTrace&TKPROF•在实例启用SQLTraceSQL_TRACE=True;•在会话启用SQLTrace1.altersessionsetSQL_TRACE=true;2.executedbms_session.set_sql_trace(true);3.executedbms_system.set_sql_trace_in_session(session_id,serial_id,true);•格式化trace文件tkproftracefile.trcoutput.txtTkprof统计•Count：执行调用次数•CPU：所使用的CPU时间•Elapsed：运行时间•Disk：物理读•Query：逻辑读(SQL)•Current：逻辑读(DML)•Rows：语句处理的行数SQL语句优化如何判断SQL所用的资源Buffergets(V$SQLAREA.BUFFER_GETS,highCPU)Diskreads(V$SQLAREA.DISK_READS,highI/O)Sorts(V$SQLAREA.SORTS,manysorts)在定位所需要调优的SQL语句后，需要收集更多的信息来进行调优。SQL语句优化从v$sqltext得到完整的sql语句从sql语句知道参考了哪些表是否定义了索引是否使用了视图段的统计信息Sql语句的执行计划SQL语句优化避免大表的全表扫描特别是返回记录只占表记录数的一小部分的情况下，可以创建一个索引来消除全表扫描。保证索引的高可选择性重建索引和数据使用小表来做驱动表.SQL语句优化避免使用函数在字段上，否则会忽略索引的使用，即使是唯一性索引。除非创建了基于函数的索引；比如to_number(char)=number;尽量使用多段SQL来替代复杂的SQL，比如用plsqlpackage可以使用hints来控制访问路径和连接顺序(joinorder)避免使用复杂视图的连接如果需要在更新后返回值，可以用DMLwithreturning可以使用case语句来合并多个查询;Oracle连接方式嵌套循环连接(NestedLoop)排序合并连接(SortMerge)哈希连接(Hashjoin)Oracle连接方式嵌套循环连接（NestedLoop）1、Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（OuterTable）。由驱动表进行驱动连接的表称为内部表（InnerTable）。2、提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。Oracle连接方式嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果集。通常要求驱动表的记录（符合条件的记录，通常通过高效的索引访问）较少，且被驱动表连接列有唯一索引或者选择性强的非唯一索引时，嵌套循环连接的效率是比较高的。嵌套循环连接返回前几行的记录是非常快的，这是因为使用了嵌套循环后，不需要等到全部循环结束再返回结果集，而是不断地将查询出来的结果集返回。在这种情况下，终端用户将会快速地得到返回的首批记录，且同时等待Oracle内部处理其他记录并返回。如果查询的驱动表的记录数非常多，或者被驱动表的连接列上无索引或索引不是高度可选的情况，嵌套循环连接的效率是非常低的。Oracle连接方式排序合并连接(SortMerge)排序合并连接的方法非常简单。在排序合并连接中是没有驱动表的概念的，两个互相连接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接提取符合条件的记录。相比嵌套循环连接，排序合并连接比较适用于返回大数据量的结果。排序合并连接在数据表预先排序好的情况下效率是非常高的，也比较适用于非等值连接的情况，比如、=、=等情况下的连接（哈希连接只适用于等值连接）。由于Oracle中排序操作的开销是非常消耗资源的，当结果集很大时排序合并连接的性能很差Oracle连接方式哈希连接（Hashjoin）哈希连接分为两个阶段:1、构建阶段：优化器首先选择一张小表做为驱动表，运用哈希函数对连接列进行计算产生一张哈希表。通常这个步骤是在内存（hash_area_size）里面进行的，因此运算很快。2、探测阶段：优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话，那么这些记录将会被丢弃而不进行探测。哈希连接比较适用于返回大数据量结果集的连接。使用哈希连接必须是在CBO模式下，参数hash_join_enabled设置为true，且只适用于等值连接。Oracle连接方式跟表连接有关的几个HINTuse_nl（t1,t2）：表示对表t1、t2关联时采用嵌套循环连接。use_merge（t1,t2）：表示对表t1、t2关联时采用排序合并连接。use_hash（t1,t2）：表示对表t1、t2关联时采用哈希连接。leading(t)：表示在进行表连接时，选择t为驱动表。ordred：要求优化器按from列出的表顺序进行连接。需要注意的是在Oracle使用hint时,如果SQL语句中表用别名的话,那么hint中必须使用表的别名,否则hint将不会生效。子查询子查询使用in和exists如何选择in还是exists；一般来说，如果父查询的可选择性比较小的话，选择使用exists;而如果子查询的可选择姓比较小的话，选择使用in。通俗点来讲，就是如果父查询的条件较多，记录集较少的话，使用exists。如果子查询条件多，记录集较少的话，使用in。表和索引绑定变量oracle中，对于一个提交的sql语句,存在两种解析过程,一种叫做硬解析,一种叫做软解析。一个硬解析需要解析,制定执行路径,优化访问计划等许多的步骤。硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的闩（latch）资源，严重的影响系统的规模的扩大(即限制了系统的并发)。在PLSQL中主要是动态SQL的使用。1.ExecuteimmediateSQL;2.Dbms_sql.parseDbms_sql.bind_variable表统计数据行数数据块和空数据块可用空间行链接/迁移行长度分析时间高水位线SQL进行全表扫描时，读取高水位以下的数据块；可以通过以下SQL：Selectnum_rows,blocks,empty_blocks,avg_space,avg_row_len,sample_sizeFromdba_tables;行链接由于某一行变得太大，无法与其他的行一同放在创建这一行的块中；这就要求这一行离开原来的块，Oracle不能简单的移动这一行，而是留下一个“转发地址”。对于SQL来说，不会知道存在行迁移。但是遗留的问题就是需要多余的I/O才能读取该行数据。建议使用ASSM(自动段空间管理),这样实际上只需控制一个参数：PRTFREE。PCTFREE：如果数据插入后会频繁更新，且增加行的大小，则建议高PCTFREE；默认情况下是10%。临时表临时表用于保存事务或会话期间的中间结果集，临时表保存的数据只对当前会话可见，其实已经commit，其他会话也看不到它的数据。临时表比常规表生成的Redo少得多。临时表会从当前登录用户的临时表空间分配存储空间。创建临时表不涉及存储空间分配，不会分配初始区段。当会话在临时表插入数据，才会创建一个临时段。缺点之一：优化器不能正常得到临时表的真实统计，对于CBO，有效地统计对于优化器的成败至关重要。可以使用dbms_stats.set_table_stats手工设置临时表的统计信息。如果能将对临时表的INSERT语句重写为一个大查询，所得到的查询会比原来的多步过程要快。索引索引多，DML性能受到影响，索引少，又影响查询冗余而且从不使用的索引，浪费空间和计算资源索引是开放人员和DBA之间的桥梁B树索引B树索引不存在非唯一性条目，在一个为唯一性索引中，会把ROWID作为一个额外的列追加到键上，使得键唯一。所有叶子块都在树的同一层上，也就是selectindex_name,blevel,num_rowsfromdba_indexes;索引的高度=blevel+1B树索引的使用索引用于访问表中的很少一部分的行通过索引访问表时，会执行大量分散、随机的I/O;索引可以直接回答一个查询不访问底层表，只扫描索引结构本身B树索引例子表中有100000行，假设SQL要读取表20%的行，也就是20000行；再做个假设，如果行大小约8