Oracle的AWR报告分析

    * 定义：awr报告是oracle 10g下提供的一种性能收集和分析工具，它能提供一个时间段内整个系统资源使用情况的报告，通过这个报告，我们就可以了解一个系统的整个运行情况，这就像一个人全面的体检报告。

如何分析：

    * 在看awr报告的时候，我们并不需要知道所有性能指标的含义，就可以判断出问题的所在，这些性能指标其实代表了oracle内部实现，对oracle理解的越深，在看awr报告的时候，对数据库性能的判断也会越准确

    * 在看性能指标的时候，心里先要明白，数据库出现性能问题，一般都在三个地方，io，内存，cpu，这三个又是息息相关的（ps：我们先假设这个三个地方都没有物理上的故障），当io负载增大时，肯定需要更多的内存来存放，同时也需要cpu花费更多的时间来过滤这些数据，相反，cpu时间花费多的话，有可能是解析sql语句，也可能是过滤太多的数据，到不一定是和io或内存有关系了

    * 当我们把一条sql送到数据库去执行的时候，我们要知道，什么时候用到cpu，什么时候用到内存，什么时候用到io

   1. cpu：解析sql语句，尝试多个执行计划，最后生成一个数据库认为是比较好的执行计划，不一定是最优的，因为关联表太多的时候，数据库并不会穷举所有的执行计划，这会消耗太多的时间，oracle怎么就知道这条数据时你要，另一个就不是你要的呢，这是需要cpu来过滤的
   2. 内存：sql语句和执行计划都需要在内存保留一段时间，还有取到的数据，根据lru算法也会尽量在内存中保留，在执行sql语句过程中，各种表之间的连接，排序等操作也要占用内存
   3. io：如果需要的数据在内存中没有，则需要到磁盘中去取，就会用到物理io了，还有表之间的连接数据太多，以及排序等操作内存放不下的时候，也需要用到临时表空间，也就用到物理io了

这里有一点说明的是，虽然oracle占用了8G的内存，但pga一般只占8G的20%，对于专用服务器模式，每次执行sql语句，表数据的运算等操作，都在pga中进行的，也就是说只能用1.6G左右的内存，如果多个用户都执行
多表关联，而且表数据又多，再加上关联不当的话，内存就成为瓶颈了，所有优化sql很重要的一点就是，减少逻辑读和物理读

如何生成awr报告：

    * 1：登陆对应的数据库服务器
2：找到oracle磁盘空间（d:oracle\product\10.2.0\db_1\RDBMS\Admin)
3：执行cmd-cd d:回车
4: cd  d:oracle\product\10.2.0\db_1\RDBMS\Admin 回车
5：sqlplus 用户名/密码@服务连接名(例：sqlplus carmot_esz_1/carmot@igrp)
6：执行@awrrpt.sql 回车

第一步输入类型： html
第二步输入天数： 天数自定义（如1，代表当天，如果2，代表今天和昨天。。。）
第三步输入开始值与结束值：（你可以看到上面列出的数据，snap值）
      这个值输入开始，与结束
第四步输入导出表的名称：名称自定义 回车
第五步，由程序自动导完。

第六：到d:oracle\product\10.2.0\db_1\RDBMS\Admin 目录下。找到刚才生成的文件。 XXXX.LST文件

