陈欧做聚美优品网站,网站方案制作,南京seo外包,实体店怎么引流推广oracle性能优化之awr分析 作者#xff1a;bingjava 最近某证券公司系统在业务期间系统运行缓慢#xff0c;初步排查怀疑是数据库存在性能问题#xff0c;因此导出了oracle的awr报告进行分析#xff0c;在此进行记录。 导致系统的性能问题有很多#xff0c;比如内存、cpu占…oracle性能优化之awr分析 作者bingjava 最近某证券公司系统在业务期间系统运行缓慢初步排查怀疑是数据库存在性能问题因此导出了oracle的awr报告进行分析在此进行记录。 导致系统的性能问题有很多比如内存、cpu占用率过高网络延迟、系统存储io瓶颈、还有程序方面的代码逻辑、性能低下的sql语句等等这里主要从awr的角度说明如何通过awr的报告来定位问题。 一、awr报告分析及问题定位 DB Name DB Id Instance Inst num Release RAC Host DB 1527139216 DB 1 10.2.0.5.0 NO p3-DB Snap Id Snap Time Sessions Cursors/Session Begin Snap: 16021 01-Mar-16 10:00:34 213 2.4 End Snap: 16022 01-Mar-16 11:00:36 213 2.3 Elapsed: 60.04 (mins) DB Time: 176.32 (mins) 关键项说明 DB TIME代表了此统计期间的数据库负载是所有前台session花费在database调用上的总和时间包括CPU时间、IO Time、和其他一系列非空闲等待时间。如果 DB Time 接近于 Elapsed Time*cpu 数,表明数据库比较忙,cpu 负载也许比较大。这时很有可能是因为资源争用导致等待事件的结果,可以去 top 5 等待事件分析原因。 Operating System Statistics Statistic Total BUSY_TIME 1,037,128 IDLE_TIME 10,487,927 IOWAIT_TIME 19,061 NICE_TIME 316 SYS_TIME 132,552 USER_TIME 882,792 LOAD 3 RSRC_MGR_CPU_WAIT_TIME 0 VM_IN_BYTES 1,274,466,304 VM_OUT_BYTES 2,174,697,472 PHYSICAL_MEMORY_BYTES 33,712,308,224 NUM_CPUS 32 NUM_CPU_SOCKETS 2 从以上信息可知 单数据库实例非集群部署模式2个物理cpuNUM_CPU_SOCKETS232个逻辑cpuNUM_CPUS32。 cpu利用率为DB Time /(Elapsed* NUM_CPUS)176/(60*32) *100%9.2% cpu的负载处于正常水平。 Load Profile Per Second Per Transaction Redo size: 89,367.47 21,227.40 Logical reads: 105,600.68 25,083.26 Block changes: 458.93 109.01 Physical reads: 27,716.84 6,583.56 Physical writes: 30.80 7.32 User calls: 3,675.70 873.09 Parses: 324.60 77.10 Hard parses: 14.13 3.36 Sorts: 44.47 10.56 Logons: 1.69 0.40 Executes: 340.07 80.78 Transactions: 4.21 % Blocks changed per Read: 0.43 Recursive Call %: 16.91 Rollback per transaction %: 0.09 Rows per Sort: 397.30 Redosize:每秒产生的日志大小(单位字节)可标志数据变更频率,大的redosize往往对lgwr写日志和arch归档造成I/O压力也有可能造成logbuffer堵塞从而产生相关的等待事件。很繁忙的系统中日志生成量可能达到几百k甚至几M。在Top 5 Timed Events中未发现log方面的等待事件说明redo生成的频率属于正常范围。 Logical reads: 从内存中读取数据的次数次数*块数每秒钟逻辑读数据量105,600.688k825m Physical reads:当从内存中未都到数据时则从硬盘上读取数据每秒物理读数据量27,716.84 8k216m Physical reads / Logical reads27,716.84/105,600.6826%,有26%的逻辑读导致了物理io。因此此处的物理io可能是系统的性能瓶颈具体需在后面的 top 5中进行分析。 Instance Efficiency Percentages (Target 100%) Buffer Nowait %: 98.73 Redo NoWait %: 100.00 Buffer Hit %: 73.77 In-memory Sort %: 100.00 Library Hit %: 89.85 Soft Parse %: 95.65 Execute to Parse %: 4.55 Latch Hit %: 96.92 Parse CPU to Parse Elapsd %: 95.60 % Non-Parse CPU: 96.41 buffer hit表示进程从内存中找到数据块的比率,监视这个值是否发生重大变化比这个 值本身更重要。对于一般的 OLTP 系统,通常应在 95%以上。否则应考虑加大 db_cache_size, 但是大量的非选择的索引也会造成该值很高(大量的 db file sequential read)。 Latch HitLatch是一种保护内存结构的锁可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit99%否则意味着Shared Pool latch争用可能由于未共享的SQL或者Library Cache太小可使用绑定变更或调大Shared Pool解决。 Execute to Parse是语句执行与分析的比例如果要SQL重用率高则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。 Parse CPU to Parse Elapsd该指标反映了快照内解析CPU时间和总的解析时间的比值(Parse CPU Time/ Parse Elapsed Time); 若该指标水平很低,那么说明在整个解析过程中 实际在CPU上运算的时间很短,而主要的解析时间都耗费在各种其他非空闲的等待事件上了此值越高越好。 Shared Pool Statistics Begin End Memory Usage %: 56.42 55.58 % SQL with executions1: 54.12 49.23 % Memory for SQL w/exec1: 49.88 48.29 SQL with executions 代表了sql重复执行的比例本报告中是54%是比较低的说明存在sql硬编码的情况同时上面的Execute to Parse也只有4.55%也说明了sql解析的重用率低。 内存利用率为55%左右属于正常情况。 Top 5 Timed Events 业务11:0012:00期间 Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class CPU time 10,028 94.8 db file scattered read 6,943,920 644 0 6.1 User I/O read by other session 4,837,558 578 0 5.5 User I/O CSS initialization 13 65 4,967 .6 Other db file sequential read 512,027 58 0 .6 User I/O 业务15:0016:00期间 Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class CPU time 2,569 95.8 SQL*Net more data to client 1,150,806 233 0 8.7 Network db file scattered read 1,381,500 136 0 5.1 User I/O CSS initialization 13 63 4,878 2.4 Other db file sequential read 42,488 30 1 1.1 User I/O db file scattered read: 表明Oracle内核请求从磁盘读取多个数据块到buffer cache中 这种情况通常显示与全表扫描相关的等待。当数据库进行全表扫时,基于性能的考虑, 数据会分散读入Buffer Cache。如果这个等待事件比较显著,可能说明对于某些全表扫描的表,没有创建索引或者没有创建合适的索引。 read by other session: Oracle 操作的最小单位是块(Block),当对数据块做修改时,其他的会话将被阻止对这个数据块上的数据做修改但是可以以一致性的方式读取这个数据块(from undo)。当前的用户修改完这个数据块后,将会立即释放掉加在这个数据块上的排他锁,这样另一个会话就可以继续修改它,这种加锁的机制叫Latch。当一个会话将数据块都到内存中时其它的会话同时也请求了这个数据块就导致被等待的会话出现read by other session。而当前会话一般是db file scattered read或db file sequential read。 从本次awr报告中都发现db file scattered read、db file sequential read、read by other session这几个事件的等待次数很高因此可以判断当前业务场景存在热点块竞争问题。 SQL*Net more data to client 当服务器端有太多的数据需要发给客户端时,可能会产生此等待事件,也可能由于网络问题导致服务器无法及时地将信息或者处理结果发送给客户端, 同样会产生这个等待。在15:00-16:00业务期间此等待事件相对较高从SQL*Net看并不像应用程序应用程序是JDBC Thin Client可能是第三方的oracle监控程序导致的。 File IO Stats Tablespace Filename Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms) JSZ35_TBS tbs01.dbf 2,635,786 732 0.10 14.88 4,032 1 2,016,907 0.12 JSZ35_TBS tbs02.dbf 2,730,384 758 0.09 12.89 10,420 3 1,679,836 0.12 JSZ35_TBS tbs03.dbf 2,084,937 579 0.08 12.19 9,183 3 1,141,265 0.13 以上数据文件平均每秒被读700多次平均每秒读取的数据块为14块左右。 Tablespace IO Stats Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms) JSZ35_TBS 1,420,317 394 0.11 14.73 9,502 3 113 2.30 Segments by Buffer Busy Waits Owner Tablespace Name Object Name Subobject Name Obj. Type Buffer Busy Waits % of Capture JSZ35 JSZ35_TBS TF_SUBJECTPRICE_TMP TABLE 30 32.26 JSZ35 JSZ35_TBS IND_T_*LOG INDEX 21 22.58 JSZ35 JSZ35_TBS PK_T_**_TMP INDEX 15 16.13 JSZ35 JSZ35_TBS T_***HER CHER_P2016 TABLE PARTITION 9 9.68 JSZ35 JSZ35_TBS IND_T_***HER INDEX 7 其它业务时间段 Owner Tablespace Name Object Name Subobject Name Obj. Type Buffer Busy Waits % of Capture JSZ35 JSZ35_TBS IND_T_*LOG INDEX 60 68.18 JSZ35 JSZ35_TBS IND_T_***SED INDEX 20 22.73 JSZ35 JSZ35_TBS TF_SUBJECTPRICE_TMP TABLE 18 17.65 JSZ35 JSZ35_TBS IND_T_***HER INDEX 7 6.86 Segments by Physical Reads Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total JSZ35 JSZ35_TBS T_***NCE ANCE_P2015 TABLE PARTITION 81,573,441 81.70 JSZ35 JSZ35_TBS T_***NCE ANCE_P2016 TABLE PARTITION 12,884,029 12.90 JSZ35 JSZ35_TBS T_***CE RICE_P2016 TABLE PARTITION 3,471,341 3.48 热点数据块主要是T_***NCE、T_***CE引起。 数据块热点问题io等待的主要对象为 T_***LOG、TF_SUBJECTPRICE_TMP、TS_PROCESSED、TF_SUBJECTPRICE_TMP、T_***NCE、T_***CE 可结合SQL ordered by CPU Time最耗时的sql、SQL ordered by Gets逻辑读最多的sql、SQL ordered by Reads物理读最多的sql来定位具体的sql语句。 二、问题总结及解决方式 本报告期系统的cpu、内存表现正常造成系统性能问题的主要原因为物理读过多产生io等待同时由于相关业务表存在频繁的并发访问现象逻辑读较多且性能较差而导致了数据块竞争问题。逻辑读是消耗cpu的而物理读是消耗io的这也说明了系统的大部分时间都消耗在io等待上所以cpu相对空闲。 优化方案主要包括应用层的优化和oracle数据库的优化 一、应用层的优化目标主要在于降低对数据库的访问频率、合理有效使用索引合理有效使用索引需通过对sql语句的执行计划进行分析和调优 T_***LOG可能存在较频繁的插入数据操作可采用以下方式减少对数据库的提交操作 将此表的单条insert的操作改为批量入库提交的方式比例100条记录入库一次。 T_***_TMP可能存在读写混合的场景需根据业务分析是否有优化的空间。 T_***NCE、T_***CE、T_A***T关于此表的相关访问应该是最需要优化的了需优化的sql语句为比如索引是否合理 关键sql语句SELECT /* LEADING (A3 A2 A1) PQ_DISTRIBUTE (A1, BROADCAST, NONE)USE_NL (A1) FULL (A1) PQ_DISTRIBUTE (A2, BROADCAST, NONE)USE_NL (A2) FULL (A2) FULL (A3) */ A3.FSETCODE, A2.FDATE, A1.FSETNAME, SUM(CASE WHEN A3.FACCTATTR LIKE ??±????% THEN A2.FENDBAL ELSE 0 END ), SUM(CASE WHEN A3.FACCTATTR LIKE ???±??% THEN A2.FENDBAL ELSE 0 END ) FROM T_A***T A3, T_***NCE A2, T_AS**T A1 WHERE A3.FACCTDETAIL1 AND A2.FDATETO_DATE(TO_CHAR(:1), yyyy-mm-dd) AND (A3.FACCTATTR LIKE ??±????% OR A3.FACCTATTR LIKE ???±??) AND A3.FSETCODEA1.FSETCODE AND A3.FSETCODEA2.FSETCODE AND A3.FACCTCODEA2.FACCTCODE GROUP BY A3.FSETCODE, A2.FDATE, A1.FSETNAME select sum(NVL(fbacccredit, 0)) as fje from(select fsetcode, facctcode, fbacccredit from T_***NCE where fsetcode:1 and fdate:2 ) a left join T_A***T b on a.fsetcode b.fsetcode and a.facctcode b.facctcode where b.facctattr like :3 and b.facctdetail1 select a.fdate, a.fsetcode, a.fzqdm, a.fhqssj, a.fhqpjj, a.fbjsj, a.fsjsj, a.fzdcj, a.fjyzt, a.fjysc, a.fzqlb, a.fsyqx, a.fdatasource, a.fyqfyfx, a.fgzjgly, a.ftpdate from T_***CE a where fsh 1 and fdate to_date(2016-02-29, yyyy-MM-dd) and a.fsetcode 0 union select a.fdate, a.fsetcode, a.fzqdm, a.fhqssj, a.fhqpjj, a.fbjsj, a.fsjsj, a.fzdcj, a.fjyzt, a.fjysc, a.fzqlb, a.fsyqx, a.fdatasource, a.fyqfyfx, a.fgzjgly, a.ftpdate from (select fdate, fsetcode, fzqdm, fhqssj, fhqpjj, fbjsj, fsjsj, fzdcj, fjyzt, fjysc, fzqlb, fsyqx, fdatasource, fyqfyfx, fgzjgly, ftpdate, fsh from T_***CE where fzqlb JJ) a right join (select FDate, FZqdm, fjysc From T_***CE where fsh 1 and fdate to_date(2016-02-26, yyyy-MM-dd) and fsetcode 0 and fzqlb JJ) b on b.FDate a.FDate and a.FZqdm b.FZqdm and a.fjysc b.fjysc and a.fsh 1 where fsetcode 0 and a.fjysc Y 关键的sql语句其中上面的第一条语句执行情况SQL ordered by Elapsed Time Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text 3,519 3,601 0 33.26 f089ggtmuxsnu oraclep3tgbmsdb1 (TNS V1-V3) SELECT /* LEADING… 1,305 1,086 158 8.26 12.34 7m0bfdfskwgcc JDBC Thin Client select sum(… 该语句执行了3600秒即整个快照期都还未执行完成该语句是三张表的关联统计查询oracle自动对其进行并行查询可能由于此三张表T_A***T、T_***NCE、T_AS**T的数据量较大尤其是T_A***T的数据量较大时更是影响性能采用并行查询后反而导致了对io的争用降低了性能。 4、全表扫描问题 大表在一小时内发生了822次全表扫描如果表的数据比较大则对性能有很大影响。小表每秒中有28次全表扫描需重点优化以上3条sql语句。 table scans (direct read) 0 0.00 0.00 table scans (long tables) 822 0.23 0.07 table scans (rowid ranges) 0 0.00 0.00 table scans (short tables) 102,749 28.52 8.27 total number of times SMON posted 22 0.01 二、oracle优化 1、合理设置DB_FILE_MULTIBLOCK_READ_COUNT此参数控制在多数据块读时一次读入数据块的次数。适当增加这个参数大小能够提高多数据块操作如全表扫描的IO效率。 2、可以考虑对以上热点表重建索引、分区表等方式来降低该数据段上的IO负荷将历史数据进行分离比如根据业务情况将2015年之前的数据转移到另外的备份库中。 3、因Buffer Hit只有73%可根据Buffer Pool Advisory调整buffer pool大小为16g。 4、将频繁并发访问的表或数据移到另一数据块或者进行更大范围的分布(可以增大pctfree值 扩大数据分布减少竞争)。 5、属于index block的表如T_***SED、T_***_TMP应该考虑重建索引、分割索引或使用反向键索引。关于反向键索引需根据sql语句查询特点进行有选择使用如果在where中对索引列进行了范围搜索则会导致该索引无效会进行全表扫描反向键索引只对\有效。 转载于:https://www.cnblogs.com/bingjava/p/5255760.html
