awr分析报告详解.docx

awr分析报告详解.docx

《awr分析报告详解.docx》由会员分享，可在线阅读，更多相关《awr分析报告详解.docx（17页珍藏版）》请在冰豆网上搜索。

awr分析报告详解

Awr-Oracle10g新特性—工作量自动收集-性能调优

当数据库发生了性能问题时，如何去定位？

比较常用的方法是采用一个既定的模式：

解决诸如“是不是同一问题的再现？

”、“是否在某一特殊时间段发生？

”、“两个问题之间是否存在联系？

”等问题，这样通常能得到一个比较好的诊断结果。

作为一个DBA，你可能使用一个第三方或者自己开发的工具来收集数据库运行期间的精细统计数据，并从中得到性能度量数据。

你需要将这些发生问题时的度量数据与当前数据进行比较。

重现以前的时间能使现在的问题变得明朗。

因此，持续的收集相关统计数据对于性能分析来说十分重要。

在某些情况下，在解决收集统计数据这方面的问题上有自己内置的工具——statspack。

尽管在某些情况下的作用非常大，但它缺乏解决性能问题所必须的健壮性。

提供了一个标志性的改进特性：

自动工作量存储（AutomaticWorkloadRepositoryAWR）。

AWR是随着数据库一起被安装的，它不仅能收集统计数据，还能从统计数据中分析出度量数据。

通过运行$ORACLE_HOME/rdbms/admin目录下的awrrpt.sql脚本可以生产AWR从统计和度量数据中分析报告。

这个分析报告最能体现出AWR的性能分析能力。

这个脚本看起来很像statspack，它会列出所有可用的AWR快照并要求输入两个特定的快照编号作为一个间隔段。

它能产生两种类型的输出：

文本格式（除了AWR统计信息外和statspack报告基本类似）和默认的格式（通过超连接等方式来提供一个友好的界面）。

下面来运行以下这个脚本，对它产生的分析报告及AWR的性能分析能力做一个认识。

AWR的使用

首先来了解一下AWR是如何设计的，并了解一下它的构造。

基本上来说，AWR应该是一个Oracle用来收集性能相关统计数据并从中得出性能度量数据来追踪潜在问题的内置工具。

和statspack不一样，AWR的快照信息是由一个新的后台进程MMON及其线程来每隔一个小时自动收集的。

为了节省空间，这些收集的数据会在7天后自动清除。

快照收集的频率和保留时间都是可以被用户修改的。

可以通过以下脚本查看当前的设置：

SQL>selectsnap_interval,retentionfromdba_hist_wr_control;

SNAP_INTERVALRETENTION

--------------------------------------

+0000001:

00:

00.0+0000700:

00:

00.0

这个结果表明当前的快照是每隔一个小时收集一次，并且会被保留7天。

要改变这个设置，比如需要设置成每隔半小时收集一次，并且只保留3天，可以使用以下语句（参数的单位都是分）：

SQL>begin

2dbms_workload_repository.modify_snapshot_settings（

3interval=>30,

4retention=>3*24*60

5）;

6end;

SQL>selectsnap_interval,retentionfromdba_hist_wr_control;

SNAP_INTERVALRETENTION

--------------------------------------

+0000000:

30:

00.0+0000300:

00:

00.0

oracle10G性能调优

2011-01-0421:

18

了解使用Oracle10g新顾问程序的方法，消除调优工作中的猜测。

"自动"一词似乎已经毫无限制地应用于Oracle数据库10g的许多新特性当中了--如自动数据库诊断监测器（ADDM）、自动工作量信息库（AWR）、自动空间管理和自动SQL调优。

但是，先不要认为Oracle正在设法让我们失业，应把"自动"理解为"自动驾驶仪"，而不是"自动开罐器"。

没有人会仅仅因为飞机上的仪器内置了某些智能而使其能在高空飞行，就建议将机长从驾驶舱撤出。

同样，对于数据库调优，即使是专家也可能使用一些智能性建议。

我们都已采用TKPROF、ExplainPlan和Statspack等工具来确保获得最佳性能。

我们已经重新运行统计、删除统计、修改init.ora参数、创建索引、删除索引、重新编写SQL，采用了各种方法，以寻求更好的性能。

利用数据库管理员的技巧包来找出问题点的解决方案固然有其优点，但这也是重复而耗时的。

