SQL语言级别优化.doc

SQL语言级别的优化 ORACLE调整层次结构 SQL的执行过程 对sql解析过程影响的参数 query_rewrite_enable cursor_shareing(直接量|绑定变量、force|exact) 解释： force：如果直接量不影响语句的意义，force选项强制那些除了直接量完全相同的语句共享一个指针 exact：完全一样的sql语句共享一个指针。 oracle9i中的窥视功能解释。 减少解析sql语句的技术： 将sql封装在存储过程中 在sql中避免使用直接量 执行计划的目的：速度最快、资源最少 ORACLE的连接方法 嵌套循环连接：从驱动表读取记录，将结果集嵌套第二个表的匹配子集中。 看下面的执行计划 select /*+rule*/ ename,dname from emp,dept where emp.deptno=dept.deptno 散列连接 将驱动表加载到RAM，在内存中建立一个基于散列连接算法的散列表，得使用散列技术连接第二个表。 如果表的大小不同，而且小表的大小接近hash_area_size的可用内存。 通过alter session修改hash_area_size的大小 select /*+ordered use_hash(emp,dept)*/ ename,dname from emp,dept where emp.deptno=dept.deptno 排序合并联结 适合情况： 连接字段没有索引 查询返回两个表的大部分记录 CBO认为全表扫描比索引扫描要快 select /*+use_merge(emp,dept)*/ename,dname from emp,dept where emp.deptno=dept.deptno create table dept1 as select * from dept select /*+use_merge(emp,dept1)*/ ename,dname from emp,dept1 where emp.deptno=dept1.deptno and empno7000 基于规则的优化器RBO、基于成本优化器CBO（choose|first_rows|all_rows） RBO的原则： 总以为索引最快 总是从驱动表开始(认为from 后面最后一张表为驱动表（数据最少）) 只有在不可避免的情况下才使用全表扫描 任何索引都有可以采用 …. CBO的原则 表数据 记录的条数 物理数据块的数目 索引数据 索引中唯一值的数目 索引中值的分布 索引的可选择性 索引朱的因素 choose: 如果有统计数据，则分析统计数据以及一些系统参数决定采用first_rows还是full_rows 如果没有统计数据，则采用rule 如果有的表有统计数据，而有的表没有统计数据，则….,应该避免这种情况。 First_rows让终端用户尽快看到一些数据 All_rows保证总体查询最快。 基于成本的优化器的并不是总能做出正确的选择（通过hint修改执行计划） 为CBO收集统计信息的语法： analyze table table_name compute statistics (for all indexed columns) analyze table table_name estimate statistics sample 5000 rows (for all indexed columns) 用sqlplus生成分析的sql脚本 analyze table emp estimate statistics sample 500 rows analyze index emp_mgr compute statistics 用dbms_stats.gather_table_stats declare  i integer;begin dbms_stats.gather_table_stats(ownname = ,tabname = ,partname = ,estimate_percent = ,degree = ); dbms_stats.gather_index_stats(ownname = ,indname = ,partname = ,estimate_percent = ,degree = ); end; SQL的内部处理： Sql的SGA的统计资料 SELECT name,value from v$sysstat where name like table% NAME VALUE 1 table scans (short tables) 10853 2 table scans (long tables) 28