STATSPACK

 STATSPACK

                         It's Easy with AJEET,


In this Section I am going to provide the steps how to configure stats pack and how to generate stats pack report in oracle.

Stats pack is a earlier version of AWR, which is used for getting details of processes going internally in oracle database and can be used for performance tuning.

Installation of  STATSPACK  needs the following steps:

·    Creating the perfstat tablespace

·    Creating the perfstat user(automatically created)

·    Creating the tables(automatically created)

·    Installing the packages

·    Collecting data

·    Scheduling data collection snapshots with dbms_job

Create tablespace

create tablespace statspack_data datafile '/data/oracle/database/11gR2/oradata/scratch/statspack_data01.dbf' size 2G autoextend on maxsize 5G extent management local uniform size 1M  segment space management auto;

(Note: only tablespace should be created not the user as user perfstat will be created automatically when we run spcreate.sql)

CONFIGURING THE STATSPACK PACKAGE

1.      Conn as sysdba

2.      Run the SPCREATE.SQL file

              SQL> @?/rdbms/admin/spcreate

Enter password: give u r password for  the user perfstat

            Enter default tablespace: statspack_data

            Enter temporary tablespace:temp

3.      If any error occurred  then check the following log files

ORACLE_HOME/bin/spcusr.lis

            ORACLE_HOME/bin/spctab.lis

            ORACLE_HOME/bin/spcpkg.lis

4. Conn as sysdba and make timed statistic to true dynamically

              alter system set TIMED_STATISTICS=TRUE; 

Collecting data

SQL> CONNECT perfstat/log           (The perfstat user will be created automaticaly when we run spcreate.sql)

            SQL> EXECUTE statspack.snap;

            PL/SQL procedure successfully completed.

Scheduling data collection snapshots with dbms_job

Schedule a snapshot to be run on this instance every hour, on the hour

(Note: - Run any one of the below, don’t’ run both or don’t’ run twice it creates the snap how many times  you run this .sql file or the script)

Conn as sysdba

SQL>@?/rdbms/admin/spatuo.sql

(OR)

variable jobno number;

variable instno number;

BEGIN

  SELECT instance_number INTO :instno FROM v$instance;

  DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', TRUNC(sysdate+1/24,'HH'), 'TRUNC(SYSDATE+1/24,''HH'')', TRUE, :instno);

  COMMIT;

END;

/

To check that the job is scheduled in dbjob

SQL>select * from dba_jobs;

(The above configuration must be done only once don’t repeat the above process at the next time, from next time only execute the below statement i.e., run only the spreport.sql)

Generate the stats-pack

(This is to Generate the report from the already generated snaps)

            as a perfstat user

SQL> @?/rdbms/admin/spreport

            The SPREPSQL.SQL report prompts you for the following:

                                    •           Beginning snapshot ID

                                    •           Ending snapshot ID

                                    •           Name of the report text file to be created

To purge the data of stats-pack

Important Note

Note that purging may require the use of a large rollback segment, as all data relating each snapshot to be purged will be deleted. You can get around the issue by issuing the alter session set rollback segment command before running sppurge.sql. The example below shows a sample execution of this script:

SQL> connect perfstat/perfstat
Connected.

SQL> set transaction use rollback segment big_rbs;
Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/sppurge
 

Manual:

Use these two scripts run either sptrunc.sql (to truncate tables of perfstat user) or sppurge.sql (to delete rows from tables of perfstat user)

           

To truncate use this command:

SQL> conn perfstat/log

SQL> @?/rdbms/admin/sptrunc.sql    

 (or)  

To purge use this command:

SQL> @?/rdbms/admin/sppurge

 (We can use this command like “delete from stats$snapshot where snap_id < 10000;”)

Automatic:

Add the following parameters in the sppuge.sql (/rdbms/admin/sppurge.sql) to purge the snaps older than 15 days.

column min_snap_id new_val LoSnapId

column max_snap_id new_val HiSnapId

select nvl(min(s.snap_id), 0) min_snap_id, nvl(max(s.snap_id), 0) max_snap_id

  from perfstat.stats$snapshot s

     , perfstat.stats$database_instance di

                          where s.dbid              = :dbid

                          and di.dbid             = :dbid

                          and s.instance_number   = :inst_num

                          and di.instance_number  = :inst_num

                          and di.startup_time     = s.startup_time

                          and s.snap_time < sysdate-14;

