1、数据库性能诊断的七种武器,ITPUB：Ora-600 liyinan,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,主要议题,性能优化面临的挑战调优工具的变迁诊断工具中的七种武器Q & A,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,性能优化面临的挑战,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,1、架构和业务的设计与变更 2、熟悉各种数据库参数、系统参数 3、应用逻辑与SQL代码实现 4、选择合适的存储方式存储盘阵、存储模式、存储参数、存储表空间、存储对象等 5、复杂的网络配置 还有更多
2、。 DBA的事情好多哦,为满足业务的运行要求，高性能要求是目前IT系统普遍面临的最棘手问题，尤其是客户面对着目前越来越庞大系统和数据，系统整合、数据大集中似乎成了趋势，而对我们来说，则充满了压力和挑战。,性能优化面临的挑战,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,调优工具的变迁,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Oracle 调优工具的变迁,朦胧之初(v5) Debug code初见端倪(v6) Counters/Ratios BSTAT/ESTAT SQL*Trace有所发展(v7) 出现了 Wait Ev
3、ent counters向timers的变迁,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Oracle 调优工具的变迁,快速进化(8i) 宽广的范围 STATSPACK逐渐完善(9i) 更精细的收集 - Session tuning using 10046 SQL traces 更加全面的STATSPACK 智能化、自动化开始初现日趋完美 (10g) 基于数据库自动化基础的更完美的优化 自动化收集 更加广泛的收集 保留一段时间的历史 提供了：ASH, AWR, ADDM, EM等功能调用 形成了越来越完善的性能优化诊断工具,李轶楠 Mail:ora- 133
4、31192030 技术服务人生，学识只为轻松,诊断工具中的七种武器,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,诊断工具中的七种武器,碧玉刀 动态性能视图：刀是最大众化的武器，小到刮刀、折刀、剃刀、西瓜刀、切菜刀、剔骨刀，大到柳叶刀、圆月弯刀、武士刀、青龙偃月刀。不论大小长短、不论古今中外，刀是最常见的武器。不过再普通的刀，到了高手的手中，也会成为神兵利器，刀如此，Oracle的动态性能视图也如此，无论各种性能问题，根源皆可寻究于此。V$SYSSTAT V$SESSION V$SESSTAT V$SGASTAT V$FILESTAT V$UNDOSTAT V
5、$ROLLSTAT V$WAITSTAT V$LOCK V$LATCH V$SQL V$SQLAREA V$SQLTEXT V$PROCESS V$LIBRARYCACHE V$ROWCACHE ,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,动态性能视图,大处着眼，小处着手 不是每个问题，都那么清楚的摆在我们面前，细致的察看，仔细的分析，利器才是利器 select name,value from v$sysstat where name like %SQL%; NAME VALUE - - bytes sent via SQL*Net to client 2
6、.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,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,诊断工具中的七种武器,长生剑 等待事件：剑，轻灵、快速、灵敏，甚至于诡异。谈笑间，轻松快意时寻出敌人弱点，以闪电般的速度刺
7、入敌人最虚弱的部位，一击破敌。性能优化的核心是什么，快速准确的定位，不需要华丽的显示，不需要冗长的信息，需要的仅仅是一个准确的定位，等待事件就是此中利器。,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,等待事件,v$system_event / v$session_event / v$session_wait 竞争即等待 寻找第一眼的感觉 从v$system_event中发现系统问题 从v$session_event中发现会话问题 从v$session_wait的参数中找到竞争对象,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松
8、,等待事件,熟悉各种主要的等待事件,快速定位问题所在 Top 5 Timed Events 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 CPU time 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 fi
9、le sync 28,880 171 2.1 Commit wait for a undo record等待与回滚段的大量回滚有关，一般是出现了大事务回退造成 select sid, event, wait_class from v$session_wait s where s.event not like %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 s
10、elect pid, state, undoblocksdone from v$fast_start_servers; PID STATE UNDOBLOCKSDONE - - - 133 RECOVERING 7124 ,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,等待事件,buffer busy waits(数据高速缓存忙等待) db file scattered read(数据文件离散读取) db file sequential read(数据文件顺序读) direct path read(直接路径读取) direct path write(直接路径写
11、出) enqueue(队列) free buffer waits(空闲缓冲区等待) latch free(锁存器空闲) log buffer space(日志缓冲区空间分配) log file switch(archiving needed) log file switch(checkpoint incomplete) log file sync(日志文件同步),李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,诊断工具中的七种武器,霸王枪 statspack：枪中之霸王，胆气之结晶。枪具有剑的轻灵，又有棍的霸道，不论是快速定位，还是全面分析，都是statspac
12、k所能胜任的。,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,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,
13、李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Statspack的输出,包含的信息： 数据库和实例名称 获取快照的时间 当前高速缓存的大小 负载概览 实例效率百分比 前五个等待事件 等待事件的完整列表 共享池中SQL语句的信息 实例活动统计 表空间和文件I/O 缓冲区统计信息 回退段或还原段统计信息 栓锁活动 字典高速缓存统计信息 库高速缓存统计 SGA统计 Init.ora参数的启动值,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Statspack内容,李轶楠 Mail:ora- 13331192030 技术服务人生，学识
14、只为轻松,Statspack内容,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Statspack内容,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Statspack内容,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,诊断工具中的七种武器,孔雀翎 ash、awr、addm、addr：是一种暗器，但又不是暗器。悄然，自动，不动声色间，一切皆在握。Oracle在10g开始，推出了一系列自动化、智能化的工具，虽然这些工具在以前或多或少都有相似的影子，但功能的增强、理念的增强，造就了这些以前所不具备
15、的新工具。,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Active Session History-活动会话历史,查找数据库的瞬间问题,每秒钟自动从内存中抓取样例的活动会话信息 可以从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 S
16、QL 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) 可以通过活动会话信息追溯到性能问题的根源,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,ASH报告,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,ASH报
17、告,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,ASH报告,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,活动会话信息,什么资源在竞争？ 向下追溯到哪个程序带来了竞争？以及哪个SQL带来了竞争？,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,活动会话信息中的TOP,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,通过TOP SQL进一步发现问题,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,10g的数据库中内置了工作负载信息库
18、 AWR是Oracle10g数据库自动化管理的基础架构 自动捕获工作负载数据 默认情况下，每隔 60 分钟保存一次，或者手动保存 7 天的数据 存储在新SYSAUX 表空间内 服务器自动管理空间要求 自动清除旧数据 存储不同类别的数据： 基本统计，例如物理读取 SQL 统计，例如磁盘读取（每个 sql 语句） 量度，例如，物理读取数量/秒 通过awrrpt.sql可以产生与statspack类似的性能差异报告,Automatic Workload Repository- 自动负载信息库 (AWR),李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,AWR报告,李轶
19、楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,AWR报告,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Automatic Database Diagnostic Monitor - 自动数据库诊断监控(ADDM),AWR收集完信息后自动调用，为数据库提供性能诊断分析报告 分析依赖于AWR收集的性能信息快照，对比两次收集快照的性能差异，提供分析建议 由Oracle自动调用，也可以手动调用 可以分析当前的，最近一次收集的，也可以分析之前还存在的AWR快照 对RAC 架构同样适用 分析结果在数据库的相应字典表中存储，可以通过dbms_
20、advisor包的get_task_report过程来获取已经分析的结果，也可以通过addmrpt.sql脚本对特定的快照进行分析,SQL Advisor,High-load SQL,IO / CPU issues,RAC issues,Automatic Diagnostic Engine,Snapshots in Automatic Workload Repository,Self-Diagnostic Engine inside DB,System Resource Advice,Network + DB config Advice,李轶楠 Mail:ora- 13331192030 技
21、术服务人生，学识只为轻松,性能诊断：以前与现在的情况,以前 检查系统利用率 查看等待事件 观察latch争用情况 查看共享池和库缓存latch的等待情况 检查 v$sysstat 查看 “parse time elapsed” “parse time cpu” 以及硬分析数量超过正常的情况 通过以下方法识别 SQL 识别具有很多硬解析的会话并跟踪它们，或者 在 v$sql 中检查具有相同散列计划的多个语句 检查所访问的对象并查看 SQL 通过观察 SQL 包含文字的情况来识别“硬解析”问题 支持游标共享,Oracle10g1. 查看 ADDM 建议2. ADDM 建议使用 cursor_sha
22、ring,情况：硬解析问题,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,ADDM分析报告,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,ADDM分析报告细节,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Automatic Database difference Report - AWR数据对比报告(ADDR),对AWR报告的一种补充 基于基线的理念，对比比单纯的报告更能够说明问题 比基线更灵活，产生报告时随意选择对比基线 通过awrddrpt.sql可以获取性能异常时间与正常时间段AWR报
23、告的对比值，能够快速发现性能差异，从而定位问题,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,AWR Compare Period Report,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,AWR Compare Period Report: Configuration,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,AWR Compare Period Report: Report Details,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,孔雀翎在手，优
24、化就是这么easy,性能信息和负载量的捕捉 系统统计信息,等待事件,SQL负载等 性能问题分析 与正常阶段比，哪种资源消耗明显？ 与正常阶段比，哪些语句出现了明显问题？ 哪种操作带来了问题？ 什么资源上出现了瓶颈？ 瓶颈的原因是什么？ 性能调整方案 多个性能问题，哪个影响更大 每个性能问题，应该怎么解决 如果不能解决，考虑进一步调用哪个工具进行分析处理,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,诊断工具中的七种武器,多情环 sql tuning advisor/sql access advisor：多情环似乎是一个情种，谁拥有它似乎都会产生感情，从而对许多
25、江湖中的事看的很淡。在Oracle应用中，谁对性能影响最大，不言而喻，是SQL，准确地说是SQL语句的算法，可以说，80%以上的性能问题都可以通过调整SQL来解决或者缓解，拥有调优SQL性能的能力，基本上可以算作一个DBA高手咯。,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,以前 检查系统使用情况 查看等待事件 查看数据库分散读取上的等待事件 通过以下方法识别 SQL（难以操作） 识别具有大量数据库分散读取等待事件的会话并跟踪它们，或者在 OEM 中查看最突出的会话 获得解释计划 检查被访问的对象（大小/基数） 查看 SQL 统计信息和/或与对象统计信息相比
26、较 (v$sql) （难以操作） 识别问题 联系打包应用程序的供应商 为供应商提供测试方案 供应商提供补丁/升级 安装在客户的下一个维护周期中的补丁/升级,Oracle10g 查看 ADDM 建议 根据链接来运行自动 SQL 调整 接受来自 SQL 调整的 SQL 描述文件建议,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,执行计划,执行计划是一系列的优化器用来完成SQL操作的步骤和操作,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,曾经我们如何查看执行计划,通过下面的工具能够看到执行计划 EXPLAIN PLAN V$SQL
27、_PLAN SQL Trace SQL*Plus AUTOTRACE 看到执行计划不是目的，优化与分析仍然靠DBA去努力。,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,SQL调优建议,SQL Tuning & Access Advisors 能够对系统中的SQL语句提供优化指导 从多个不同的方向为SQL提供优化建议 建议包括了：统计信息的重新收集，创建/删除索引，创建/删除物化视图，是否需要物化视图日志，SQL语句的书写以及固化执行计划的SQL Profiling 通过存储在Oracle内部的SQL Profiling 能够在不改变SQL代码的基础上强制执行
28、计划,SQL Profile,Packaged Apps +,Indexes, MVs, Partitions,Well-tuned SQL,Customizable Apps +,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,SQL Tuning Advisor Overview,Add Missing Indexes,Modify SQL Constructs,Create a SQL Profile,Automatic Tuning Optimizer,SQL Structure Analysis,Access Path Analysis,SQL Pro
29、filing,Statistics Analysis,Gather Missing or Stale Statistics,DBA,SQL Tuning Recommendations,SQL Tuning Advisor,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,SQL Tuning Usage Scenarios,SQL Tuning Advisor,ADDM,High-load SQL,Cursor Cache,AWR,SQL Tuning Set (STS),User-defined,Filter / Rank,SQL Sources,Manual
30、 Selection,Automatic Selection,AWR,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,SQL Tuning in Oracle Database 10g End-to-End Workflow,Workload,AWR,one hour,A good end-to-end solution, but manual intervention is required,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Automatic SQL Tuning in Oracle 11g,Its Automa
31、tic!,Workload,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Automatic SQL Tuning,完全自动的SQL优化 自动捕捉高负载的SQL 自动创建SQL Profile，不改变SQL代码自动优化 SQL 不能完全取代DBA，代码的书写还是需要DBA来调整的,Packaged Apps,Custom Apps,Automatic SQL Tuning,SQL Profiles,Nightly,Well-tuned SQL,Automatic implement,Manually implement,SQL Analysis,Report,
32、李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,SQL Access Advisor Overview,Partitions (11g only),MV and MV Logs,Bit-map indexes,Automatic Tuning Optimizer,Access Path Analysis,B*-tree indexes,DBA,Recommendations,SQL Access Advisor,除了像在 Oracle 数据库 10g 中一样可以分析索引、物化视图等，Oracle数据库 11g 中的 SQL Access Advisor 还可以分
33、析表和查询以提供可能的分区策略 这在设计最佳模式时可以提供很大帮助,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,诊断工具中的七种武器,离别钩 提示(hints)，Oracle很强大的工具，优化SQL的利器，能够强制SQL的执行算法，确保SQL执行按照我们希望的执行计划。钩，用的好伤人，用不好伤己，hints也如此。非高手者，非思路清晰者，且忌乱用，用不好的话，你会很受伤的。,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,一些典型的hints,首选用于测试执行计划 其次可用于在需求确定时，固化执行计划 常用的hints： FI
34、RST_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, , ) 它很锋利，小心“伤人”,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,诊断工具中的七种武器,拳头：没有武器就是有武器，有武器就是没有武器。最后一种武器-拳头，就是对整个体系的全面理解，无形的武器胜于有形的武器，就像太极，没有招数就是最好的招数。作为一个DBA，或者更高一些，
35、作为一个架构管理员，能够理解整个业务系统，对数据库、存储、网络、系统、应用软件、业务流程都非常清楚，甚至于对使用者的使用习惯都非常清楚，优化就不再是什么高难度了。天地之大皆装于我胸中，万物皆为我之神兵。如果真有那么一天一切都在你的掌握之中，优化也许会比吃饭更容易一些咯,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,结尾,优化的工具有千千万，找到适合的最关键 精通一、两个工具，比什么工具都“会”使更有用 工具就是工具，最终优化人来定 工具是可以换的，人“才”是换不来的 优化应该在系统中整体贯穿，需要我们用优化工具的时候似乎已经有点晚。,李轶楠 Mail:ora- 13331192030 技术服务人生，学识只为轻松,Q & A,感谢参与,
