数据库性能诊断的七种武器
主要议题
• 性能优化面临的挑战
• 调优工具的变迁
• 诊断工具中的七种武器
• Q & A
性能优化面临的挑战
1、架构和业务的设计与变更
2、熟悉各种数据库参数、系统参数
3、应用逻辑与SQL代码实现
4、选择合适的存储方式
存储盘阵、存储模式、存储参数、存储表空间、存储对象等
5、复杂的网络配置
还有更多。。。
DBA的事情好多哦…
为满足业务的运行要求，高性能要求是目前IT系统普遍面临的最棘手问题，尤其是客户面对着目前越来越庞大系统和数据，系统整合、数据大集中似乎成了趋势，而对我们来说，则充满了压力和挑战。
Oracle 调优工具的变迁
• 朦胧之初(v5)
• Debug code
• 初见端倪(v6)
• Counters/Ratios
• BSTAT/ESTAT
• SQL*Trace
• 有所发展(v7)
• 出现了 Wait Event
• counters向timers的变迁
• 快速进化(8i)
• 宽广的范围
• STATSPACK
• 逐渐完善(9i)
• 更精细的收集 - Session tuning using 10046 SQL traces
• 更加全面的STATSPACK
• 智能化、自动化开始初现
• 日趋完美 (10g) – 基于数据库自动化基础的更完美的优化
• 自动化收集
• 更加广泛的收集
• 保留一段时间的历史
• 提供了：ASH, AWR, ADDM, EM等功能调用
• 形成了越来越完善的性能优化诊断工具
诊断工具中的七种武器
一、动态性能视图
• 碧玉刀 — 动态性能视图：刀是最大众化的武器，小到刮刀、折刀、剃刀、西瓜刀、切菜刀、剔骨刀，大到柳叶刀、圆月弯刀、武士刀、青龙偃月刀。。。不论大小长短、不论古今中外，刀是最常见的武器。不过再普通的刀，到了高手的手中，也会成为神兵利器，刀如此，Oracle的动态性能视图也如此，无论各种性能问题，根源皆可寻究于此。
• V$SYSSTAT V$SESSION V$SESSTAT V$SGASTAT
• V$FILESTAT V$UNDOSTAT V$ROLLSTAT V$WAITSTAT
• V$LOCK V$LATCH V$SQL V$SQLAREA V$SQLTEXT
• V$PROCESS V$LIBRARYCACHE V$ROWCACHE ……
• 大处着眼，小处着手
• 不是每个问题，都那么清楚的摆在我们面前，细致的察看，仔细的分析，利器才是利器
• select name,value fromv$sysstat where name like '%SQL%';
NAME VALUE
------------------------------------------------------- ----------
bytes sent via SQL*Net to client 2.0196E+12
bytes received via SQL*Net from client 1.3342E+12
SQL*Net roundtrips to/from client 7397997982
bytes sent via SQL*Net to dblink 1.5108E+12
bytes received via SQL*Net from dblink 1.1800E+11
二、等待事件
• 长生剑 — 等待事件：剑，轻灵、快速、灵敏，甚至于诡异。谈笑间，轻松快意时寻出敌人弱点，以闪电般的速度刺入敌人最虚弱的部位，一击破敌。
性能优化的核心是什么，快速准确的定位，不需要华丽的显示，不需要冗长的信息，需要的仅仅是一个准确的定位，等待事件就是此中利器。
• v$system_event /v$session_event / v$session_wait
• 竞争即等待
• 寻找第一眼的感觉
• 从v$system_event中发现系统问题
• 从v$session_event中发现会话问题
• 从v$session_wait的参数中找到竞争对象
• 熟悉各种主要的等待事件,快速定位问题所在
• Top 5 TimedEvents Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ----------------
wait for a undo record 35,928 3,451 65 50.1 Other
CPUtime 1,687 24.4
db file scattered read 392,504 821 11.7 User I/O
wait for stopper event to be i 4,027 278 69 3.4 Other
log file sync 28,880 171 2.1 Commit
• wait for a undo record等待与回滚段的大量回滚有关，一般是出现了大事务回退造成
• select sid, event, wait_classfrom v$session_wait s where s.event notlike ‘%message%’;
SID EVENT WAIT_CLASS
------------------- ----------------------------------------------------------------
507 PX Deq: Txn Recovery Start Idle
511 PX Deq: Txn Recovery Start Idle
268 PX Deq: Txn Recovery Start Idle
……
• select pid, state,undoblocksdone from v$fast_start_servers;
PIDSTATE UNDOBLOCKSDONE
---------- ----------- --------------
133RECOVERING 7124
……
• buffer busy waits(数据高速缓存忙等待)
• db file scattered read(数据文件离散读取)
• db file sequential read(数据文件顺序读)
• direct path read(直接路径读取)
• direct path write(直接路径写出)
• enqueue(队列)
• free buffer waits(空闲缓冲区等待)
• latch free(锁存器空闲)
• log buffer space(日志缓冲区空间分配)
• log file switch(archivingneeded)
• log file switch(checkpointincomplete)
• log file sync(日志文件同步)
三、Statspack
• 霸王枪 — statspack：枪中之霸王，胆气之结晶。枪具有剑的轻灵，又有棍的霸道，不论是快速定位，还是全面分析，都是statspack所能胜任的。
• 有了全面的信息收集，分析问题变得简单了
• Statspack的安装
$ORACLE_HOME/rdbms/admin/spcreate.sql
• 收集统计信息
$ORACLE_HOME/rdbms/admin/statspack.snap
• 自动收集统计信息
$ORACLE_HOME/rdbms/admin/spauto.sql
• 生成报表
$ORACLE_HOME/rdbms/admin/spreport.sql
• 要收集计时信息，设置：
TIMED_STATISTICS = True
包含的信息：
• 数据库和实例名称
• 获取快照的时间
• 当前高速缓存的大小
• 负载概览
• 实例效率百分比
• 前五个等待事件
• 等待事件的完整列表
• 共享池中SQL语句的信息
• 实例活动统计
• 表空间和文件I/O
• 缓冲区统计信息
• 回退段或还原段统计信息
• 栓锁活动
• 字典高速缓存统计信息
• 库高速缓存统计
• SGA统计
• Init.ora参数的启动值
四、Active Session History-活动会话历史
孔雀翎 — ash、awr、addm、addr：是一种暗器，但又不是暗器。悄然，自动，不动声色间，一切皆在握。Oracle在10g开始，推出了一系列自动化、智能化的工具，虽然这些工具在以前或多或少都有相似的影子，但功能的增强、理念的增强，造就了这些以前所不具备的新工具。
• 每秒钟自动从内存中抓取样例的活动会话信息
• 可以从v$active_session_history获得会话近期的活动信息
• select a.sql_text from v$sql a
where sql_id in (select sql_id from v$active_session_history where session_id=157);
• 信息直接从内存结构中获取，并不保存，仅在系统运行中有效
• 可以得到
• SID
• SQL ID
• Program
• Wait event#
• Object, File, Block
• actual wait time (if capturedwhile waiting)
• 通过ashrpt.sql可以产生ash分析报告，发现某个时段的TOP
(Top Events/ Top SQL/ Top Sessions/ Top Objects/Files/Latches)
• 可以通过活动会话信息追溯到性能问题的根源
• 什么资源在竞争？
• 向下追溯到哪个程序带来了竞争？以及哪个SQL带来了竞争？
• 性能信息和负载量的捕捉
• 系统统计信息,等待事件,SQL负载等
• 性能问题分析
• 与正常阶段比，哪种资源消耗明显？
• 与正常阶段比，哪些语句出现了明显问题？
• 哪种操作带来了问题？
• 什么资源上出现了瓶颈？
• 瓶颈的原因是什么？
• 性能调整方案
• 多个性能问题，哪个影响更大
• 每个性能问题，应该怎么解决
• 如果不能解决，考虑进一步调用哪个工具进行
五、sql tuningadvisor/sql access advisor
• 多情环 — sql tuning advisor/sql access advisor：多情环似乎是一个情种，谁拥有它似乎都会产生感情，从而对许多江湖中的事看的很淡。在Oracle应用中，谁对性能影响最大，不言而喻，是SQL，准确地说是SQL语句的算法，可以说，80%以上的性能问题都可以通过调整SQL来解决或者缓解，拥有调优SQL性能的能力，基本上可以算作一个DBA高手咯。。。
• 执行计划是一系列的优化器用来完成SQL操作的步骤和操作
• 通过下面的工具能够看到执行计划
• EXPLAIN PLAN
• V$SQL_PLAN
• SQL Trace
• SQL*Plus AUTOTRACE
• 看到执行计划不是目的，优化与分析仍然靠DBA去努力。。。
•
六、hints
• 离别钩 —提示(hints)，Oracle很强大的工具，优化SQL的利器，能够强制SQL的执行算法，确保SQL执行按照我们希望的执行计划。钩，用的好伤人，用不好伤己，hints也如此。非高手者，非思路清晰者，且忌乱用，用不好的话，你会很受伤的。。。
• 首选用于测试执行计划
• 其次可用于在需求确定时，固化执行计划
• 常用的hints：
• FIRST_ROWS, ALL_ROWS ,RULE
• FULL(tab)
• INDEX( tab index )
• NO_INDEX ( tab index )
• USE_NL(tab)
• USE_MERGE(tab..)
• USE_HASH(tab1 tab2)
• PARALLEL ( table,<degree> [, <instances>] )
• 它很锋利，小心“伤人”
七、对数据库系统架构运行原理的理解
• 拳头：没有武器就是有武器，有武器就是没有武器。最后一种武器-拳头，就是对整个体系的全面理解，无形的武器胜于有形的武器，就像太极，没有招数就是最好的招数。
作为一个DBA，或者更高一些，作为一个架构管理员，能够理解整个业务系统，对数据库、存储、网络、系统、应用软件、业务流程都非常清楚，甚至于对使用者的使用习惯都非常清楚，优化就不再是什么高难度了。天地之大皆装于我胸中，万物皆为我之神兵。如果真有那么一天一切都在你的掌握之中，优化也许会比吃饭更容易一些咯
结尾
• 优化的工具有千千万，找到适合的最关键
• 精通一、两个工具，比什么工具都“会”使更有用
• 工具就是工具，最终优化人来定
• 工具是可以换的，人“才”是换不来的
• 优化应该在系统中整体贯穿，需要我们用优化工具的时候似乎已经有点晚。。。