Category: Statspack

General DBA, Statspack

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 … Continue reading How to schedule a snap for STATSPACK?

Alex LimaLeave a comment
General DBA, Statspack

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 … Continue reading How to delete or truncate Statspack data?

Alex Lima1 Comment
Statspack, Tuning

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” … Continue reading How to setup/install StatsPack

Alex LimaLeave a comment