Oracle_SQL优化方案

nullOracle SQL 优化 万承书Oracle SQL 优化 万承书Oracle 优化器Oracle 优化器Oracle的优化器有两种方式，一种是基于规则的优化方式(RBO)，另外一种是基于代价的优化方式(CBO),Oracle推荐在oracle8以后的版本强烈推荐使用CBO的方式。 CBO方式，看语句的代价(CBO)，这里的代价一般指CPU和内存，优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因此应及时更新这些信息。Oracle 优化器Oracle 优化器Rule：基于规则的方式。 Choose：默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。 First Rows：它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。 All Rows:也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走RBO的方式。Oracle 优化器Oracle 优化器设定选用哪种优化模式： Instance级别我们可以通过在initSID.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS，如果没设定OPTIMIZER_MODE参数则默认用的是Choose方式。 Sessions级别通过ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。 语句级别用Hint（/*+ ... */）来设定诊断工具诊断工具Statspack/AWR Report Explain Plan SQL Trace和TKPROF PLSQL Developer工具Statspack /AWR ReportStatspack /AWR ReportSQL Ordered by gets SQL Ordered by reads SQL Ordered by executions SQL Ordered by parse calls 执行计划执行计划 utlxplan.sql创建plan_table explain plan for select last_name from per_all_peoples_all; 然后查询表plan_table或 select * from table(dbms_xplan.display); 检查V$sql_plan PLSQL 工具PLSQL 工具SQL Trace & TKPROFSQL Trace & TKPROF 在实例启用SQL Trace SQL_TRACE = True; 在会话启用SQL Trace 1. alter session set SQL_TRACE = true; 2. execute dbms_session.set_sql_trace(true); 3. execute dbms_system.set_sql_trace_in_session(session_id,serial_id,true); 格式化trace文件 tkprof tracefile.trc output.txtTkprof 统计Tkprof 统计 Count：执行调用次数 CPU：所使用的CPU时间 Elapsed：运行时间 Disk：物理读 Query：逻辑读(SQL) Current：逻辑读(DML) Rows：语句处理的行数 SQL语句优化SQL语句优化 如何判断SQL所用的资源 Buffer gets (V$SQLAREA.BUFFER_GETS, high CPU) Disk reads (V$SQLAREA.DISK_READS,high I/O) Sorts (V$SQLAREA.SORTS, many sorts) 在定位所需要调优的SQL语句后，需要收集更多的信息来进行调优。SQL语句优化SQL语句优化从v$sqltext得到完整的sql语句 从sql语句知道参考了哪些表 是否定义了索引 是否使用了视图 段的统计信息 Sql语句的执行计划 SQL语句优化SQL语句优化避免大表的全表扫描 特别是返回记录只占表记录数的一小部分的情况下，可以创建一个索引来消除全表扫描。 保证索引的高可选择性 重建索引和数据 使用小表来做驱动表.SQL语句优化SQL语句优化避免使用函数在字段上，否则会忽略索引的使用，即使是唯一性索引。除非创建了基于函数的索引；比如to_number(char)=number; 尽量使用多段SQL来替代复杂的SQL，比如用plsql package 可以使用hints来控制访问路径和连接顺序(join order) 避免使用复杂视图的连接 如果需要在更新后返回值，可以用DML with returning 可以使用case语句来合并多个查询;Oracle 连接方式Oracle 连接方式嵌套循环连接(Nested Loop) 排序合并连接(Sort Merge) 哈希连接(Hash join)Oracle 连接方式Oracle 连接方式嵌套循环连接（Nested Loop） 1、 Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（Outer Table）。由驱动表进行驱动连接的表称为内部表（Inner Table）。 2、 提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。 Oracle 连接方式Oracle 连接方式嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果集。通常要求驱动表的记录（符合条件的记录，通常通过高效的索引访问）较少，且被驱动表连接列有唯一索引或者选择性强的非唯一索引时，嵌套循环连接的效率是比较高的。 嵌套循环连接返回前几行的记录是非常快的，这是因为使用了嵌套循环后，不需要等到全部循环结束再返回结果集，而是不断地将查询出来的结果集返回。在这种情况下，终端用户将会快速地得到返回的首批记录，且同时等待Oracle内部处理其他记录并返回。如果查询的驱动表的记录数非常多，或者被驱动表的连接列上无索引或索引不是高度可选的情况，嵌套循环连接的效率是非常低的。Oracle 连接方式Oracle 连接方式排序合并连接(Sort Merge) 排序合并连接的 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 非常简单。在排序合并连接中是没有驱动表的概念的，两个互相连接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接提取符合条件的记录。相比嵌套循环连接，排序合并连接比较适用于返回大数据量的结果。 排序合并连接在数据表预先排序好的情况下效率是非常高的，也比较适用于非等值连接的情况，比如>、>=、<=等情况下的连接（哈希连接只适用于等值连接）。由于Oracle中排序操作的开销是非常消耗资源的，当结果集很大时排序合并连接的性能很差Oracle 连接方式Oracle 连接方式哈希连接（Hash join） 哈希连接分为两个阶段: 1、 构建阶段：优化器首先选择一张小表做为驱动表，运用哈希函数对连接列进行计算产生一张哈希表。通常这个步骤是在内存（hash_area_size）里面进行的，因此运算很快。 2、 探测阶段：优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话，那么这些记录将会被丢弃而不进行探测。 哈希连接比较适用于返回大数据量结果集的连接。使用哈希连接必须是在CBO模式下，参数hash_join_enabled设置为true，且只适用于等值连接。Oracle 连接方式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.Execute immediate SQL; 2.Dbms_sql.parse Dbms_sql.bind_variable表统计数据表统计数据行数 数据块和空数据块 可用空间 行链接/迁移 行长度 分析时间高水位线高水位线SQL进行全表扫描时，读取高水位以下的数据块； 可以通过以下SQL： Select num_rows, blocks, empty_blocks, avg_space, avg_row_len, sample_size From dba_tables;行链接行链接由于某一行变得太大，无法与其他的行一同放在创建这一行的块中；这就要求这一行离开原来的块，Oracle不能简单的移动这一行，而是留下一个“转发地址”。对于SQL来说，不会知道存在行迁移。但是遗留的问题就是需要多余的I/O才能读取该行数据。 建议使用ASSM(自动段空间管理),这样实际上只需控制一个参数：PRTFREE。 PCTFREE：如果数据插入后会频繁更新，且增加行的大小，则建议高PCTFREE；默认情况下是10%。 临时表临时表临时表用于保存事务或会话期间的中间结果集，临时表保存的数据只对当前会话可见，其实已经commit，其他会话也看不到它的数据。 临时表比常规表生成的Redo少得多。 临时表会从当前登录用户的临时表空间分配存储空间。创建临时表不涉及存储空间分配，不会分配初始区段。当会话在临时表插入数据，才会创建一个临时段。 缺点之一：优化器不能正常得到临时表的真实统计，对于CBO，有效地统计对于优化器的成败至关重要。可以使用dbms_stats.set_table_stats手工设置临时表的统计信息。 如果能将对临时表的INSERT语句重写为一个大查询，所得到的查询会比原来的多步过程要快。索引索引 索引多，DML性能受到影响，索引少，又影响查询 冗余而且从不使用的索引，浪费空间和计算资源 索引是开放人员和DBA之间的桥梁B树索引B树索引 B树索引不存在非唯一性条目，在一个为唯一性索引中，会把ROWID作为一个额外的列追加到键上，使得键唯一。 所有叶子块都在树的同一层上，也就是 select index_name,blevel,num_rows from dba_indexes; 索引的高度 = blevel + 1B树索引的使用B树索引的使用 索引用于访问表中的很少一部分的行 通过索引访问表时，会执行大量分散、随机的I/O; 索引可以直接回答一个查询 不访问底层表，只扫描索引结构本身B树索引例子B树索引例子表中有100000行，假设SQL要读取表20%的行，也就是20000行；再做个假设，如果行大小约80字节，那么块大小为 8KB的的块则大约100行，这说明表大约1000个块。 那么通过索引读取的话，大约20000个TABLE ACCESS BY ROWID操作来执行查询，那么要处理20000个块，但整个表才1000个块。 在这种情况下，全表扫描比用索引要高效。 B树索引的聚簇因子B树索引的聚簇因子 DBA_INDEXES.CLUSTERING_FACTOR 如果该值与块数接近，说明表相当有序 如果该值与行数接近，说明表的次序可能是非常随机的位图索引位图索引用一个索引键条目存储指向多行的指针 位图索引对于相异基数(distinct cardinality)低的数据最为合适 适合数据仓库的即席查询，但不适合OLTP，因为如果一个会话修改索引的数据，这个索引条目指向的所有行都会被锁定，非常影响并发性。基于函数的索引基于函数的索引需要在某个字段执行大小写无关的查询，如果没有函数索引，可能需要增加一个额外的列，通过触发器维护。 可以通过创建视图将函数的字段隐藏在视图中。 查询性能数量级的增长时间字段时间字段where trunc(create_date)=trunc(:date1)where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60)where create_date>=trunc(:date1) and create_date <= trunc(:date1 ) + 1PROCESS_FLAG例子PROCESS_FLAG例子PROCESS_FLAG有两个可取值，Y、N，新增行时为N，代表该行数据未处理，处理完成后则更像为Y； 这种情况看起来很适合位图索引，但实际上由于会不断将N更新为Y，不适合并发修改。 想做的是只对列值为N的记录建立索引。如果采用B树索引，索引非常大，而且索引高度会较高。 我们可以使用函数索引来实现针对列值为N的记录建立索引。 Create index process_flag_idx On table(case process_flag when ‘N’then ‘N’end );为什么不用索引？为什么不用索引？查询语句的谓词没有使用B树索引的最前列 Select count(*) from t，如果字段没有NOT NULL 谓词使用函数 谓词使用了隐含的函数 使用索引反而慢，CBO没有使用索引 好久没分析表了，如果有没有正确的统计信息，CBO无法做出正确的决定