Oracle数据库性能优化方法论

1、Oracle数据库性能优化方法论技术创新，变革未来智慧IT目录性能优化方法论1-1性能优化和资源性能优化工具讲解1-21-3什么是OWIOWI ORACLE WAIT INTERFACE:等待事件 ORACLE 7 :104个ORACLE 8:140个，ORACLE 8I:220 ORACLE9I:400ORACLE10G:800 ORACLE11G:1367 ORACLE12c:OWI性能指标性能指标：响应时间：从发出指令之后到收到结果之间消逝的时间响应时间（Response Time）=工作时间（service Time）+等待时间（wait time） 只要降低工作时间和等待时间，响应时

2、间自然随之降低，并增加用户满意度吞吐量：固定时间之内可以完成的工作量OWI特点OWI ORACLE WAIT INTERFACEOWI是面向问题的OWI是定量的OWI是征兆学的OWI是不断进步完善的OWI 最强有力的表现形式-AWR报告OWI重要体现视图V$SESSIONV$ACTIVE_SESSION_HISTORYV$PROCESSV$TRANSACTIONV$LOCK,V$LOCK_OBJECTV$SQL,V$SQLTEXTV$DATABASEV$DATAFILESelect table_name from dict where table_name like%SESSION%;OWI事

3、件之高速缓冲区什么是高速缓冲区相关等待事件：Latch cache buffer chains 2.Latch cache buffer lru chains3.Buffer busy wait/read by other session 4.Write complete waits5.Free buffer waitsOWI事件之库高速缓冲区1.什么是库高速缓冲区相关等待事件： 1.Latch:shared_pool 2.Latch:library cache3.Library cache lock/library cache pinOWI事件之行高速缓冲区1.什么是行高速缓冲区相关等待事件

4、： 1.Row cache lock 2.enq:SQ-contention 3.DFS lock handleOWI事件事务上的等待事务运行过程中可能的等待：1.enq:TM-contention 2.enq:TX-row lock contention 3.enq:TX-allocate ITL Entry4.enq:TX-index contentionOWI事件段上的等待段在数据库运行过程中可能的等待：1.enq:HW-contention 2.enq:ST-contention 3.enq:TT-contention4.enq:US-contentionOWI事件IO上的等待体现在I

5、O上的等待： 1.db file scattered read 2.db file sequential read 3.Direct path read 4.Direct path write5.db file parallel write 6.Control file parallel writeOWI事件在重做缓冲区上的等待体现在REDO上的等待1.latch:redo writing,latch:redo allocation,latch:redo copy 2.Log file sync3.Log file parallel write 4.Log buffer space5.Log

6、file switch completion,log file switch,checkpoint incompleteOWI事件在网络上的等待体现在REDO上的等待1.SQL*Net message from/to client 1.SQL*Net more data from/to client 1.SQL*Net message from/to dblink1.SQL*Net more data from/to dblink性能优化和资源目录1-2性能优化工具讲解1-1性能优化方法论1-3性能优化方法论资源ORACLE数据库的性能依赖于数 据库所在的硬件及操作系统能 上，因此，我们在诊断

7、性能问 题的时候，应将操作系统资源 指标作为整体性能指标的一部 分。定位影响性能的主要资源：查看CPU资源查看内存资源查看IO资源CPU资源内存资源I/O资源性能优化方法论-CPUCPU资源:CPU资源是否紧张我们可以通过检查CPU的利用率及等待运行的进程数来了解，一般来 说CPU 的运算速度主要受主频和高低缓存大小的影响。OLAP系统由于进程数量少，所以其性能和CPU的频率关系较大。OLTP系统由于进程数量多，所以其性能和CPU的数量关系较大。Oracle会根据系统的CPU数量自动调节参数（CPU_COUNT）和进程数量（LMS进程等）。提示：CPU使用率不是越低越好，我们需要做到的是，在不

8、出现资源阻塞的情况下，充分发挥CPU资源的能力性能优化方法论-CPUCPU资源紧张的征兆：操作系统IDLE很低，等待队列很高可以通过vmstat,top/topas等工具观察性能优化方法论-CPU资源紧张原因SQL执行计划异常,大量全表扫描latch（内存锁）或者mutex争用高并发的SQL解析CPU硬件资源不足bug性能优化方法论内存内存资源操作系统内存消耗主要用 于数据库（SGA）和进程 消耗（PGA）,为计算型内 存资源。性能优化方法论内存内存不足的症状系统产生大量交换，进一步可能导致本地硬盘100% 繁忙严重情况下，会导致系统无法响应（登录）。在RAC中，由于进程长时间得不到内存派生，

