===

关于AWR见：Oracle10g调优工具AWR说明(http://www.taoyoyo.net/ttt/post/300.html )

 

===

--生成AWR REPROT

--快照常用操作

--基线常用操作

--AWR报告日常分析

--AWR报告详细分析

--AWR 报告示例

 

===

 

oracle内置功能，直接运行awrrpt脚本(%oracle_home%/rdbms/admin/awrrpt.sql)就可以生成报告了，html和text格式都可以，当然必须得有快照才能生成报告。

 

SQL> conn / AS SYSDBA

SQL> @D:oracleproduct10.2.0db_2RDBMSADMINawrrpt.sql

过程中需要设置以下值：

report_type=html或text  (采用默认为html)

num_days=1  (输入天数，可以查看天数内的快照ID)

begin_snap=输入开始快照ID

end_snap=输入结束快照ID

report_name=输入要保存的文件路径和文件名。

 

(

需要注意的是使用 AWR 需要有 Diagnostic Pack License。Oracle 后来推出了一个解决方案可以禁止掉该特性。

在 Note.436386.1 有说明:

SQL> @dbms_awr.plb

然后执行：

SQL> dbms_awr.disable_awr();

如果用 sys 之外的用户创建 AWR 报告，则需要进行合适的授权。否则会报告错误 PACKAGE 执行错误。

 

SQL> CONNECT / AS SYSDBA;

SQL> GRANT ADVISOR TO foo;

SQL> GRANT SELECT_CATALOG_ROLE TO foo;

SQL> GRANT EXECUTE ON sys.dbms_workload_repository TO foo;

 

要注意 Bug 4597354 在创建基线数据的时候，对性能有很大影响。在一个非常繁忙的系统上不要进行此操作。

如果结合 EM 用 AWR 是很方便的.

)

 

===

 

--查看当前的AWR保存策略、设置：快照间隔、保存时间

select * from dba_hist_wr_control;

 

SQL> col SNAP_INTERVAL format a20

SQL> col RETENTION format a20

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL

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

262089084 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

 

　　以上结果表示,每小时产生一个SNAPSHOT，保留7天。

 

--调整AWR产生snapshot的频率和保留策略，如将收集间隔时间改为30分钟一次,并且保留5天时间（单位都是分鍾）：

exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);

 

  设置快照时间间隔为 20 分钟，保留时间为两天 -- 您可以发出以下命令。参数以分钟为单位。

  exec dbms_workload_repository.modify_snapshot_settings ( interval => 20, retention => 2*24*60 );

 

--关闭AWR，把interval设为0则关闭自动捕捉快照

exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

 

--手工创建一个快照

exec dbms_workload_repository.create_snapshot();

或

begin

  dbms_workload_repository.create_snapshot();

end; 

 

--查看快照

select * from sys.wrh$_active_session_history

select * from dba_hist_snapshot ds 

select count(*),min(snap_id),max(snap_id) from wrh$_active_session_history;

 

--查看快照最大编号

SQL> var snap_id number

SQL> exec :snap_id:=dbms_workload_repository.create_snapshot

SQL> print snap_id

 

--手工删除指定范围的快照

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 973, high_snap_id => 999, dbid => 262089084);

或

begin

  dbms_workload_repository.drop_snapshot_range(low_snap_id => 96, high_snap_id => 96, dbid => 1160732652);

end;

 

===

 

基线（baseline）是一种机制,这样你可以在重要时间的快照信息集做标记。一个基线定义在一对快照之间,快照通过他们的快照序列号识别.每个基线有且只有一对快照。一次典型的性能调整实践从采集量度的基准线集合、作出改动、然后采集另一个基准线集合开始。可以比较这两个集合来检查所作的改动的效果。在 AWR 中，对现有的已采集的快照可以执行相同类型的比较。

 

假定一个名称为 apply_interest 的高度资源密集的进程在下午 1:00 到 3:00 之间运行，对应快照 ID 95 到 98。我们可以为这些快照定义一个名称为apply_interest_1 的基准线:

 

--查看基线

select *From dba_hist_baseline;

