AWR日常数据库分析V0.2.doc

河北网通综合客服系统AWR日常数据库分析手册2008年4月大唐软件技术有限责任公司Copyright By CATTSoft. All Rights Reserved版本修订历史起止日期作者/修订人更改章节修改描述版本/状态2008-04-3刘建华全部初始创建V0.12008-04-9刘建华1.2.6添加解释V0.21. AWR日常数据库分析1.1. 通过TOAD出AWR Rpt：AWR的介绍见河北网通综合客服系统oracle维护手册第九章，这里介绍的是使用TOAD的工具来快速的导出AWR报告。进入TOAD,连接入相关数据库。点击Database菜单，选择Monitor的第一项ADDM/AWR。进入ADDM/AWR界面后默认停留在ADDM&AWR Report的大TAB页的ADDM页，需要点击下边的AWR Report-HTML Format。选择起始和结束的快照点，目前数据库是按照默认的1小时做一次快照报告，保留1周，选择好后点击Generate Report按钮即可生成HTML格式的AWR报告，注意最好保存到本地来做查看。1.2. AWR Rpt中比较重要的TOP SQL监控：AWR Rpt主要可以用来抓TOP SQL。从SQL ordered by Elapsed Time开始都显示的大SQL，需要点击SQL ID来查看具体的SQL内容，并且点击后退可以回到原来的位置。注意需要查看SQL MODULE的连接方式，一般如果是用工具登陆上来的不是程序中的SQL。日常的SQL抓取可以采用中的语句来抓取。做为开发和维护人员首先应该重点关注以下TOP SQL：1.2.1. SQL ordered by Elapsed Time： 记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和，而不是单次SQL执行时间 Elapsed Time = CPU Time + Wait Time)。SQL ordered by Elapsed Time Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Elapsed Time (s)CPU Time (s)Executions Elap per Exec (s) % Total DB TimeSQL IdSQL ModuleSQL Text152,148150,929205,2180.7424.22an4rtjrb8tbq5 update SYS.AUD$ set XID = :1 w.109,969109,0322,00354.9017.500twzs3px5d7wj DECLARE job BINARY_INTEGER := .38,79738,4608,8434.396.176gvch1xu9ca3g DECLARE job BINARY_INTEGER := .29,31213,414102931.154.6726ndjmd3nnmwx BEGIN CS_SLT_DSCLJG(:1, :2, :3. Elapsed Time(S): SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。Elapsed Time = CPU Time + Wait Time CPU Time(s): 为SQL语句执行时CPU占用时间总时长，此时间会小于等于Elapsed Time时间。单位时间为秒。 Executions: SQL语句在监控范围内的执行次数总计。 Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。 % Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。 SQL ID: SQL语句的ID编号，点击之后就能导航到下边的SQL详细列表中，点击IE的返回可以回到当前SQL ID的地方。 SQL Module: 显示该SQL是用什么方式连接到数据库执行的，如果是用SQL*Plus或者PL/SQL链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。 SQL Text: 简单的sql提示，详细的需要点击SQL ID。1.2.2. SQL ordered by CPU Time: 记录了执行占CPU时间总和时间最长的TOP SQL(请注意是监控范围内该SQL的执行占CPU时间总和，而不是单次SQL执行时间)。1.2.3. SQL ordered by Gets: 记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占Gets总和，而不是单次SQL执行所占的Gets). 1.2.4. SQL ordered by Reads:记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和，而不是单次SQL执行所占的磁盘物理读)。1.2.5. SQL ordered by Executions: 记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数。1.2.6. SQL ordered by Parse Calls: 记录了SQL的软解析次数的TOP SQL。 说到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：1、语法检查(syntax check)检查此sql的拼写是否语法。2、语义检查(semantic check)诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。3、对sql语句进行解析(prase)利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。4、执行sql，返回结果(execute and return)其中，软、硬解析就发生在第三个过程里。Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；假设存在，则将此sql与cache中的进行比较；假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。这就是在很多项目中，倡导开发设计人员对功能相同的代码要努力保持代码的一致性，以及要在程序中多使用绑定变量的原因。/*/大家都在说在Sql中使用了Bind Var（绑定变量）会提高不少性能，那他到底是如何提高性能的呢？使用了Bind Var能提高性能主要是因为这样做可以尽量避免不必要的硬分析（Hard Parse）而节约了时间，同时节约了大量的CPU资源。当一个Client提交一条Sql给Oracle后，Oracle首先会对其进行解析（Parse），然后将解析结果提交给优化器（Optimiser）来进行优化而取得Oracle认为的最优的Query Plan，然后再按照这个最优的Plan来执行这个Sql语句（当然在这之中如果只需要软解析的话会少部分步骤）。但是，当Oracle接到Client提交的Sql后会首先在共享池（Shared Pool）里面去查找是否有之前已经解析好的与刚接到的这一个Sql完全相同的Sql（注意这里说的是完全相同，既要求语句上的字符级别的完全相同，又要求涉及的对象也必须完全相同）。当发现有相同的以后解析器就不再对新的Sql在此解析而直接用之前解析好的结果了。这里就节约了解析时间以及解析时候消耗的CPU资源。尤其是在OLTP中运行着的大量的短小Sql，效果就会比较明显了。因为一条两条Sql的时间可能不会有多少感觉，但是当量大了以后就会有比较明显的感觉了。上面说到了硬解析（Hard Parse），那这个Hard Parse到底是个啥呢？Parse主要分为三种：1、Hard Parse （硬解析）2、Soft Parse （软解析）3、Soft Soft Parse（好像有些资料中并没有将这个算在其中）Hard Parse就是上面提到的对提交的Sql完全重新从头进行解析（当在Shared Pool中找不到时候将会进行此操作），总共有一下5个执行步骤：1：语法分析2：权限与对象检查3：在共享池中检查是否有完全相同的之前完全解析好的如果存在,直接跳过4和5,运行Sql（此时算soft parse）4：选择执行计划5：产生执行计划Soft Parse就如果是在Shared Pool中找到了与之完全相同的Sql解析好的结果后会跳过Hard Parse中的后面的两个步骤。Soft Soft Parse实际上是当设置了session_cursor_cache这个参数之后，Cursor被直接Cache在当前Session的PGA中的，在解析的时候只需要对其语法分析、权限对象分析之后就可以转到PGA中查找了，如果发现完全相同的Cursor，就可以直接去取结果了，也就就是实现了Soft Soft Parse。不过在计算解析次数的时候是只计算Hard Parse和Soft Parse的（其实Soft Soft Parse好像也并不能算是做了Parse ）：Soft Parse百分比计算：Round(100*(1-:hprs/:prse),2) hprs：硬解析次数；prse：解析次数Parse比率计算： Round(100*(1-prse/exec) ,2) exec：执行次数 软解析过多的意思就是说session_cursor_cache的数值有可能小了，一些频繁调用的SQL在Cache中找不到相同的Cursor。1.2.7. SQL ordered by Sharable Memory: 记录了SQL占用library cache的大小的TOP SQL。Sharable Mem (b)：占用library cache的大小。单位是byte。1.2.8. SQL ordered by Version Count: 记录了SQL的打开子游标的TOP SQL。 在硬解析的过程中，进程会一直持有library cach latch，直到硬解析结束。硬解析结束以后，会为该SQL产生两个游标，一个是父游标，另一个是子游标。父游标里主要包含两种信息：SQL文本以及优化目标（optimizer goal）。父游标在第一次打开时被锁定，直到其他所有的session都关闭该游标后才被解锁。当父游标被锁定的时候是不能被交换出library cache的，只有在解锁以后才能被交换出library cache，这时该父游标对应的所有子游标也被交换出library cache。子游标包括游标所有的信息，比如具体的执行计划、绑定变量等。子游标随时可以被交换出library cache，当子游标被交换出library cache时，oracle可以利用父游标的信息重新构建出一个子游标来，这个过程叫reload。可以使用下面的方式来确定reload的比率： SELECT 100*sum(reloads)/sum(pins) Reload_Ratio FROM v$librarycache; 一个父游标可以对应多个子游标。子游标具体的个数可以从v$sqlarea的version_count字段体现出来。而每个具体的子游标则全都在v$sql里体现。当具体的绑定变量的值与上次的绑定变量的值有较大差异（比如上次执行的绑定变量的值的长度是6位，而这次执行的绑定变量的值的长度是200位）时或者当SQL语句完全相同，但是所引用的对象属于不同的schema时，都会创建一个新的子游标1.2.9. SQL ordered by Cluster Wait Time: 记录了集群的等待时间的TOP SQL1.3. 其他管理员相关问题解析：1.3.1. Load Profile Per SecondPer TransactionRedo size:65,768.9017,459.39Logical reads:31,757.568,430.54Block changes:271.0671.96Physical reads:188.8650.13Physical writes:11.903.16User calls:197.1552.34Parses:241.4064.08Hard parses:1.900.50Sorts:12.463.31Logons:0.150.04Executes:313.8883.32Transactions:3.77% Blocks changed per Read:0.85Recursive Call %:95.92Rollback per transaction %:30.79Rows per Sort:232.93说明：Redo size:每秒产生的日志大小(单位字节)，可标志数据变更频率, 数据库任务的繁重与否Logical reads:平决每秒产生的逻辑读，单位是blockblock changes:每秒block变化数量，数据库事物带来改变的块数量Physical reads:平均每秒数据库从磁盘读取的block数Physical writes:平均每秒数据库写磁盘的block数User calls:每秒用户call次数Parses: 每秒解析次数，近似反应每秒语句的执行次数, 软解析每秒超过300次意味着你的应用程序效率不高，没有使用soft soft parse，调整session_cursor_cacheHard parses:每秒产生的硬解析次数, 每秒超过100次，就可能说明你绑定使用的不好Sorts:每秒产生的排序次数Executes:每秒执行次数Transactions:每秒产生的事务数，反映数据库任务繁重与否Recursive Call %: 如果有很多PLSQL，那么他就会比较高Rollback per transaction %:看回滚率是不是很高，因为回滚很耗资源如果回滚过高，可能说明你的数据库经历太多的无效操作过多的回滚可能还会带来Undo Block的竞争该参数计算公式如下: Round(User rollbacks / (user commits + user rollbacks) ,4)* 100%1.3.2. Instance Efficiency Percentages (Target 100%) Buffer Nowait %:100.00Redo NoWait %:100.00Buffer Hit %:99.41In-memory Sort %:100.00Library Hit %:99.50Soft Parse %:99.21Execute to Parse %:23.09Latch Hit %:99.71Parse CPU to Parse Elapsd %:27.72% Non-Parse CPU:99.56说明:Buffer Nowait %:在缓冲区中获取Buffer的未等待比率, Buffer Nowait99%说明，有可能是有热, 块(查找x$bh的 tch和v$latch_children的cache buffers chains) Redo NoWait %:在Redo缓冲区获取Buffer的未等待比率Buffer Hit %:数据块在数据缓冲区中得命中率，通常应在90%以上，否则，需要调整, 小于 95%,重要的参数,小于90%可能是要加db_cache_size,但是大量的非选择的索引也会造成该值很高（大量的db file sequential read）In-memory Sort %:在内存中的排序率Library Hit %:主要代表sql在共享区的命中率，通常在95%以上，否，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。Soft Parse %:近似看作sql在共享区的命中率，小于 Executions,就可能出现该比小于0的情况, 该值0通常说明shared pool设置或效存在问题造成反复解析，reparse可能较严重,或者可是同snapshot有关如果该值为负值或者极低,通常说明数据库性能存在问题Latch Hit %: Latch Hit99%，否则存在严重的性能问题，比如绑定等会影响该参数Parse CPU to Parse Elapsd %:解析实际运行事件/(解析实际运行时间+解析中等待资源时间)越高越好% Non-Parse CPU:查询实际运行时间/(查询实际运行时间+sql解析时间)，太低表示解析消耗时间过多。100*（parse time cpu / parse time elapsed）= Parse CPU to Parse Elapsd % 1.3.3. Shared Pool Statistics BeginEndMemory Usage %:78.6488.68% SQL with executions1:98.9595.09% Memory for SQL w/exec1:87.6695.84说明：Memory Usage %:共享池内存使用率，应该稳定在70%-90%间，太小浪费内存，太大则内存不足。% SQL with executions1:执行次数大于1的sql比率，若太小可能是没有使用bind variables。% Memory for SQL w/exec1:也即是memory for sql with execution 1:执行次数大于1的sql消耗内存/所有sql消耗的内存 1.3.4. Top 5 Timed Events EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait ClassCPU time521,56083.0enq: TX - row lock contention81,39939,2644826.2Applicationdb file sequential read118,477,26832,68905.2User I/Ogc cr disk read58,354,31016,30902.6Clustergcs log flush sync755,3125,8818.9Other 说明： 首要的5个等待事件(Top 5 wait events)，是整个报告中最能反映问题的一部分1. DB File Scattered Read -通常与全表扫描有关，需要确认是否真的需要全表扫描，能否改用索引或者把把较小的表整个的放入内存缓冲区中，避免反复磁盘读取2. DB File Sequential Read 表明有很多索引读，需要你调整代码，特别是表连接部分，适当的调整DB_CACHE_SIZE的值3. Free Buffer 没有可用的内存缓冲区而等待，增大DB_CACHE_SIZE,加速检查点和调整代码4. Buffer Busy Wait -段头出现问题，增加freelists或者freelist maxtrans-数据块问题，分离热点数据，采用反向关键字索引，采用小的数据块，增大initrans和maxtrans-undo header问题，增加回滚段-undo block问题，增加提交频率，增大回滚段5. Latch Free-library Cache问题，使用绑定变量，调整shared_pool_size-shared pool问题，使用绑定变量，调整shared_pool_size-redo Allocation,最小化redo生成并避免不必要的提交-redo Copy, 增大_log_simultaneous_copies-Row Cache Object,增大共享池-Cache Buffers Chain,_Db_block_Hash_Buckers应被增大或变为质数-Cache Buffers LRU Chain,设置DB_BLOCK_LRU_LACHES或者使用多个缓冲区池6. Enqueue ST-使用本地表空间或者预先分配大扩展7. Enqueue HW 预先分配扩展与高水位线之上8. Enqueue TX4 增大表或者索引的initrans和maxtrans9. Enqueue TM为外键建立索引，查看应用程序的表锁10. Log Buffer Space增大日志缓冲区，重做日志放在快速磁盘上11. Log File Switch 归档设备太慢或者太满，增加或者扩大重做日志12. Log File Sync 每次提交更多记录，更快的存放重做日志的磁盘，裸设备13. idle event 其他的一些等待事件可以忽略1.3.5. 分析样例：系统总体信息Load Profile Per Second Per Transaction - - Redo size: 14,823.12 15,040.46 Logical reads: 14,399.91 14,611.05 Block changes: 93.92 95.30 Physical reads: 101.82 103.32 Physical writes: 61.94 62.85 User calls: 70.76 71.80 Parses: 23.70 24.05 Hard parses: 2.74 2.78 Sorts: 27.37 27.77 Logons: 2.77 2.81 Executes: 31.37 31.83 Transactions: 0.99 % Blocks changed per Read: 0.65 Recursive Call %: 73.20Rollback per transaction %: 0.00 Rows per Sort: 17.06Instance Efficiency Percentages (Target 100%) Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 100.00 In-memory Sort %: 99.99 Library Hit %: 95.93 Soft Parse %: 88.45 Execu