ORACLE SQL 调优与规范.pptVIP

ORACLE SQL 调优与规范 ORACLE 内核SQL处理概述 SQL处理流程图 图中构件介绍 ORACLE优化器 主要数据访问路径 SQL处理流程图 图中构件介绍 Paser(分析器)；负责SQL语法及语义检查。 Optimizer Mode(优化器模式)；包括cost-based optimizer (CBO)和rule-based optimizer (RBO)。 Row Source Generator(行资源产生器)；负责接收优化器的最佳查询计划（Query plan）,并输出SQL运行计划（execution plan ）。 图中构件介绍（续） SQL Execution Engine (SQL运行引擎)；基于SQL的运行计划(execution plan )执行生成查询结果(Result)。 Dictionary(字典表); 存储相关收集统计数据.如user_tables、user_indexes等。 ORACLE优化器 CBO(cost-based optimizer )基于成本优化器 RBO（ rule-based optimizer ）基于规则优化器 主要数据访问路径 Full table scans(全表扫描) Index scans （索引扫描) Nested loops (嵌套连接) Hash joins (哈希连接) SORT MERGE?JOIN (排序合并连接);场合：, =, , or =. (RBO) ORACLE SQL 调优 减少SQL Parse,提高SQL CACHE命中率 选择合适的优化器（ Optimizer ） 收集统计数据 重构索引 尽可能减少数据所访问次数，能一次获取不用多次 SQL书写规范 执行计划(Explain plan) SQL TRACE（TKPROF） 减少SQL Parse,提高SQL CACHE命中率 避免使用动态SQL JDBC： sqlStr=“select * from tab1 where col1=“+strCol1; IBATIS: ![CDATA[ ??? ?????????????? a.DEPARTMENT_CODE like $departmentCode$% ??? ???]] 推荐使用BIND变量 JDBC: sqlStr=“select * from tab1 where col1=?“; IBATIS: ![CDATA[ ??? ?????????????? a.DEPARTMENT_CODE like #departmentCode#||’% ??? ???]] 选择合适的优化器（ Optimizer ） 使用CBO，基于成本优化器,前提要收集统计信息； ORACLE通过指定参数OPTIMIZER_MODE 指定优化器模式； 可用值RULE、ALL_ROWS、FIRST_ROWS、FIRST_ROWS_n和CHOOSE SQL中指定优化器模式； SELECT /*+ALL_ROWS*/ * FROM DUAL; INSERT /*+ALL_ROWS*/ INTO … UPDATE /*+ALL_ROWS*/ … DELETE /*+ALL_ROWS*/ … SELECT /*+FIRST_ROWS_1*/ … 收集统计数据 收集分析表和索引统计 ANALYZE TABLE tab1 compute statistics; ANALYZE INDEX idx1 compute statistics; 使用DBMS_STATS 包收集统计数据 exec DBMS_STATS.GATHER_SCHEMA_STATS (EPCISBASE,null,false,FOR ALL INDEXED COLUMNS SIZE SKEWONLY,3,DEFAULT,TRUE); 重构索引 索引列选择性要强 组合索引 WHERE子句经常出现的列，并条件选择性强的列要索引。 函数索引 尽可能减少数据所访问次数，能一次获取不用多次 使用CASE子句 select count(case when salary 2000 then 1 else null END) count1, count(case when salary between 2001 and 4000 then 1 else null END) count2, count(case when salary 4000 then 1 else null END) count2 FROM emp; 使用DML Returning子句 insert into UNDERWRITE_INFO ()