select * from wrm$_baseline;

 

--创建baseline：保存这些数据用于将来分析和比较

exec dbms_workload_repository.create_baseline(start_snap_id => 1003, end_snap_id => 1013, ^apply_interest_1^);

或

exec dbms_workload_repository.create_baseline(95, 98, ^apply_interest_1^);

或

BEGIN

  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id => 7550,baseline_name => ¨am_baseline¨);

END;

 

在一些调整步骤之后，我们可以创建另一个基准线 — 假设名称为 apply_interest_2，然后只为那些与这两条基准线相关的快照比较量度

SQL> exec dbms_workload_repository.create_baseline(92, 94, ^apply_interest_2^);

 

--删除基线：可以在分析之后使用 drop_baseline() 来删除基准线；快照将保留(也可级联删除)。 此外，当清除例程开始删除旧的快照时，与基准线相关的快照不会被清除，从而允许进行进一步的分析.

exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => ^apply_interest_1^, cascade => FALSE);

 

--删除基线，同时级联删除快照

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name =>¨am_baseline¨, cascade => true);

 

 

===

 

3.1 SQL ordered by Elapsed Time

记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和，而不是单次SQL执行时间 Elapsed Time = CPU Time + Wait Time)。

--Elapsed Time(S): SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。Elapsed Time = CPU Time + Wait Time

--CPU Time(s): 为SQL语句执行时CPU占用时间总时长，此时间会小于等于Elapsed Time时间。单位时间为秒。

--Executions: SQL语句在监控范围内的执行次数总计。

--Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。

--% Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。

--SQL ID: SQL语句的ID编号，点击之后就能导航到下边的SQL详细列表中，点击IE的返回可以回到当前SQL ID的地方。

--SQL Module: 显示该SQL是用什么方式连接到数据库执行的，如果是用SQL*Plus或者PL/SQL链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。

--SQL Text: 简单的sql提示，详细的需要点击SQL ID。

 

3.2 SQL ordered by CPU Time:

记录了执行占CPU时间总和时间最长的TOP SQL(请注意是监控范围内该SQL的执行占CPU时间总和，而不是单次SQL执行时间)。

 

3.3 SQL ordered by Gets:

记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占Gets总和，而不是单次SQL执行所占的Gets)。

 

3.4 SQL ordered by Reads:

记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和，而不是单次SQL执行所占的磁盘物理读)。

 

3.5 SQL ordered by Executions:

记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数。

 

3.6 SQL ordered by Parse Calls:

记录了SQL的软解析次数的TOP SQL。说到软解析(soft prase)和硬解析(hard prase)见：http://www.taoyoyo.net/ttt/post/305.html

 

3.7 SQL ordered by Sharable Memory:

记录了SQL占用library cache的大小的TOP SQL。Sharable Mem (b)：占用library cache的大小，单位是byte。

 

3.8 SQL ordered by Version Count:

记录了SQL的打开子游标的TOP SQL。

 

3.9 SQL ordered by Cluster Wait Time:

记录了集群的等待时间的TOP SQL

 

===

