数据库性能监控之问题发现及解决办法

Oracle数据库监控主讲：kevin.zhang问题发现及解决方法性能监控的必要性数据库常见问题分析调优工具的历史变迁诊断的六种方法监控系统设计问题及讨论讨论点监控的必要性是否稳定？实时状态？重复故障？是否扩展？是否安全？DBA工作现状故障无影无踪，问题得不到根本解决被动处理被动的等待故障的发生，然后解决问题扩展无评估业务增长，数据库出现瓶颈，但缺乏扩展评估的依据重复工作处理重复而简单的故障，时间总不够用束手无措故障无影无踪，问题得不到根本解决数据库常见问题分析系统问题程序问题数据库问题•CPU瓶颈•vmstat-n3•sar-u210•sar-q210•较小的内存•sga_target•pga_aggregate_target•Memory_target（11g）•I/0吞吐能力•iostat•sar–d210•vmstat-waI/O等待严重系统问题•应用程序问题•频繁的建立连接•大量的SQL软，硬解析•session_cached_cursors避免软解析•Valuebind避免硬解析•并发连接问题•过多并发操作，长时间占用共享资源，导致其他会话无法得到需要的资源，锁，缓存而处于等待状，造成应用的无响应现象程序问题•数据库配置问题•无常，短暂的性能问题•数据库性能日益下降•效率低或高负载的SQL语句•SQL优化后出现不可意料的性能下降数据库问题调优工具的历史变迁起步阶段（V5）–Debugcode小有建树（V6）–Counters/Ratio–BSTAT/ESTAT–SQL*Trace渐进式发展（V7）–出现了WaitEvent–counters向timers的变迁快速发展（8i）–扩大数据搜集范围–STATPACK逐步完善（9i）–更精细的收集-Sessiontuningusing10046SQLtraces–更加全面的STATSPACK–智能化、自动化开始初现日渐成熟（10g）–自动化收集–更加广泛的收集–保留一段时间的历史–提供了：ASH,AWR,ADDM,EM等功能调用–形成了越来越完善的性能优化诊断工具诊断的六种方法动态性能视图--性能诊断的基础数据来源–v$sysstatv$session–v$sessstatv$sgastat–v$filestatv$undostat–v$waitstatv$lock–v$latchv$sql–v$librarycachev$processes–v$session_longopsv$session_wait–……案例-1：利用的动态性能视图发现占CPU高的数据库用户SQL:SELECTn.username,s.sid,s.value,FROMv$sesstats,v$statnamet,v$sessionnWHEREs.statistic#=t.statistic#ANDn.sid=s.sidANDt.name=‘CPUusedbythissession’ORDERBYs.valueDESC;案例-2：数据库表空间监控SQL：见备注结果：TABLESPACE_NAMESUM_SPACE(M)SUM_BLOCKSUSED_SPACE(M)USED_RATE(%)FREE_SPACE(M)FREE_RATE(%)-----------------------------------------------------------------------------------------------------------------------------------UNDOTBS165832037.557.6927.542.31USERS1012808.8188.11.1911.9SYSTEM6908832068599.2850.72SYSAUX618.6979192586.8894.8631.815.14TEMP5469125410000等待事件—性能优化的核心–v$system_event/v$session_event/v$session_wait–竞争即等待–从v$system_event中发现系统问题–从v$session_event中发现会话问题–从v$session_wait的参数中找到竞争对象主要等待事件•缓冲区忙等待•dbfilescatteredread•dbfilesequentialread•freebufferwaits•latchfree•eng:TX-rowblockcontention•缓冲区忙等待•常见原因是对属于表和索引的数据块的争用，以及对段头的争用•也可能发生在回退段和回退段块的争用•SQL–定位争用发生的位置SELECTclass，countFROMv$waitstatWHEREcount0ORDERBYcountDESC；•dbfilescatteredread•全表扫描或索引快速扫描•V$FILESTAT•phyrds•Phyblkrd•dbfilesequentialread•索引读或者等待物理I/O调用返回时•在统计数据极大的情况下，结合磁盘I/O分析•freebufferwaits•数据写入进程跟不上对缓冲区高速缓存的请求•诊断•SGA•db_writer_processes•latchfreewaitevent•latch保护SGA中共享数据结构•sharedpoollatch•cachebuffersLRUchainSTATPACK•快速定位•全面分析•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=TrueSTATPACK内容Oracle10g新诊断工具•ASH•活动会话历史记录•v$active_session_history•AWR•自动负荷信息库•dba_hist_*•ADDM•以AWR为基础，输出对性能问题的描述及建议的解决方法•ADDR•ActiveSessionHistory–每秒钟自动从内存中获取的活动会话信息，不保存–通过ashrpt.sql可以产生ash分析报告，发现某个时段的TOP值(TopEvents/TopSQL/TopSessions/TopObjects/Files/Latches)–SQL:找到157会话当前执行的SQL语句selecta.sql_textfromv$sqlwheresql_idin(selectsql_idfromv$active_session_historywheresession_id=157);查找数据库的瞬间问题示例--ASH报告示例--ASH报告continue示例--ASH报告continue•AutomaticWorkloadRepository--自动负荷信息库–AWR是Oracle10g及以后版本数据库自动化管理的基础架构–自动捕获工作负载数据•默认情况下，每隔60分钟保存一次，或者手动保存7天的数据•dba_hist_*–存储在新SYSAUX表空间内–服务器自动管理空间要求•自动清除旧数据–存储不同类别的数据：•基本统计，例如物理读取•SQL统计，例如磁盘读取（每个sql语句）•量度，例如，物理读取数量/秒–通过awrrpt.sql可以产生与statspack类似的性能差异报告示例--AWR报告示例--AWR报告continue•AutomaticDatabaseDiagnosticMonitorSQLAdvisorHigh-loadSQLIO/CPUissuesRACissuesSnapshotsinAutomaticWorkloadRepositorySystemResourceAdviceNetwork+DBconfigAdviceSelf-DiagnosticEngineinsideDB•ADDM诊断的关键问题–高代价的SQL语句–I/O性能问题–锁定和并发问题–额外的解析–资源瓶颈–过少的内存分配–连接管理问题，如频繁登陆/注销活动•ADDM分析结果–Statistics_level（typical或者all）–dbms_advisor.get_task_reportSELECTdbms_advisor.get_task_report(task_name,’TEXT’,’ALL’)FROMdba_advisor_tasksWHEREtask_id=(SELECTmax(t.task_id)FROMdba_advisor_taskst,dba_advisor_loglWHEREt.task_id=l.task_idANDt.advisor_name=‘ADDM’ANDl.status=‘COMPLETED’–$ORACLE_HOME/rdbms/admin/addmrpt.sql示例--ADDM报告示例--ADDM报告continue•AutomaticDatabasedifferenceReport–基于基线的理念，对比比单纯的报告更能够说明问题–比基线更灵活，产生报告时随意选择对比基线–通过awrddrpt.sql可以获取性能异常时间与正常时间段AWR报告的对比值，能够快速发现性能差异，从而定位问题示例--ADDR报告•提示(hints)–Oracle很强大的工具，优化SQL的利器，能够强制SQL的执行算法，确保SQL执行按照我们希望的执行计划。–典型的hits•FIRST_ROWS,ALL_ROWS,RULE•FULL(tab)•INDEX(tabindex)•NO_INDEX(tabindex)•USE_NL(tab)•USE_MERGE(tab..)•USE_HASH(tab1tab2)•PARALLEL(table,degree[,instances])监控架构主机与数据库性能数据收集层数据分析，建立监控分析层展示层架构图…DBServerMonitorServer辅助服务器MonitorCenter监控数据流收集层AgentShell+Crontab监控数据：系统磁盘状态表空间状态信息系统负载，数据库负载CPU使用率OracleAlert日志OS日志采集…数据传输SSH节点间通信信任分析层邮件报警sendmail短信报警飞信API数据分析Shell脚本标准接口架构统一的数据结构统一的接口程序短信报警邮件报警文件传输文件同步rsync+crontabscc用户程序用户程序标准接口架构图示用户程序接口程序或配置文件接口程序或配置文件接口程序MonitorServer接口程序或配置文件…展示层公共接口MobilePhoneE-mailWeb展示Nagio系统服务状态开发监控插件Cacti走势图NagioCacti淘宝数据库监控平台SQL维度分析Q&A谢谢！