9、则容易引起脑裂（Brain split）性能优化方法论内存内存不足常见的原因SGA设置过大操作系统参数设置不合理，如AIX 5L的maxperm%大量的连接进程PGA异常增长RMAN备份内存硬件资源不足bugAIX 5L的maxperm参数设置性能优化方法论-IO存储优化吞吐量和IOPS是考察存储性能的两个主要指标。传统存储的最终瓶颈在于磁盘寻道时间，转速越快，磁盘数量越多，存储性能越高。固态硬盘是很重要的里程碑，是解决磁盘瓶颈的重要技术。性能优化方法论-IO存储异常症状硬件错误，如存储不可写。这类错误往往会在警告日志错报错。存储性能缓慢，该类错误需要DBA预估值，相对比较难发现。性能优化方法

10、论-IO导致性能的原因分析SQL执行计划异常，如大量的全表扫描。DB CACHE配置不合理（过小）。异步I/O参数配置不足。存储硬件配置不足。条带设计不合理。访问热点。BUG。硬件错误磁盘队列深度不足硬件本身如转速较低性能优化方法论-IO木桶原理：“木桶效应”指的是一个由长短 不同的木板组成的木桶，决定其 水容量的大小的并非木桶中最长 的一块木板或者所有木板的平均 值，而是取决于最短的那块木板性能优化工具讲解性能优化和资源目录1-31-1性能优化方法论1-2性能优化方法论工具自动化性能优化工具自动化性能优化是一个趋势。但是Oracle的建议只能当做一个工具。在越来越自动化的今天，对DBA要求其

11、实更高了。性能优化方法论工具性能优化的三大利器AWR(Automatic Workload Repository)AWR是Oracle 10g中的一个新特性，类似于10g以前的statspack。不过在使用上 要比statspack简单，提供的性能指标要比statspack多很多，能更好的帮助DBA来发现数据库的性能瓶颈ASH (Active Session History)ASH以V$SESSION为基础，每秒采样一次，记录活动会话等待的事件。不活动的会话 不会采样，采样工作由新引入的后台进程MMNL来完成$ORACLE_HOME/rdbms/admin/rdbms/ashrpt.sqlAD

12、DM (Automatic Database Diagnostic Monitor AWR)是Oracle内部的一个顾问系统，能够自动的完成最数据库的一些优化的建议，给出SQL的优化，索引的创建，统计量的收集等建议什么是AWRAWR (Automatic Workload Repository)一堆历史性能数据，放在 SYSAUX 表空间上， AWR 和 SYSAUX 都是 10g 出 现的，是 Oracle 调优的关键特性；大约 1999 年左右开始开发，已经有 15 年历史默认快照间隔 1 小时， 10g 保存 7 天、 11g 保存 8 天; 可以通过DBMS_WORKLOAD_REPO

13、SITORY.MODIFY_SNAPSHOT_SETTINGS 修改AWR 程序核心是 dbms_workload_repository 包 运行脚本：?/rdbms/admin/awrrpt 本实例?/rdbms/admin/awrrpti RAC 中选择实例号手动执行一个快照：exec dbms_workload_repository.create_snapshot;性能优化方法论AWR详解AWR使用方法生成报告命令：SQL?/rdbms/admin/awrrpt.sql-普通AWR报告 SQL?/rdbms/admin/awrgrpt.sql-采集RAC集群报告 SQL?/rdbms/a

14、dmin/awrrpti.sql-采集RAC二节点报告报告汇总信息，涵盖最基本也是最直观的AWR新能信息， 包括内存配置，参数指标，数据库命中率，等待事件等高负载SQL，按CPU 处理时间，逻辑读，物理读，执行次 数，解析次数，高版本等多个方面展现数据库高负载SQL数据库指标统计，涵盖IO，内存advisory，等待事件，undo统计，latch 统计参数文件内容，包括各种系统参数，隐含参数，event等的设置性能优化方法论AWR详解AWR使用方法性能优化方法论AWRLoad Profile部分指标定义redo size单位 bytes， redo size 可以用来估量 update/ins

15、ert/delete 的频率，大的 redo size 往往对 lgwr 写日志，和 arch 归档造成 I/O 压力， Per Transaction 可以用来分 辨是 大量小事务， 还是少量大事务。如上例每秒 redo 约 1MB ，每个事务 800 字节，符合 OLTP 特征LogicalRead单位 次数*块数，逻辑读耗CPU，主频和 CPU 核数都很重要，逻辑读高则 DB CPU 往往高，也往往可以看到 latch: cache buffer chains 等待。 大量 OLTP 系统 (例如 siebel)可以高达几十乃至上百 GbytesBlock changes单位 次数*块数

