Use these steps to install Statepack

  1. Execute the command : export Oracle_SID=qdmsprod
  2. Login into Oracle with the sys user.
  3. Create a tablespace Statspack with a size of approximately 150-250 Mb. This is used for collecting the database statistics.

    SQL>create tablespace statspack datafile ‘<datafile-path>/stats01.ora’ size 250m;
  4. Install Statspack. Execute spdrop.sql if statspack is already installed.

    SQL> @<oracle_home>/rdbms/admin/spdrop.sql
    Execute spcreate.sql to install statspack
    SQL> @<oracle_home>/rdbms/admin/spcreate.sql

    Enter the following values for the different items it prompts:

i) Password for the user perfstat – quarkdms
ii) Default Tablespace – statspack
iii) Temporary Tablespace – Any Temporary tablespace of the database

  1. Connect with the user perfstat:

    SQL> conn perfstat/quarkdms
    Execute the script spauto.sql to automate the collection of statspack statistics every hour

Important Note: Please note the Job ID of the job.