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 … Continue reading How to schedule a snap for STATSPACK?
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 … Continue reading How to delete or truncate Statspack data?
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” … Continue reading How to setup/install StatsPack