性能测试系列:Oracle数据库awr报告使用与分析

一 AWR报告生成

1、生成AWR(Automatic Workload Repository)报告:
sqlplus / as sysdba
SQL>@?/rdbms/admin/awrrpt.sql

2、修改采集时间和统计信息保留时间:
SQL>exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>7*24*60);
SQL>select * from dba_hist_wr_control;

3、手动执行一个快照:
SQL>exec dbms_workload_repository.create_snapshot;

4、创建一个AWR基线:
SQL>exec dbms_workload_repository.create_baseline(start_snap_id,end_snap_id,baseline_name);


二 AWR主要指标说明

指标

指标说明

redo size

单位bytes,redosize可以用来估量update/insert/delete的频率

大的redosize往往对lgwr写日志和arch归档造成I/O压力

Logical Read

单位  次数*块数逻辑读耗CPU,主频和CPU核数都很重要

逻辑读高则DBCPU往往高,也往往可以看到latch: cache buffers chains等待。 

Block changes

单位次数*块数描绘数据变化频率

Physical Read

单位次数*块数物理读消耗IO,体现在IOPS和吞吐量等不同维度上;但减少物理读可能意味着消耗更多CPU

physical read包含了physical reads cache和physical reads direct

Physical writes

单位次数*块数,主要是DBWR写datafile,也有direct path write。

dbwr长期写出慢会导致定期log file switch(checkpoint no complete) 检查点无法完成的前台等待。 

physical write 包含了physical writes direct +physical writes from cache

User Calls

单位次数,用户调用数

Parses

解析次数,包括软解析+硬解析

软解析每秒超过300次意味着”应用程序”效率不高,调整session_cursor_cache。

Hard Parses

万恶之源,Cursor pin s on X, library cache: mutex X , latch: row cache objects /shared pool…

 硬解析最好少于每秒20次,每秒超过100次,说明绑定使用的不好,也可能共享池设置不合理。

W/A MB processed

单位MB  W/A workarea  workarea中处理的数据数量结合 In-memory Sort%, sorts (disk) PGA Aggr一起看

Transactions

每秒事务数,是数据库层的TPS,可以看做压力测试或比对性能时的一个指标,孤立看无意义。

% Blocks changed per Read

每次逻辑读导致数据块变化的比率;如果’redo size’, ‘block changes’ ‘pct of blocks changed per read’,三个指标都很高,说明系统正执行大量insert/update/delete;

Rollback per transaction %

 

事务回滚比率。  Rollback per transaction %= (rollback)/(transactions)
回滚率过高,说明数据库经历了太多的无效操作 ,过多的回滚还会带来Undo Block的竞争。

Rows per Sort


平均每次排序涉及到的行数 ;  Rows per Sort= ( sorts(rows) ) / ( sorts(disk) + sorts(memory))

Buffer Nowait %

session申请一个buffer(兼容模式)不等待的次数比例,需要访问buffer时立即可以访问的比率;
一般大于99%,否则可能存在争用。

buffer HIT%

高速缓存命中率,反映物理读和缓存命中间的纠结,这个指标即便99% 也不能说明物理读等待少了;
小于90%,可能要加db_cache_size;
db_cache_size过小引起大量的db file sequential /scattered read等待事件;
与 buffer HIT%相关的指标有 table scans(long tables) 大表扫描,
此外相关的还有Buffer Pool Statistics 、Buffer Pool Advisory等。

 Redo nowait%

session在生成redo entry时不用等待的比例,redo相关的资源争用,小于90%,考虑增加log buffer;
redo space request争用可能造成生成redo时需求等待,需要关注是否有十分频繁的 log switch ;
过小的redo logfile size 如果配合较大的SGA和频繁的commit提交都可能造成该问题;
考虑增大redo logfile : 1~4G 每个,7~10组都是合适的,同时考虑优化redo logfile和datafile 的I/O。

In-memory Sort%

在内存中完成的排序比率 ;In-memory Sort% =  sort(memory) / ( sort(disk)+ sort(memory) )
低于95%,通过适当调大初始化参数PGA_AGGREGATE_TARGET或SORT_AREA_SIZE。

Library Hit%

library cache命中率,合理值:>95% ,否则加大共享池,使用绑定变量,修改cursor_sharing等参数;
保持shared pool有足够的Free Memory,且没有过多的内存碎片;
保证SQL语句绑定变量和游标可以共享。

Soft Parse %

快照时间内软解析次数和总解析次数 (soft+hard 软解析次数+硬解析次数)的比值;
若指标很低,说明可能存在大量的hard parse硬解析;
大量的硬解析会消耗更多的CPU并产生解析争用(此时可以考虑使用cursor_sharing=FORCE);
通过设置 session_cached_cursors参数和反复重用游标可以让解析更轻量级;
小于<95%,需要考虑绑定,低于80%,可以认为sql基本没有被重用。

