李轶楠细数数据库性能诊断兵器谱

    【技术开发 评论】

    编者按：工欲善其事，比先利其器

    4月2日-3日，中国首届数据库技术大会在北京盛大召开。在这一面向数据库及商业智能技术专业人群的技术盛会上，各路DBA、数据库开发，商业智能技术高手云集，分享各自在数据库技术领域深入研究的体会和最佳实践。

    在4月3日上午进行的“Oracle实践案例应用专场”演讲中，来自于ITPUB社区的资深版主李轶楠给大家分享了题为“Oracle优化工具中的七种武器”的演讲。李轶楠在社区中的帐号为Ora-600。他擅长Oracle DBA的相关技能，有丰富的Oracle架构设计、实施部署、故障诊断、性能优目前已获得8i/9i/10g OCP认证，同时还得到了Oracle官方授予的Oracle ACE称号。

ITPUB社区的资深版主李轶楠 讲座现场

    当前，为满足业务的运行要求，高性能要求是目前IT系统普遍面临的最棘手问题，尤其是客户面对着目前越来越庞大系统和数据，系统整合、数据大集中似乎成了趋势，而对我们来说，则充满了压力和挑战。
   
    李轶楠在演讲中谈到，在此背景下，数据库性能优化面临诸多挑战，包括：架构和业务的设计与变更；熟悉各种数据库参数、系统参数；应用逻辑与SQL代码实现；如何选择合适的存储方式；存储盘阵、存储模式、存储参数、存储表空间、存储对象等；面对复杂的网络配置等等诸多方面。

    同时，他指出，Oracle数据库性能调优工具也在不断变迁，从8i中的快速进化，到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等等。

    李轶楠谈到，不是每个问题，都那么清楚的摆在我们面前，细致的察看，仔细的分析，利器才是利器 

    武器二：长生剑 — 等待事件

    剑，轻灵、快速、灵敏，甚至于诡异。谈笑间，轻松快意时寻出敌人弱点，以闪电般的速度刺入敌人最虚弱的部位，一击破敌。
性能优化的核心是什么，快速准确的定位，不需要华丽的显示，不需要冗长的信息，需要的仅仅是一个准确的定位，等待事件就是此中利器。

    竞争即等待，关注等待事件：v$system_event / v$session_event / v$session_wait 有助于我们发现系统的性能瓶颈。比如：
从v$system_event中，我们能发现系统问题；从v$session_event中发现会话问题； 从v$session_wait的参数中可以找到竞争对象。

    熟悉各种主要的等待事件,快速定位问题所在 ，如图

    常用等待事件：

    • 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(archiving needed)

    • log file switch(checkpoint incomplete)

    • log file sync(日志文件同步)


 

    武器三：霸王枪 — statspack

    枪中之霸王，胆气之结晶。枪具有剑的轻灵，又有棍的霸道，不论是快速定位，还是全面分析，都是statspack所能胜任的。有了全面的信息收集，分析问题变得简单了。

    Statspack输出包含丰富的信息，如数据库和实例名称；获取快照的时间；当前高速缓存的大小；负载概览；实例效率百分比；前五个等待事件；等待事件的完整列表；共享池中SQL语句的信息；实例活动统计；表空间和文件I/O；缓冲区统计信息；回退段或还原段统计信息；字典高速缓存统计信息；库高速缓存统计；SGA统计；Init.ora参数的启动值等。

    Statspack内容：

    武器四：孔雀翎 — ash、awr、addm、addr

    是一种暗器，但又不是暗器。悄然，自动，不动声色间，一切皆在握。Oracle在10g开始，推出了一系列自动化、智能化的工具，虽然这些工具在以前或多或少都有相似的影子，但功能的增强、理念的增强，造就了这些以前所不具备的新工具。 

    Active Session History（ASH）-活动会话历史，每秒钟自动从内存中抓取样例的活动会话信息，比如，通过以下语句： 

    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 captured while waiting) 

    通过ashrpt.sql可以产生ash分析报告，发现某个时段的TOP (Top Events/ Top SQL/ Top Sessions/ Top Objects/Files/Latches)
