性能测试系列: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) |
Rows per Sort |
|
Buffer Nowait % |
session申请一个buffer(兼容模式)不等待的次数比例,需要访问buffer时立即可以访问的比率; |
buffer HIT% |
高速缓存命中率,反映物理读和缓存命中间的纠结,这个指标即便99% 也不能说明物理读等待少了; |
Redo nowait% |
session在生成redo entry时不用等待的比例,redo相关的资源争用,小于90%,考虑增加log buffer; |
In-memory Sort% |
在内存中完成的排序比率 ;In-memory Sort% = sort(memory) / ( sort(disk)+ sort(memory) ) |
Library Hit% |
library cache命中率,合理值:>95% ,否则加大共享池,使用绑定变量,修改cursor_sharing等参数; |
Soft Parse % |
快照时间内软解析次数和总解析次数 (soft+hard 软解析次数+硬解析次数)的比值; |
Execute to Parse% |
执行解析比 ,公式为 1-(parse/execute),目标为100%; (Execute次数 - Parse次数)/Execute次数 x 100% |
Latch Hit% |
申请不要等待的比率,Latch Hit%:= (1 – (Sum(misses) / Sum(gets))) |
Memory Usage % |
shared pool的空间使用率,稳定在75%-90% |
% SQL with executions>1 |
复用的SQL占总的SQL语句的比率, |
% Memory for SQL w/exec>1 |
执行2次以上的SQL所占内存占总的SQL内存的比率 |
% Non-Parse CPU |
CPU非分析时间在整个CPU时间的百分比,查询实际运行时间/(查询实际运行时间+sql解析时间) |
三 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。详情;