How to schedule a snap for STATSPACK?

One can automate taking of snapshots of STATSPACK by executing DBMS_JOB or by scheduling it through crontab.

Schedule automatic STATSPACK through DBMS_JOB – By executing @?/rdbms/admin/spauto.sql it will schedule to run statspack once every hour which is the default.

Output:
SQL> connect perstat@TESTDB
Password: **********
SQL> @?/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

JOBNO
———-
2

Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter – the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC
———- ——— ——–
2 27-DEC-11 19:00:00

One can view the job by executing the following SQL.

SQL> select * FROM User_jobs WHERE job = 2;

JOB LOG_USER PRIV_USER
———- —————————— ——————————
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
—————————— ——— ——– ——— ——– ———
NEXT_SEC TOTAL_TIME B
——– ———- –
INTERVAL
——————————————————————————–
FAILURES
———-
WHAT
——————————————————————————–
NLS_ENV
——————————————————————————–
MISC_ENV INSTANCE
—————————————————————- ———-
1 SYS SYS
SYS 27-DEC-11
19:00:00 0 N
trunc(SYSDATE+1/24,'HH')

statspack.snap;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000 1

To change the snap interval one can execute dbms_job.interval in the example below it changes to collect every 1/2 hr
SQL> exec dbms_job.interval(1, 'trunc(SYSDATE+1/48,''HH'')');

PL/SQL procedure successfully completed.

SQL> select * FROM User_jobs;

JOB LOG_USER PRIV_USER
———- —————————— ——————————
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
—————————— ——— ——– ——— ——– ———
NEXT_SEC TOTAL_TIME B
——– ———- –
INTERVAL
——————————————————————————–
FAILURES
———-
WHAT
——————————————————————————–
NLS_ENV
——————————————————————————–
MISC_ENV INSTANCE
—————————————————————- ———-
1 SYS SYS
SYS 27-DEC-11
19:00:00 0 N
trunc(SYSDATE+1/48,'HH')

statspack.snap;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000 1

Advertisements

How to delete or truncate Statspack data?

One can truncate data in STATSPACK or delete range of snaps stored.

To truncate STATSPACK data

Using $ORACLE_HOME/rdbms/admin/sptrunc.sql, one can truncate STATSPACK data. To run the script you will need to connect as PERFSTAT user.

perfstat@TESTDB> @?/rdbms/admin/sptrunc.sql

Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables. You may
wish to export the data before continuing.

About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. &'exit&'), otherwise if you would like to begin
the truncate operation, press

Enter value for begin_or_exit:

To delete STATSPACK data – This will prompt for starting snap id and end snap id.
One can run the following SQL that will show all the snap ids on the current instance.
select s.snap_id
, to_char(s.snap_time,&' dd Mon YYYY HH24:mi:ss&') snap_date
, s.baseline
, s.snap_level #level#
, di.host_name host_name
, s.ucomment
from perfstat.stats$snapshot s
, perfstat.stats$database_instance di
, v$database v
, v$instance i
where s.dbid = v.dbid
and di.dbid = v.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.startup_time = s.startup_time
order by di.db_name, i.instance_name, s.snap_id
/

SNAP_ID SNAP_DATE B level
———- ——————— – ———-
HOST_NAME
—————————————————————-
UCOMMENT
——————————————————————————–
1 27 Dec 2011 18:30:56 5
MACHINE
2 27 Dec 2011 18:30:58 6
MACHINE

How to setup/install StatsPack

Statspack can be installed by executing the following script, it will prompt for password for user PERFSTAT, the tablespace for PERFSTAT and temporary tablespace for PERFSTAT.

-- script to install statspack, the script writes output to spckpg.lis and after the script you are logged in as user PERFSTAT
@?/rdbms/admin/spcreate.sql

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
SQL> show user;
USER is “PERFSTAT”

-- script to install statspack, login as sysdba
@?/rdbms/admin/spdrop.sql