AWR报告详细分析(节自http://www.taoyoyo.net/lib/Single.asp?pID=1561)

 

db time= cpu time + wait time（不包含空闲等待） （非后台进程）， 就是db time就是记录的服务器花在数据库运算(非后台进程)和等待(非空闲等待)上的时间 

 

系统为24核CPU , 在snapshot间隔中，总共约1380.04分钟，CPU時間共有1380.4*24=33129.6分钟，这里的DB time为2591.15分钟，表示cpu花费了2591.15分钟在处理Oracle非空闲等待和运算上(比方逻辑读)

 

也就是说cpu有 2591.15/33129.6*100% (的百分比: 7.82%)花费在处理Oracle的操作上，这不包括后台进程, 这台服务器的平均负载相对较低。从awr report的Elapsed 和DB Time就能大概了解db的负载.

 

就是說：  通過 DB Time / (Elapsed * CPU核數) *100% 得出的值就說明了CPU花费在处理Oracle的操作上的比例 (不包括后台进程)。  比例越高，說明負載越高。

 

Load Profile说明了目前库整体状态。

 

Redo size:  平均每秒或每事务重做产生的日志大小为161K  (单位：Bytes) ，每事务产生5K重做日志。

 

Physical  writes:  平均每秒物理写为 66.52 blocks . 

 

Physical reads / Logical reads= 430.48 / 38788.19 = 1.1% 的逻辑读导致了物理I/O。平均每个事物产生逻辑读 1351.11 (blocks) 。这个数字应该越小越好。 Read的單位是block .

 

Parses:     CPU每秒需要进行 1454.21 次解析，系统比较繁忙，每秒有35.79次硬解析(硬解析占比例2.5% ) ，说明 1/35.79 = 0.02 秒CPU就要处理一个新的SQL语句，说明系统内不同SQL语句较多，建议多使用绑定变量及procedure处理。

 

Sorts:  每秒70.30次排序也是比较多的。

 

Transactions：每秒产生的事务数，反映数据库任务繁重与否

 

% Blocks changed per Read： 说明 1-2.43%=97.57% 的逻辑读是用于那些只读的而不是可修改的块， 平均每次操作只更新占2.43% 的块 。也就是 ( 收集快照的这23个小时中 ) DML更新操作的块占整个被操作(逻辑读)的块的比例是2.43% 。

 

Recursive Call% :    表示71.77%的SQL都是通过PL/SQL来执行的。Recursive：递归的

 

Rollback per transaction %：   表示事务回滚的百分比，越小越好。 19.95值已经非常高了(表示每个事物产生0.1995个回滚)，系统存在回滚方面的问题，因为回滚的代价非常昂贵，即平均每5（1/0.1995）个事务就要产生一次回滚 。 结合前面的transactions 每秒为 28.71个，即每秒会有28.71/5 = 5.7 次回滚。 应该仔细检查系统为何产生如此高的回滚率。

 

数据库实例效率。目标值都是100% 。

 

Buffer Nowait %： 在缓冲区中获取Buffer的未等待比率(buffer cache请求的命中率)，如果Buffer Nowait<99% 说明，有可能是有热块 (查找x$bh的 tch和v$latch_children的cache buffers chains)。

 

Redo NoWait %： 在Redo缓冲区获取Buffer的未等待比率。

 

Buffer Hit %： 数据块在数据缓冲区中的命中率，通常应在95%以上，否则，小于95%，需要调整重要的参数，小于90%可能是要加db_cache_size，但是大量的非选择的索引也会造成该值很高（大量的db file sequential read）。

 

In-memory Sort %：在内存中的排序率。如果太低，需要考慮增加PGA或检查程序减少排序。

 

Library Hit %：主要代表SQL在共享区(库缓存)的命中率，通常在95%以上，否则需要考虑加大Shared Pool ，绑定变量，修改cursor_sharing (需要谨慎修改此参数) 等参数。

 

Soft Parse %：  软解析百分比，近似看作SQL在共享区的命中率，小于<95%,需要考虑到绑定，如果低于80%，那么就可能SQL基本没有被重用。

 

Execute to Parse % ：   SQL语句执行与解析的比率。如果某条新的SQL语句经过一次解析然后执行，且再也不在同一个session中执行的话，那么比率为0，这个比率应该越高越好。比如这里的36.04%说明， 同一个session中执行的SQL语句中只有36.04%的SQL是已经解析好了的(不需要再次解析)。 说明DB中新的SQL语句相对较多。

 

Execute to parse＝round(100 * (1-Parses/Executions),2),   如果parse次数大于executions，可能会导致此值为负数，对性能会有影响 。 这个值越接近100%越好 (即Parses/Executions 越接近0，也即几乎所有SQL都是已经解析过的，只要执行就好了)。

 

Latch Hit %：   每次申请一个latch的时候，成功的概率有多少。如果低于99%, 则说明有latch竞争问题。 要确保>99%，否则存在严重的性能问题， 比如绑定变量使用，热点块分散，共享池调整(过小) 等。

 

Parse CPU to Parse Elapsd %：

 

计算公式为：Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)。此处为89.28%，用于解析花费的每个CPU秒花费了大约1/0.8928=1.12秒的wall clock（挂钟）时间， 这说明花了0.12秒时间等待一个资源。如果该比率为100%，意味着CPU时间等于经过的时间，没有任何等待。值越大，说明消耗在等待资源上的时间越少。

 