具体分析过程： 

    * 在分析awr报告之前，首先要确定我们的系统是属于oltp，还是olap（数据库在安装的时候，选择的时候，会有一个选项，是选择oltp，还是olap）
      对于不同的系统，性能指标的侧重点是不一样的，比如，library hit和buffer hit，在olap系统中几乎可以忽略这俩个性能指标，而在oltp系统中，这俩个指标就非常关键了

    * 首先要看俩个时间
      Elapsed: 240.00 (mins) 表明采样时间是240分钟，任何数据都要通过这个时间来衡量，离开了这个采样时间，任何数据都毫无疑义
      DB Time: 92,537.95 (mins) 表明用户操作花费的时候，包括cpu时间喝等待时间，也许有人会觉得奇怪，为什么在采样的240分钟过程中，用户操作时间竟然有92537分钟呢，远远超过了
      采样时间，原因是awr报告是一个数据的集合，比如在一分钟之内，一个用户等待了30秒，那么10个用户就等待了300秒，对于cpu的话，一个cpu处理了30秒，16个cpu就是4800秒，这些时间都是以累积的方式记录在awr报告中的。

          再看sessions，可以看出连接数非常多

    * 为了对数据库有个整体的认识，先看下面的性能指标

 
  
 
 
   1. Buffer Nowait 说明 在从内存取数据的时候，没有经历等待的比例，期望值是100%
   2. Buffer Hit 说明从内存取数据的时候，buffer的命中率的比例，期望值是100%，但100%并不代表性能就好，因为这只是一个比例而已，举个例子，执行一条 sql语句，# 执行计划是需要取10000个数据块，结果内存中还真有这10000个数据块，那么比例是100%，表面上看是性能最高的，还有一个执行计划是需要500 个数据块，内存中有250个，另外250个需要在物理磁盘中取，
      这种情况下，buffer hit是50%，结果呢，第二个执行计划性能才是最高的，所以说100%并不代表性能最好
   3. Library Hit 说明sql在Shared Pool的命中率，期望值是100%
   4. Execute to Parse 说明解析sql和执行sql之间的比例，越高越好，说明一次解析，到处执行，如果parse多，execute少的话，还会出现负数，因为计算公式是100*（1-parse/execute）
   5. Parse CPU to Parse Elapsd 说明在解析sql语句过程中，cpu占整个的解析时间比例，，期望值是100%，说明没有产生等待，需要说明的是，即使有硬解析，只要cpu没有出现性能问题，也是可以容忍的，比较硬解析也有它的好处的
   6. Redo NoWait 说明在产生日志的时候，没有产生等待，期望值是100%
   7. Soft Parse 说明软解析的比例，期望值是100%，有一点要说明的是，不要单方面的追求软解析的高比例，而去绑定变量，要看性能的瓶颈在哪里
   8. Latch Hit 说明latch的命中率，期望值是100%，latch类似锁，是一种内存锁，但只会产生等待，不会产生阻塞，和lock还是有区别的，latch是在并发的情况下产生的
   9. Non-Parse CPU 说明非解析cpu的比例，越高越好，用100减去这个比例，可以看出解析sql所花费的cpu，100-99.30=0.7，说明花费在解析sql上的cpu很少

    * 结合Time Model Statistics

 
 

         可以看出，在整个sql执行时间（sql execute elapsed time）时间为5552019秒中，解析时间（parse time elapsed）用了36秒，硬解析时间（hard parse elapsed time）用了34秒虽然硬解析时间占了整个解析时间的绝大部分，但解析时间是花的很少的，所以可以判断出，sql的解析没有成为性能的瓶 颈，进一步推测，sql在获取数据的过程中遇到了瓶            颈

    * 继续看Top 5 Timed Events，从这里可以看出等待时间在前五位的是什么事件，基本上就可以判断出性能瓶颈在什么地方

 
 
   1. buffer busy waits 说明在获取数据的过程中，频繁的产生等待事件，很有可能产生了热点块，也就是说，很多会话都去读取同样的数据块，这一事件等待了5627394次，总共等待了5322924秒，平均等待时间为946毫秒，而且频率也是最高的，有95.9%，等待类别是并发
      这里有一个概念：oracle操作的最小单位是块，当一个会话要修改这个块中的一条记录，会读取整个块，如果另一个会话要修改的数据也正好在这个块中，虽然这俩个
   2. 会话修改的记录不一样，也会产生等待direct path write temp和direct path read temp 说明用到了临时表空间，那我们再看一下Tablespace IO Stats

 
 
          各项指标都是非常高的，再根据上面的In-memory Sort是100%，没有产生磁盘排序，也就在排序的时候没有用到临时表空间，进一步推测，多个session，每个session执行的sql语句中多表关联，产生了很多中间数据，pga内存中放不下，
          用到了临时表空间，也有可能是用到了lob字段，在用lob字段的时候，也会用到临时表

    * 继续看SQL Statistics
      根据buffer busy waits等待次数，时间，频率都是最高的，我们重点看逻辑读，物理读，和执行时间最长的sql，把排在前几位的拿出来优化
      优化的原则为降低物理读，逻辑读，sql语句中的子操作执行次数尽量少，在看oracle估计出来的执行计划是看不出子操作的执行次数的，要看运行时的执行计划

    * 有兴趣的话还可以看一下Segment Statistics
      列出了用到的索引和表的使用情况，从这里也能看出索引和表的使用频率

    * 也可以看一下Load Profile
      里面列出了每秒，每个事务所产生的日志，逻辑读和物理读等指标

