数据库性能优化方法&案例分析
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
数
•磁盘I/O和物理结构
的设计和调整
•充分使用PL/SQL
•系统资源竞争的监测
和调整
•应用系统的设计
•减少锁冲突
•操作系统平台的优化
设计
开发
上线
• 设计和开发阶段的优化 – 80%以上
20/80定律
• 应用设计开发 -- 80% 系统配置 -- 20% • 80%的性能问题是由20% 的应用导致的 • 80%的性能问题可以由 20%的优化技术所解决
<Insert Picture Here>
数据库性能优化方法&案例分析
内容
从案例看性能优化 Oracle性能优化方法论 更多案例分析 性能优化服务 问答
案例1：数据访问都在内存完成
• 数据访问都在内存完成，几乎 没有I/O，但CPU经常达到90% 以上的状态 • 主要问题：主要业务表缺乏索 引，导致大量全表扫描
性能是升级的第一大风险
• 某省移动公司仍然运行在9i 平台 • >90%升级问题并不是数据库升级，而是升级之后的应用性能 问题 • 升级 = > 性能优化
R2
R2
还是索引这样基础的问题
• 语句条件： …… where applyoid = :applyoid and region = 538; …… • 目前的索引 ：(REGION, SUBSID)，可选性并不高 • 优化建议：创建applyoid字段索引 • 复合索引的前缀性和可选性
开发、设计、运行维护各阶段 均可能导致性能问题
案例3：神奇的Oracle内部参数
• 内部参数列表
Parameter Name _b_tree_bitmap_plans _bump_highwater_mark_count _cursor_features_enabled _db_block_hash_buckets _db_block_hash_latches _db_block_numa _enable_NUMA_optimization _enqueue_hash_chain_latches _fix_control _in_memory_undo _index_join_enabled _optim_peek_user_binds _optimizer_mjc_enabled _sort_elimination_cost_ratio _sqlexec_progression_cost _table_lookup_prefetch_size _wait_for_sync Begin value FALSE 30 10 134217728 1048576 1 FALSE 256 5705630:ON, 5765456:3 TRUE FALSE FALSE FALSE 10 0 0 FALSE End value (if different)
3385*8K = 26M
2381 *8K = 18M
R2
9i难以解决的问题：中间表
Insert Insert Insert update Delete Delete
• 问题：
• • • • 高水位（HWM值）太高 大量全表扫描 中间表高可用性很高 传统办法难以解决
R2
碎片问题解决的新技术：10g Shrink
系统软件 硬件配置 Bug 其它 架构和应用设计
应用开发
用数据诠释20/80规则
原有语句 新语句 （不分 区） 00:26.0 6,554M 176M 3 新语句 (分 区 ) 00:12.6 4,763M 178M 3 新语句 （分区， 新索引） 00:02.8 97M 26M 3
响应速度 内存消耗 I/O消 耗 记录数
谨慎使用内部参数
内容
从案例看性能优化 Oracle性能优化方法论 更多案例分析 性能优化服务 问答
如何建设高质量的IT系统
• 系统性能出 现问题! 用户
涉及层面
•
实现目标
硬件
问题原因
响应速度
吞吐量 高可用性 数据/备份恢复 数据安全性 易管理性 扩展性
网络 操作系统 集群软件 数据库
• 内部event设置
Parameter Name
event
Begin value
44951 trace name context forever, level 1024:10753 trace name context forever, level 2:38087 trace name context forever, level 1:10183 trace name context forever, level 1:10027 trace name context forever, level 1:10028 trace name context forever, level 1:10191 trace name context forever, level 1:10411 trace name context forever, level 1:10629 trace name context forever, level 32:14532 trace name context forever, level 1:10091 trace name context forever, level 1:10142 trace name context forever, level 1:3806
01:01.3 12,251M 369M 3
• • • •
第一列为数据库非分区时的运行情况 第二列为将子查询调整为多表连接之后的优化效果 结论：调整语句编写方式，特别 第三列为数据库分区之后的优化效果 是新建复合分区索引的优化效 第四列为新建一个复合分区索引之后的优化效果 果，远远大于数据库分区之后
的优化效果 80%的性能问题可以由20%的优 化技术所解决
应用开发技术运用策略
比较项目 操作特点 响应速度 吞吐量 并发访问量 联机业务 批处理业务 日常业务操作，尤其是包含 后台操作，例如统计报表、 大量前台操作 大批量数据加载 优先级最高，要求反应速度 要求速度高、吞吐量大 非常高 小 非常高 小 大 不高 大
• 正确解决方式：通过SR申请Patch
• event：44951 trace name context forever, level 1024分析 • 《'enq HW - contention' For Busy LOB Segment [ID 740075.1]》 • 问题原因：Bug 6376915，对LOB 字段进行大量 DML操作时，enq HW – 启示 3：系统参数调整是性能优化 contention等待事件非常高 的基础和必要条件，但不是充分
业务特征
单笔事务的资源消耗
SQL语句类型 索引类型 索引量 访问方式 技术运用 连接方式 BIND变量 并行技术 分区技术 物化视图使用
主 要 是 插 入 和 修 改 操 作 主要是大量查询操作或批量 （DML） DML操作 B*索引 适量 按索引访问 Nested_loop 使用或强制使用 使用不多 使用，但目标不同 少量使用 Bitmap、Bitmap Join索引 多 全表扫描 Hash Join 不使用 大量使用 使用，但目标不同 大量使用
End value (if different)
案例3：神奇的Oracle内部参数
• _b_tree_bitmap_plans分析 • 《ORA-00600[Qkabix] Error Caused by a Particular Query [ID 743212.1]》 • 问题原因：Bug 5945708，触发ORA-00600[Qkabix]错误 • Workaround: _b_tree_bitmap_plans = FALSE
自底向上
数据库性能管理的全面性
业务分析人员
1. 业务需求优化 2. 数据库逻辑设计优化 3. 应用流程设计优化 4. SQL语句优化 5. 数据库物理设计优化
设计人员 应用开发人员 数据库管理人员
6. 内存和CPU的优化 7. 磁盘I/O系统设计和优化 8. 系统资源竞争的监测和优化
9. 操作系统优化 10. 网络配置优化
响应速度 优化前 00:12:59.12 优化后 00:00:01.26 3*8K = 24K 2*8K = 16K
R2
内存消耗
I/O消耗
2663578*8K= 20809M
118542*8K = 926M
发现最大的性能问题
• 语句片段
SELECT /*+ use_nl(a)*/ …… FROM tbcs.smtemplate b, tbcs.smnotify_kf a …. …
• 解决方式：10.2.0.4 patchset
• Workaround: •为LOB段提前分配空间
条件
内部参数更多用于故障诊断
故障诊断更主要方式是安装补丁 和版本升级，而不是设置内部参 •为包含该LOB字段的表进行分区 数
•设置event：44951 trace name context forever, level <1 – 1024>
上线之后的优化过程(自底向上)
检查系统的利用率 检查等待事件 检查物理I/O 确定范围 – 系统级, 模块级, 用户级? 确定最消耗资源的SQL STATSPACK/AWR报告 Top Sessions in OEM 6. 分析执行计划 explain plan 7. 分析访问的对象 (size/cardinality) 8. 分析连接、访问方式… … 9. 找出相关问题 10. 确定解决方案 11. 测试解决方案 12. 生产环境实施解决方案 13. 分析结果 1. 2. 3. 4. 5.
1000
500 0
案例2：统计信息问题
• 问题现象：CPU、I/O：100%！
• 问题原因： 统计信息不准确，导致所有操作为全表扫描 • 解决方式：更新统计信息
SQL> exec 启示2：性能问题与优化器、 dbms_stats.gather_schema_stats(ownname=>< 用户名 统计信息采集紧密相关 >,cascade=>TRUE);
wenku.baidu.com
• 解决方式：创建一个索引
System Summary imsdb1 2011-3-25
CPU% usr%+sys% IO/sec 2000 1500 Disk xfers
启示1：高配置硬 件不能解决所有性 能问题
100 80 60 40 20 0 12:15 12:20 12:25 12:30 12:35 12:40 12:45 12:50 12:55 13:00 13:05 13:10 13:15 13:20 13:25 13:30 13:35 13:40 13:45 13:50 13:55 14:00 14:05 14:10
网络
操作系统
应用软件 • 涉及时间周期
数据库 应用
系统设计 开发和测试
容灾能力
主机/存储
产品上线
运行维护
数据库性能优化过程-自顶向下
效益
•业务规则的优化设计 •合理的索引策略
代价
•内存和CPU的优化和
调整
•数据库体系结构的优
化设计
•数据库逻辑结构设计 •数据库物理结构设计
•合理的访问路径 •优化SQL语句的分析
• 问题原因：两个表都非常小，全表扫描是正确执行路径，但两个表被强制 按nested loop进行连接，导致大量内存操作 • 问题解决：去掉HINT。
优化前 00:00:38.94 9171128*8K=7,164M 2381 *8K = 18M 优化后 00:00:01.32
响应速度 内存消耗 I/O消耗
Data
未使用 空间
未使用 空间 HWM
在线进行
Shrink Shrink 操作 Operation
释放的空间
HWM
内容
从案例看性能优化 Oracle性能优化方法论 更多案例分析 性能优化服务 问答
技术方案—— 系统性能优化管理
性能调优和管理方法
操作系统管理员 网络管理员
目前分工和定位
客户： 项目管理和生产系统运行维护
应用开发商： 应用软件设计和开发
R2
产品供应商和紧急救援服务
建议的分工和定位
客户： 项目管理和全程参与项目建设
应用开发商： 应用软件设计和开发
R2
ACS服务：
全程技术支持
产品供应商和紧急救援服务
内容
从案例看性能优化 Oracle性能优化方法论 更多案例分析 性能优化服务 问答
•磁盘I/O和物理结构
的设计和调整
•充分使用PL/SQL
•系统资源竞争的监测
和调整
•应用系统的设计
•减少锁冲突
•操作系统平台的优化
设计
开发
上线
• 设计和开发阶段的优化 – 80%以上
20/80定律
• 应用设计开发 -- 80% 系统配置 -- 20% • 80%的性能问题是由20% 的应用导致的 • 80%的性能问题可以由 20%的优化技术所解决
<Insert Picture Here>
数据库性能优化方法&案例分析
内容
从案例看性能优化 Oracle性能优化方法论 更多案例分析 性能优化服务 问答
案例1：数据访问都在内存完成
• 数据访问都在内存完成，几乎 没有I/O，但CPU经常达到90% 以上的状态 • 主要问题：主要业务表缺乏索 引，导致大量全表扫描
性能是升级的第一大风险
• 某省移动公司仍然运行在9i 平台 • >90%升级问题并不是数据库升级，而是升级之后的应用性能 问题 • 升级 = > 性能优化
R2
R2
还是索引这样基础的问题
• 语句条件： …… where applyoid = :applyoid and region = 538; …… • 目前的索引 ：(REGION, SUBSID)，可选性并不高 • 优化建议：创建applyoid字段索引 • 复合索引的前缀性和可选性
开发、设计、运行维护各阶段 均可能导致性能问题
案例3：神奇的Oracle内部参数
• 内部参数列表
Parameter Name _b_tree_bitmap_plans _bump_highwater_mark_count _cursor_features_enabled _db_block_hash_buckets _db_block_hash_latches _db_block_numa _enable_NUMA_optimization _enqueue_hash_chain_latches _fix_control _in_memory_undo _index_join_enabled _optim_peek_user_binds _optimizer_mjc_enabled _sort_elimination_cost_ratio _sqlexec_progression_cost _table_lookup_prefetch_size _wait_for_sync Begin value FALSE 30 10 134217728 1048576 1 FALSE 256 5705630:ON, 5765456:3 TRUE FALSE FALSE FALSE 10 0 0 FALSE End value (if different)
3385*8K = 26M
2381 *8K = 18M
R2
9i难以解决的问题：中间表
Insert Insert Insert update Delete Delete
• 问题：
• • • • 高水位（HWM值）太高 大量全表扫描 中间表高可用性很高 传统办法难以解决
R2
碎片问题解决的新技术：10g Shrink
系统软件 硬件配置 Bug 其它 架构和应用设计
应用开发
用数据诠释20/80规则
原有语句 新语句 （不分 区） 00:26.0 6,554M 176M 3 新语句 (分 区 ) 00:12.6 4,763M 178M 3 新语句 （分区， 新索引） 00:02.8 97M 26M 3
响应速度 内存消耗 I/O消 耗 记录数
谨慎使用内部参数
内容
从案例看性能优化 Oracle性能优化方法论 更多案例分析 性能优化服务 问答
如何建设高质量的IT系统
• 系统性能出 现问题! 用户
涉及层面
•
实现目标
硬件
问题原因
响应速度
吞吐量 高可用性 数据/备份恢复 数据安全性 易管理性 扩展性
网络 操作系统 集群软件 数据库
• 内部event设置
Parameter Name
event
Begin value
44951 trace name context forever, level 1024:10753 trace name context forever, level 2:38087 trace name context forever, level 1:10183 trace name context forever, level 1:10027 trace name context forever, level 1:10028 trace name context forever, level 1:10191 trace name context forever, level 1:10411 trace name context forever, level 1:10629 trace name context forever, level 32:14532 trace name context forever, level 1:10091 trace name context forever, level 1:10142 trace name context forever, level 1:3806
01:01.3 12,251M 369M 3
• • • •
第一列为数据库非分区时的运行情况 第二列为将子查询调整为多表连接之后的优化效果 结论：调整语句编写方式，特别 第三列为数据库分区之后的优化效果 是新建复合分区索引的优化效 第四列为新建一个复合分区索引之后的优化效果 果，远远大于数据库分区之后
的优化效果 80%的性能问题可以由20%的优 化技术所解决
应用开发技术运用策略
比较项目 操作特点 响应速度 吞吐量 并发访问量 联机业务 批处理业务 日常业务操作，尤其是包含 后台操作，例如统计报表、 大量前台操作 大批量数据加载 优先级最高，要求反应速度 要求速度高、吞吐量大 非常高 小 非常高 小 大 不高 大
• 正确解决方式：通过SR申请Patch
• event：44951 trace name context forever, level 1024分析 • 《'enq HW - contention' For Busy LOB Segment [ID 740075.1]》 • 问题原因：Bug 6376915，对LOB 字段进行大量 DML操作时，enq HW – 启示 3：系统参数调整是性能优化 contention等待事件非常高 的基础和必要条件，但不是充分
业务特征
单笔事务的资源消耗
SQL语句类型 索引类型 索引量 访问方式 技术运用 连接方式 BIND变量 并行技术 分区技术 物化视图使用
主 要 是 插 入 和 修 改 操 作 主要是大量查询操作或批量 （DML） DML操作 B*索引 适量 按索引访问 Nested_loop 使用或强制使用 使用不多 使用，但目标不同 少量使用 Bitmap、Bitmap Join索引 多 全表扫描 Hash Join 不使用 大量使用 使用，但目标不同 大量使用
End value (if different)
案例3：神奇的Oracle内部参数
• _b_tree_bitmap_plans分析 • 《ORA-00600[Qkabix] Error Caused by a Particular Query [ID 743212.1]》 • 问题原因：Bug 5945708，触发ORA-00600[Qkabix]错误 • Workaround: _b_tree_bitmap_plans = FALSE
自底向上
数据库性能管理的全面性
业务分析人员
1. 业务需求优化 2. 数据库逻辑设计优化 3. 应用流程设计优化 4. SQL语句优化 5. 数据库物理设计优化
设计人员 应用开发人员 数据库管理人员
6. 内存和CPU的优化 7. 磁盘I/O系统设计和优化 8. 系统资源竞争的监测和优化
9. 操作系统优化 10. 网络配置优化
响应速度 优化前 00:12:59.12 优化后 00:00:01.26 3*8K = 24K 2*8K = 16K
R2
内存消耗
I/O消耗
2663578*8K= 20809M
118542*8K = 926M
发现最大的性能问题
• 语句片段
SELECT /*+ use_nl(a)*/ …… FROM tbcs.smtemplate b, tbcs.smnotify_kf a …. …
• 解决方式：10.2.0.4 patchset
• Workaround: •为LOB段提前分配空间
条件
内部参数更多用于故障诊断
故障诊断更主要方式是安装补丁 和版本升级，而不是设置内部参 •为包含该LOB字段的表进行分区 数
•设置event：44951 trace name context forever, level <1 – 1024>
上线之后的优化过程(自底向上)
检查系统的利用率 检查等待事件 检查物理I/O 确定范围 – 系统级, 模块级, 用户级? 确定最消耗资源的SQL STATSPACK/AWR报告 Top Sessions in OEM 6. 分析执行计划 explain plan 7. 分析访问的对象 (size/cardinality) 8. 分析连接、访问方式… … 9. 找出相关问题 10. 确定解决方案 11. 测试解决方案 12. 生产环境实施解决方案 13. 分析结果 1. 2. 3. 4. 5.
1000
500 0
案例2：统计信息问题
• 问题现象：CPU、I/O：100%！
• 问题原因： 统计信息不准确，导致所有操作为全表扫描 • 解决方式：更新统计信息
SQL> exec 启示2：性能问题与优化器、 dbms_stats.gather_schema_stats(ownname=>< 用户名 统计信息采集紧密相关 >,cascade=>TRUE);
wenku.baidu.com
• 解决方式：创建一个索引
System Summary imsdb1 2011-3-25
CPU% usr%+sys% IO/sec 2000 1500 Disk xfers
启示1：高配置硬 件不能解决所有性 能问题
100 80 60 40 20 0 12:15 12:20 12:25 12:30 12:35 12:40 12:45 12:50 12:55 13:00 13:05 13:10 13:15 13:20 13:25 13:30 13:35 13:40 13:45 13:50 13:55 14:00 14:05 14:10
网络
操作系统
应用软件 • 涉及时间周期
数据库 应用
系统设计 开发和测试
容灾能力
主机/存储
产品上线
运行维护
数据库性能优化过程-自顶向下
效益
•业务规则的优化设计 •合理的索引策略
代价
•内存和CPU的优化和
调整
•数据库体系结构的优
化设计
•数据库逻辑结构设计 •数据库物理结构设计
•合理的访问路径 •优化SQL语句的分析
• 问题原因：两个表都非常小，全表扫描是正确执行路径，但两个表被强制 按nested loop进行连接，导致大量内存操作 • 问题解决：去掉HINT。
优化前 00:00:38.94 9171128*8K=7,164M 2381 *8K = 18M 优化后 00:00:01.32
响应速度 内存消耗 I/O消耗
Data
未使用 空间
未使用 空间 HWM
在线进行
Shrink Shrink 操作 Operation
释放的空间
HWM
内容
从案例看性能优化 Oracle性能优化方法论 更多案例分析 性能优化服务 问答
技术方案—— 系统性能优化管理
性能调优和管理方法
操作系统管理员 网络管理员
目前分工和定位
客户： 项目管理和生产系统运行维护
应用开发商： 应用软件设计和开发
R2
产品供应商和紧急救援服务
建议的分工和定位
客户： 项目管理和全程参与项目建设
应用开发商： 应用软件设计和开发
R2
ACS服务：
全程技术支持
产品供应商和紧急救援服务
内容
从案例看性能优化 Oracle性能优化方法论 更多案例分析 性能优化服务 问答