Sql优化总结

Sql优化总结：

[code]

1.SQL优化基本原则：
1.合理使用索引；2.使用合理的表连接方式；3.降低逻辑读；4.减少不必要的排序；5.减少重分析。

2.SQL语句处理一共分为6步：
1.Open cursor 2.Parse 3.Bind variables 4.Execution 5.Fetch 6.Close cursor.

3.优化SQL语句的第一原则就是减少硬解析，尽量保证代码重用，这样做的好处：
1.减少因过多硬解析带来的CPU资源占用。
2.减少对Library Cache的需求量。
3.减少Shared Pool Latch的发生；

4.绑定变量可以减少硬解析，但是不是所有语句都适合使用绑定变量。例如某些列值分布不均匀的字段，如果使用绑定变量，反而可能产生执行计划选择错误的情况。原因：采用绑定变量之后，系统会复用执行计划，这样就不会关心所给定的变量值在表中的分布情况，例如一个字段有50个值，其中value=1的值有 100条，占1%，而value=2的值5000条，占50%，此时如果使用绑定变量，则value=1和value=2所使用的执行计划相同，必然会影响执行效率，会造成执行效率不稳定（因为不是最优）。

5.优化器：Optimizer.是一组Oracle的内部例行程序，用于决定一条SQL语句怎样最优执行。对于用户来讲，优化器是透明的。可以通过 explain plan /set autotrace /set event 10046等方式来查看SQL语句的执行计划。
Oracle 9i支持CBO和RBO，如果某给某个表执行了统计信息，则会采用CBO（基于成本的优化），如果某表没有统计信息，则基于RBO进行优化，RBO优化则主要根据SQL语句的书写水平所决定，比如小表，中间表，查询条件顺序等，遵循从右至左，从下到上的顺序。
Oracle 10g已经放弃RBO，而全部采用CBO。CBO的执行计划选择的成功率能达到95%以上。而且CBO基本上不对SQL语句的书写要求很严格，CBO只依赖于统计数据，只要统计数据准确，一般都会选择出比较优化的执行计划。

6.可以通过查询V$SQL_PLAN 查看某一条SQL语句的执行计划。（可以分析别人的SQL语句）。

7.索引是有序的，其包括跟节点，分支节点，叶子节点。读取索引时，每个跟、分支、和叶子节点都要进行一次独立的IO操作。索引的使用方法是：首先通过关键字在索引中查找(如果是b-tree索引，会从跟节点开始一级一级的向下查找），找到索引块后，获取Rowid，然后再根据Rowid读取数据块，当所有数据获取完成之后，将结果集返回。也就是说，在这个过程中，使用一个索引至少要执行3次IO读取（跟、分支、节点），然后还要根据Rowid去读取数据块；当进行全表扫描所产生的IO少于通过索引获取数据产生的IO时，CBO会自动选择Full table scan。

8.Fast Index scan是指通过索引就可以直接返回结果。因为要查询的列是索引列，不需要再读取对应的数据块。

9.当索引过于分散时，要考虑重建索引。
判断索引是否分散，可以执行下面的SQL语句：
SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS WASTAGE FROM INDEX_STATS;
IF WASTAGE > 20% THEN ALTER INDEX INDEX_NAME REBUILD;

10.BITMAP索引。原理、特点、适用环境、优点、缺点。

11.一般来说Sort merge join效率最低 ， Nested Loop Join 效率一般， Hash Join效率较高。也有特例，加入两个表已经是排好顺序的，则sort merge join效率较高；又或者两个表都很小Nested Loop效率较高；如果是大表操作，则hashjoin效率较高。

12.Oracle收集的统计信息包括表、索引和列。其中收集表的信息包括记录数，平均行长度，行迁移数量、行连接数量、平均可用的空闲空间 (DBA_TABLES)；索引：包括索引BLEVEL（越高效率越低），索引叶子节点数量、每个关键字的平均叶子块数、每个关键字的平均数据块数、索引记录数、索引块和唯一值数、聚簇因子（DBA_INDEXES)；列：最大值，最小值等，唯一值数（USER_TAB_COL_STATISTICS)。

13.ORACLE hits。

[/code]