statspack report

[Nirmalkumar]

Hi all,

i want to create the script for statspack report in a automated way..

The below script is automated snap id creation script

/bin/sh
ORACLE_SID=DWHDWP
export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/9.2.0.6
export ORACLE_HOME

$ORACLE_HOME/bin/sqlplus 'perfstat/perfstat' << EOF

spool /local/app/ora9i/STATSPACK_RUN/statspack_run.log

execute statspack.snap

spool off
quit
EOF

cat /local/app/ora9i/STATSPACK_RUN/statspack_run.log | mailx -s "STATSPACK_RUN" -r NJCSORDBDWHOR01@autodesk.com hcl.dba@autodesk.com

we scheduled it the above script in crontab in various timings.

After that we manually create the statsreport for various snap id.

Now i want to create a statsreport in a automated way(by creating a script) and will mail to the respective mail id.

could u please give ur ideas to create a script.

Thanks,
Nirmal.

[Steven E. Protter]

Shalom,

What you need to understand generally is:

1) cron has no environment. No ORACLE_SID,no PATH, no nothing. Your script must set everything up for Oracle prior to running any commands.

2) That spooled log may get awfully big so beware of blowing default sendmail message size restrictions which are 2 MB if memory serves.

SEP

[Yogeeraj_1]

Hi Nirmal,

A few clarifications:
1. To be able to generate a meaningful Statspack report, you should generated two snapshots within an interval of time. Generally accepted intervals are of 15mins.

You may wish to automate this operation at specific periods of time during the operation hours of your system using the DBMS_JOB or DBMS_SCHEDULER packages.

A sample script on how to do this is supplied in ?/rdbms/admin/spauto.sql, which schedules a snapshot every hour, on the hour.

2. After you have generated the two snapshots, you will have to run the spreport.sql script to produce the Statspack Report.

e.g.
SQL> connect perfstat/perfstat
SQL> @?/rdbms/admin/spreport

You will be prompted for:
1. The beginning snapshot Id
2. The ending snapshot Id
3. The name of the report text file to be created

For more information, please refer to Metalink Note 94224.1 - FAQ - STATSPACK COMPLETE REFERENCE

hope this helps!

kind regards
yogeeraj

[Nirmalkumar]

Hi yogeeraj,

Thank u for ur reply..

iam able to get statsreport manually by running spreport.sql script with begin and end snap ids.

My question is could u please help me out to create a shell script to send a statsreport through mail in automated way.

Thanks,
Nirmal.

[Yogeeraj_1 · Accepted solution]

hi nirmal,

unfortunately, i am running oracle10g 10g which automates this via a thing called AWR (automatic workload repository), it just uses the database to hold data.

I have not configured the database for statspack so i cannot really test.

But basically, what you will need to do is:
1. determine the 2 snap ids
2. run sqlplus / @script parm1 parm2 parm3....
3. Email the report file to the desired recipients.

hope this helps!

kind regards
yogeeraj

[Hein van den Heuvel]

>> After that we manually create the statsreport for various snap id.

>> Now i want to create a statsreport in a automated way(by creating a script) and will mail to the respective mail id.


I always thought it was a bit of challenge to automatically create a statspack report, that is without waiting for the prompt to select the begin and end snapshot numbers.

So for my benchmark work I adapted the standard spreport.sql to select specific snaps relative to the end.

You can readily adapt this again to select a specific snap comment or other criteria.

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

Rem
Rem sphigh.sql, Cloned from spreport.sql by Hein van den Heuvel. Apr 2004
Rem set up statspack parameters for high-load phase being 2 snaps away.
Rem

--
-- Get the current database/instance information - this will be used
-- later in the report along with bid, eid to lookup snapshots

column snap_id format 9990 heading 'Snap|Id';
column begin_snap heading "Begin Snap" new_value begin_snap format 99999;
column end_snap heading "End Snap" new_value end_snap format 99999;
column report_name new_value report_name noprint;

column inst_num heading "Inst Num" new_value inst_num format 99999;
column inst_name heading "Instance" new_value inst_name format a12;
column db_name heading "DB Name" new_value db_name format a12;
column dbid heading "DB Id" new_value dbid format 9999999999 just c;

select d.dbid dbid
, d.name db_name
, i.instance_number inst_num
, i.instance_name inst_name
, s.snap_id snap_id
, (s.snap_id - 1) end_snap
, (s.snap_id - 2) begin_snap
, 'sph' || to_char(snap_time, 'YYYYMMDDHH24MISS')||
'_'||(s.snap_id - 2)||'_'||(s.snap_id - 1) report_name
from v$database d,
v$instance i,
stats$snapshot s
where s.snap_id = (select max(snap_id) from stats$snapshot )
and d.dbid = s.dbid
and i.instance_number = s.instance_number;

@/benchmark/sql/sprepins
--
-- End of file