% Non-Parse CPU：     计算公式为：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。太低表示解析消耗时间过多。这个比值越接近100% 越好，说明数据库大部分时间是花在执行SQL语句上，而不是解析SQL语句。 

 

Memory Usage%   :   表示被使用的部分占shared pool总大小的百分比，如果太低，浪费内存，如果值太高，说明利用率过大，可能因为shared pool中对象被经常刷新出内存，导致SQL语句硬解析增加。这个数字应该长时间稳定在75%～90% 。

 

%SQL with executions>1:  表示shared pool中执行次数大于1次的SQL语句占SQL语句总数比例是94.48%。

 

%Memory for SQL w/exec>1:    这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗的内存占shared pool的百分比。这个数字将在总体上与% SQL with executions>1非常接近，除非有某些查询任务消耗的内存没有规律。 在稳定状态下，总体上会看见随着时间的推移大约有75%～85%的共享池被使用。如果报表的时间窗口足够大到覆盖所有的周期，执行次数大于一次的SQL语句的百分率应该接近于100%。这是一个受观察之间持续时间影响的统计数字。可以期望它随观察之间的时间长度增大而增大。

 

Top 5 Timed Events中如果是空闲等待时间，可以无需关注，我们只需要关心非空闲等待事件。  常见的空闲事件有: 

 

dispatcher timer

　　 lock element cleanup

　　 Null event

　　 parallel query dequeue wait

　　 parallel query idle wait - Slaves

　　 pipe get

　　 PL/SQL lock timer

　　 pmon timer- pmon

　　 rdbms ipc message

　　 slave wait

　　 smon timer

　　 SQL*Net break/reset to client

　　 SQL*Net message from client

　　 SQL*Net message to client

　　 SQL*Net more data to client

　　 virtual circuit status

　　 client message

 

Top 5 Timed Events中列出的等待事件不一定是当时列出的5项，每次收集都会变化，

 

这里列出经常出现的一些事件做简单分析。  注意在Oracle 9.2 以前这个项目叫做Top 5 Wait Events， 在9.2及之后版本才更改为 Top 5 Timed Events， 且包含”CPU time” 其中的Waits 表示等待次数，Time(s)表示等待时间(秒)，一般主要看等待时间。 Avg Wait (ms) 表示平均每次等待的时间， % Total Call Time 表示该等待事件在总的调用时间里的百分比是多少， Wait Class表示等待的级别。

 

CPU time    :     CPU time其实不是真正的等待事件。是衡量CPU是否瓶颈的一个重要指标, 

 

Elapsed Time = CPU Time + Wait Time 。  一般来讲，一个良好的系统，CPU TIME 应该排在TOP 5 TIME Event的最前面，否则，就要进行调整以减少其他的WAIT TIME。 当然这也是相对的， 如果不存在显著的 latch wait 或过高的logical read  等,  CPU time 占的比例高才是令人放心的。 也就是说CPU在高效率干活是好事，但是是否因为低效的设置或SQL而消耗CPU时间就需要注意了。  

 

db file sequential read 与 db file scattered read.   

 

这两个事件是出现比较频繁的事件。 他们表明Oracle内核请求从磁盘读取数据块 (到buffer cache中), 他们的区别就是

 

sequential 是单块读（串行读），而scattered 表示多块读。(和是否全表扫描无关, 只是全表扫描一般表现为多块读) 。 这两个事件描述的是如何将数据块存储到内存中的, 而不是如何从磁盘进行读取。

 

db file scattered read 

 

一次获取的block被分散在buffer的不连续空间中，通常表示全表扫描过多，可检查应用程序是否合理的使用了索引，数据库是否合理的创建了索引 。 db file scattered read是用来表示顺序读取(例如,全表扫描)。

 

