ORACLE执行计划和SQL调优.pptVIP

ORACLE 调优 吴志忠 2009.09 面向对象 懂基本数据库常识 有ORACLE开发使用经验 乐于接受性能优化探讨 内容安排 第一部分：背景知识 第二部分：SQL调优 第三部分：工具介绍 第四部分 ROWID高级应用 第五部分 附录 调优方面 1 商业逻辑 2 优化数据设计 3 优化应用程序设计 4 优化数据库逻辑设计 5 优化数据库操作 6 优化访问路径 7 优化内存分配 8优化I/O 和物理结构 9 优化资源争用 10优化所采用的平台 * 越靠前越重要 第一部分 背景知识 SQL处理流程 优化器 缓冲池 执行计划 变量绑定 索引 表连接 SQL 执行的步骤 解析：安全性检查，语法检查； 创建：评估多个执行计划，并选择一个最优的执行计划； 执行：捆绑变量，执行已经创建的执行计划； 获取：获取结果集，进行转换，排序等； SQL处理流程 · 第1步: Create a Cursor 创建游标 · 第2步: Parse the Statement 分析语句 · 第5步: Bind Any Variables 绑定变量 · 第7步: Run the Statement 运行语句 · 第9步: Close the Cursor 关闭游标 如果使用了并行功能，还会包含下面这个阶段： · 第6步: Parallelize the Statement 并行执行语句 如果是查询语句，则需要以下几个额外的步骤 · 第3步: Describe Results of a Query 描述查询的结果集 · 第4步: Define Output of a Query 定义查询的输出数据 · 第8步: Fetch Rows of a Query 取查询出来的行 SQL优化器(Optimizer) 概念：是一个为所有的sql语句创建执行计划的工具。 目的：生成最快的，消耗资源最少的执行计划。 两种优化器 a. RBO, Rule-Based Optimizer b. CBO, Cost-Based Optimizer RBO特性 总是使用索引（不识别位图索引或基于函数的索引） 总是从驱动表开始 只有在不可避免的情况下，才使用全表扫描 索引选择的随机特性 *从ORACLE 10G开始，开始废弃RBO优化器。这句话并不是指在ORACLE 10G中不能使用RBO，而是从ORACLE 10G开始开始，不再为RBO的BUG提供修补服务 CBO 特性 前提条件：存在表和索引的统计资料；使用analyze table 和 analyze index 命令从表或索引中收集统计资料（表的记录平均长度，记录数等）；如果没有现存的统计资料，将在sql运行时收集资料，会大大降低性能； 影响CBO执行计划成本评估的初始化参数较多（optimizer_search_limit,optimizer_max_permutations,optimizer_index_caching,hash_area_size,hash_join_enable,hash_multiblock_io_count,star_transformation_enable,optimizer_index_cost_adj等） SQL优化器模式 实例级通过对init.ora中OPTIMIZER_MODE参数,2会话级ALTER SESSION SET OPTIMIZER_MODE=;3语句别 hint CHOOSE 不是优化器,决定用什么优化器的参数: 如果表有分析资料,便用CBO,否则用RBO 在缺省情况下,ORACLE采用CHOOSE优化器模式 CHOOSE RBO CBO FIRST_ROWS_n (OLTP系统) FIRST_ROWS ALL_ROWS(DSS 系统) 表分析 为了使用CBO,必须经常运行analyze 命令,以增加数据库中的对象统计信息 多表连接时只要有一个表分析过，就用CBO 7i: ANALYZE和DBMS_UTILITY 从815开始有DBMS_STATUS 从10g以后可以自动分析表,有个系统job(GATHER_STATS_JOB )。 SQL优化器处理体系结构 解析程序 优化程序 行源产生程序 SQL执行 SQL优化器处理流程图-1 SQL优化器处理流程图-2 SQL优化器处理流程图-3 缓冲池 PGA Program Global Area是为每个连接到Oracle database的用户进程保留的内存 SGA System Global Area是Oracle Instance的基本组成部分，在实例启动时分配; 系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。 缓冲池-SGA 共享池(Shared pool)