OLTP数据库优化方案

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. 高效SQL
5.1 5.2 5.3 5.4 优化规则30条 关于Hit提示优化 DML语句优化 批量读取游标数据优化
3.最佳表连接方式
3.1 3.2 3.3 3.4 ORACLE表介绍 RDBMS表连接方式介绍 执行计划中表连接方式介绍 执行计划中表连接方式比较
6. 数据模型与SQL
6.1 数据逻辑模型设计 6.2 数据库物理设计 6.3 书集推荐
2/40

1.0 OLTP 与OLAP区别
•对于Oracle数据库的数据存取，主要有四个不同的调整级别， •第一级调整是操作系统级包括硬件平台, •第三级是Oracle数据库设计级的调整, 第二级调整是Oracle RDBMS级的调整, 第四级调整级是SQL级。通常依此四级调整级别对数据库进行调
整、优化，数据库的整体性能会得到很大的改善。
3/40

1.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/40

1.4 数据库优化方法论
优化方法论 方法论一
Oracle里SQL优化的本质是基于对 CBO和执行计划的深刻理解;
方法论二
并行执行目标SQL语句”，这实际上 是以额外的资源消耗来换取执行时间 的缩短，很多情况下使用并行是针对 某些SQL的唯一优化手段。
方法论三
平衡系统的资源消耗” 可以避免不 必要的资源争用所导致的目标SQL 语句执行时间的增长。
•大部分SQL优化的问题都可以通过增加或者减少索引的方式来解决，但这绝不是全部！ •时刻牢记调优三板斧：索引，join顺序，join算法。 •BI数据仓库中，各子系统的同一数据，因不标准而导致重复统计，导致分析结果误差
5/40

4.数据模型现状分析
1.2数据库性能优化方案
性能优化方案
6/40

4.数据模型现状分析
1.2 数据库性能优化期待效果
期待效果
7/40

•

•有备而来，去了解系统一切正常的情况下性能怎么样。搜集运行监视信息来跟踪一段时 间内系统行为的变化。 •了解整个场景，不要局限于你从ORACLE 上看到的，也要搜集并分析来自于操作系统、 存储、应用程序甚至来自用户的数据。了解系统本身将有助于你解释监控数据。 •只调整能解释你看到的症状的参数，如果连发动机都无法启动就不要更换轮胎。不要试 图通过降低 CPU 来解决磁盘的瓶颈。 •一次只改一个参数，在更改其他参数之前先观察效果。

4.数据模型现状分析
1.4 数据库体系结构
Oracle 10g,11g,12c体系结构，解决问题的利器：运用整体结构知识分析问题所在。
10/40

4.数据模型现状分析
1.4 数据库体系结构-2
Oracle 10g,11g,12c体系结构，解决问题的利器：运用整体结构知识分析问题所在。
11/40

4.数据模型现状分析
1.5 逻辑读 逻辑写
CBO
Oracle 10g,11g,12c体系结构，解决问题的利器：运用整体结构知识分析问题所在。
•基于代价的优化方式(Cost-Based Optimization， 简称为CBO) ；它是看语句的代价(Cost)，这里的代 价主要指Cpu和内存。优化器在判断是否用这种方式 时,主要参照的是表及索引的统计信息。统计信息给出 表的大小、有少行、每行的长度等信息。这些统计信 息起初在库内是没有的，是做 analyze 后才出现的， 很多的时侯过期统计信息会令优化器做出一个错误的 执行计划,因些应及时更新这些信息。
12/40

2.1 Oracle 生成和显示执行计划的方法
执 计划 : 过工具( )：PL/SQL Developer TOAD； 种 过ORACLE 带 DBMS_XPLAN 显 执 计划。
基于Web的应用程 序
一条合法的语句在执行 之后，就会在内存中至 少产生一条执行计划， 可以从视图v$sql_plan 查询。 每一条执行计划对于一 个游标。
13/40