OLTP数据库优化方案及案例ORACLE数据库SQL优化方案、案例Edgar Liu© 2015 Huatek CO., LTD. All Rights Reserved.2015.3.17目录1. 优化方案与基础知识1.1 问题SQL来源（现象） 1.2 数据库性能优化方案及期待效果 1.3 优化方法论及优化分析树 1.4 数据库体系结构 1.5 逻辑读 逻辑写4.索引设计与查询条件4.1 4.2 4.3 4.4 索引介绍 索引设计步骤 索引创建原则 索引失效与不足2. 执行计划分析2.1 执行计划查看方法 2.2 执行计划示例 2.3 执行计划三部分 2.4 硬解析和软解析5. 高效SQL5.1 5.2 5.3 5.4 优化规则30条 关于Hit提示优化 DML语句优化 批量读取游标数据优化3.最佳表连接方式3.1 3.2 3.3 3.4 ORACLE表介绍 RDBMS表连接方式介绍 执行计划中表连接方式介绍 执行计划中表连接方式比较6. 数据模型与SQL6.1 数据逻辑模型设计 6.2 数据库物理设计 6.3 书集推荐2/401.0 OLTP 与OLAP区别•对于Oracle数据库的数据存取，主要有四个不同的调整级别， •第一级调整是操作系统级包括硬件平台, •第三级是Oracle数据库设计级的调整, 第二级调整是Oracle RDBMS级的调整, 第四级调整级是SQL级。
通常依此四级调整级别对数据库进行调整、优化，数据库的整体性能会得到很大的改善。
3/401.1 问题SQL来源ORACLE概念: AWR=Auto Workload Repository;ASH= Active Session History, ADDM= Automatic Database Diagnostic Monitor数据标准化的必要性 AWR报告或者Statspack报告 SQL ordered by Elapsed Time SQL ordered by CPU Time SQL ordered by Gets等部分 ADDM报告 程序检查•FINDING 1:：SQL statements consuming significant database time were found. •FINDING 2：Time spent on the CPU by the instance was responsible for a substantial part of database time. •Etc…4/401.4 数据库优化方法论优化方法论 方法论一Oracle里SQL优化的本质是基于对 CBO和执行计划的深刻理解;方法论二并行执行目标SQL语句”，这实际上 是以额外的资源消耗来换取执行时间 的缩短，很多情况下使用并行是针对 某些SQL的唯一优化手段。
方法论三平衡系统的资源消耗” 可以避免不 必要的资源争用所导致的目标SQL 语句执行时间的增长。
•大部分SQL优化的问题都可以通过增加或者减少索引的方式来解决，但这绝不是全部！ •时刻牢记调优三板斧：索引，join顺序，join算法。
•BI数据仓库中，各子系统的同一数据，因不标准而导致重复统计，导致分析结果误差5/404.数据模型现状分析1.2数据库性能优化方案性能优化方案6/404.数据模型现状分析1.2 数据库性能优化期待效果期待效果7/40••有备而来，去了解系统一切正常的情况下性能怎么样。
搜集运行监视信息来跟踪一段时 间内系统行为的变化。
•了解整个场景，不要局限于你从ORACLE 上看到的，也要搜集并分析来自于操作系统、 存储、应用程序甚至来自用户的数据。
了解系统本身将有助于你解释监控数据。
•只调整能解释你看到的症状的参数，如果连发动机都无法启动就不要更换轮胎。
不要试 图通过降低 CPU 来解决磁盘的瓶颈。
•一次只改一个参数，在更改其他参数之前先观察效果。
4.数据模型现状分析1.4 数据库体系结构Oracle 10g,11g,12c体系结构，解决问题的利器：运用整体结构知识分析问题所在。
10/404.数据模型现状分析1.4 数据库体系结构-2Oracle 10g,11g,12c体系结构，解决问题的利器：运用整体结构知识分析问题所在。
11/404.数据模型现状分析1.5 逻辑读 逻辑写CBOOracle 10g,11g,12c体系结构，解决问题的利器：运用整体结构知识分析问题所在。
•基于代价的优化方式(Cost-Based Optimization， 简称为CBO) ；它是看语句的代价(Cost)，这里的代 价主要指Cpu和内存。
优化器在判断是否用这种方式 时,主要参照的是表及索引的统计信息。
统计信息给出 表的大小、有少行、每行的长度等信息。
这些统计信 息起初在库内是没有的，是做 analyze 后才出现的， 很多的时侯过期统计信息会令优化器做出一个错误的 执行计划,因些应及时更新这些信息。
12/402.1 Oracle 生成和显示执行计划的方法执 计划 : 过工具( )：PL/SQL Developer TOAD； 种 过ORACLE 带 DBMS_XPLAN 显 执 计划。
基于Web的应用程 序一条合法的语句在执行 之后，就会在内存中至 少产生一条执行计划， 可以从视图v$sql_plan 查询。
每一条执行计划对于一 个游标。
13/402.3 执行计划示例1表部分2谓词部分3统计信息14/402.3 执行计划查看—表部分执行计划表部分序号 列名 解释这一行是这一条语句的hash值，我们知道oracle对每条语句产生的执行 1 Plan hash value 计划放在share pool里面，第一次要经过硬解析，产生hash值。
下次再 执行该语句时候比较hash值，如果相同就不要执行硬解析。
2 3 4 5Operation（ 操作） Name（被操作的对象） Row Byte把sql进行分解被操作的对象 有的地方也叫Cardinality（用plsqldev里面解释计划窗口） 扫描的数据的字节数没有单位，是一个相对值，是sql文以cbo方式解析执行时，供oracle用 6 cost 来评估cbo成本，选择执行计划用的。
公式：Cost=（Single block I/O cost+ Multiblock I/O cost+ cost）/sreadtim 15/40 CPU2.3 执行计划查看– 谓词信息Predicate Information序号 列名 解释属性 属性索引（access） 非索引（filter）INDEX FULL SCAN vs INDEX FAST FULL SCAN总结 1、当select where 现 发 index f ull scan与index fast full scan 2、查询 数据 总数 据 个 10% 3、 count(*) 总 选择index fast full scan， order by 句 总 选择index full scan 4、index fast full scan 块读 读 块，产 db file scattered reads 件，读 时高 ， 为 读 5、index full scan 单块读 读 块，产 db file sequential reads 件，当 该 读 扫 ， 6、绝 数 况 ，index fast full scan 优 index f ull scan， order by时， 会 对读 块 过 7、index fast full scan 过牺 内 与临时 空间换 ， 内 饱 状态应进 权 .16/402.3 执行计划—统计信息AUTOTRACE Statistics列解释序号 列名 解释1 2 3 4 5 6 7recursive calls db block gets consistent gets physical reads redo size sorts (memory) sorts (disk)递归调查 从buffer cache中读取的block的数量 从buffer cache中读取的undo数据的block的数 量 从磁盘读取的block的数量 DML生成的redo的大小 在内存执行的排序量 在磁盘上执行的排序量17/402.3 硬解析和软解析解析类型分类解析过程 在执行软软解析之前，首先要进行软解析，MOS上说执行3次的SQL语句 会把游标缓存到PGA，这个游标一直开着，当再有相同的SQL执行时， 则跳过解析的所有过程直接去取执行计划。
软软解析软解析1.语法、语义及权限检查； 2.将整条SQL hash后从库缓存中执行计划。
硬解析1.语法、语义及权限检查； 2.查询转换（通过应用各种不同的转换技巧，会生成语义上等同的新 的SQL语句，如count(1)会转为count(*)）； 3.根据统计信息生成执行计划（找出成本最低的路径，这一步比较耗 时）； 4.将游标信息（执行计划）保存到库缓存。
18/40•Oracle数据种类 ：Heap Table、组织 IOTCluster。
•注意：如果使用from子句指定内、外连接，则必须要使用on子句指定连接条件；如果使用（+）操作 符指定外连接，则必须使用where子句指定连接条件。
•对于外连接， 也可以使用“(+) ”来表示。
关于使用（+）的一些注意事项： •1.（+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。
•2. 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都 包含（+）操作符 •3.（+）操作符只适用于列，而不能用在表达式上。
•4.（+）操作符不能与or和in操作符一起使用。
•5.（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。
Merge Join间 连 。
种：Nested Loops，Hash JoinSort Merge Join.来介绍 种连select from where/*+ ordered */ ename, dept.deptno dept, emp dept.deptno = emp.deptnoselect from where/*+ ordered */ ename, dept.deptno emp, dept dept.deptno = emp.deptno;select from where/*+ ordered */ ename, dept.deptno emp, dept dept.deptno = emp.deptno;3.4 执行计划中 执行计划中表连接方式比较表连接方式NESTED LOOP对于被连接的数据子集较小的情况， 嵌套循环连接是个较好的选择。