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。