做了一个脚本sprpt_batch.sh:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
read line snap_i_id=$1 end_snap=$2 sqlplus -s /nolog<<EOF conn /as sysdba; define begin_snap=${snap_i_id}; define end_snap=${end_snap}; define report_name=sprpt_batch_${snap_i_id}_${end_snap}.txt set echo off set feedback off @myspreport exit EOF |
将$ORACLE_HOME/rdbms/admin/spreport.sql和$ORACLE_HOME/rdbms/admin/sprepins.sql拷贝到工作目录下，重命名成myspreport.sql和mysprepins.sql
将mysprepins.sql中的部分注释掉：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
/* select to_char(s.startup_time,' dd Mon "at" HH24:mi:ss') instart_fmt , di.instance_name inst_name , di.db_name db_name , s.snap_id snap_id , to_char(s.snap_time,'dd Mon YYYY HH24:mi') snapdat , s.snap_level lvl , substr(s.ucomment, 1,60) commnt from stats$snapshot s , stats$database_instance di where s.dbid = :dbid and di.dbid = :dbid and s.instance_number = :inst_num and di.instance_number = :inst_num and di.dbid = s.dbid and di.instance_number = s.instance_number and di.startup_time = s.startup_time order by db_name, instance_name, snap_id; */ |
因为这一部分是显示statspack的保存记录的时间和snap id的。我们做成脚本，就不需要让他们显示了。
(一)
取单个statspack，可以执行：
|
1 |
sh sprpt_batch.sh <begin_snap_id> <end_snap_id> |
如：
|
1 |
sh sprpt_batch.sh 118712 118713 |
（二）
如果要取最近30天的statspack，每隔15分钟为间隔，可以在数据库中
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
sqlplus "/ as sysdba" Set line 300 Set pages 10000 Spool exec_script.sh select script_text from (select 'sh sprpt_batch.sh ' || lag(snap_id) over(partition by startup_time order by snap_id) || ' ' || snap_id || chr(10) || chr(10) as script_text, lag(snap_id) over(partition by startup_time order by snap_id) as last_value, a.* from STATS$SNAPSHOT a order by snap_id desc ) where last_value is not null and snap_time>=sysdate-30 spool off |
生成出来的结果如下：
|
1 2 3 4 5 |
sh sprpt_batch.sh 119082 119083 sh sprpt_batch.sh 119081 119082 sh sprpt_batch.sh 119080 119081 sh sprpt_batch.sh 119079 119080 …… |
然后执行这个exec_script.sh脚本，就可以批量的生成statspack了。
（三）如果需要每个1小时一个statspack，这个也可以做到，只需将snap_id排序，mod取4整除，（因为每4个snap id是一个小时）。
见下：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select 'sh sprpt_batch.sh ' || last_value || ' ' || snap_id || chr(10) || chr(10) as script_text from (select lag(snap_id) over(partition by startup_time order by snap_id) as last_value, kk.* from (select mod(rank() over(partition by startup_time order by snap_id), 4) as by_hour, a.* from STATS$SNAPSHOT a) kk where by_hour = 1) where last_value is not null and snap_time >= sysdate - 30 |
一条评论
这个太赞了