db file sequential read 

通常暗示着通过索引获取数据量比较大（比如通过索引进行范围扫描获取表数据百分比过大或者错误的使用索引），多表连接的时候连接顺序不当，hash join时hash_area_size无法容纳hash table 等。db file sequential read是用来表示随机读取(例如,索引扫描)。

 

深入解析db file sequential read 及 db file scattered read ：

 

定义

 

事件名db file sequential read与db file scattered read描述的是如何将数据块存储到内存中的,而不是如何从磁盘进行读取. 如果填充磁盘读取的内容的内存是连续的, 发生的磁盘读就是db file sequential read ,  当填充从磁盘读取的数据的内存的连续性无法被保证的时候,发生的磁盘读就是db file scattered read. 

 

db file sequential read 

 

Oracle 为所有的单块读取生成db file sequential read事件（既然是单个，当然是连续的，你可以发现db file sequential read 等待事件的P3参数一般都是1）. Oracle始终将单个数据块存储在单个缓存块(cache buffer)中,  因此单块读取永远不会产生db file scattered read事件.  对于索引块，如果不是快速全索引扫描，一般都是一个一个块读取的，所以说，这个等待事件很多时候都是索引读取引起的。 

 

这一事件通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，可能没有正确的使用驱动表; 或者可能说明不加选择地进行索引。 在大多数情况下我们说，通过索引可以更为快速的获取记录，所以对于一个编码规范、调整良好的数据库，这个等待很大是很正常的。但是在很多情况下，使用索引并不是最佳的选择，比如读取较大表中大量的数据，全表扫描可能会明显快于索引扫描，所以在开发中我们就应该注意，对于这样的查询应该进行避免使用索引扫描。

 

db file scattered read  

 

db file scattered read 一般都是等待同时读取多个块到内存中。为了性能和更有效的内存空间利用，oracle一般会把这些块分散在内存中。db file scattered read 等待事件的P3参数指出了每次I/O读取的块数。每次I/O读取多少个块， 由参数db_file_multiblock_read_count控制。 全表扫描或者快速全索引扫描时一般使用的这种读取块的方式，所以，该等待很多时候都是因为全表扫描引起的 ；在大部分情况下, 全表扫描与快速全索引扫描都会产生一次或多次db file scattered read. 不过, 有时 , 这些扫描只会产生db file sequential read.  

 

因为全表扫描被置于LRU（Least Recently Used，最近最少使用）列表的冷端（cold end），对于频繁访问的较小的数据表，可以选择把他们Cache到内存中，以避免反复读取。当这个等待事件比较显著时，可以结合v$session_longops 以及动态性能视图来进行诊断，该视图中记录了长时间（运行时间超过6秒的）运行的事物，可能很多是全表扫描操作（不管怎样，这部分信息都是值得我们注意的）。 

 

latch free 

 

latch是一种轻量级的锁。一般来说，latch由三种内存元素组成：pid（进程id），内存地址和内存长度。Latch保证对共享数据结构的排它性访问，以此来保证内存结构的完整性不受到损坏。在多个会话同时修改或者检视(inspect)sga中同一个内存结构时，必须串行化访问以保证sga中数据结构的完整性。 

 

Latch只是用来保护sga中的内存结构。对数据库中的对象的保护，使用的lock而不是latch。Oracle sga中有许多latch，用来保护sga中各种内存结构不会因为并发访问而损坏。常见的Latch Free等待事件是由于热块 (buffer cache中的latch争用) 及未使用绑定变量(shared pool中的latch争用) 导致的。

 

 最常见的Latch集中于Buffer Cache的竞争和Shared Pool的竞争。和Buffer Cache相关的主要Latch竞争有cache buffers chains和cache buffers lru chain，和Shared Pool相关的主要Latch竞争有Shared Pool Latch和Library Cache Latch等。  Buffer Cache的Latch竞争经常是由于热点块竞争或低效的SQL语句引起； Shared Pool的Latch竞争通常是由于SQL的硬解析引起。过大的共享池可能导致shared pool latch 争用(9i之前的版本)；

 

