--获取 dbid
select dbid from v$database;
--获取 实例号
select instance_number from v$instance;
--查看dba_advisor_log视图获取历史快照信息 (注:在快照任务名称里也可以找到对应的dbid和instance_num)
select * from dba_advisor_log order by task_id desc ;
--查看AWR报告信息
/*
L_DBID:dbid L_INST_NUM:instance_number 后两个参数为起始终止快照id (注:being_snap与end_snap两个快照之间,数据库是不能有重启记录的),
对于awr报告条件许可的情况,最好利用图形工具查看.排版便于阅读,该包还有另一个函数 awr_report_html,参数一样,查询复制出来保存为html文件查看
*/
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(L_DBID => 3333333333,
L_INST_NUM => 1,
L_BID => 33332,
L_EID => 33333));
--查看ADDM报告 (输出为clob类型)
SELECT DBMS_ADDM.GET_REPORT('ADDM:3333333333_1_33333') FROM DUAL;
--获取AWR报告里的top5 events
WITH TEMP_VIEW AS
(SELECT OUTPUT, ROWNUM R
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(L_DBID => 3333333333,
L_INST_NUM => 1,
L_BID => 33332,
L_EID => 33333)))
select a.output
from temp_view A, temp_view B
where B.output like 'Top 5 Timed Foreground Events%'
and A.r between B.r and B.r + 10
order by a.r
select dbid from v$database;
--获取 实例号
select instance_number from v$instance;
--查看dba_advisor_log视图获取历史快照信息 (注:在快照任务名称里也可以找到对应的dbid和instance_num)
select * from dba_advisor_log order by task_id desc ;
--查看AWR报告信息
/*
L_DBID:dbid L_INST_NUM:instance_number 后两个参数为起始终止快照id (注:being_snap与end_snap两个快照之间,数据库是不能有重启记录的),
对于awr报告条件许可的情况,最好利用图形工具查看.排版便于阅读,该包还有另一个函数 awr_report_html,参数一样,查询复制出来保存为html文件查看
*/
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(L_DBID => 3333333333,
L_INST_NUM => 1,
L_BID => 33332,
L_EID => 33333));
--查看ADDM报告 (输出为clob类型)
SELECT DBMS_ADDM.GET_REPORT('ADDM:3333333333_1_33333') FROM DUAL;
--获取AWR报告里的top5 events
WITH TEMP_VIEW AS
(SELECT OUTPUT, ROWNUM R
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(L_DBID => 3333333333,
L_INST_NUM => 1,
L_BID => 33332,
L_EID => 33333)))
select a.output
from temp_view A, temp_view B
where B.output like 'Top 5 Timed Foreground Events%'
and A.r between B.r and B.r + 10
order by a.r