oracle awr报告
Posted by wukaiqiang; tagged with none
概述
数据库的性能瓶颈通常是由于某些sql语句效率不高，在大数据量或者高并发的情况下，会拖慢整体性能。要找到这些语句可以通过查找报告中的“SQL Statistics”章节，此章节按各种资源分别列出对资源消耗最严重的SQL语句，并显示它们所占统计期内全部资源的比例，这给出我们调优指南。例如在一个系统中，CPU资源是系统性能瓶颈所在，那么优化buffer gets最多的SQL语句将获得最大效果。在一个I/O等待是最严重事件的系统中，调优的目标应该是physical IOs最多的SQL语句。
awr报告中SQL优化主要查看如下内容：
SQL ordered by Elapsed Time
总时间消耗最长的sql语句
SQL ordered by CPU TimeCPU
总耗时最长的sql语句
SQL ordered by Gets
读取缓存最多的sql语句
常见sql调优方法：
1、根据查询条件创建索引。
2、用exists、not exists替代in和not in。
3、用表连接代替exists 。
4、减少order by 、union的使用，这两个关键字都很耗性能。
5、避免使用distinct，效率较低。
6、select 避免使用* 。
7、减少访问数据库的次数，绑定变量，预处理。
8、用where子句代替having子句。
9、避免在索引列上使用is null 和is not null。将会使索引失效。
10、避免在索引列上使用计算。如select…from pay where money*10>100
Cache Sizes
shared pool主要包括library cache和dictionary cache。library cache用来存储最近解析（或编译）后SQL、PL/SQL和Java classes等。library cache用来存储最近引用的数据字典。发生在library cache或dictionary cache的cache miss代价要比发生在buffer cache的代价高得多。因此shared pool的设置要确保最近使用的数据都能被cache。
Load Profile
显示数据库负载概况，将之与基线数据比较才具有更多的意义，如果每秒或每事务的负载变化不大，说明应用运行比较稳定。单个的报告数据只说明应用的负载情况，绝大多数据并没有一个所谓“正确”的值，然而Logons大于每秒1~2个、Hard parses大于每秒100、全部parses超过每秒300表明可能有争用问题。
Redo size：每秒/每事务产生的redo大小（单位字节），可标志数据库任务的繁重程序。
Logical reads：每秒/每事务逻辑读的块数
Block changes：每秒/每事务修改的块数
Physical reads：每秒/每事务物理读的块数
Physical writes：每秒/每事务物理写的块数
User calls：每秒/每事务用户call次数
Parses：SQL解析的次数
Hard parses：其中硬解析的次数，硬解析太多，说明SQL重用率不高。
Sorts：每秒/每事务的排序次数
Logons：每秒/每事务登录的次数
Executes：每秒/每事务SQL执行次数
Transactions：每秒事务数
Blocks changed per Read：表示逻辑读用于修改数据块的比例
Recursive Call：递归调用占所有操作的比率
Rollback per transaction：每事务的回滚率
Rows per Sort：每次排序的行数
注:
Oracle的硬解析和软解析
提到软解析(soft parse)和硬解析(hard parse)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：
1、语法检查(syntax check)
检查此sql的拼写是否语法。
2、语义检查(semantic check)
诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
3、对sql语句进行解析(parse)
利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。
4、执行sql，返回结果(execute and return)
其中，软、硬解析就发生在第三个过程里。
Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；
假设存在，则将此sql与cache中的进行比较；
假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。
诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。
创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。
Instance Efficiency Percentages (Target 100%)
本节包含了Oracle关键指标的内存命中率及其它数据库实例操作的效率。
Buffer Nowait表示在内存获得数据的未等待比例。
buffer hit表示进程从内存中找到数据块的比率，监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统，如果此值低于80%，应该给数据库分配更多的内存。
Redo NoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低（可参考90%阀值），考虑增加LOG BUFFER。
library hit表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率，当应用程序调用SQL或存储过程时，Oracle检查Library Cache确定是否存在解析过的版本，如果存在，Oracle立即执行语句；如果不存在，Oracle解析此语句，并在Library Cache中为它分配共享SQL区。低的library hit ratio会导致过多的解析，增加CPU消耗，降低性能。如果library hit ratio低于90%，可能需要调大shared pool区。
Latch Hit：Latch是一种保护内存结构的锁，可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit>99%，否则意味着Shared Pool latch争用，可能由于未共享的SQL，或者Library Cache太小，可使用绑定变更或调大Shared Pool解决。
Parse CPU to Parse Elapsd：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)，越高越好。
Non-Parse CPU ：SQL实际运行时间/(SQL实际运行时间+SQL解析时间)，太低表示解析消耗时间过多。
Execute to Parse：是语句执行与分析的比例，如果要SQL重用率高，则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。
In-memory Sort：在内存中排序的比率，如果过低说明有大量的排序在临时表空间中进行。考虑调大PGA。
Soft Parse：软解析的百分比（softs/softs+hards），近似当作sql在共享区的命中率，太低则需要调整应用使用绑定变量。
Shared Pool Statistics
Memory Usage %：对于一个已经运行一段时间的数据库来说，共享池内存使用率，应该稳定在75%-90%间，如果太小，说明Shared Pool有浪费，而如果高于90，说明共享池中有争用，内存不足。
SQL with executions>1：执行次数大于1的sql比率，如果此值太小，说明需要在应用中更多使用绑定变量，避免过多SQL解析。
Memory for SQL w/exec>1：执行次数大于1的SQL消耗内存的占比。
Top 5 Timed Events
显示了系统中最严重的5个等待，按所占等待时间的比例倒序列示。当我们调优时，总希望观察到最显著的效果，因此应当从这里入手确定我们下一步做什么。例如如果‘buffer busy wait’是较严重的等待事件，我们应当继续研究报告中Buffer Wait和File/Tablespace IO区的内容，识别哪些文件导致了问题。如果最严重的等待事件是I/O事件，我们应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量I/O，并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待，就需要察看详细的LATCH统计识别哪些LATCH产生的问题。
在这里，log file parallel write是相对比较多的等待，占用了7%的CPU时间。
通常，在没有问题的数据库中，CPU time总是列在第一个。