为了分析这几天数据库状况,进行statspack report快照统计信息分析,特此编写如下脚本方便执行收集所需信息。
alter session set nls_date_format='yyyymmdd-hh24miss';
select snap_id,snap_time from stats$snapshot order by snap_id;
--查询从哪天哪个快照开始执行,准备执行收集到哪天哪个快照。
--这里为收集1400到1406共7组快照统计信息(快照每半小时执行一次),即2007年08月16日-13:00~2007年08月16日-16:30的快照信息。
set serveroutput on
spool statspack_report_scripts.txt
declare
sp_exe varchar2(100);
sp_id1 varchar2(10);
sp_id2 varchar2(10);
sp_name varchar2(150);
begin
for i in 1400..1406 loop
select '@?/rdbms/admin/spreport.sql' into sp_exe from dual;
select to_char(snap_id) into sp_id1 from stats$snapshot where snap_id=i;
select to_char(snap_id) into sp_id2 from stats$snapshot where snap_id=i+1;
select 'hnmyz1_sp_'||to_char(b.snap_time,'yyyymmdd')||'-'||to_char(b.snap_time,'hh24mi')||'-'||
(select to_char(a.snap_time,'hh24mi') from stats$snapshot a where a.snap_id=b.snap_id+1)
into sp_name
from stats$snapshot b where snap_id=i;
dbms_output.put_line(sp_exe);
dbms_output.put_line(sp_id1);
dbms_output.put_line(sp_id2);
dbms_output.put_line(sp_name);
end loop;
end;
/
spool off
输出结果:
17:17:37 23 /
@?/rdbms/admin/spreport.sql
1400
1401
hnmyz1_sp_20070816-1300-1330
@?/rdbms/admin/spreport.sql
1401
1402
hnmyz1_sp_20070816-1330-1400
@?/rdbms/admin/spreport.sql
1402
1403
hnmyz1_sp_20070816-1400-1430
@?/rdbms/admin/spreport.sql
1403
1404
hnmyz1_sp_20070816-1430-1500
@?/rdbms/admin/spreport.sql
1404
1405
hnmyz1_sp_20070816-1500-1530
@?/rdbms/admin/spreport.sql
1405
1406
hnmyz1_sp_20070816-1530-1600
@?/rdbms/admin/spreport.sql
1406
1407
hnmyz1_sp_20070816-1600-1630
PL/SQL procedure successfully completed.
17:17:37 SQL> spool off
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/93855/viewspace-962357/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/93855/viewspace-962357/