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…

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…

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…