第二篇：ORACLE性能AWR报告的使用和分析

ORACLE性能诊断AWR报告的使用和分析

为满足业务的运行要求，高性能要求是目前IT系统普遍面临的最棘手问题，尤其是客户面对着目前越来越庞大系统和数据，系统整合、数据大集中似乎成了趋势。针对系统性能优化的诊断和分析，数据库方向又是其中的重要一环，本文将针对ORACLE中常用的性能诊断工具AWR报告，进行分析说明。

一、   ORACLE性能诊断工具

ORACLE数据库的性能的诊断工具有很多种，在9i之前主要通过手工进行采集分析，例如使用动态视图和Statspack报告来获取数据库性能状态信息，10g以后ORACLE数据库的性能诊断和改进建议越来越自动化，不过能够熟悉并掌握ORACLE的相关性能诊断工具的使用，仍对性能问题的准确和有效处理提供有利的帮助。以下是ORACLE中常用的一些分析工具。

l  动态性能视图

动态性能视图是ORACLE中最常用，也是最简单的一种工具。无论何种性能问题，都能在动态性能视图中找到线索，不过仅10g中动态性能视图就高达几百个，每个视图都包括很多诊断信息，想在众多的视图中找到问题的根源，也是一件费力的事情。一般常用的动态性能视图有：v$session、v$session_wait、v$process、v$sql、v$lock、v$latch、v$sysstat、v$system_event、v$sgastat。

l  Statspack报告

statspack 是Oracle 9i 之前使用的一个数据库收集工具，收集了数据库全面信息，包括负载概览、前五个等待事件、高速缓存的大小、共享池中SQL语句、表空间和文件I/O、库高速缓存、SGA统计等。

l  AWR和ADDM报告

AWR是10g以后提供的一个新工具，Oracle 建议用户用这个取代 Statspack，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题，并自动生成ADDM（自动数据库诊断监控）报告，为用户提供数据库性能诊断分析建议。

l  SQL执行计划和建议

数据库中SQL的执行效率可能是对系统影响最大的一个因素，利用ORACLE执行计划的分析，可以准确知道SQL执行的代价，并提供多个方面的调整建议，来进行SQL代码的优化分析。

二、   生成AWR报告

以下，本文将针对oracle10g后提供的常用性能分析报告AWR，依此来描述和分析数据库的性能点和优化建议。

AWR由ORACLE自动产生，默认30分钟采集一次，保留5天的记录。但是也可以通过DBMS_WORKLOAD_REPOSITORY包来手工创建、删除和修改。使用脚本awrrpt.sql或awrrpti.sql来查看AWR报告，这两个脚本都在目录$ORACLE_HOME/rdbms/admin中，报告可以保存为文本文件或HTML文件。

生成AWR报告的步骤如下：

sqlplus sys/sys@127.0.0.1/scmis as sysdba

SQL> @c:/oracle/product/10.2.4/db_1/RDBMS/ADMIN/awrrpt.sql

输入 report_type 的值:html     （注：确定报告的格式）

输入 num_days 的值:1     （注：选择快照的天数）

输入 begin_snap 的值:425       （注：起始快照）

输入 end_snap 的值:427 （注：结束快照）

输入 report_name 的值:d:\scmis-awr-20##-10-29.html     （注：报告生成的名称和位置）

三、   AWR报告分析

1 

2 

3 

AWR报告头记录了数据库名称和起始快照时间，报告头中主要分析Elapsed（总时间）和DB Time（DB消耗的时间，不包括后台进行的消耗时间），如果DB Time/Elapsed比值较大，说明数据库系统压力较大，例如下图中系统包括16CPU(2*8核)，每个cpu耗时26.7min，负载为26.7/60.03=44.5%，说明数据库服务器存在较大的负荷。

AWR报告总览包括了五个部分：缓存尺寸（Cache Sizes）、负载性能（Load Profile）、数据库效率（Instance Efficiency Percentages）、共享池统计（Shared Pool Statistics）、TOP5事件（Top 5 Timed Events）。这五个部分也就是整个报告核心，记录了数据库系统的关键性能参数和状况。

