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

ORACLE性能诊断AWR 报告 软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载 的使用和分析为满足业务的运行要求，高性能要求是目前IT系统普遍面临的最棘手问题，尤其是客户面对着目前越来越庞大系统和数据，系统整合、数据大集中似乎成了趋势。针对系统性能优化的诊断和分析，数据库方向又是其中的重要一环，本文将针对ORACLE中常用的性能诊断工具AWR报告，进行分析说明。ORACLE性能诊断工具ORACLE数据库的性能的诊断工具有很多种，在9i之前主要通过手工进行采集分析，例如使用动态视图和Statspack报告来获取数据库性能状态信息，10g以后ORACLE数据库的性能诊断和改进建议越来越自动化，不过能够熟悉并掌握ORACLE的相关性能诊断工具的使用，仍对性能问题的准确和有效处理提供有利的帮助。以下是ORACLE中常用的一些分析工具。动态性能视图动态性能视图是ORACLE中最常用，也是最简单的一种工具。无论何种性能问题，都能在动态性能视图中找到线索，不过仅10g中动态性能视图就高达几百个，每个视图都包括很多诊断信息，想在众多的视图中找到问题的根源，也是一件费力的事情。一般常用的动态性能视图有：v$session、v$session_wait、v$process、v$sql、v$lock、v$latch、v$sysstat、v$system_event、v$sgastat。Statspack报告statspack是Oracle9i之前使用的一个数据库收集工具，收集了数据库全面信息，包括负载概览、前五个等待事件、高速缓存的大小、共享池中SQL语句、 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 空间和文件I/O、库高速缓存、SGA统计等。AWR和ADDM报告AWR是10g以后提供的一个新工具，Oracle建议用户用这个取代Statspack，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题，并自动生成ADDM（自动数据库诊断监控）报告，为用户提供数据库性能诊断分析建议。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报告的步骤如下：sqlplussys/sys@127.0.0.1/scmisassysdbaSQL>@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-2011-10-29.html（注：报告生成的名称和位置）AWR报告分析AWR报告头记录了数据库名称和起始快照时间，报告头中主要分析Elapsed（总时间）和DBTime（DB消耗的时间，不包括后台进行的消耗时间），如果DBTime/Elapsed比值较大，说明数据库系统压力较大，例如下图中系统包括16CPU(2*8核)，每个cpu耗时26.7min，负载为26.7/60.03=44.5%，说明数据库服务器存在较大的负荷。即:427.44/60.3*16*100%=44.5%1、 sessions表示采集是实例连接的会话数，这个数可以让我们了解数据库并发用户的大概情况。如果是新接手的数据库，对判断数据库的类型可以做参考2、 Cursors/Session，平均每个会话卡开的游标数。3、 DBTime4、 这个数值比较重要，它表示用户操作花费的时间，包括cpu和等待事件。有时候DBTime会比Elapsed时间要长。因为AWR是一个数据的合集，比如说1分钟内一个用户等待10秒钟，那么10个用户是300秒（5分钟）；cpu的时间也是一样一分钟之内，一个cpu处理30秒，那么4个cpu就是1.2分钟，8个就是2.4分钟，这些都以累计的方式记录在awr报告当中的。AWR报告总览包括了五个部分：缓存尺寸（CacheSizes）、负载性能（LoadProfile）、数据库效率（InstanceEfficiencyPercentages）、共享池统计（SharedPoolStatistics）、TOP5事件（Top5TimedEvents）。这五个部分也就是整个报告核心，记录了数据库系统的关键性能参数和状况。缓存尺寸（CacheSizes）主要记录总的缓存大小BufferCache和SGA缓存尺寸SharedPoolSize，SGA是ORACLE中非常重要的内存共享区，对系统内的所有进程都是共享的。当多个用户同时连接到一个例程时，所有的用户进程、服务进程都可以共享使用这个SGA区。Sharedpool可以分为库缓存（librarycache）和数据字典缓存（dictionarycache）。Librarycache存放了最近执行的SQL语句、存储过程、 函数 excel方差函数excelsd函数已知函数     2 f x m x mx m      2 1 4 2拉格朗日函数pdf函数公式下载 、解析树以及执行计划等。而dictionarycache则存放了在执行SQL语句过程中，所参照的数据字典的信息，包括SQL语句所涉及的表名、表的列、权限信息等。负载性能（LoadProfile）这个部分记录了数据库负载情况，绝对值的分析意义不大，需要与之前的基线数据比较才具有更多的意义，单个的报告数据只说明应用的负载情况，绝大多数据并没有一个所谓“正确”的值。其中重要的几个对于Logons大于每秒1~2个，表明可能有争用问题；对于Hardparses大于每秒100，parses大于每秒300，表明硬解析太多，SQL重用率不高，需要解决SQL代码变量绑定问题，并调整共享池参数、调整cursor_sharing参数；对于Sorts大于每秒100，表明排序过多，需要减少SQL代码中排序操作，或调整排序空间。Logons:Logonsshowhowmanyusersareloggedontothedatabasepersecond这个表里应该注重：1）logicalreads和physicalreads，同时也可以得到平均每个逻辑读导致多少物理读，即19.1/37410.4=0.05%。平均每个事务产生了9040.68个逻辑读，这个数字应该越小越好。2）parses 和hardparses：从表中可以看到cpu平均每秒进行了81.24个解析（超过100个应该注意），每秒进行5.39（超过10个应该注意）次硬解析，即cpu每秒要处理5.39个全新的sql。数据库效率（InstanceEfficiencyPercentages）记录了Oracle关键指标的内存命中率及数据库实例其它操作的效率，这个部分反应了数据库中最重要指标的命中率。缓冲区未等待率(buffernowait%)：指在缓冲区中获取buffer的未等待比率。该指标的值应接近100%，如果该值较低，则可能要增大buffercache,，不应该低于99%。redo缓冲区未等待率(redonowait%)：指在redo缓冲区获取buffer的未等待比率。该指标的值应接近100%，如果该值较低，则有2种可能的情况：1）onlineredolog没有足够的空间；2）log切换速度较慢。缓冲区命中率(bufferhit%)：指数据块在数据缓冲区中的命中率。该指标的值通常应在90%以上（不应该低于99%），否则，需要调整。如果持续小于90%，可能要加大db_cache_size。但有时，缓存命中率低并不意味着cache设置小了，可能是潜在的全表扫描降低了缓存命中率。内存排序率(in-memorysort%)：指排序操作在内存中进行的比率。该指标的值应接近100%，如果指标的值较低，则表示出现了大量排序时的磁盘i/o操作，可考虑加大sort_area_size参数的值。共享区命中率(libraryhit%)：该指标主要代表sql在共享区的命中率。该指标的值通常应在95%以上，否则需要考虑加大共享池（修改shared_pool_size参数值），绑定变量，修改cursor_sharing等参数。软解析的百分比(softparse%)：该指标是指oracle对sql的解析过程中，软解析所占的百分比。该指标的值通常应在95%以上，如果低于80%，那么就可能sql基本没被重用，sql没有绑定变量，需要考虑绑定变量。闩锁命中率(latchhit%)：指获得latch的次数与请求latch的次数的比率。该指标的值应接近100%，如果低于99%，需要分析闩锁竞争，明确是应用锁、数据字典锁、内存控制锁的哪一种。通过进一步分析LatchStatistics章节或动态性能视图v$session_wait,v$latch,v$latch_children。sql语句执行与解析的比率(executetoparse%)：指sql语句执行与解析的比率。该指标的值应尽可能到高，如果过低，可以考虑设置session_cached_cursors参数。%Non-ParseCPU:说明花费在十几工作的时间和花费在解析上的时间的对比executetoparse%，说明sql语句执行与解析的比率共享池统计（SharedPoolStatistics）记录了在采集点时刻，共享池（sharepool）内存被使用的比例。这个指标的值应保持在75%~90%,如果这个值太低，就浪费内存，如果太高，会使共享池外部的组件老化，如果sql语句被再次执行，则就会发生硬分析。其中执行次数大于1的sql比率（SQLwithexecutions>1），如果此值太小，说明需要在应用中更多使用绑定变量，避免过多SQL解析。MemoryUsage，说明在sharedpool中，被使用的部分占sharedpool总尺寸的百分比。这个值应保持适中，(如85%）,如果太高，则会引起sharedpool中的对象被刷出内存，从而导致sql语句的硬解析增加，太低则浪费内存；SQLwithexecutions>1，执行次数大于1次的sql占总sql数的百分比，越大越好；MemoryforSQLw/exec>1，在sharedpool中执行次数大于1次的sql语句所消耗的内存占sharedpool的百分比TOP5事件（Top5TimedEvents）这个部分也是AWR报告中非常重要的部分，从这里可以看出等待时间在前五位的是什么事件，基本上就可以判断出性能瓶颈在什么地方。通常，在没有问题的数据库中，CPUtime总是列在第一个，其他几类重要影响性能的事件分析如下。缓冲区忙(bufferbusy)：当一个会话想要访问缓存中的某个块，而这个块正在被其它会话使用时，将会产生该等待事件。这时候，其它会话可能正在从数据文件向缓存中的这个块写入信息，或正在对这个块进行修改。出现这个等待事件的频度不应大于1%。如果这个等待事件比较显著，则需要根据等待事件发生在缓存中的哪一块（如字段头部、回退段头部块、回退段非头部块、数据块、索引块等），采取相应的优化方法。文件分散读取(dbfilescatteredread)：该等待事件通常与全表扫描有关。因为全表扫描是被放入内存中进行的进行的，通常情况下它不可能被放入连续的缓冲区中，所以就散布在缓冲区的缓存中。如果这个等待事件比较显著，可能说明对于某些全表扫描的表，没有创建索引或没有创建合适的索引。尽管在特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。文件顺序读取(dbfilesequentialread)：该等待事件通常与单个数据块相关的读取操作有关。如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，或者可能不合适地使用了索引。对于大量事务处理、调整良好的系统，这一数值大多是很正常的，但在某些情况下，它可能暗示着系统中存在问题。应检查索引扫描，以保证每个扫描都是必要的，并检查多表连接的连接顺序。另外db_cache_size?也是这些等待出现频率的决定因素。队列(enqueue)：队列是一种保护共享资源的锁定机制。该锁定机制保护共享资源，如记录中的数据，以避免两个人在同一时间更新同一数据。如果enqueue等待事件比较显著，则需要根据enqueue等待类型，采取相应的优化方法。闩锁释放(latchfree)：latch是一种低级排队机制(它们被准确地称为相互排斥机制),用于保护系统全局区域(sga)中共享内存结构。该等待事件意味着进程正在等待其他进程已持有的latch。对于常见的latch等待通常的解决方法：1）sharepoollatch：在oltp应用中应该更多的使用绑定变量以减少该latch的等待。2）librarycachelatch：同样的需要通过优化sql语句使用绑定变量减少该latch的等待。日志文件同步(logfilesync)：这个等待事件是指当一个会话完成一个事务（提交或者回滚数据）时，必须等待lgwr进程将会话的redo信息从日志缓冲区写到日志文件后，才能继续执行下去。这个等待事件的时间过长，可能是因为commit太频繁或者lgwr进程一次写日志的时间太长（可能是因为一次logiosize太大），可调整_log_io_size。waitforaundorecord：数据库恢复HYPERLINK"http://blog.csdn.net/aicon/article/details/6283770"readbyothersessionREADBYOTHERS  SESSIONS的根本原因就是因为你某条SQL做了大量block的扫描,我猜想那条SQL至少要50万个逻辑读. 除了解决SQL问题,基本没有别的办法TimeModelStatisticsStatisticNameTime(s)%ofDBTimesqlexecuteelapsedtime85,698.4999.38DBCPU26,832.0231.12parsetimeelapsed369.050.43hardparseelapsedtime324.190.38failedparseelapsedtime109.550.13sequenceloadelapsedtime62.490.07PL/SQLexecutionelapsedtime17.780.02hardparse(sharingcriteria)elapsedtime7.540.01PL/SQLcompilationelapsedtime1.420.00connectionmanagementcallelapsedtime0.490.00hardparse(bindmismatch)elapsedtime0.130.00repeatedbindelapsedtime0.120.00DBtime86,229.43 backgroundelapsedtime1,211.05 backgroundcputime46.42 Sqlexecuteelapsed time  数据库执行SQL总时间parse time elapsed 解释SQL总时间hard parse elapsed time 硬解释SQL的总时间PL/SQL execution elapsed time pl/sql执行时间DBCPU用户占用CPU的总时间failed parse elapsed time 遇到SQL解释时间SQL统计（SQLStatistics）AWR报告中还有一块对性能影响最大的指标，TOPSQL统计。本节按各种资源分别列出对资源消耗最严重的SQL语句，并显示它们所占统计期内全部资源的比例，提供给我们调优依据。SQLorderedbyElapsedTime：记录了执行总和时间的SQL，记录的是监控范围内该SQL的执行时间总和，需要综合分析CPU时间（CPUTime）和执行次数（Executions）才能得到单个SQL的代价。单次执行开销较大的SQL属于重点优化之列。SQLorderedbyCPUTime：记录了执行占CPU时间总和时间最长的SQL，再CPU消耗较大的系统中，重点优化此类SQL。SQLorderedbyGets：记录了执行占总buffergets(逻辑IO)的SQL，查找总的缓冲区获取比较高的SQL，并根据平均每次执行缓冲区获取的数量判断优化的余地有多大。优化这些SQL，有助于减少CPU开销以及数据缓冲池相关的闩锁竞争。SQLorderedbyReads：记录了执行占总磁盘物理读(物理IO)的SQL，查找总的物理读比较高的SQL，并根据平均每次执行物理读的数量判断优化的余地有多大。优化这些SQL，有助于减少I/O开销和CPU开销。SQLorderedbyExecutions：记录了按照SQL的执行次数排序的SQL，执行次数多的SQL也是需要重点优化，使sql语句中的子操作执行次数尽量少。SQLorderedbyParseCalls：记录了解析次数排序的SQL，避免出现硬解析，采用使用绑定变量等方式。SQLorderedbySharableMemory：记录了SQL占用librarycache的大小的SQL。SQLorderedbyVersionCount：记录了SQL的打开子游标的SQL。SQLorderedbyClusterWaitTime：记录了集群的等待时间的SQL。IOStats-->TablespaceIOStatsTablespaceReadsAvReads/sAvRd(ms)AvBlks/RdWritesAvWrites/sBufferWaitsAvBufWt(ms)SYSAUX9,55304.071.6519,729000.00UNDOTBS7,87903.211.008,2520205.50SYSTEM2,49604.741.624,469000.00USERS36403.081.574000.00TEMP3403.2412.3525000.00TEST24047.501.004000.001）可以找到具有频繁读写活动的表空间或数据文件，如果临时表空间的写入数量最高，说明排序太多太大；2）从AVGBLKS/RD列可以看出，哪些表空间上经历了最多的全表扫描，如果值大于1，则应该将该值与初始化参数db_file_multiblock_read_count的值进行比较，如果他们越接近，说明在该表空间上进行的大部分是全表扫描；3）检查AVRD(MS),该列表明I/O读的时间，值应该小于20ms，如果过大应该检查是否将读写很频繁的文件放在了同一个磁盘上SegmentStatistics1）SegmentsbyLogicalReads或SegmentsbyPhysicalReads 可以找到逻辑读或物理读比较大的对象，并查找原因，是否可以通过创建新索引、或采用分区表等来降低对象的逻辑读以及物理读；2）SegmentsbyRowLockWaits ，通过这个报表可以找到获得行级锁最严重的对象，需要跟开发人员探讨解决方法；3）SegmentsbyITLWaits ，这个报表可以标明获得ITL等待最严重的对象，如果发现了ITL等待很严重的对象，则应该将对象的initrans参数设置为并发操作该对象的进程个数；4）SegmentsbyBufferBusyWaits，获得bufferbusywaits最严重的对象。在同一时刻只有一个进程能够访问同一个数据块，其它进程必须等待。解决的关键是优化那些扫描了过多数据块的sql语句，减少他们要扫描的数据块。如果已经优化了sql语句，则可以考虑增大pctfree的值，从而减少一个数据块中能够包含的数据行数，从而将对象的数据行分部到更多的数据块里去。InstanceActivityStatistics实例活动统计数据比较在内存中和磁盘中的排序量，如果磁盘排序太高就需要增加PGA_AGGREGATE_TARGET(或者旧版本中增大SORT_AREA_SIZE)如果磁盘的读操作较高，表明可能执行了全表扫描，如果目前存在大量的较大的对较大表的全表扫描，就应当评估最常用的查询并通过增加索引来提高效率。大量的非一致性读操作意味着使用了过多的索引或者使用了非选择性索引。如果脏读缓冲区数量高于所请求的空闲缓冲区的数量（超过5%），那么说明DB_CACHE_SIZE太小，或者没有建立足够多的检查点。如果叶节点的分裂数量很高可以考虑重建已增长或已经碎化的索引。consistentgets:没有使用selectforupdate子句的查询在缓冲中访问的数据块数量，这个数量加上DBBLOCKGETS统计信息的值就是逻辑读操作总数DBBLOCKGETS:使用了INSERTUPDATEDELETEORSELECTFORUPDATE语句在缓存中访问的数据块数量。PHYSICALREADS:没有从缓存中度取得数据量。可以从磁盘，操作系统缓存或者磁盘缓存中读取，以满足SELECT,SELECTFORUPDATE,INSERT,UPDATE,DELETE语句LOGICALREADS=CONSISTENTGETS+DBBLOCKGETS缓存命中率HITRATIO=(LOGICALREADS-PHYSICALREADS)/LOGICALREADS*100%        =(CONSISTENTGETS+DBBLOCKGETS-PHYSICALREADS)/(CONSISTENTGETS+DBBLOCKGETS)*100%缓存命中率应该高于95%，否则需要增加DB_CACHE_SIZE。DIRTYBUFFERSINSPECTED:从LRU列表中清除掉的脏读（经过修改的）数据缓冲区的数量，如果此值超过0，可以考虑增加DB_WR进程。ENQUEUETIMEOUTS:请求入列的次数（锁定），以及所请求的特定队列不可用的次数。如果这个统计信息大于0，就需要调查锁定问题。FREEBUFFERINSPECTED:由于是脏读数据、被固定或者正忙等原因儿跳过的缓冲区数量。如果数量很大的话就说明缓冲区缓存太小了。PARSECOUNT:一条SQL语句被解析的次数。RECURSIVECALLS:数据库中递归调用的数量。如果某个进程中的递归调用数量大于4，就应当检查数据字典缓存的命中率，以及是否有表或者索引的范围过大。除非使用了大量PL/SQL，否则在用户调用中，递归调用所占的比例应该低于10%。REDOSIZE:写入日志中，以字节为单位的重做信息的数量。该信息将有助于确定重做日志的大小。SORTS(DISK):磁盘排序的数量。磁盘排序除以内存排序数量不应该高于5%.否则需要调整SORT_AREA_SIZE,PGA_AGGREGATE_TARGET的大小注意：SORT_AREA_SIZE分配的内存是面向每个用户的，PGA_AGGREGATE_TARGET分配的内存是面向所有会话的。SORTS(MEMORY):在内存中排序的数量。SORTS(ROWS):参加排序的数据行的数量。TABLEFETCHBYROWID:通过访问ROWID访问的数据行的数量。该值很高通常意味着就获取数据的操作而言，应用程序调整的不错。TABLEFETCHCONTINUEDROW:获取的数据行的数量，可以是链化数据行，也可以是迁移的数据行表空间和文件I/O统计数据对于带缓存的磁盘I/O时间通常少于1ms.在init.ora文件中可以设置参数DB_FILE_MULTIBLOCK_READ_COUNT有助于磁盘读取时间，该参数控制在全表扫描时一次I/O中读入的数据块数量，这将减少扫描一张表所需的I/O数量，从而提高全表扫描的性能。但是，设置该参数的结果是优化器可能会执行更多的全表扫描，所以需要将OPTIMIZER_INDEX_COST_ADJ设为一个值，例如10，来消除这个问题，并且驱动索引的使用。  数据字典和库缓存的统计数据如果报表中PCTMISS值很高，你应当提高应用程序中游标的共享程度或者增加共享池的尺寸。 AWR报表和STATSPACK输出结果中首先需要查看的10项内容1）首要的5个等待时间；2）负载简档；3）实力效率和命中率；4）等待事件；5）闩锁等待；6）首要的SQL;7）实例活动；8）文件I/O和段统计数据；9）内存分配；10）缓冲区等待；具体案例分析DBTime>内核数*Elapsed的时间如果DBTime>内核数*Elapsed的时间，说明数据库负载非常严重，可通过TOPEVENT和LOADPROFILE等定位瓶颈