Oracle数据库10g内置的自动调优功能不仅提供了这些功能，还提供了更多功能，并达到了极致--性能最佳的数据库--运行速度极快。

这都基于Oracle数据库该版本所内置的新的智能型基础架构。

智能型基础架构

Oracle数据库10g综合的智能型基础架构可对整个数据库进行探测，使数据库能够在运行过程中对其自身进行监测和诊断，并通知数据库管理员出现了问题，数据库管理员便可采取有效的纠正措施。

简要地说，Oracle数据库10g中这种新的智能型基础架构的几个关键组件包括AWR、ADDM和一个"自动顾问程序"（automaticadvisors）数组，它们可以使数据库管理员避免许多猜测和重复劳动。

简单地说，AWR包含了Statspack所提供的功能，还会汇总大量新的统计数据。

AWR会收集、处理和维护性能统计数据（默认情况下，AWR每60分钟进行一次数据快照），用于问题检测和自我调优，并将所收集的数据存储在可由ADDM来分析的数据库中。

ADDM蕴含了Oracle公司内外许多Oracle专家的集体智慧，可为有效管理和诊断数据库性能提供基础性的知识和分析。

它进行"根本原因"（root-cause）分析，并跨几种重要的数据库对象类（如应用程序、模式和内存利用率）提出具体建议。

例如，对于一个特定的模式对象，ADDM要确定"对数据库块的读写争用耗费大量的数据库时间，"并给出此结论的报告（在通过命令行生成的ADDM报告中或通过Oracle企业管理器［OEM］控制台进行）。

这样一个结论的详细信息中可能还会包括这样一个事实：

向需要空闲列表的表中插入数据存在着一种高级插入方式。

同时会建议：

"考虑在一个本地管理的表空间中使用Oracle的自动段空间管理……"建议还可以包括：

对数据库资源消耗多于共享的SQL语句运行特定的顾问程序对话。

在以后的专栏中，我将探讨ADDM、AWR及其他一些利用了这一新基础架构智能的新顾问程序。

优化器改进

在这批新工具和智能型基础架构中，数据库管理员能最快享受到的好处之一是快速而轻松地调优SQL语句。

SQL调优顾问程序使你可以在不修改源代码的情况下调优SQL语句。

这一特性很有用，尤其是对打包的应用程序，例如，当你等待厂家的补丁程序时，但是它也可以用于任何SQL的调优（例如，通过游标高速缓存，或指定一个SQL文本串）。

在进行详细讨论之前，我们先简要地看一看这一特殊顾问程序（明确地讲是优化器）所依赖的隐含功能的概况。

一般来讲，OracleSQL性能的核心是Oracle基于成本的优化器（CBO），该组件对获得数据的可能途径进行评估，并从许多可能的备选方案中生成最佳执行计划。

执行计划定义了Oracle数据库执行语句所用的步骤组合；它们包括语句所访问的每个表的访问方法以及这些表的排序（连接顺序）。

优化器可确定执行特定SQL语句的最有效方式。

对于任一特定的SQL语句，指定其有效的可选方式的可能数目后，优化器会对它们进行快速评估，在一秒钟之内生成一个执行计划。

除了优化器的这一所谓"正常"模式外，Oracle数据库10g现在还提供一种"调优"模式（在Oracle文献中有时称作"自动调优优化器"）。

正如其名称意义所示，优化器的调优模式明确地用于SQL调优对话（使用SQL调优顾问程序和SQL访问顾问程序），以生成可在运行时加速性能的附加信息。

调优模式包含了正常模式的性能，同时还提供扩展功能，因此它能够在创建执行计划的过程中进行进一步的分析。

在调优模式下，优化器进行四个关键级别的分析，生成可以为优化器返回SQL语句结果提供附加信息的统计数据：

SQL统计数据分析。

优化器会检查缺少或陈旧的统计数据，并给出适当的建议--例如，为指定的数据库对象收集统计数据--以确保生成最佳执行计划。

（优化器还会生成附加信息，如果建议的措施未被采用，这些信息会存储在一个在运行时使用的SQL附加信息集合中）。

SQL附加信息集合。

优化器会进行更广泛的分析，并汇总可以使查询运行更为理想的必要附加信息，将其存储在一个SQL附加信息集合中。

SQL附加信息集合包含的信息可以使SQL编译器对特定的SQL文本的执行计划进行优化。