当latch在系统范围内的等待时间比较显著时，你可以通过v$latch中的sleeps列来发现争用显著的latch：

Select  name,  gets,  misses, immediate_gets,  immediate_misses,  sleeps

from   v$latch  order  by sleeps  desc  ;    

 

buffer busy waits 

 

发生条件：

 

block正被读入缓冲区或者已经在缓冲区正被其他session修改, 一个会话尝试去pin 住它，这时当前block已经被pin住，就发生了竞争，产生一个buffer busy waits, 该值不应该大于1% 。可以查看v$waitstat 看到大概的buffer busy waits 分布 。

 

解决办法：

出现此情况通常可能通过几种方式调整： 增大data  buffer, 增加freelist，减小pctused, 增加回滚段数目，增大initrans,  考虑使用LMT+ASSM,  确认是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小)  . 

 

该等待事件表示正在等待一个以非共享方式使用的缓冲区，或者表示当前正在被读入buffer cache。一般来说Buffer Busy Wait不应大于1%。     检查缓冲等待统计部分（如下）Segments By Buffer Busy Waits (或V$WAITSTAT)，看一下等待是否位于段头(Segment Header)。       如果是，可以考虑增加自由列表(freelist，对于Oracle8i DMT)或者增加freelist groups(在很多时候这个调整是立竿见影的， 在8.1.6及以后版本，动态修改feelists需要设置COMPATIBLE至少为8.1.6)， Oracle9i或以后可以使用ASSM .

 

alter table xxx storage(freelists  n); 

 

--查找等待块类型

 

 

对于不同的等待块类型，我们采取不同的处理办法：

 

1.data segment header：

进程经常性的访问data segment header通常有两个原因：获取或修改process freelists信息;  扩展高水位标记。    针对第一种情况，进程频繁访问process freelists信息导致freelist争用，我们可以增大相应的segment对象的存储参数freelist或者freelist groups；若由于数据块频繁进出freelist而导致进程经常要修改freelist，则可以将pctfree值和pctused值设置较大的差距，从而避免数据块频繁进出freelist；  对于第二种情况，由于该segment空间消耗很快，而设置的next extent过小，导致频繁扩展高水位标记，解决的办法是增大segment对象的存储参数next extent或者直接在创建表空间的时候设置extent size uniform. 。 

 

2.data block：

某一或某些数据块被多个进程同时读写，成为热点块，可以通过如下办法来解决这个问题：

(1) 降低程序的并发度，如果程序中使用了parallel查询，降低parallel degree，以免多个parallel slave同时访问同样的数据对象而形成等待降低性能

(2) 调整应用程序使之能读取较少的数据块就能获取所需的数据，减少buffer gets和physical reads

(3) 减少同一个block中的记录数，使记录分布于更多的数据块中，这可以通过若干途径实现：可以调整segment对象的pctfree值，可以将segment重建到block size较小的表空间中，还可以用alter table minimize records_per_block语句减少每块中的记录数

(4) 若热点块对象是类似自增id字段的索引，则可以将索引转换为反转索引，打散数据分布，分散热点块 ； 如果等待处于索引块，应该考虑重建索引、分割索引或使用反向键索引。

 

对于多事务并发访问的数据表，关于ITL的竞争和等待可能出现，为了减少这个等待，可以增加initrans，使用多个ITL槽.  

 

3.undo segment header：

undo segment header争用是因为系统中undo segment不够，需要增加足够的undo segment，根据undo segment的管理方法，若是手工管理模式，需要修改rollback_segments初始化参数来增加rollback segment， 若是自动管理模式，可以减小transactions_per_rollback_segment初始化参数的值来使oracle自动增多rollback segment的数量

 

4.undo block：

undo block争用是由于应用程序中存在对数据的读和写同时进行（需要适当减少大规模的一致性读取），读进程需要到undo segment中去获得一致性数据，解决办法是错开应用程序修改数据和大量查询数据的时间 ASSM 结合LMT 彻底改变了Oracle 的存储机制，位图freelist 能够减轻缓冲区忙等待(buffer busy wait)，这个问题在Oracle9i 以前的版本里曾是一个严重的问题。

 

