前言
数据库的调优的手法
- 调优是什么
这意味着“去除成为瓶颈的地方，最大化发挥H/W性能”。 - 首先，找出发生错误的位置（原因）
- 接下来，作为改善原因的对策，进行以下操作
- 通过减少无用的处理，减少消费成本
- 追加瓶颈地点的H/W，整理H/W资源的消费平衡
- 如果改善了第一个瓶颈，那么也就可以获得其他地方的瓶颈
- 因为瓶颈是移动的，一般而言，到达用户要求的服务水平或者CPU资源的瓶颈为止，都会反复执行
- 本章将介绍如何通过灵活使用Oracle Database 11g所提供的功能，简单高效执行调优。
面向OLTP的调优手法
设定脚本与目标
- 设定脚本
- 假设您是网络购物网站的数据库管理者。
- 现在互联网盛况空前，用户在线访问激增
- 某天，接到了用户表示画面操作消耗过多时间的投诉。
- 很快就找出了原因，上级命令立即调优。。。
※数据库环境以及生成负荷工具“Oracle Load Testing”的准备完成后，马上就开始负荷测试。
※数据库服务器中，知道物理内存到达极限位置都假定完成负载。
- 完成目标
- 通过在应用中操作，可以提高大约100倍数据库的吞吐量（Transaction Per Sec）！
系统质量的问题
通过Oracle Application Testing Suite解決
Oracle Application Testing Suite 9.2
简单迅速地从用户視点的执行测试产品群
Oracle Load Testing
Accelerator for Database
- 支持对数据库直接进行负荷测试
- 数据库的连接方式
- Oracle Thin (oracle.jdbc.driver.OracleDriver)
- ODBC (sun.jdbc.odbc.JdbcOdbcDriver)
- 可能参数的负荷
- 执行Query、DML、DDL
- 执行PL/SQL
- 测试SQL行数技术
- 通过Java API执行扩展
虽说如此，实际制成SQL的是数据库语句件输入
- 可以通过Open Script，对测试脚本输入SQL
- 数据库重放捕获语句件
- 通过Oracle Real Application Testing的数据库replay捕获的事务SQL
- 比如，正式环境中执行的SQL进行脚本化的情况
- SQL 以及PL/SQL 结构脚本
- custom SQL、PL/SQL记录的text语句件
Oracle Load Testing
丰富的报告功能
- 可以瞬间制成需要的结果数据的图表
- 可以在负荷测试中进行参考，可以通过on-demand调优
- 可以在一个图表中表示多个条件不同的测试结果
- 图表可以对图像语句件以及CSV语句件输出
【参考】 User I/O相关的主要待机项目
- db file sequential read
- 经过缓冲区的单一块单位读入时，会产生的待机项目。
- 主要在使用索引访问表时局时会发生。缓冲区命中率较差时会频繁发生。
- db file scattered read
- 经过缓冲区的多块单位读入时会产生的待机项目。
- 因为只要是在执行Table Full Scan 时发生，判断是否支持了合适的索引后再觉得是否使用。
- 另外，Pre-Warming功能有效时，读入所有索引的叶块的处理中也会发生（Index Full Scan以及Index Fast Full Scan）。
- direct path read
- 不经过缓冲区，在多块读入时产生的待机项目。主要是并行执行Table Full Scan时产生。
- 但是，Oracle 11g 以后的版本中，串行执行Table Full Scan时，对于缓冲区的尺寸较大时使用。
数据库的OLTP处理的基本操作
SQL的处理时间的详细映射
- 一般而言，SQL的大部分处理时间都是HDD中的数据读入等待
- DRAM（内存）中通过将数据移动到缓冲区中，可以实现响应时间高速化
OLTP系统的课題①
数据量的増加与性能的影响
OLTP系统的课題②
用户数的増加与性能的影响
OLTP系统的问题的传统解决方法
高价的系统投资
Solid State Drive/Device（SSD）的出现
HDD高速备用device
- 性价比： HDD < SSD < DRAM
- 如果不擅长HDD，请使用“Small Random Read” （10~30倍）
- SSD：灵活使用记忆的闪存
HDD中，在访问数据时，所需要的seek时间（在磁盘上移动头所需要的时间）以及search时间（目标数据回转到头位置的等待时间）
- 如果在SSD上构成数据库的话，I/O性能会比HDD高得多。
特别是在多件搜索处理中的OLTP系处理中效果较好
- 其他，数据库中会发生“Small Random Read”的处理
- 临时表区域的读入
- 恢复处理
Database Smart Flash Cache
使得读入数据块高速化
Database Smart Flash Cache的效果
SQL的处理时间的详细
- Buffer Cache中缓冲区未命中时，I/O的等待时间也会大幅度减少
缓冲区命中时，可以让响应时间相同
Database Smart Flash Cache
应用案例
- Database Smart Flash Cache因为是缓冲区的扩展区域，满足以下条件的话，可以实现以下效果
- 待机项目“db file sequential read”频繁发生
- Buffer Pool Advisory（AWR / STATSPACK）表示Buffer Cache的増加是有效的
- 存储I/O性能为瓶颈，CPU资源还有富余
※ 并行执行大量数据的搜索处理时，像Direct Path Read一样，执行不经过缓冲区的数据块时，在Database Smart Flash Cache区域中，因为数据还没有被缓冲，所以无法获得这个效果。
Database Smart Flash Cache
设定参数
- 设定SSD的路径
- db_flash_cache_file = ‘<filename>’
- 分割Database Smart Flash Cache的区域的尺寸
- db_flash_cache_size = <size>
- 尺寸的估计方法
- Database Smart Flash Cache的尺寸推荐范围为Database Buffer Cache的2倍到10倍。
- Database Smart Flash Cache有效时，就会在缓冲区中分割储存在Database Smart Flash Cache区域中的数据块的管理区域。管理区域尺寸可以通过以下式子来估算。
[db_flash_cache_size] / [db_block_size] x 100( 若 RAC 则100这个因子改变）
Database Smart Flash Cache
特征
- 高成本性能
- 通过作为缓冲区来灵活使用，可以将访问频率较高的数据移动到SSD上
- 对应服务器上的Flash PCI Card
- 对现有系统的导入变得简单并且成本较低
- 采用更加便利的LRU算法
- 在RAC节点间保存Flash Cache上的数据的一致性
- 通过FLASH_CACHE { KEEP | NONE | DEFAULT } 属性，可以在表以及分区单位中调整Database Smart Flash Cache区域的使用
（KEEP：优先缓冲 NONE：不缓存DEFAULT：标准操作）
- 缓冲区的扩展区域
- 与缓冲区相同，实例重启后需要warm up
- 可以不去管Database Smart Flash Cache上的数据进行备份
Oracle Enterprise Manager
查看User I/O的待机项目的详情
- HDD的I/O（db file sequential read）慢慢往SSD的I/O（db flash cache single block physical read）中迁移，待机会话数减少
Oracle Enterprise Manager
比较待机时间的直方图
- 比较“db flash cache single block physical read”以及 “db file sequential read”的I/O时间的直方图
- Flash Cache的方每次需要的时间都非常快速
- 重要的不是比较待机次数，而是比较一次待机所需要的时间
面向DWH的调优手法
设定脚本以及目标
- 设定脚本
- 假设您是DWH的数据库管理者。
- 一年以来，公司业务都顺利进行，某日，市场部部长向您提出以下要求
- 一年前，得到DWH系统的分析结果需要10秒左右，但最近却需要1-2分钟。
- 因为等待时间较长，职员的下班时间变晚，因此产生了额外的加班费用
- 首先需要找出原因，为了能够变成原来的响应时间，马上开始进行调优。
- 另外，前提是无法有效利用Enterprise Edition的功能。
- 目标
- 通过使用Enterprise Edition功能，可以使得DWH系统的分析查询（SELECT语句语句）提高10秒左右！
面向DWH的调优手法
对象SQL
Comment