SQL附加信息集合在运行时使用（当优化器返回正常模式时），用于提高SQL的性能，但不改变源代码。

SQL访问分析。

优化器对访问方式进行分析，核查索引是否处于最佳使用状态，如果不是，则建议适当地创建索引，以提供更快的访问方式。

（可以单独运行不同的SQL访问顾问程序工具来汇总所有访问结构的建议--特别是物化视图、物化视图记录和整个SQL工作量的索引。

在以后的专栏中我会介绍这一工具。

）

SQL结构分析。

当优化器构建执行计划、给出提高性能的建议时，它会分析SQL语句的结构（语义、语法和设计），生成大量的注释和诊断。

例如，为了显著提高性能，优化器可能会建议用NOTEXISTS代替NOTIN，尽管NOTEXISTS在语义上与NOTIN不同，但它们所产生的结果相同。

（不过，只有在该查询的相关连接列中没有NULL值时，你才应该这样更改--这就是SQL调优顾问程序让你来决定是否采用结构分析所生成建议的原因。

）

如果在"综合"模式下运行SQL调优顾问程序，四个分析都会进行；但是SQL统计数据分析、SQL访问分析和SQL结构分析只在"有限"模式下进行--不生成SQL附加信息集合。

如果你想调优应用程序代码，如含有打包应用程序的代码，你应使用综合模式，以确保获得SQL附加信息集合。

优化器的调优模式在SQL调优顾问程序和SQL访问顾问程序对话期间使用。

在综合模式下使用SQL调优顾问程序来为一个或多个SQL语句生成SQL附加信息集合可能会花很长时间--优化器在忙于汇总和生成附加统计数据和注释。

不过，通过更改默认设置值（30分钟），可以限制优化器花在特定调优任务上的时间。

不过，在调优SQL语句，尤其是在处理打包应用程序（其SQL源代码不能直接控制）时，使用SQL附加信息集合将提供极大的好处。

SQL调优顾问程序可以在OEM基于Web的新控制台接口内的很多地方启动，也可以通过DBMS_SQLTUNE包用（AQL*Plus）命令行来进行访问。

要使用SQL调优顾问程序（或任何其他顾问程序），都需要具有ADVISOR（顾问程序）权限（Oracle数据库10g版本最新提供；默认情况下，数据库管理员具有ADVISOR权限）。

ADDM：

Oracle10g诊断功能的中枢

新的自管理Oracle数据库的一个创新就是诊断自身性能问题的能力。

Oracle数据库10g中包括一个内置于数据库核心的自诊断引擎，叫做自动数据库诊断监测器（ADDM）。

ADDM以较短的定期间隔（默认为30分钟）自动监测数据库状态，提供持续的数据库性能诊断。

ADDM（以及顾问程序）中的很多数据都会根据相应的数据类型显示为图形--如按时间绘制的线图、条形图、饼状图，因此这些信息一目了然。

除了查看主动ADDM分析的结果之外，还可以使用OEM的PL/SQL接口通过OEM或命令行手动运行ADDM。

ADDM对潜在的瓶颈进行自顶向下的分析，得出包括根本原因和带有原理阐述建议的一组分析结果。

除了识别问题以外，ADDM还对每个问题对系统总体性能有多大影响及解决该问题后会得到多少好处进行报告。

这种影响－好处分析有助于数据库管理员将精力集中在那些解决后能获得最大性能收益的问题上。

主动调优对话期的调优步骤

无论是调优单个语句还是调优具有不同来源（ADDM、AWRTopSQL或二者组合）的一系列语句，所有的调优活动都是以创建调优任务为起点的。

对于打包应用程序，占用太多资源的SQL代码很可能会显现在由特定SQL_ID标识的OEMTopSQL页中（在Oracle数据库10g中，每个SQL语句现在都由一个SQL_ID来标识）。

以下是从命令行使用DBMS_SQLTUNE包来调优SQL语句的方法。

步骤1创建调优任务以标识SQL语句。

在本例中，SQL文本是指使用绑定变量的语句。

create_tuning_task（

sql_text=>'select*fromempwhere

emp_id=:

bnd',

bind_list=>

sql_binds（anydata.ConvertNumber（100））,

user_name=>'scott',

scope=>'comprehensive',

time_limit=>60,

task_name=>'my_sql_tuning_task',

description=>'tasktotuneaqueryon

aspecifiedemployee'）;