Ø  缓存尺寸（Cache Sizes）

主要记录总的缓存大小Buffer Cache和SGA缓存尺寸Shared Pool Size，SGA是ORACLE中非常重要的内存共享区，对系统内的所有进程都是共享的。当多个用户同时连接到一个例程时，所有的用户进程、服务进程都可以共享使用这个SGA区。Shared pool可以分为库缓存（library cache）和数据字典缓存（dictionary cache）。Library cache存放了最近执行的SQL语句、存储过程、函数、解析树以及执行计划等。而dictionary cache则存放了在执行SQL语句过程中，所参照的数据字典的信息，包括SQL语句所涉及的表名、表的列、权限信息等。

Ø  负载性能（Load Profile）

这个部分记录了数据库负载情况，绝对值的分析意义不大，需要与之前的基线数据比较才具有更多的意义，单个的报告数据只说明应用的负载情况，绝大多数据并没有一个所谓“正确”的值。其中重要的几个对于Logons大于每秒1~2个，表明可能有争用问题；对于Hard parses大于每秒100，parses大于每秒300，表明硬解析太多，SQL重用率不高，需要解决SQL代码变量绑定问题，并调整共享池参数、调整cursor_sharing参数；对于Sorts大于每秒100，表明排序过多，需要减少SQL代码中排序操作，或调整排序空间。

Ø  数据库效率（Instance Efficiency Percentages）

记录了Oracle关键指标的内存命中率及数据库实例其它操作的效率，这个部分反应了数据库中最重要指标的命中率。

l  缓冲区未等待率(buffer nowait %)：指在缓冲区中获取buffer的未等待比率。该指标的值应接近100%，如果该值较低，则可能要增大buffer cache。

l  redo缓冲区未等待率(redo nowait %)：指在redo缓冲区获取buffer的未等待比率。该指标的值应接近100%，如果该值较低，则有2种可能的情况：1）online redo log没有足够的空间；2）log切换速度较慢。

l  缓冲区命中率(buffer hit %)：指数据块在数据缓冲区中的命中率。该指标的值通常应在90%以上，否则，需要调整。如果持续小于90%，可能要加大db_cache_size。但有时，缓存命中率低并不意味着cache设置小了，可能是潜在的全表扫描降低了缓存命中率。

l  内存排序率(in-memory sort %)：指排序操作在内存中进行的比率。该指标的值应接近100%，如果指标的值较低，则表示出现了大量排序时的磁盘i/o操作，可考虑加大sort_area_size参数的值。

l  共享区命中率(library hit %)：该指标主要代表sql在共享区的命中率。该指标的值通常应在95%以上，否则需要考虑加大共享池（修改shared_pool_size参数值），绑定变量，修改cursor_sharing等参数。

l  软解析的百分比(soft parse %)：该指标是指oracle对sql的解析过程中，软解析所占的百分比。该指标的值通常应在95%以上，如果低于80%，那么就可能sql基本没被重用，sql没有绑定变量，需要考虑绑定变量。

l  闩锁命中率(latch hit %)：指获得latch的次数与请求latch的次数的比率。该指标的值应接近100%，如果低于99%，需要分析闩锁竞争，明确是应用锁、数据字典锁、内存控制锁的哪一种。通过进一步分析Latch Statistics章节或动态性能视图v$session_wait,v$latch,v$latch_children。

l  sql语句执行与解析的比率(execute to parse %)：指sql语句执行与解析的比率。该指标的值应尽可能到高，如果过低，可以考虑设置session_cached_cursors参数。

Ø  共享池统计（Shared Pool Statistics）

记录了在采集点时刻，共享池（share pool）内存被使用的比例。这个指标的值应保持在75%~90%,如果这个值太低，就浪费内存，如果太高，会使共享池外部的组件老化，如果sql语句被再次执行，则就会发生硬分析。其中执行次数大于1的sql比率（SQL with executions>1），如果此值太小，说明需要在应用中更多使用绑定变量，避免过多SQL解析。

Ø  TOP5事件（Top 5 Timed Events）

这个部分也是AWR报告中非常重要的部分，从这里可以看出等待时间在前五位的是什么事件，基本上就可以判断出性能瓶颈在什么地方。通常，在没有问题的数据库中，CPU time总是列在第一个，其他几类重要影响性能的事件分析如下。

