墨墨导读:本文将对Redo Nowait指标的算法和诊断进行深度解析。
为了系统化的梳理 AWR 的知识体系,我们整理了一个系列文章,希望从原理、使用到 AWR 报告的解读,给读者展示全面的 AWR 知识体系,本文是这个系列文章的第四篇。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 520403 31457280 1 NO ACTIVE 1.3861E+10 23-JUN-05
2 1 520404 31457280 1 NO ACTIVE 1.3861E+10 23-JUN-05
3 1 520405 31457280 1 NO ACTIVE 1.3861E+10 23-JUN-05
4 1 520406 31457280 1 NO CURRENT 1.3861E+10 23-JUN-05
5 1 520398 31457280 1 NO ACTIVE 1.3860E+10 23-JUN-05
6 1 520399 31457280 1 NO ACTIVE 1.3860E+10 23-JUN-05
7 1 520400 104857600 1 NO ACTIVE 1.3860E+10 23-JUN-05
8 1 520401 104857600 1 NO ACTIVE 1.3860E+10 23-JUN-05
9 1 520402 104857600 1 NO ACTIVE 1.3861E+10 23-JUN-05
oracle:/oracle >ps -ef|grep ora_dbw
oracle 2266 1 0 Mar 31 ? 811:42 ora_dbw0_hysms02
oracle 21023 21012 0 18:52:59 pts/65 0:00 grep ora_dbw
oracle:/oracle >top
last pid: 21145; load averages: 3.38, 3.45, 3.67 18:53:38
725 processes: 711 sleeping, 1 running, 10 zombie, 3 on cpu
CPU states: 35.2% idle, 40.1% user, 9.4% kernel, 15.4% iowait, 0.0% swap
Memory: 3072M real, 286M free, 3120M swap in use, 1146M swap free
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
11855 smspf 1 59 0 1355M 1321M cpu/0 19:32 16.52% oracle
2264 oracle 1 0 0 1358M 1316M run 283.3H 16.36% oracle
11280 oracle 1 13 0 1356M 1321M sleep 79.8H 0.77% oracle
21043 oracle 1 43 0 3264K 2056K cpu/9 0:01 0.31% top
2266 oracle 1 60 0 1357M 1317M sleep 811:42 0.18% oracle
26257 smspf 82 59 0 447M 178M sleep 533:04 0.15% java
注意到2266号进程消耗的CPU不过0.18%,显然并不繁忙,那么瓶颈就很可能在IO上。
gqgai:/home/gqgai>iostat -xn 3
extended device statistics
r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device
........
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 c0t6d0
0.3 8.3 0.3 47.0 0.0 0.1 0.0 9.2 0 8 c0t10d0
0.0 8.3 0.0 47.0 0.0 0.1 0.0 8.0 0 7 c0t11d0
11.7 65.3 197.2 522.2 0.0 1.6 0.0 20.5 0 100 c1t1d0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 hurraysms02:vold(pid238)
extended device statistics
r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device
........
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 c0t6d0
0.3 13.7 2.7 68.2 0.0 0.2 0.0 10.9 0 12 c0t10d0
0.0 13.7 0.0 68.2 0.0 0.1 0.0 9.6 0 11 c0t11d0
11.3 65.3 90.7 522.7 0.0 1.5 0.0 19.5 0 99 c1t1d0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
--
-- Instance Efficiency Percentages
column ldscr format a50
column nl format a60 newline;
select 'Instance Efficiency Percentages (Target 100%)' ldscr
,'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' ldscr
,' Buffer Nowait %:' dscr
, round(100*(1-:bfwt/:gets),2) pctval
,' Redo NoWait %:'
, decode(:rent,0,to_number(null), round(100*(1-:rlsr/:rent),2)) pctval
,' Buffer Hit %:' dscr
, round(100*(1-(:phyr-:phyrd-:phyrdl)/:gets),2) pctval
,' In-memory Sort %:'
, decode((:srtm+:srtd),0,to_number(null),
round(100*:srtm/(:srtd+:srtm),2)) pctval
,' Library Hit %:' dscr
, round(100*:lhtr,2) pctval
,' Soft Parse %:'
, round(100*(1-:hprs/:prse),2) pctval
,' Execute to Parse %:' dscr
, round(100*(1-:prse/:exe),2) pctval
,' Latch Hit %:'
, round(100*(1-:lhr),2) pctval
,'Parse CPU to Parse Elapsd %:' dscr
, decode(:prsela, 0, to_number(null)
, round(100*:prscpu/:prsela,2)) pctval
,' % Non-Parse CPU:'
, decode(:tcpu, 0, to_number(null)
, round(100*1-(:prscpu/:tcpu),2)) pctval
from sys.dual;
从以上计算公式中可以找到:
出处:墨天轮(https://www.modb.pro/db/6196,复制到网页中打开或者点击“阅读原文”)
扩展阅读

数据和云
ID:OraNews
如有收获,请划至底部,点击“在看”,谢谢!
最后修改时间:2020-05-07 23:38:09
文章转载自数据和云,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。