Execute  to Parse%

执行解析比 ,公式为 1-(parse/execute),目标为100%;
Execute to Parse和soft parse% 都很低 说明确实没有绑定变量 ;
如果 soft parse% 接近99% 而Execute to Parse 不足90% 说明没有执行;
解析比低, 需要通过静态SQL、动态绑定、session_cached_cursor、open cursors等减少软解析。

(Execute次数 - Parse次数)/Execute次数 x 100%
如偏小,说明解析(硬解析和软解析)的比例过大,快速软解析比例小。
根据实际情况,适当调整参数session_cursor_cache,提高会话中sql执行的命中率。
如为负数,通常说明shared pool设置或者语句效率存在问题,造成反复解析,reparse可能较严重,
或者是可能同snapshot有关,通常说明数据库性能存在问题。

Latch Hit%

申请不要等待的比率,Latch Hit%:=  (1 – (Sum(misses) / Sum(gets)))
Latch Hit>99%,否则Shared Pool latch争用,可能未共享的SQL,或Library Cache太小,
可使用绑定变量或增大Shared Pool。

 Memory Usage %  

shared pool的空间使用率,稳定在75%-90%
太低,表明共享池设置过大,
一直很高,关注sga breakdown中的shared pool free memory,
推荐free memroy 至少300~500MB ,以避免隐患。

 % SQL with executions>1  

复用的SQL占总的SQL语句的比率,
如太小,在应用中更多使用绑定变量,避免过多SQL解析

 % Memory for SQL

w/exec>1 

 执行2次以上的SQL所占内存占总的SQL内存的比率

% Non-Parse CPU

 

CPU非分析时间在整个CPU时间的百分比,查询实际运行时间/(查询实际运行时间+sql解析时间)
太低,表示解析消耗CPU时间过多。

 

 

三 AWR报告分析

分析内容:

1、CPUs、Elapsed、DB Time

2、Load Profile

3、Instance Efficiency Percentages

4、Top 10 Foreground Events by Total Wait Time

5、SQL Statistics

6、Global Cache Load Profile

7、Global Cache Efficiency Percentages

8、Global Cache and Enqueue Services


四 SQL语句分析

1、SQL> explain plan for sql ;

2、SQL>select * from table(dbms_xplan.display_cursor(sql_id));

3、SQL>@?/rdbms/admin/sqltrpt.sql sql_id


五 Oracle常见等待事件解决方法

Sequential Read:调整相关的索引和选择合适的驱动行源

Scattered Read:表明出现很多全表扫描。优化code,cache小表到内存中。

Free Buffer:增大DB_CACHE_SIZE,增大checkpoint的频率,优化代码; oracle之检查点(Checkpoint);数据库日志用来断电回滚,日志通过buffer触发IO写入磁盘,这个触发由检查点来做。

Buffer Busy Segment header:增加freelist或者freelistgroups
Buffer Busy Data block:隔离热块;使用反转索引;使用更小的块;增大表的initrans
Buffer Busy Undo header:增加回滚段的数量或者大小
Buffer Busy Undo block Commit more:增加回滚段的数量或者大小
Enqueue–ST:使用本地管理的表空间或者增加预分配的盘区大小
Enqueue–HW:在HWM之上预先分配盘区
Enqueue–TX4:在表或者索引上增大initrans的值或者使用更小的块
Log Buffer Space:增大LOG_BUFFER,改善I/O
Log File Switch:增加或者增大日志文件
Log file sync:减小提交的频率;使用更快的I/O;或者使用裸设备
Write complete waits:增加DBWR;提高CKPT的频率;
gc cr/current request:增加带宽;隔离热块;增加LMS进程数;提高磁盘IO
gc cr multi block busy:在RAC层面将应用功能分离
gc buffer busy acquire/release:分区;反向索引;提高SQL效率;将不同应用功能数据分布在不同数据库实例上
DFS lock handle:增大序列缓存,不使用排序选项

Latch Free:检查具体的等待latch类型,解决方法如下

 

Library cache:使用绑定变量; 调整shared_pool_size.
Shared pool:使用绑定变量; 调整shared_pool_size.
Redo allocation:减小 redo 的产生; 避免不必要的commits.
Redo copy:增加 _log_simultaneous_copies.
Row cache objects:增加shared_pool_size
Cache buffers chain:增大 _DB_BLOCK_HASH_BUCKETS ; make it prime.
Cache buffers LRU chain:使用多个缓冲池;调整引起大量逻辑读的查询

 

TBL$OR$IDX$PART$NUM,这个BUG的主要影响范围是 12.1.0.1 (Base Release) 和 11.2.0.4。详情

posted @ 2020-08-20 10:06  zhaot1993  阅读(1011)  评论(0编辑  收藏  举报