Oracle 宣称ASSM 显著地提高了DML 并发操作的性能，因为(同一个)位图的不同部分可以被同时使用，这样就消除了寻找剩余空间的串行化。根据Oracle 的测试结果，使用位图会消除所有分段头部(对资源)的争夺，还能获得非常快的并发插入操作。在Oracle9i或以后版本之中，Buffer Busy wait 不再常见。

 

Free buffer waits 

 

表示data buffer里没有空闲可用buffer，使得当前会话进程处于Free buffer wiats等待状态， Free buffer waits的等待原因一

 

般有以下几个：

-  DATA BUFFER太小；

-  DBWR进程写的效率比较低；

-  LGWR写的太慢，导致DBWR等待；

-  正在把大量脏块写入到磁盘；

-  SQL语句效率低，需对Top SQL进行优化。

 

enqueue 

 

队列竞争： enqueue是一种保护共享资源的锁定机制。该锁定机制保护共享资源，如记录中的数据，以避免两个人在同一时间更新同一数据。Enqueue 包括一个排队机制，即FIFO（先进先出）排队机制。  Enqueue等待常见的有ST、HW 、TX 、TM等 

 

ST enqueue，用于空间管理和字典管理的表空间(DMT)的区间分配，在DMT中典型的是对于uet$和fet$数据字典表的 争用。对于支持LMT的版本，应该尽量使用本地管理表空间. 或者考虑手工预分配一定数量的区(Extent)，减少动态扩展时发生的严重队列竞争。

 

HW enqueue指和段的高水位标记相关等待；手动分配适当区可以避免这一等待。

 

TX锁（事务锁）是最常见的enqueue等待。TX enqueue等待通常是以下三个问题之一产生的结果。

第一个问题是唯一索引中的重复索引，你需要执行提交（commit）/回滚（rollback）操作来释放enqueue。

第二个问题是对同一位图索引段的多次更新。因为单个位图段可能包含多个行地址（rowid），所以当多个用户试图更新同一段时，可能一个用户会锁定其他用户请求的记录，这时等待出现。直到获得锁定的用户提交或回滚， enqueue释放。 第三个问题，也是最可能发生的问题， 多个用户同时更新同一个块。如果没有足够的ITL槽，就会发生块级锁定。通过增大initrans和/或maxtrans以允许使用多个ITL槽（对于频繁并发进行DML操作的数据表，在建表之初就应该考虑为相应参数设置合理的数值，避免系统运行以后在线的更改，在8i之前，freelists等参数不能在线更改，设计时的考虑就尤为重要），或者增大表上的pctfree值，就可以很容易的避免这种情况。

 

TM enqueue队列锁在进行DML操作前获得，以阻止对正在操作的数据表进行任何DDL操作(在DML操作一个数据表时，其结构不能被更改)。

 

log file parallel write  /  log file sync (日志文件同步)

 

如果你的Log group 存在多个组成员，当flush log buffer 时，写操作是并行的，这时候此等待事件可能出现。 

 

触发LGWR进程的条件有：

  1. 用户提交

  2. 有1/3重做日志缓冲区满 

  3. 有大于1M的重做日志缓冲区未被写入磁盘

  4. 3秒超时

  5. DBWR 需要写入的数据的SCN大于LGWR记录的SCN，DBWR 触发LGWR写入。

 

当一个用户提交(commits)或者回滚(rollback),session的redo信息需要写出到redo logfile中.用户进程将通知LGWR执行写出操作,LGWR完成任务以后会通知用户进程.  这个等待事件就是指用户进程等待LGWR的写完成通知.    对于回滚操作，该事件记录从用户发出rollback命令到回滚完成的时间.

 

如果该等待过多，可能说明LGWR的写出效率低下，或者系统提交过于频繁.  针对该问题，可以关注:  log file parallel write等待事件 。  user commits,user rollback等统计信息可以用于观察提交或回滚次数 

 

解决方案： 