因为本例任务中的时限被定为60，所以我们允许优化器用整整一分钟时间来进行分析。

还要注意作用域参数的"综合"设置，它意味着由优化器进行、用以提高性能的任何附加分析都可以在SQL附加信息集合中获得。

要调优打包应用程序中的SQL，需要找出其SQL_ID［如果它引出了问题（例如），就可以在OEM的TopSQL页找到它，或通过查询新框架（SQL_ADVISOR_%）的表］，并按下面代码所示创建调优任务：

create_tuning_task（sql_id=>'q1rsx05369psft'）;

根据优化器实际所用时间（直至最高时限）的不同，create_tuning_task函数最终会返回一个可标识该任务的独特的字符ID；此ID可与其他API一起使用［例如interrupt（中断）、cancel（取消）、drop（删除）］。

步骤2现在已经有了此任务，并具备了我们的参数设置，但若不执行并启动进程，它什么都不会做。

要执行此调优任务：

execute_tuning_task（task_name=>'my_sql_tuning_task'）;

任务完成后会返回提示。

该任务的执行结果会在后台被发送到该新框架（基础架构）所依赖的表中。

你可以使用DBA_ADVISOR_%视图（DBA_ADVISOR_FINDINGS、DBA_ADVISOR_RECOMMENDATIONS等）之类的许多新视图来查询这些表，其中存储着执行结果和建议。

步骤3通过调用report_tuning_task过程来查看结果：

setlong10000;

selectreport_tuning_task（task_name=>'my_sql_tuning_task'）fromdual;

report_tuning_task过程生成任务结果的完整报告，其中包括执行结果和建议，以及输出到控制台的内容。

（OEM也能达到同样的详细程度。

）

步骤4适当地采纳建议。

假定已在综合模式下运行了该调优任务，并生成了一个SQL附加信息集合，则可以通过执行accept_sql_profile命令来使用SQL附加信息集合：

accept_sql_profile（

taskname=>'my_sql_tuning_task',

name=>'my_sql_profile'）;

上例中的name是可选项；如果未给出name，系统会为该附加信息集合生成一个惟一名称。

接受SQL附加信息集合会将其永久地存储到数据字典中，在运行时会用到它：

应用程序下次运行时，优化器（返回"正常"模式）将在后台使用该附加信息集合来加速应用该优化器的SQL语句性能，而与其来源（打包应用程序或其他）无关。

什么会更容易呢？

结论

至少可以这样说，Oracle数据库10g中可管理性的新改进非常丰富，它为当今忙得不可开交的数据库管理员提供了一个得力的"自动驾驶仪"。

本专栏介绍的只是SQL调优顾问程序新功能的皮毛。

SQL调优功能可以轻松地提高任何SQL代码的性能，包括打包应用程序中的SQL代码，而无需修改源代码，数据库管理员也不必指出要向优化器发送哪些提示来提高性能。

这一新基础架构所提供的功能和众多新的顾问程序都不会取代数据库管理员，但它们将使我们能够专注于工作中更有挑战性和更有趣的部分。

Oracle10g调优：

Oracle10gAWR使用方法及分析

如何分析AWR

（1）

Kaya发表于

如果关注数据库的性能，那么当拿到一份AWR报告的时候，最想知道的第一件事情可能就是系统资源的利用情况了，而首当其冲的，就是CPU。

而细分起来，CPU可能指的是

OS级的User%,Sys%,Idle%

DB所占OSCPU资源的Busy%

DBCPU又可以分为前台所消耗的CPU和后台所消耗的CPU

如果数据库的版本是11g，那么很幸运的，这些信息在AWR报告中一目了然:

OS级的%User为75.4，%Sys为2.8，%Idle为21.2，所以%Busy应该是78.8。

DB占了OSCPU资源的69.1，%BusyCPU则可以通过上面的数据得到：

%BusyCPU=%TotalCPU/（%Busy）*100=69.1/78.8*100=87.69，和报告的87.7相吻合。

如果是10g呢，则需要手工对Report里的一些数据进行计算了。

HostCPU的结果来源于DBA_HIST_OSSTAT，AWR报告里已经帮忙整出了这段时间内的绝对数据（这里的时间单位是centisecond,也就是1/100秒）

这里，

%User=USER_TIME/（BUSY_TIME+IDLE_TIME）*100=146355/（152946+41230）*100=75.37

