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

表和索引 绑定变量 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树索引不存在非唯一性条目，在一个为唯一性索引中，会把ROWID作为一个额外的列追加到键上，使得键唯一。 所有叶子块都在树的同一层上，也就是 select index_name,blevel,num_rows from dba_indexes; 索引的高度 = blevel + 1 B树索引的使用 索引用于访问表中的很少一部分的行 通过索引访问表时，会执行大量分散、随机的I/O; 索引可以直接回答一个查询 不访问底层表，只扫描索引结构本身 B树索引例子 表中有100000行，假设SQL要读取表20%的行，也就是20000行；再做个假设，如果行大小约80字节，那么块大小为 8KB的的块则大约100行，这说明表大约1000个块。 那么通过索引读取的话，大约20000个TABLE ACCESS BY ROWID操作来执行查询，那么要处理20000个块，但整个表才1000个块。 在这种情况下，全表扫描比用索引要高效。 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 ) + 1 PROCESS_FLAG例子 PROCESS_FLAG有两个可取值，Y、N，新增行时为N，代表该行数据未处理，处理完成后则更像为Y； 这种情况看起来很适合位图索引，但实际上由