SQL性能与优化.ppt

SQL性能优化 2010年12月 林俊華 内容 1.1 SQL执行步骤 语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义 语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限 视图转换，将涉及视图的查询语句转换为相应的对基表查询语句 表达式转换，将复杂的SQL表达式转换为较简单的等效连接表达式 选择优化器，不同的优化器一般产生不同的“执行计划” 选择连接方式，ORACLE有6种连接方式，对多表连接 ORACLE 可选择适当的连接方式 选择连接顺序，对多表连接ORACLE选择哪一对表先连接，选择这两表中哪个表做为源数据表 选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，比如是选用全表搜索还是利用索引或是其他的方式 运行“执行计划” 1.2 Oracle的优化器 两种优化器： 基于规则的优化器(RBO，Rule Based Optimizer) 基于代价的优化器(CBO，Cost Based Optimizer，最佳吞吐量为目标) RBO优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则，对数据是不敏感的。它只借助少量的信息来决定一个sql语句的执行计划，包括： ?? 1) sql语句本身??? 2) sql中涉及到的table、view、index等的基本信息??? 3) 本地数据库中数据字典中的信息(远程数据库数据字典信息对RBO是无效的) CBO优化器是看语句的代价(Cost)，通过代价引擎来估计每个执行计划所需的代价，该代价将每个执行计划所耗费的资源进行量化，CBO根据这个代价选择出最优的执行计划。一个查询所耗费的资源可分为三部分：I/O代价、CPU代价、NETWORK代价。 1.2 Oracle的优化器 ORACLE V7以来缺省的设置应是“choose”，即如果对已分析的表查询的话选择CBO，否则选择RBO。为了使用基于成本的优化器CBO,必须经常运行analyze命令,以增加数据库中的对象统计信息(object statistics)的准确性： ANALYZE TABLE MMS_ESTINF COMPUTE STATISTICS; 如果参数设为“rule”，则不论表是否分析过，一概选用RBO，除非在语句中用HINT强制（如果语（句）法不对，则ORACLE会自动忽略所写的HINT，不报错）： SELECT /*+ ALL_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’; 设置优化器,可以通过对init.ora 文件中OPTIMIZER_MODE 参数的各种声明,如RULE, CHOOSE, ALL_ROWS, FIRST_ROWS, FIRST_ROWS_n . 你当然也在SQL句级或是会话(session)级对其进行覆盖： ALTER SESSION SET OPTIMIZER_MODE = CHOOSE; （※） 1)all_rows:all_rows是基于成本的优化方法，目的是提供整体最佳的吞吐量和最小的资源消耗。all_rows提示倾向使用全表扫描，而且不适用于OLTP数据库。使用all_rows提示应该保障查询中涉及的表和索引拥有使用analyze命令分析得到的统计资料。2)rule:rule提示使Oracle为查询提供基于规则的优化模式。在怀疑CBO生成了非优化的执行计划时，通常首先尝试使用rule提示。Rule提示忽略表和索引的统计资料，并且使用基本的试探法生成执行计划。3)first_rows：这个提示是基于成本的优化方法，目的是提供最快的反应时间。使用first_rows提示应该保障查询中涉及的表和索引拥有使用analyze命令分析得到的统计资料 1.3 Oracle的连接方式 六种连接方式： 嵌套循环连接（NESTED LOOP JOIN） 群集连接 （CLUSTER JOIN，嵌套循环连接的一个特例） 排序合并连接（SORT MERGE JOIN） 笛卡尔连接（CARTESIAN JOIN，表1的纪录数为m,表2的为m,则会产生m*n条纪录数，性能很差，必须要解决） ORACLE 7.3中，新增加了哈希连接（HASH JOIN） 在ORACLE 8中，新增加了索引连接（INDEX JOIN） 由于优化器选择方式的不同，以及统计信息的缺失或统计信息的不准确，ORACLE自动选择的表连接方式不一定是最优的。当SQL语句的执行效率很低时，可通过auto trace对执行计划进行跟踪和分析。当出现多表连接时，需要仔细分析是否有更佳的连接条件。根据系统的特点，必要时可以在SQL中添加HINT,从而改变SQL的执行计划，从