%Sys =SYS_TIME/（BUSY_TIME+IDLE_TIME）*100

%Idle=IDLE_TIME/（BUSY_TIME+IDLE_TIME）*100

值得注意的，这里已经隐含着这个AWR报告所捕捉的两个snapshot之间的时间长短了。

有下面的公式

BUSY_TIME+IDLE_TIME=ELAPSED_TIME*CPU_COUNT

正确的理解这个公式可以对系统CPU资源的使用及其度量的方式有更深一步的理解。

因此ELAPSED_TIME=（152946+41230）/8/100= 242.72seconds

当然，这正确无误。

至于DB对CPU的利用情况，这就涉及到10g新引入的一个关于时间统计的视图了，v$sys_time_model，简单而言，Oracle采用了一个统一的时间模型对一些重要的时间指标进行了记录，具体而言，这些指标包括:

1）backgroundelapsedtime

2）backgroundcputime

3）RMANcputime（backup/restore）

1）DBtime

2）DBCPU

2）connectionmanagementcallelapsedtime

2）sequenceloadelapsedtime

2）sqlexecuteelapsedtime

2）parsetimeelapsed

3）hardparseelapsedtime

4）hardparse（sharingcriteria）elapsedtime

5）hardparse（bindmismatch）elapsedtime

3）failedparseelapsedtime

4）failedparse（outofsharedmemory）elapsedtime

2）PL/SQLexecutionelapsedtime

2）inboundPL/SQLrpcelapsedtime

2）PL/SQLcompilationelapsedtime

2）Javaexecutionelapsedtime

2）repeatedbindelapsedtime

我们这里关注的只有和CPU相关的两个:

backgroundcputime和DBCPU。

这两个值在AWR里面也有记录:

TotalDBCPU=DBCPU+backgroundcputime=1305.89+35.91=1341.8seconds

再除以总的BUSY_TIME+IDLE_TIME

%TotalCPU=1341.8/1941.76=69.1%，这刚好与上面Report的值相吻合。

其实，在LoadProfile部分，我们也可以看出DB对系统CPU的资源利用情况。

用DBCPUperSecond除以CPUCount就可以得到DB在前台所消耗的CPU%了。

这里5.3/8=66.25%

比69.1%稍小，说明DB在后台也消耗了大约3%的CPU，这是不是一个最简单的方法了呢？

如何分析AWR

（2）

Kaya发表于

上一篇提到了DBCPU，这是一个用于衡量CPU的使用率的重要指标。

假设系统有N个CPU，那么如果CPU全忙的话，一秒钟内的DBCPU就是N秒。

如何去表征一个系统的繁忙程度呢？

除了利用CPU进行计算外，数据库还会利用其它计算资源，如网络，硬盘，内存等等，这些对资源的利用同样可以利用时间进行度量。

假设系统有M个session在运行，同一时刻，有的session可能在利用CPU，有的session可能在访问硬盘，那么，在一秒钟内，所有session的时间加起来就可以表征系统在这一秒内的繁忙程度，一般的，这个和的最大值应该为M。

这其实就是Oracle提供的另一个重要指标：

DBtime，它用以衡量前端进程所消耗的总时间。

对除CPU以后的计算资源的访问，Oracle用等待事件进行描述。

同样地，和CPU可分为前台消耗CPU和后台消耗CPU一样，等待事件也可以分为前台等待事件和后台等待事件。

DBTime一般的应该等于DBCPU+前台等待事件所消耗时间的总和。

等待时间通过v$system_event视图进行统计，DBTime和DBCPU则是通过同一个视图，即v$sys_time_model进行统计。

LoadProfile一节就有了对DBTime的描述:

这个系统的CPU个数是8，因此我们可以知道前台进程用了系统CPU的7.1/8=88.75%。

DBTime/s为11.7，可以看出这个系统是CPU非常繁忙的。

里面CPU占了7.1，则其它前台等待事件占了11.7–7.1=4.6WaitTime/s。

DBTime占DBCPU的比重呢？

7.1/11.7=60.68%

Top5TimedEvents，或许很多人都对它有所耳闻，按照CPU/等待事件占DBTime的比例大小，这里列出了Top5。

如果一个工作负载是CPU繁忙型的话，那么在这里应该可以看到DBCPU的影子。

注意到，我们刚刚已经算出了DBCPU的%DBtime，60%。

其它的e