♣
题目 部分【DB笔试面试818】在Oracle中,如何获取ASH报告?
♣答案部分
获取ASH报告可以有3种方式:(1)脚本生成,(2)OEM生成,(3)存储过程生成。
(1)ASH报告生成脚本如下所示,根据提示输入相应的值即可获得ASH报告。
l Linux:@?/rdbms/admin/ashrpt.sql
l Windows:@?\rdbms\admin\ashrpt.sql
(2)使用OEM,可以在性能页,单击“运行ASH报告”按钮生成ASH报告,由于OEM生产用的相对比较少,这里就不讨论了。
(3)可以利用存储过程DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML()来获取ASH报告的文本内容,然后将文本内容拷贝到文本文件中,最后修改文本文件的后缀名为html即可打开html格式的ASH报告。例如取3116-3117之间的快照:
1SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(3424884828,1, (SELECT A.END_INTERVAL_TIME FROM DBA_HIST_ASH_SNAPSHOT A WHERE A.SNAP_ID =3116) , (SELECT A.END_INTERVAL_TIME FROM DBA_HIST_ASH_SNAPSHOT A WHERE A.SNAP_ID =3117)));
可以利用如下的脚本来批量生成要运行的存储过程:
1SELECT D.SNAP_ID,
2 D.DBID,
3 D.INSTANCE_NUMBER,
4 (SELECT (NB.SNAP_INTERVAL)
5 FROM DBA_HIST_WR_CONTROL NB) SNAP_INTERVAL,
6 (SELECT (NB.RETENTION)
7 FROM DBA_HIST_WR_CONTROL NB) RETENTION,
8 TO_CHAR(D.STARTUP_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') STARTUP_TIME,
9 TO_CHAR(D.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') BEGIN_INTERVAL_TIME,
10 TO_CHAR(D.END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') END_INTERVAL_TIME,
11 (D.FLUSH_ELAPSED) FLUSH_ELAPSED,
12 D.SNAP_LEVEL,
13 D.ERROR_COUNT,
14 D.SNAP_FLAG,
15 'SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(' ||
16 D.DBID || ',' || D.INSTANCE_NUMBER ||
17 ', (SELECT A.END_INTERVAL_TIME FROM DBA_HIST_ASH_SNAPSHOT A WHERE A.SNAP_ID =' ||
18 (D.SNAP_ID - 1) ||
19 ') , (SELECT A.END_INTERVAL_TIME FROM DBA_HIST_ASH_SNAPSHOT A WHERE A.SNAP_ID =' || (D.SNAP_ID) || ')));' ASH_REPORT
20 FROM DBA_HIST_ASH_SNAPSHOT D
21 ORDER BY D.SNAP_ID DESC;