《Oracle性能优化科学与艺术》读书笔记

《Oracle性能优化科学与艺术》读书笔记 (2007-12-12 12:08:49)

转载

标签： it/科技

分类： Oracle DB

英文原著书名：The Art and Science of Oracle Performance Tuning

中文译著书名：Oracle性能优化科学与艺术

原著作者：（美）Christopher Lawson

译作者：谢君英 周丽琴

出版社：清华大学出版社

主要内容：

介绍了一种行之有效的性能优化过程

如何客观地对问题进行分类

优化的人为因素和商业因素以及处理它们的技巧

如何通过Trace实用程序找到问题的根本原因

等待事件统计数字的实际应用

如何分析SQL语句的开销

使用统计数字和SQL优化技巧获得可以量化的结果

如何设计和测试解决方案

通过详细案例来阐明本书的要点

章节：

第1章 性能优化的挑战

第2章 许多优化策略失败的原因

第3章 Oracle医师

第4章 处理人际关系

第5章 Oracle侦探

第6章 收集更多的线索

第7章 Oracle病理学家

第8章 分析SQL瓶颈

第9章 分析SQL连接

第10章 病理学家的工具箱：其他策略

第11章 Oracle艺术家

第12章 艺术家的调色板

第13章 Oracle魔术师

第14章 案例分析

附录A 有用的V$视图定义

贴士

什么都无法替代对数据库基本原理的理解
成功的性能优化需要理解数据库基本原理，重点了解导致瓶颈的根本原因
有了性能优化的良好方法，许多性能瓶颈问题实际上非常容易解决
如何获得适当信息来着手性能优化是最大的难题
不存在可以解决所有问题的工具，分析人员的分析才是最重要的
只有在用户（Belinda注：DBA或调优人员）具备解决问题的能力，并可理解工具的功用后，性能优化工具才是有价值的。添加硬件可以临时掩盖住问题，但是很少能从根本上解决问题
培训和认证是好的起点，但并不等于具备了实际能力
不要轻易排除应用程序设计对性能的影响的可能
精心设计的运行日志能够为性能优化者节省大量的时间（Oracle侦探）
如同检查大量逻辑或物理I/O一样，一定要检查执行次数过多的查询（Oracle侦探）
SQL优化是整个性能优化过程的核心（Oracle病理学家）
从约束比例最强的表开始连接，且应查看结果集的缩减比例，而不是返回的行数，然后，在剩下的表中使用约束性最强的表继续连接（Oracle病理学理）
除非有非常充足的理由这样做，否则令init.ora中TIMED_STATISTICS
=
TRUE（Oracle病理学家）
不要假设已存在正确的索引――即便是在产品系统（Oracle艺术家）
留心要标准化数据的查询（Oracle艺术家）
在分析巨大的表时，慎用默认的取样率（Oracle艺术家）
使用Oracle新发行的版本的时候，一定要小心，甚至要抱有怀疑的态度（Oracle艺术家）
记住，许多系统崩溃都是由人为错误造成的（Oracle魔术师）
所有对产品的修改都应该并入到脚本中（Oracle魔术师）

从“医生”到“魔术师”

医师	侦探	病理学家	艺术家	魔术师
定义问题	调查	分离原因	提出解决方案	实施
	尽力重现并量化问题