16、，描绘数据变化频率Physical Read单位次数*块数，物理读消耗 IO 读，体现在 IOPS 和吞吐量等不同纬度上；但 减少物理读可能意味着消耗更多CPU。好的存储每秒物理读能力达到几GB， 例如Exadata。 这个physical read 包含了 physical reads cache 和 physical reads direct性能优化方法论AWRLoad Profile部分指标定义Physicalwrites单位 次数*块数，主要是 DBWR 写 datafile，也有 direct path write。 dbwr长期写出慢会导致定期 log file switch(ch

17、eckpoint no complete) 检查点无法完成的前台等待。 这个 physical write包含了 physical writes direct +physical writes from cacheUser Calls单位次数，用户调用数， more details from internalParses解析次数，包括软解析+硬解析，软解析优化得不好，则夸张地说几乎等于每秒 SQL 执行次数。 即执行解析比 1:1，而我们希望的是 解析一次 到处运行哦！Logons登陆次数， logon storm 登陆风暴，结合 AUDIT 审计数据一起看。短连接 的附带效应是游标缓存无用E

18、xecutes执行次数，反应执行频率性能优化方法论AWRLoad Profile部分指标定义Rollback回滚次数， 反应回滚频率， 但是这个指标不太精确，参考而已，别太当 真Transactions每秒事务数，是数据库层的 TPS，可以看做压力测试或比对性能时的一个指标，孤立看无意义% Blocks changed per Read每次逻辑读导致数据块变化的比率；如果redo size, =block changes =pct of blocks changed perread三个指标都很高，则说明系统正执行大量 insert/update/delete;pct of blocks cha

19、nged per read = (block changes ) /( logical reads)Load Profile,负载指标 在本环节提供了2个维度per second 和per transactionper Second:主要是把快照内的delta值除以快照时间的秒数，是我们审视数据的主 要维度，任何性能数据脱离了时间模型则毫无意义。per transaction : 基于事务的维度， 与 per second 相比是把除数从时间的秒数改 为了该段时间内的事务数性能优化方法论AWR命中率部分：上述所有指标的目标均为 100%，即越大越好，在少数 bug 情况下可能超过 100%或者

20、为负值Buffer Nowait % 会话申请一个 buffer(兼容模式)不等待的次数比例。 需要访问 buffer 时立即可以访问的比率，不兼容的情况在9i 中是buffer busy waits，从 10g 以 后 buffer busy waits 分离为 buffer busy wait 和 readby other session2 个等待事件buffer HIT%: 经典的经典，高速缓存命中率，反应物理读和缓存命中间的纠结， 但这个指标即便 99% 也不能说明物理读等待少了性能优化方法论AWRRedo nowait%: 会话在生成redo entry时不用等待的比例，redo 相

21、关的资源争用例如redo space request 争用可能造成生成redo 时需求等待。此项数据来源于v$sysstat 中的(redo log space requests/redo entries)，一般来说10g 以后不太用关注log_buffer 参数的大小，需要关 注是否有十分频繁的 log switch ，过小的 redo logfilesize 如果配合较大的 SGA 和频繁的 commit 提交都可能造成该问题。 考虑增到 redo logfile 的尺寸 : 14G 每个，710 组都 是合适的。同时考虑优化 redo logfile 和 datafile 的 I/OIn

22、-memory Sort%:这个指标因为它不计算 workarea 中所有的操作类型，所以现在越来越鸡 肋了，纯粹在内存中完成的排序比例。Library Hit%: library cache 命中率，申请一个library cache object 例如一个 SQL cursor 时，其已经在library cache 中的比例Soft Parse: 软解析比例，无需多说的经典指标Execute to Parse% 指标反映了执行解析比 其公式为 1-(parse/execute) , 目标为 100% 及接近于只执行而不解析性能优化方法论AWR命中率部分：Latch Hit%: willi

23、ng-to-wait latch 闩申请不要等待的比例。Parse CPU To Parse Elapsd:该指标反映了 快照内解析 CPU 时间和总的解析时间的比 值(Parse CPU Time/ Parse Elapsed Time)； 若该指标水平很低，那么说明在整个解 析过程中实际在 CPU 上运算的时间是很短的，而主要的解析时间都耗费在各种其 他非空闲的等待事件上了%Non-Parse CPU 非解析cpu 比例，公式为 (DB CPU Parse CPU)/DB CPU， 若大多 数 CPU 都用在解析上了，则资源存在没有合理利用现象性能优化方法论AWRTOP等待部分：性能优化方

24、法论AWR时间模型部分：parse time elapsed、 hard parse elapsed time 结合 起来看解析是否是主要矛盾，若是则重点是软解 析还是硬解析sequence load elapsed time sequence 序列争用是否是问题焦点PL/SQL compilation elapsed time PL/SQL 对象编译 的耗时注意PL/SQL execution elapsed time 纯耗费在 PL/SQL 解释器上的时间。不包括花在执行和解析 其包含SQL 上的时间connection management call elapsed time 建立数据 库 session 连接和断开的耗时failed parse elapsed time 解析失败，例如由于ORA-4031hard parse (sharing criteria)