oracle中批量取statspack的脚本

做了一个脚本sprpt_batch.sh:

read line
snap_i_id=$1
end_snap=$2
 
sqlplus -s /nolog< 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中的部分注释掉：

/*
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，可以执行：

sh sprpt_batch.sh
如：

sh sprpt_batch.sh 118712 118713

（二）
如果要取最近30天的statspack，每隔15分钟为间隔，可以在数据库中

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

生成出来的结果如下：

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是一个小时）。
见下：

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