通过活动会话历史信息，可以追溯到性能问题的根源，找出什么资源在竞争？向下追溯到哪个程序带来了竞争？以及哪个SQL带来了竞争？

    用户可以找出活动会话信息中的TOP，如通过TOP SQL可以进一步发现问题。

    Automatic Workload Repository-自动负载信息库 (AWR)

    • 10g的数据库中内置了工作负载信息库

    • AWR是Oracle10g数据库自动化管理的基础架构

    • 自动捕获工作负载数据

    • 默认情况下，每隔  60 分钟保存一次，或者手动保存 7 天的数据

    • 存储在新SYSAUX 表空间内

    • 服务器自动管理空间要求

    • 自动清除旧数据

    • 存储不同类别的数据：

    • 基本统计，例如物理读取

    • SQL 统计，例如磁盘读取（每个 sql 语句）

    • 量度，例如，物理读取数量/秒

    • 通过awrrpt.sql可以产生与statspack类似的性能差异报告

    Automatic Database Diagnostic Monitor -自动数据库诊断监控(ADDM) ：

    • AWR收集完信息后自动调用，为数据库提供性能诊断分析报告 

    • 分析依赖于AWR收集的性能信息快照，对比两次收集快照的性能差异，提供分析建议 

    • 由Oracle自动调用，也可以手动调用 

    • 可以分析当前的，最近一次收集的，也可以分析之前还存在的AWR快照

    • 对RAC 架构同样适用 

    • 分析结果在数据库的相应字典表中存储，可以通过dbms_advisor包的get_task_report过程来获取已经分析的结果，也可以通过addmrpt.sql脚本对特定的快照进行分析。

    Automatic Database difference Report - AWR数据对比报告(ADDR)：

    •  对AWR报告的一种补充

    • 基于基线的理念，对比比单纯的报告更能够说明问题

    • 比基线更灵活，产生报告时随意选择对比基线

    • 通过awrddrpt.sql可以获取性能异常时间与正常时间段AWR报告的对比值，能够快速发现性能差异，从而定位问题 

    性能调优工具关联图示：

    武器五多情环 — sql tuning advisor/sql access advisor

    多情环似乎是一个情种，谁拥有它似乎都会产生感情，从而对许多江湖中的事看的很淡。在Oracle应用中，谁对性能影响最大，不言而喻，是SQL，准确地说是SQL语句的算法，可以说，80%以上的性能问题都可以通过调整SQL来解决或者缓解，拥有调优SQL性能的能力，基本上可以算作一个DBA高手咯。

    SQL 调整以前的手段包括：检查系统使用情况；查看等待事件；查看数据库分散读取上的等待事件；通过以下方法识别 SQL（难以操作）；识别具有大量数据库分散读取等待事件的会话并跟踪它们，或者在 OEM 中查看最突出的会话；获得解释计划；检查被访问的对象（大小/基数）；查看 SQL 统计信息和/或与对象统计信息相比较 (v$sql) （难以操作）；识别问题；联系打包应用程序的供应商；为供应商提供测试方案；供应商提供补丁/升级；安装在客户的下一个维护周期中的补丁/升级；

    在Oracle 10g以后，我们可以：

    通过查看 ADDM 建议；或者根据链接来运行自动 SQL 调整；还可以接受来自 SQL 调整的 SQL 描述文件建议。

    SQL Tuning & Access Advisors能够对系统中的SQL语句提供优化指导 ，从多个不同的方向为SQL提供优化建议，建议包括了：统计信息的重新收集，创建/删除索引，创建/删除物化视图，是否需要物化视图日志，SQL语句的书写以及固化执行计划的SQL Profiling；通过存储在Oracle内部的SQL Profiling 能够在不改变SQL代码的基础上强制执行计划。


 

    武器六离别钩 —提示(hints)

    Oracle很强大的工具，优化SQL的利器，能够强制SQL的执行算法，确保SQL执行按照我们希望的执行计划。钩，用的好伤人，用不好伤己，hints也如此。非高手者，非思路清晰者，且忌乱用，用不好的话，你会很受伤的。。。

    一些典型的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, [, ] )

 

    武器七：拳头

    没有武器就是有武器，有武器就是没有武器。最后一种武器-拳头，就是对整个体系的全面理解，无形的武器胜于有形的武器，就像太极，没有招数就是最好的招数。

    作为一个DBA，或者更高一些，作为一个架构管理员，能够理解整个业务系统，对数据库、存储、网络、系统、应用软件、业务流程都非常清楚，甚至于对使用者的使用习惯都非常清楚，优化就不再是什么高难度了。天地之大皆装于我胸中，万物皆为我之神兵。如果真有那么一天一切都在你的掌握之中，优化也许会比吃饭更容易一些咯

    最后，他总结道：优化的工具有千千万，找到适合的最关键；精通一、两个工具，比什么工具都“会”使更有用；工具就是工具，最终优化人来定；工具是可以换的，人“才”是换不来的；优化应该在系统中整体贯穿，需要我们用优化工具的时候似乎已经有点晚。