Oracle医师：
之所以要以一位“医师”的心态去定义问题，是由于前文的贴士所提到的一样，“有了性能优化的良好方法，许多性能瓶颈问题实际上非常容易解决”。
而像“医生”一样详细的询问以了解性能问题的表述通常可以包括如下方面：
应用程序是什么？
用户或使用者是谁？
使用的数据库是什么？
该程序的功能是什么？
详细的不同时段不同条件的运行时间是怎样的？
所得数据的日期范围是什么？
所得数据的记录数量是怎样的？
……
而作为调优，在数据库一方通常所作的操作往往不外乎如下几种（看起来好像真的非常简单）：
新建或修改索引
新建或重建表统计信息
增大缓冲区缓存
增大共享池
将表固定（Pin）在内存
将软件包（Package）固定（Pin）在内存中
增加重做日志（Redo）的大小
增加重做日志（Redo）的数量
归档日志不同的目标位置
改变回滚段的大小
……
当然，前文的提示中也有提到，性能调优除了DB端的，有很大可能也是应用程序编写不当造成的.
Oracle侦探：
以“医生”的身份正确的表述了问题过程，需要以“侦探”的身份去确认问题、重现问题、量化问题、找出所有异常因素
检查基本信息通常包括：
检查DB的警告日志，观察所有异常消息，包括空间（Tablespace）或回滚问题
检查关于空间错误的其他线索
确定没有达到文件的AUTOEXTEND限制
确定文件的日志文件系统没有达到最大值
确定没有达到最大用户数
确定包含Lisener.log的文件系统没有达到最大值
确定生成了统计数据
常见的异常因素可能有：
是否使用了透明网关？
激活了MTS了吗？
使用Oracle名称了吗？
是否使用了特殊的init.ora参数？
数据库为块设置了极限大小吗？
是否使用了位图索引？
对于数据仓库，是否被激活了星型转换（Star
Transformation） Star
Transformation是Oracle为超大数据库表采取的一种复杂连接方式
Oracle病理学家：
个人认为此处与我们的实际工作关联最为紧密……
性能问题常见的原因：
应用程序的设计以及与数据库的交互
第一，设计方案可能让应用程序只处理来自非常小的表产生的结果。这意味着，不能够只用很小的数据量对程序进行测试，负载测试和伸缩性测试密切关系到一个程序在设计阶段对性能的控制
第二，应用程序采用了逐行处理的算法，而不是处理整个行集。
第三，应用程序定义的SQL可能会限制索引的使用
第四，SQL中的表连接顺序的效率不高。
第五，应用程序允许空查询。即，应用程序在判断用户的查询合理性方面做得很差，很多必须严格约束的条件经常不作限制，导致不必要的大数据量查询。
数据库设计
错误地激活了不必要的数据库功能
没有更新统计数字，原来的统计数字迷惑了优化器
数据分布不均，但未使用柱状图（histogram）
索引
对过多的列使用索引
陈旧的索引（需要重建）
缺少索引/错误的索引
init.ora参数
过小的SGA，例如，将DB_BLOCK_BUFFER设置得太低（或太高，耗尽服务器内存）
共享池太大
使用了根本无需使用的功能，如MTS(Multi-Threaded
Server)
错误地设置了DB_FILE_MULTIBLOCK_READ_COUNT
批处理任务产生的问题
硬件（包括网络）
分析SQL瓶颈：
更新并查看统计数字
确保运行的SQL是必要的并且可实现其对应的业务逻辑
该SQL运行的频度
查看结果集大小（行数，Block数，字节数）
绘出表连接图
生成原SQL的执行计划
生成原SQL运行的统计数据，包括已耗时间、磁盘I/O、逻辑读等等
分析Oracle在运行SQL前根据预估的统计数据产生的执行计划
查看并分析执行计划中的View（通常带聚合的View是不能够被执行计划并入到查询内部的）
查看并分析执行计划中的Filter（如果过滤发生在全表扫描中，则执行计划中不会显示出Filter，而是显示全表扫描）
查看并分析执行计划中的全表扫描
查看并分析SQL语句中的子查询
调整SQL后，查看新的执行计划
调整SQL后，查看新的运行后统计数据，包括已耗时间、磁盘I/O、逻辑读等
确保优化结果并不是Oracle
DB缓存带来的（缓存可以大大降低物理读，但逻辑读实际并未降低）
分析SQL连接：
连接种类
值连接（等值、不等值）
内连接
外连接
群集连接（需预先建立群集，很少用）
笛卡尔积（没有连接条件的两个表）
星型连接（应用于数据仓库，找不到最小的入口表）
连接处理
嵌套循环(Nested
Loop)
排序合并(Sort-Merge)
哈希连接(Hash Join)
优化连接
绘制SQL连接图

