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
- 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.
