4
自己常用的awr脚本，包含部分awr裸数据的分析
5墨值下载
1、dba_hist_sys_time_model 视图应用
DBA_HIST_SYS_TIME_MODEL displays historical system time model statistics.
This view contains snapshots of V$SYS_TIME_MODEL 视图的信息其实对应的 AWR 的 Time
Model Statistics，比如我们可以通过这个视图来查看每个 snapshot 的 db time、硬解析、解析、解
析错误、sql 执行时间、序列加载时间、连接数据库的时间、rman 备份恢复时间占据具体时间等
SQL> select distinct stat_name from dba_hist_sys_time_model order by stat_name;
STAT_NAME
----------------------------------------------------------------
DB CPU
DB time
Java execution elapsed time
OLAP engine CPU time
OLAP engine elapsed time
PL/SQL compilation elapsed time
PL/SQL execution elapsed time
RMAN cpu time (backup/restore)
background IM population cpu time
background IM population elapsed time
background IM prepopulation cpu time
background IM prepopulation elapsed time
background IM repopulation cpu time
background IM repopulation elapsed time
background IM trickle repopulation cpu time
background IM trickle repopulation elapsed time
background cpu time
background elapsed time
connection management call elapsed time
failed parse (out of shared memory) elapsed time
failed parse elapsed time
hard parse (bind mismatch) elapsed time
hard parse (sharing criteria) elapsed time
hard parse elapsed time
inbound PL/SQL rpc elapsed time
parse time elapsed
repeated bind elapsed time
sequence load elapsed time
sql execute elapsed time
dba_hist_sysstat 视图中的 value 单位 cs 单位,dba_hist_sys_time_model 视图中的单位是 us 单位
DB TIME 波动：
set linesize 220 pagesize 1000
col begin_interval_time for a30
col end_interval_time for a30
col stat_name for a40
WITH sysstat
AS (
SELECT ss.instance_number inst_id,
sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.VALUE e_value,
LAG(ss.VALUE) OVER(partition by ss.instance_number ORDER BY ss.snap_id)
b_value
FROM dba_hist_sys_time_model ss, dba_hist_snapshot sn
WHERE sn.begin_interval_time >= SYSDATE - 4
AND ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.dbid = (SELECT dbid FROM v$database)
and ss.stat_name = 'DB time'
and ss.instance_number in (select instance_number from v$instance)
)
select inst_id,
begin_interval_time,
end_interval_time,
stat_name,
round((e_value - b_value)/1000/1000/60) value_min
from sysstat
order by 2 desc, 3 desc;
INST_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
STAT_NAME VALUE_MIN
---------- ------------------------------ ------------------------------
---------- ----------
1 06-DEC-17 10.15.05.210 AM 06-DEC-17 10.30.10.851 AM DB time
1323
1 06-DEC-17 10.00.37.915 AM 06-DEC-17 10.15.05.210 AM DB time
1546
1 06-DEC-17 09.45.30.655 AM 06-DEC-17 10.00.37.915 AM DB time
1491
1 06-DEC-17 09.30.24.044 AM 06-DEC-17 09.45.30.655 AM DB time
1297
1 06-DEC-17 09.15.17.041 AM 06-DEC-17 09.30.24.044 AM DB time
1186
1 06-DEC-17 09.00.10.499 AM 06-DEC-17 09.15.17.041 AM DB time
1337
1 06-DEC-17 08.45.03.642 AM 06-DEC-17 09.00.10.499 AM DB time
800
1 06-DEC-17 08.30.22.213 AM 06-DEC-17 08.45.03.642 AM DB time
677
1 06-DEC-17 08.15.16.649 AM 06-DEC-17 08.30.22.213 AM DB time
618
1 06-DEC-17 08.00.10.584 AM 06-DEC-17 08.15.16.649 AM DB time
669
1 06-DEC-17 07.45.04.891 AM 06-DEC-17 08.00.10.584 AM DB time
440
1 06-DEC-17 07.30.31.712 AM 06-DEC-17 07.45.04.891 AM DB time
416
1 06-DEC-17 07.15.25.140 AM 06-DEC-17 07.30.31.712 AM DB time
412
1 06-DEC-17 07.00.18.771 AM 06-DEC-17 07.15.25.140 AM DB time
647
1 06-DEC-17 06.45.11.962 AM 06-DEC-17 07.00.18.771 AM DB time
716
1 06-DEC-17 06.30.05.566 AM 06-DEC-17 06.45.11.962 AM DB time
562
1 06-DEC-17 06.15.29.852 AM 06-DEC-17 06.30.05.566 AM DB time
604
1 06-DEC-17 06.00.23.776 AM 06-DEC-17 06.15.29.852 AM DB time
603
1 06-DEC-17 05.45.15.877 AM 06-DEC-17 06.00.23.776 AM DB time
629
1 06-DEC-17 05.30.09.976 AM 06-DEC-17 05.45.15.877 AM DB time
of 19
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论