SELECT customer_name, address,
credit_card_name, expire_date
FROM customer C, credit_card CC
WHERE
C.customer_id = CC.customer_id
AND CC.expire_date < (SYSDATE +
1)

影响连接的init.ora参数
HASH_JOIN_ENABLED
HASH_AREA_SIZE
HASH_MULTIBLOCK_IO_COUNT
SORT_AREA_SIZE
DB_FILE_MULTIBLOCK_READ_COUNT
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_INDEX_CACHING
OPTIMIZER_MAX_PERMUTATIONS
PGA_AGGREGATE_TARGET
WORKAREA_SIZE_POLICY

Oracle艺术家：
常规解决方案
索引的前导列是不是和典型的搜索条件匹配
索引有没有被查询中的函数所限制
是否适合使用位图索引
是否需要重建索引
考虑使用聚合
增大统计数字的取样
特殊连接技巧
物化视图
配置非CPU资源的限定
SQL提示
柱状图
重构应用主程序
处理Oracle
Bug和其他局恨性
更改硬件
Oracle魔术师：
比照系统不同的一些切入点
产品中存在不同的统计数据
生成了不同的索引
init.ora参数不同
数据内容存在很大的差异
数据库版本不同
数据库补丁级别不同
应用程序不同

工具箱

查找磁盘读较大的SQL SELECT executions, disk_reads, buffer_gets,
sql_text
FROM v$sql
WHERE disk_reads / (executions + 0.0001)
> 10000;
查找逻辑读较大的SQL SELECT executions, disk_reads, buffer_gets,
sql_text
FROM v$sql
WHERE disk_reads / (buffer_gets + 0.0001)
> 20000;
找出相同格式被执行多次的SQL SELECT persistent_mem, SUM
(disk_reads) disk_reads
FROM v$sql
GROUP BY
persistent_mem
HAVING SUM (disk_reads) > 1000000;

--persistent_mem stands for bytes that
consumes
找出几乎完全相同的SQL SELECT SUBSTR (sql_text, 1, 50)
similar_sql, COUNT (*)
FROM v$sql
GROUP BY SUBSTR
(sql_text, 1, 50)
HAVING COUNT (*) > 1000;
SQL
Trace ALTER SESSION SET sql_trace=TRUE;
ALTER SESSION SET
sql_trace=FALSE;
TKPROF tracefile_name.trc outfile_name EXPLAIN =
chris/gold
查看等待 SELECT event, time_waited
FROM
v$system_event
WHERE event = 'db file scattered
read';
SELECT evt.SID, ssn.username, evt.time_waited,
evt.average_wait
FROM v$session_event evt, v$session
ssn
WHERE evt.event = 'SQL*Net message to
client'
AND evt.SID =
ssn.SID
AND evt.time_waited > 10000
ORDER BY
evt.time_waited;
查看用户是否被阻塞 SELECT username, lockwait, row_wait_obj#,
row_wait_row#
FROM v$session
WHERE SID =
40;
生成柱状图的方法 ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS
col_name SIZE buckets;
ANALYZE TABLE table_name COMPUTE STATISTICS FOR ALL
COLUMNS SIZE buckets;
ANALYZE TABLE table_name COMPUTE STATISTICS FOR ALL
INDEXED COLUMNS SIZE buckets;
EXEC dbms_stats.gather_table_stats(ownname
=> '',tabname => '',method_opt => 'FOR COLUMNS major SIZE
10');

本书可以解答的问题

如何准确获取统计数字、柱状图
如何解读执行计划以及Trace文件
SQL有哪些连接
绘制SQL连接图有哪些必要考量
什么叫Nested
Loop
什么叫Sort-Merge
什么叫Hash Join
什么叫Table Access Full
什么叫Index
Scan
什么叫Index Scan Full
什么叫Access By ROWID
什么叫Bitmap
Index
什么叫MV
怎样使用With
怎样使用动态SQL＋绑定变量
SQL
Hint有哪些
init.ora参数有哪些
DBA常用视图有哪些