A bash script for generating multiple STATSPACK reports.
GOAL

You can automate STATSPACK reports generation in the Oracle Database by using the dbs_gstatspack.sh bash script. The provided script simplifies the STATSPACK generation process when there is a need to generate multiple STATSPACK reports quickly for database analysis.

SOLUTION

The script is meant to generate multiple STATSPACK reports between 2 snap IDs. The script requires a begin snap ID, an end snap ID, the interval between snap IDs, and the directory path for storing generated reports.

s|start      - A begin SNAP ID 
e|end        - An end SNAP ID 
i|increment  - [Optional] A number of snapshots in a report. The default is 1.
d|directory  - [Optional] A directory for storing STATSPACK reports. The default is the script directory.
debug        - [Optional] Enables debug output when executing the script

For instance 

## To generate 1 STATSPACK report based on 5 snapshots 

./dbs_gstatspack.sh s=1 e=5 i=5 

## To generate 4 STATSPACK reports based on 2 adjacent snapshots each
## Snapshots ranges are 1-2, 2-3, 3-4, 4-5

./dbs_gstatspack.sh s=1 e=5 i=1
NOTE

Convert the script from DOS text file format to UNIX text file format

{
wget https://dbpilot.net/wp-content/uploads/2022/01/dbs_gstatspack.v003.zip --no-check-certificate
unzip dbs_gstatspack.v003.zip
chmod 500 dbs_gstatspack.sh
dos2unix dbs_gstatspack.sh
}

Example of the script usage

oracle@dbpilot|16:31|~:<meridb2>/home/oracle/dbs_gstatspack.sh s=27239 e=27243 i=1 d=/home/oracle/statspack/07APR2021

   Number of snapshots         : 353
   Lower available snapshot    : 26883
   Highest available snapshot  : 27243

   Requested lower snapshot    : 27239 (07-APR-2021 12:00)
   Requested upper snapshot    : 27243 (07-APR-2021 16:00)
   Requested snapshot interval : 1
   Directory for reports       : /home/oracle/statspack/07APR2021

   27239 27240
   27240 27241
   27241 27242
   27242 27243
   
oracle@dbpilot|16:32|~:<meridb2>ll /home/oracle/statspack/07APR2021

-rw-r--r-- 1 oracle oinstall 229752 Apr  7 16:32 MERIDB2_07_APR_2021_1200_1300_27239_27240.lst
-rw-r--r-- 1 oracle oinstall 207261 Apr  7 16:32 MERIDB2_07_APR_2021_1300_1400_27240_27241.lst
-rw-r--r-- 1 oracle oinstall 217646 Apr  7 16:32 MERIDB2_07_APR_2021_1400_1500_27241_27242.lst
-rw-r--r-- 1 oracle oinstall 200371 Apr  7 16:32 MERIDB2_07_APR_2021_1500_1600_27242_27243.lst

Written At
09 JAN 202223:30
Red Hat Release
7.x x64
Database Release
19.13.0.0.0