Add this parameter in sppurge.sql or create another file and schedule it in cronjobs. Add start and end of file.

Manually removing STATSPACK snapshots
 

Fortunately, STATSPACK uses foreign-key referential integrity constraints with the ON CASCADE DELETE option. This means that all information for a given snapshot can be deleted by deleting the corresponding stats$snapshot record. For example, suppose that you wanted to delete all snapshots for 2002, and these were snapshots that have a snap_id of less than 10,000. The following DELETE would remove all of these snapshots, and all subordinate detail rows:
 

SQL > delete from stats$snapshot where snap_id < 10000;
28923563 rows deleted.
 

Of course, you can selectively delete rows from STATSPACK. For example, you might want to keep all of the system statistics and delete all of the SQL statements that were more than six months old. In this case, you could selectively remove the rows from stats$sql_summary:

SQL > delete from stats$sql_summary where snap_time < sysdate - 180;
2888363 rows deleted.
 

Now let's take a look at some handy shell scripts that will make your use of STATSPACK easier.
 

Removing ranges of snapshots

The STATSPACK utility provides a batch mode purging function. To run the sppurge.sql script in batch mode, you must assign SQL*Plus variables to identify the low snapshot and the high snapshot for purging.

#!/bin/ksh

# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ORACLE_HOME/bin/sqlplus system/manager<<!

select * from v\$database;
connect perfstat/perfstat
define losnapid=$2
define hisnapid=$3
@sppurge
exit
!

If db_job fails than perform the following steps

If the job fails to run and after checking alert log if we get the error like below

--------------------------------------------------------------------------------------
Mon May 21 06:06:08 2012
Errors in file /XXXX/bdump/PROd_j000_3976.trc:
ORA-12012: error on auto execute of job 23
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 1361
ORA-06512: at "PERFSTAT.STATSPACK", line 2471
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1
--------------------------------------------------------------------------------------
As Job is scheduled through DBMS_JOBS package so it will run 16 times till it will get broken.

(Note: - please check the job no properly before performing the following operation please conform the job no and then you proceeded)

1.     Check the status of the Job from dba_jobs.

select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,LAST_DATE,LAST_SEC,NEXT_DATE,NEXT_SEC,TOTAL_TIME,BROKEN,WHAT,FAILURES from dba_jobs where JOB=41;

The output will give all details about the job 23.(job number from the error)  Please be sure it is stats-pack job only.

(Note: - Once you get the error immediately make the job broken as below. If you don't make it broken it will run 16 times and fail every time. We will get the error in the alert log 16 times.)

2.     Making Job broken manually

Before making the job broken be sure job number is correct.

sqlplus perfstat/<Passwd>

SQL> exec dbms_job.broken(41,TRUE);

PL/SQL procedure successfully completed.

Now the job got broken it won't execute again.
Check again the status

SQL> select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,LAST_DATE,LAST_SEC,NEXT_DATE,NEXT_SEC,TOTAL_TIME,BROKEN,WHAT,FAILURES from dba_jobs where JOB=23;

(After Job got broken it will not execute at all till we make it run once and then automatically it will schedule itself. But for running again we need to wait for some time. )

3.     Now wait for some 2 to 3 hours (Approx. time. We don't know exact   time) and then execute the job again. As this is the issue so till information is not cleared from the v$ tables the job can't run again. If you try it will fail again.

Note:- if you run the job and you got issue again then the broken status of the job will change to "N" and you need to again make it broken again so that it won't run 16 times with same problem.

E.g,
Execute the Job again

sqlplus perfstat/<Passwd>

SQL> exec DBMS_JOB.RUN(41);
[[[[
PL/SQL procedure successfully completed.

Check the Job status again. It will show that it is not broken and is executed the time you run the command and after 30 Minutes it will execute again. please be sure it is running fine.

4.     Again check that the job is running.

SQL> select * from dba_jobs where job=41;

5.     To remove the job

SELECT * FROM dba_jobs_running;

SELECT JOB FROM DBA_JOBS WHERE JOB=21;

BEGIN
EXECUTE DBMS_JOB.REMOVE(21);
COMMIT;
END;

SELECT JOB FROM DBA_JOBS WHERE JOB=21;

                       That's It 

                                                             There you good to go.................................................

Please subscribe for latest updates.