Oracle数据库性能优化与运维最佳实践

Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|Confidential–OracleInternalOracle数据库性能优化与运维最佳实践Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|运维•优化方法论•SQL优化•实例优化Confidential–OracleInternal优化•定制运维工具Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|Confidential–OracleInternal优化方法论Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|Confidential–OracleInternal系统运行缓慢可能的原因•数据库•应用•主机配置•网络环境•存储配置与状态•部署方式Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|谁进行优化Confidential–OracleInternal•数据库•应用•主机配置•网络环境•存储配置与状态•部署方式•数据库管理员•应用架构师/开发人员•系统管理员•网络管理员•存储管理员•系统架构师Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|Confidential–OracleInternal•性能优化范围：–应用程序：•SQL语句性能•更改管理–实例优化：•内存•数据库结构•实例配置–其他系统交互：•存储I/O•网络DBA与应用相关人员共享DBA与其他管理员共享进行优化的范围DBACopyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|有效的优化目标Confidential–OracleInternal•具备下列特征：–具体化–可量化–可实现Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|Confidential–OracleInternal–自上而下优化以下内容：•在优化应用程序代码之前先优化设计•在优化实例之前先优化代码–对可以带来最大潜在好处的方面进行优化，并确定：•最长的等待•最慢的SQL–达到目标时停止优化。优化的指导思想Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|优化过程Confidential–OracleInternal•优化的过程都是相同的：1.定义问题并陈述目标。2.收集当前性能统计信息。3.考虑一些常见的性能错误。4.制定试用解决方案。5.实施并度量更改。6.决定：“该解决方案是否达到目标？”•否？转到步骤3并重复相关过程。•是？完成。Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|Confidential–OracleInternal–监视和诊断•使用基于AWR的工具确定问题–SQL优化–实例优化优化的实践方法Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|定义问题用户DBA监视报表和文件数据库实例反馈Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|Confidential–OracleInternal数据库时间Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|限制范围•问题出在何处？–应用程序(SQL)–实例Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|设置优先级•选择影响最大的问题：–通过完成工作（CPU时间或服务时间）与等待工作所用时间（等待时间）的对比来分析系统性能。–确定占用时间最长的组件。–细化以优化该组件。Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|Confidential–OracleInternal使用基于AWR的工具确定问题Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|https://主机名:1158/em访问数据库EM主页Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|OracleEnterpriseManager性能页Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|执行ORACLE_HOME/rdbms/admin/awrrpt.sql在EM中生成AWR报表Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|生成的AWR报表Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|–第一部分提供•概览•最重要的诊断阅读AWR报表Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|–等待事件提供由于各种原因不得不等待或必须等待的会话的有关信息。阅读AWR报表Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|V$SERVICE_WAIT_CLASSV$EVENT_NAME调度程序集群配置提交应用程序管理网络用户I/O并发系统I/O其它空闲后台进程I/O前台进程I/ODBA命令应当极少见不适当的数据库/实例配置资源管理器RAC资源用户应用程序代码非活动会话网络消息传递logfilesync内部数据库资源V$SESSION_WAIT_CLASSV$SYSTEM_WAIT_CLASS等待类等待类Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|取决于缓冲区类型。PK索引和序列出现问题时的V$SESSION_WAIT（块）V$SYSSTAT视图中的提交数和回退数，磁盘数I/O速度慢，未批量提交过度提交、I/OLogfilesyncV$SYSSTAT重做缓冲区分配重试次数，磁盘缓冲区小，I/O速度慢日志缓冲区I/OlogbufferspaceV$SQLAREA分析调用，子游标SQL分析/共享闩锁LibrarycachewaitsV$ENQUEUE_STAT取决于入队类型锁Enqueuewaits(enq:)V$SQLAREA磁盘读取数。V$FILESTAT读取时间SQL优化不当，I/O系统速度慢I/O、SQL优化dbfilescatteredread、dbfilesequentialread使用OS统计信息时的入时间缓冲区高速缓存统计信息DBWR速度慢缓冲区高速缓存、DBWRI/Ofreebufferwaits缓冲区高速缓存、DBWRbufferbusywaits检查可能的原因区域等待事件常见等待事件Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|Confidential–OracleInternalAWR案例分析Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|AWR案例分析Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|时间模型统计信息AWR案例分析Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|按用时排序的SQL按CPU时间排序的SQL按执行数排序的SQL按缓冲区获取数排序的SQL顶级SQL报表Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|SQL优化Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|关闭打开提取绑定分析执行SQL语句处理阶段Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|–分析阶段：•始终：–检查语法–检查语义和权限•软分析：–在共享池中搜索语句•硬分析：–合并视图定义和子查询–确定执行计划分析阶段Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|SQLAREA游标上下文区域SELECT语句2游标上下文区域SELECT语句1共享池SELECT语句2SELECT语句1SELECT语句1SQL存储Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|硬解析的原因–内存不足，可以缓存的SQL有限–不一致的SQL太多，但执行计划一样•Selectsalfromempwhereename=‘Smith’•Selectsalfromempwhereename=‘Jack’Stringv_id=‘Smith’;Stringv_sql=‘selectsalfromempwhereename=?';//嵌入绑定变量stmt=con.prepareStatement(v_sql);stmt.setString(1,v_id);//为绑定变量赋值stmt.executeQuery();Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|SQL语句处理阶段：绑定–绑定阶段：•检查语句的绑定变量•为绑定变量分配或重新分配值–下列情况下，绑定变量会影响性能：•通过使用共享游标来减少分析。Cursor_sharing参数有3个值可以设置：1)、EXACT：通常来说，exact值是Oracle推荐的，也是默认的，它要求SQL语句在完全相同时才会重用，否则会被重新执行硬解析操作。2)、SIMILAR：similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时，才会被重新分析，否则将重用SQL。3)、FORCE：force是在任何情况下，无条件重用SQL。Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|SQL语句处理阶段：执行和提取–执行阶段：•执行SQL语句•针对数据操纵语言(DML)语句执行所需的I/O和排序–提取阶段：•针对查询检索行•视需要对查询进行排序•使用数组提取机制Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|处理DML语句数据库数据文件控制文件重做日志文件UPDATEemployees...用户进程SGA数据库缓冲区高速缓存共享池重做日志缓冲区服务器进程3142Copyright©2017,Oracleand/oritsaffiliates.