Oracle自带能分析工具-awr介绍和分析.doc

Oracle自带性能分析工具AWR介绍和分析杭州华三通信技术有限公司目录1 Oracle 10g的AWR性能优化工具简介2 AWR配置2.1 AWR统计数据的缺省配置2.2修改AWR统计数据的设置2.3 AWR的使用2.3.1 AWR产生快照点(时间点)的方法：手动或自动方式2.3.2 AWR产生报表的方法：使用SQL命令，根据快照产生相应实例的报表2.4关注报表具体内容2.4.1 Load Profile2.4.2 Instance Efficiency Percentages (Target 100%)2.4.3 IO Stats3对AWR报表内容进行分析的ADDM工具3.1 ADDM概述3.2 ADDM的使用方法3.2.1在所有Oracle服务器上确认EM的状态，并启动EM进程3.2.2通过IE浏览器，登录EM界面3.3 ADDM查询和诊断到的问题有那些3.3.1查看集群中Oracle服务器的硬件配置3.3.2 CPU的瓶颈3.3.3内存结构上的瓶颈？3.3.4内存参数SGA、PGA查看和分析3.3.5 I/O是否存在性能瓶颈? (I/O子系统是否工作正常) ?3.3.6高负载的SQL语句(是否存在消耗过量系统资源的SQL) ?3.4 ADDM分析的结果4总结5附录1Oracle 10g的AWR性能优化工具简介AWR (Automatic Workload Repository)既自动工作负载信息库是Oracle 10g新提供的收集数据库统计信息的内置工具。它比之前的statspack有显著的改进，收集的信息也更多、更全面，使用方法也更简单。它主要采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题，如包括AWR内存区，历史数据存储文件和ASH等部件。AWR报告的内容繁多，官方文档也没有对所有参数给出说明。AWR产生的报表包括以下几部分。报表具体内容参见如下插入的对象。1、Report SummeryCache sizesLoad profileInstance Efficiency Percentages (Target 100%)Shared Pool StatisticsTop 5 Timed Events2、RAC StatisticsGlobal Cache Load ProfileGlobal Cache Efficiency Percentages (Target local+remote 100%)Global Cache and Enqueue Services - Workload CharacteristicsGlobal Cache and Enqueue Services - Messaging Statistics3、Wait Events StatisticsTime Model StatisticsWait ClassWait EventsBackground Wait EventsOperating System StatisticsService StatisticsService Wait Class Stats4、SQL StatisticsSQL ordered by Elapsed TimeSQL ordered by CPU TimeSQL ordered by GetsSQL ordered by ReadsSQL ordered by ExecutionsSQL ordered by Parse CallsSQL ordered by Sharable MemorySQL ordered by Version CountSQL ordered by Cluster Wait TimeComplete List of SQL Text5、Instance Activity StatisticsInstance Activity StatsInstance Activity Stats - Absolute ValuesInstance Activity Stats - Thread Activity6、IO StatsTablespace IO StatsFile IO Stats7、Buffer Pool Statistics8、Advisory StatisticsInstance Recovery StatsBuffer Pool AdvisoryPGA Aggr SummaryPGA Aggr Target StatsPGA Aggr Target HistogramPGA Memory AdvisoryShared Pool AdvisorySGA Target AdvisoryStreams Pool AdvisoryJava Pool Advisory9、Wait StatisticsBuffer Wait StatisticsEnqueue Activity10、Undo StatisticsUndo Segment SummaryUndo Segment Stats11、Latch StatisticsLatch ActivityLatch Sleep BreakdownLatch Miss SourcesParent Latch StatisticsChild Latch Statistics12、Segment StatisticsSegments by Logical ReadsSegments by Physical ReadsSegments by Row Lock WaitsSegments by ITL WaitsSegments by Buffer Busy WaitsSegments by Global Cache Buffer BusySegments by CR Blocks ReceivedSegments by Current Blocks Received13、Dictionary Cache StatisticsDictionary Cache StatsDictionary Cache Stats (RAC)14、Library Cache StatisticsLibrary Cache ActivityLibrary Cache Activity (RAC)15、Memory StatisticsProcess Memory SummarySGA Memory SummarySGA breakdown difference16、Streams StatisticsStreams CPU/IO UsageStreams CaptureStreams ApplyBuffered QueuesBuffered SubscribersRule Set17、Resource Limit Stats18、init.ora Parameters19、Global Enqueue Statistics20、Global CR Served Stats21、Global CURRENT Served Stats22、Global Cache Transfer Stats2AWR配置2.1AWR统计数据的缺省配置AWR实质上是一个Oracle的内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。AWR利用多个快照(既时间点)来采集数据。快照由一个称为MMON的新后台进程及其该进程自动地每小时采集一次。为了节省空间，采集的数据在7天后自动清除。快照频率（SNAP_INTERVAL）和保留时间（RETENTION）都可以由用户修改。查看当前的缺省设置，可以使用下面的语句：SQL select snap_interval, retention from dba_hist_wr_control;SNAP_INTERVAL RETENTION+00000 01:00:00.0 +00007 00:00:00.0这些SQL语句显示快照每小时采集一次，采集的数据保留7天。注意：1、snap_interval取值范围为 10分钟52560000分钟2.2修改AWR统计数据的设置例如，根据使用需要，修改快照时间间隔为20分钟，保留时间为2天，通过如下命令修改，参数以分钟为单位。修改：SQL begin dbms_workload_repository.modify_snapshot_settings ( interval = 20, retention = 2*24*60 ); end; /PL/SQL过程已成功完成。修改后，查询结果：SQL select snap_interval, retention from dba_hist_wr_control;SNAP_INTERVAL RETENTION+00000 00:20:00.0 +00002 00:00:00.02.3AWR的使用2.3.1AWR产生快照点(时间点)的方法：手动或自动方式1.AWR手动产生快照SQL exec dbms_workload_repository.create_snapshot;PL/SQL过程已成功完成。2.AWR自动产生快照根据所设定的快照频率（SNAP_INTERVAL）自动产生快照2.3.2AWR产生报表的方法：使用SQL命令，根据快照产生相应实例的报表使用SQL?/rdbms/admin/awrrpt命令产生报表。该命令实际调用的是位于名称：$ORACLE_HOME/rdbms/admin/awrrpt.sql下的脚本awr提供一个脚本来生成报表。报表的形式有两种：text和html。建议生成html格式的报表，因为可读性非常。用脚本生成awr报表需要以sys用户登录并执行命令。执行过程需要输入报表类型、天数（用来决定显示那几天内的快照(snapshot)）、begin_snap、end_snap、以及报表名称等5个参数。1.AWR产生报表举例(1)推荐采用通过客户端连接实例产生报表有两种方法执行如下命令：通过客户端连接实例或登录到Oracle服务器再连接实例。通过不同的方式登录，产生报表的位置是不同的。1、通过Windows客户端DOS窗口连接实例，产生报表的位置位于默认登录路径，如下：通过Windows DOS窗口登录并执行?/rdbms/admin/awrrpt命令，所产生的报表位于C:目录下2、通过Oracle服务器连接实例，产生报表位于/home/oracle目录下# cd /home/oracle# ls -l awrrpt_2_1_3.html-rw-r-r- 1 oracle oinstall 324609 Oct 16 14:23 awrrpt_2_12_13.html(2)查看产生快照时间点间隔和采集数据保持的天数SQL select snap_interval, retention from dba_hist_wr_control;SNAP_INTERVAL RETENTION+00000 01:00:00.0 +00007 00:00:00.0注意：默认情况下，Oracle数据库每隔1个小时会自动生成1个快照点，并保持7天内的快照点的数据，以便用户分析。(3)执行SQL命令，产生指定快照时间点间隔的数据报表SQL?/rdbms/admin/awrrptCurrent InstanceDB Id DB Name Inst Num Instance- - - -1286484956 ORA 2 ora2/生成实例ora2的统计报表Specify the Report TypeWould you like an HTML report, or a plain text report? Enter html for an HTML report, or text for plain text Defaults to html输入report_type的值:html /报表类型Type Specified: htmlInstances in this Workload Repository schema DB Id Inst Num DB Name Instance Host- - - - -* 1286484956 2 ORA ora2 aix53 1286484956 1 ORA ora1 aix54Using 1286484956 for database IdUsing 2 for instance numberSpecify the number of days of snapshots to choose fromEntering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots.输入num_days的值:3 /天数(用来决定显示那几天内的快照点(snapshot)Listing the last 3 days of Completed SnapshotsInstance DB Name Snap Id Snap Started Snap Levelora2 ORA 1 16 10月2008 12:00 1 2 16 10月2008 13:00 1 3 16 10月2008 14:00 1 4 16 10月2008 15:00 1 5 16 10月2008 16:00 1 6 16 10月2008 17:00 17 16 10月2008 17:15 1/手动生成的快照点 8 16 10月2008 18:00 1 9 16 10月2008 19:00 1 10 16 10月2008 20:00 1 11 16 10月2008 21:00 1 12 16 10月2008 22:00 1 13 16 10月2008 23:00 1 14 17 10月2008 00:00 1 15 17 10月2008 01:00 1 注：以上是根据AWR的默认设置，每小时产生一个快照点数据Specify the Begin and End Snapshot Ids输入begin_snap的值:12Begin Snapshot Id specified:12输入end_snap的值:15End Snapshot Id specified:15Specify the Report NameThe default report file name is awrrpt_2_12_13.html. To use this name, press to continue, otherwise enter an alternative.输入report_name的值:/回车，可以使用缺省值：awrrpt_2_12_13.html2.4关注报表具体内容报表内的参数内容繁多，oracle官方网站搜索，也没有找到文档对如此众多参数的介绍。从存储的角度，个人认为需要关注的内容主要有：Report SummeryCache sizesLoad profileInstance Efficiency Percentages (Target 100%)Shared Pool StatisticsTop 5 Timed EventsIO StatsTablespace IO StatsFile IO Stats2.4.1Load ProfilePer SecondPer TransactionRedo size:1,207.602,906.73Logical reads:18.4544.42Block changes:6.2314.99Physical reads:0.010.02Physical writes:0.400.96User calls:2.425.84Parses:1.393.33Hard parses:0.020.05Sorts:0.801.92Logons:0.040.10Executes:3.919.42Transactions:0.42% Blocks changed per Read:33.74Recursive Call %:85.40Rollback per transaction %:52.36Rows per Sort:11.781.Redo size表示instance每秒/每事务产生的日志大小(单位字节)，可表示数据库任务的繁重与否。如日志量比较大，特别是每个事务的日志量都比较大的时候，需要检查是否有较大的事务在运行。2.Logical reads表示instance每秒/每事务产生的逻辑读，单位是block。3.block changes表示instance每秒/每事务block变化数量，数据库事务带来改变的块数量4.Physical reads表示instance每秒/每事务产生的物理读，单位是block。如果说逻辑读是从内存中获得数据，那么，物理读就是要实实在在的从硬盘或者磁盘阵列中获得数据，这个取决于磁盘和阵列的相应速度。与逻辑读同样，物理读也可能来自于select语句，而不一定仅是来自于事务。5.Physical writes表示instance每秒/每事务产生的物理写，单位是block。这个指标在很大程度上决定了数据库系统的写压力。6.User calls表示instance每秒/每事务产生的用户调用次数。User calls/Executes基本代表了每个语句的请求次数，Executes越接近User calls越好。7.Parses表示instance每秒/每事务解析总次数。包括软分析和硬分析，但是不包括快速分析。近似反应每秒/事务语句的执行次数,软解析每秒超过300次意味着你的应用程序效率不高，没有使用soft parse，建议调整session_cursor_cache。8.Hard parses表示instance每秒/每事务产生的硬解析次数，这在高可用的OLTP环境中应当尽可能避免，应当正确地使用绑定变量避免这样的情况发生。每秒超过100次，就可能说明你绑定使用的不好9.Sorts表示instance每秒/每事务产生的排序次数。10.Executes表示instance每秒/每事务产生的语句执行次数，包括了用户执行的SQL语句与系统执行的SQL语句，表示一个系统SQL语句的繁忙程度。11.Transactions表示instance每秒/每事务产生的事务数，表示一个数据库系统的事务繁重程度。这在不同的系统中，略有差异，如在一个典型的交易系统中（如：证券、银行），可能主要是事务比较多；而在一个网站系统，可能主要是Web查询（select）比较多。12.Rollback per transaction%看回滚率是不是很高，因为回滚很耗资源。如果回滚过高，可能说明你的数据库经历太多的无效操作。过多的回滚可能还会带来Undo Block的竞争。2.4.2Instance Efficiency Percentages (Target 100%)Buffer Nowait %:100.00Redo NoWait %:100.00Buffer Hit %:99.96In-memory Sort %:100.00Library Hit %:98.67Soft Parse %:98.43Execute to Parse %:64.62Latch Hit %:100.00Parse CPU to Parse Elapsd %:32.10% Non-Parse CPU:97.681.Buffer Nowait %在缓冲区中获取Buffer的未等待比率, Buffer Nowait Executions,就可能出现该比小于0的情况,该值0通常说明shared pool设置或效存在问题造成反复解析，reparse可能较严重,或者可是同snapshot有关如果该值为负值或者极低,通常说明数据库性能存在问题。8.Latch Hit %表示内部结构维护锁命中率，通常高于99。其值低是因为shared_pool_size过大或没有使用绑定变量导致硬解析过多。9.Parse CPU to Parse Elapsd %解析实际运行事件/解析实际运行时间。2.4.3IO Stats1.Tablespace IO Statsordered by IOs (Reads + Writes) descTablespaceReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)SYSAUX3202.501.061,283020.00UNDOTBS2000.000.00480000.00SYSTEM2400.421.0065000.002.File IO Statsordered by Tablespace, FileTablespaceFilenameReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)SYSAUX/dev/rorasysaux3202.501.061,283020.00SYSTEM/dev/rorasystem2400.421.0065000.00UNDOTBS2/dev/roraundotbs200480000.003对AWR报表内容进行分析的ADDM工具3.1ADDM概述现在Oracle Database 10g中推出的这个引擎称为自动数据库诊断监控程序既ADDM(Automatic Performance Diagnosis)。为了作出决策，ADDM使用了由AWR采集的数据。在AWR进行的每一次快照采集之后，调用ADDM来检查量度并生成建议。因此，实际上拥有了一个一天二十四小时工作的自动数据库管理员，它主动地分析数据并生成建议，从而把DBA解放出来，使能够关注更具有战略意义的问题。缺省情况下，在安装Oracle 10g RAC软件时,即安装EM 10g（Oracle Enterprise Manager）,在概念上它与以前版本的不同之处在于，它不是客户端安装的工具；实际上它是位于数据库服务器本身上的HTTP服务器（称为DB控制台）。（参见图）您可以使用任何浏览器查看EM界面。3.2ADDM的使用方法3.2.1在所有Oracle服务器上确认EM的状态，并启动EM进程1.登录所有Oracle服务器，查看EM的运行状态#su - oracle$emctl status dbconsoleOracle Enterprise Manager 10g Database Control Release .0Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.http:/aix53:1158/em/console/aboutApplicationEM Daemonis notrunning./ EM没有运行-Logs are generated in directory /oracle/OraHome/aix54_ora1/sysman/log2.如果没有运行，登录所有Oracle服务器开启EM进程$ emctlstartdbconsoleOracle Enterprise Manager 10g Database Control Release .0Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.http:/aix53:1158/em/console/aboutApplicationStarting Oracle Enterprise Manager 10g Database Control . started.-Logs are generated in directory /oracle/OraHome/aix54_ora1/sysman/log3.2.2通过IE浏览器，登录EM界面1.可通过如下方法查看端口号$ pwd/oracle/OraHome/install$ more portlist.iniiSQL*Plus HTTP port number =5560Enterprise Manager Console HTTP Port (ora) = 1158Enterprise Manager Agent Port (ora) = 3938从这个文件中我们了解到，数据库ora的代理程序监听端口3938，而EM控制台监听1158。2.在IE浏览器内输入以下URL来调用EM登录画面输入http:/aix53:1158/em/console/如果不能弹出界面，请用实际IP地址登录如下：53:1158/em/console/。该URL调出登录画面，从中您可以用DBA用户登录。(1)登录界面(2)主目录显示Oracle数据库大致运行信息如：从GUI上看出“有问题的表空间”有“1”个，点击“1”可进一步查看是哪个表空间出现问题。3.3ADDM查询和诊断到的问题有那些3.3.1查看集群中Oracle服务器的硬件配置1.进入“集群数据库：ora”的“首目录”2.选择红框内的“集群：crs”并进入3.选择红框内“主机”数据，并进入4.选择“硬件：IBM_9110_510 powerpc”查看硬件信息或选择“操作系统详细资料”查看软件信息信息显示如下3.3.2CPU的瓶颈通过查看主页面内的：主机CPU来实时监测CPU的状态和瓶颈。3.3.3内存结构上的瓶颈？1.进入“集群数据库：ora”的“首目录”2.选择红框内的“集群：crs”并进入3.选择“性能”标签，并选择具体主机并查看“内存详细资料”4.详细信息如下从以上可以很明显的看出：内存存在瓶颈。已经使用了90.35%。3.3.4内存参数SGA、PGA查看和分析1.进入“集群数据库：ora”的“首目录”2.拉到最底端，选择“指导中心”连接3.在进入“内存指导”连接4.选择具体实例查看内存参数，并选择“继续”5.显示具体实例的PGA和SGA的具体内容3.3.5I/O是否存在性能瓶颈? (I/O子系统是否工作正常) ?1.进入“集群数据库：ora”的“首目录”2.选择红框内的“集群：crs”并进入3.选择“性能”标签，并选择具体主机并查看“磁盘详细资料”4.详细信息如下查看磁盘设备的写入的速度和磁盘使用率，来确认是否是磁盘存在瓶颈。3.3.6高负载的SQL语句(是否存在消耗过量系统资源的SQL) ?1.进入“集群数据库：ora”的“首目录”2.选择“SQL优化指导”并进入3.选择具体实例查看内存参数，并选择“继续”4.选择“周期SQL”5.查看SQL语句的使用情况，根据“执行占用时间”查看哪个SQL语句高负荷3.4ADDM分析的结果ADDM分析的结果以一些Finding的样式来表达。每个Finding都属于以下三种类型之一：1.问题:描述了导致数据库性能问题的根源。2.征兆:包含了可能导致其他问题的信息。3.信息:报告其他没有问题的模块。4总结AWR报表是针对实例的，多个实例需要多个AWR报表，从而了解到每个实例在应用环境的状态。AWR报表的