1.提高LGWR性能：尽量使用快速磁盘，不要把redo log file存放在raid 5的磁盘上

2.使用批量提交

3.适当使用NOLOGGING/UNRECOVERABLE等选项

 

可以通过如下公式计算平均redo写大小：

avg.redo write size = (Redo block written/redo writes)*512 bytes 

 

如果系统产生redo很多，而每次写的较少，一般说明LGWR被过于频繁的激活了.可能导致过多的redo相关latch的竞争。  

下面的等待事件与RAC有关(节点间资源争用)：

gc current block busy  ：

gcs log flush sync  ：

gc buffer busy ：热点块问题；节点隔离/业务隔离以减少节点间的资源争用；

Log File Switch

 

当这个等待出现时，表示所有的提交(commit)的请求都需要等待"日志文件切换"的完成。这个等待事件出现时通常是因为日志组循环写满以后，第一个日志归档尚未完成，出现该等待。出现该等待，可能表示io 存在问题。

 

解决办法:

　　可以考虑增大日志文件和增加日志组

　　移动归档文件到快速磁盘

　　调整log_archive_max_processes .

　　log file switch (checkpoint incomplete)-日志切换(检查点未完成)

　　该等待事件通常表示你的DBWR 写出速度太慢或者IO 存在问题。

    需要考虑增加额外的DBWR 或者增加你的日志组或日志文件大小。

 

control file sequential read / control file parallel write

 

如果等待时间比较长，非常明显， 需要考虑改善控制文件所在磁盘I/O .

 

SQL Statistics 按照不同指标排序统计的数据非常有用，结合所有的统计信息，可以

 

找出运行性能差的SQL语句，以及运行不合理(比如运行次数非常多)的SQL .  比较容易看懂，这里不详细介绍。 

 

上面的很多项都比较好理解，这里简单说一下下面的几个：

 

SQL ordered by Parse Calls : 什么是parse calls 请参考（包括硬解析，软解析，及softer解析）：http://space.itpub.net/35489/viewspace-697499

 

SQL ordered by Version Count :   包含版本较多的sql语句，即parent cursors相同，而children cursors不同的sql语句。  即，SQL文本一模一样，父亲游标可以共享，但是因为优化器环境设置不同( OPTIMIZER_MISMATCH)，  绑定变量的值的长度在第二次执行的时候发生显著的变化(BIND_MISMATCH) , 授权关系不匹配(AUTH_CHECK_MISMATCH ) 或者 基础物件转换不匹配(TRANSLATION_MISMATCH) 等导致子游标不能共享，需要新生成一个子游标 。 这与SQL共享 (即游标共享) 是有关系的 。 这种情况下的执行计划可能不同，也可能相同(我们可以通过plan_hash_value看出);   具体的mismatch可以查询 V$SQL_SHARED_CURSOR

 Advisory Statistics

 

除了通过此项查看相关建议之外。 还可以通过如下的视图查询。

 

GV_$DB_CACHE_ADVICE

GV_$MTTR_TARGET_ADVICE

GV_$PGATARGET_ADVICE_HISTOGRAM

GV_$PGA_TARGET_ADVICE

GV_$SHARED_POOL_ADVICE

V_$DB_CACHE_ADVICE

V_$MTTR_TARGET_ADVICE

V_$PGA_TARGET_ADVICE

V_$PGA_TARGET_ADVICE_HISTOGRAM

V_$SHARED_POOL_ADVICE

 

Buffer Pool Advisory  /  PGA Memory Advisory  / SGA Target Advisory / 。。。。。。

 

Wait Statistics

 

说明buffer wait 是什么样的等待块类型（参考前面的buffer wait 说明及改善方法）。 

 

Segment Statistics ：

    * Segments by Logical Reads

    * Segments by Physical Reads

    * Segments by Row Lock Waits

    * Segments by ITL Waits

    * Segments by Buffer Busy Waits

    * Segments by Global Cache Buffer Busy

    * Segments by CR Blocks Received

    * Segments by Current Blocks Received

........ 

 

===

AWR 报告示例：　Html格式     Text格式