l  缓冲区忙(buffer busy)：当一个会话想要访问缓存中的某个块，而这个块正在被其它会话使用时，将会产生该等待事件。这时候，其它会话可能正在从数据文件向缓存中的这个块写入信息，或正在对这个块进行修改。出现这个等待事件的频度不应大于1%。如果这个等待事件比较显著，则需要根据等待事件发生在缓存中的哪一块（如字段头部、回退段头部块、回退段非头部块、数据块、索引块等），采取相应的优化方法。

l  文件分散读取(db file scattered read)：该等待事件通常与全表扫描有关。因为全表扫描是被放入内存中进行的进行的，通常情况下它不可能被放入连续的缓冲区中，所以就散布在缓冲区的缓存中。如果这个等待事件比较显著，可能说明对于某些全表扫描的表，没有创建索引或没有创建合适的索引。尽管在特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。

l  文件顺序读取(db file sequential read)：该等待事件通常与单个数据块相关的读取操作有关。如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，或者可能不合适地使用了索引。对于大量事务处理、调整良好的系统，这一数值大多是很正常的，但在某些情况下，它可能暗示着系统中存在问题。应检查索引扫描，以保证每个扫描都是必要的，并检查多表连接的连接顺序。另外db_cache_size?也是这些等待出现频率的决定因素。

l  队列(enqueue)：队列是一种保护共享资源的锁定机制。该锁定机制保护共享资源，如记录中的数据，以避免两个人在同一时间更新同一数据。如果enqueue等待事件比较显著，则需要根据enqueue等待类型，采取相应的优化方法。

l  闩锁释放(latch free)：latch是一种低级排队机制(它们被准确地称为相互排斥机制),用于保护系统全局区域(sga)中共享内存结构。该等待事件意味着进程正在等待其他进程已持有的latch。对于常见的latch等待通常的解决方法：1）share pool latch：在oltp应用中应该更多的使用绑定变量以减少该latch的等待。2）library cache latch：同样的需要通过优化sql语句使用绑定变量减少该latch的等待。

l  日志文件同步(log file sync)：这个等待事件是指当一个会话完成一个事务（提交或者回滚数据）时，必须等待lgwr进程将会话的redo信息从日志缓冲区写到日志文件后，才能继续执行下去。这个等待事件的时间过长，可能是因为commit太频繁或者lgwr进程一次写日志的时间太长（可能是因为一次log io size太大），可调整_log_io_size。

Ø  SQL统计（SQL Statistics）

AWR报告中还有一块对性能影响最大的指标，TOP SQL统计。本节按各种资源分别列出对资源消耗最严重的SQL语句，并显示它们所占统计期内全部资源的比例，提供给我们调优依据。

l  SQL ordered by Elapsed Time： 记录了执行总和时间的SQL，记录的是监控范围内该SQL的执行时间总和，需要综合分析CPU时间（CPU Time）和执行次数（Executions）才能得到单个SQL的代价。单次执行开销较大的SQL属于重点优化之列。

l  SQL ordered by CPU Time： 记录了执行占CPU时间总和时间最长的SQL，再CPU消耗较大的系统中，重点优化此类SQL。

l  SQL ordered by Gets： 记录了执行占总buffer gets(逻辑IO)的SQL，查找总的缓冲区获取比较高的SQL，并根据平均每次执行缓冲区获取的数量判断优化的余地有多大。优化这些SQL，有助于减少CPU开销以及数据缓冲池相关的闩锁竞争。

l  SQL ordered by Reads：记录了执行占总磁盘物理读(物理IO)的SQL，查找总的物理读比较高的SQL，并根据平均每次执行物理读的数量判断优化的余地有多大。优化这些SQL，有助于减少I/O开销和CPU开销。

l  SQL ordered by Executions：记录了按照SQL的执行次数排序的SQL，执行次数多的SQL也是需要重点优化，使sql语句中的子操作执行次数尽量少。

l  SQL ordered by Parse Calls：记录了解析次数排序的SQL，避免出现硬解析，采用使用绑定变量等方式。

l  SQL ordered by Sharable Memory：记录了SQL占用library cache的大小的SQL。

l  SQL ordered by Version Count：记录了SQL的打开子游标的SQL。

l  SQL ordered by Cluster Wait Time：记录了集群的等待时间的SQL。