Sunday, March 19, 2006

Oracle Statspack Install And Configure

Oracle statspack is the replacement to the bstat/estat scripts. Statspack is an Oracle provided tool that takes snapshots of database instance performance and can provide report on the performance.
While there is good documentation on statspack, and at least one thick book on statspack, thick documentation may lead people to believe that it is too complicated to learn.

This quick guide covers a simple install and configuration of Oracle statspack (database version 9.2) on unix. Most DBAs should be able to these instructions and have a useful statspack implementation in less that thirty minutes.


High level instructions:

Create perfstat user in new tablespace.
Install statspack schema from $ORACLE_HOME/rdbms/admin.
Schedule statspack snaps.
Create statspack reports.


Detailed instructions:

Navigate to the statspack install files.
$ cd $ORACLE_HOME/rdbms/admin

$ ls -l sp*
-rw-r--r-- 1 oracle dba 1771 Mar 9 2002 spauto.sql
-rw-r--r-- 1 oracle dba 100932 Apr 17 2002 spcpkg.sql
-rw-r--r-- 1 oracle dba 861 Apr 17 2002 spcreate.sql
-rw-r--r-- 1 oracle dba 47661 Apr 17 2002 spctab.sql
-rw-r--r-- 1 oracle dba 9062 Apr 17 2002 spcusr.sql
-rw-r--r-- 1 oracle dba 84109 Apr 17 2002 spdoc.txt
-rw-r--r-- 1 oracle dba 758 Mar 9 2002 spdrop.sql
-rw-r--r-- 1 oracle dba 4883 Mar 9 2002 spdtab.sql
-rw-r--r-- 1 oracle dba 1363 Mar 9 2002 spdusr.sql
-rw-r--r-- 1 oracle dba 8423 Apr 1 2002 sppurge.sql
-rw-r--r-- 1 oracle dba 137070 Apr 17 2002 sprepins.sql
-rw-r--r-- 1 oracle dba 1284 Mar 9 2002 spreport.sql
-rw-r--r-- 1 oracle dba 27197 Apr 17 2002 sprepsql.sql
-rw-r--r-- 1 oracle dba 2850 Mar 9 2002 sptrunc.sql
-rw-r--r-- 1 oracle dba 588 Mar 9 2002 spuexp.par
-rw-r--r-- 1 oracle dba 30684 Apr 18 2002 spup816.sql
-rw-r--r-- 1 oracle dba 23329 Apr 18 2002 spup817.sql
-rw-r--r-- 1 oracle dba 19129 Apr 18 2002 spup90.sql

Review the installation help file.
$ more spdoc.txt
< snip >
... the minimum space requirement is approximately 100MB.

After locating a suitable disk location, create the tablespace.
$ sqlplus '/ as sysdba'

SQL> CREATE TABLESPACE PERFSTAT LOGGING
DATAFILE '/u109/oradata/DMPRD/perfstat01.dbf' SIZE 100M REUSE AUTOEXTEND
ON NEXT 10M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
SQL> /
Tablespace created.

Create the perfstat user. "Perfstat" is hard coded in Oracle's create scripts.
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spcreate
There will be prompts for the perfstat user's password, default tablepace, and temporary tablespace.

Check spool files for errors. Note the password is shown in a spool file.
$ more spc*.lis

Verify instance timed_statistics is set to true. Statspack reports are less useful if this is set to false.
$ sqlplus '/ as sysdba'

SQL> show parameter timed_statistics

NAME TYPE VALUE
------------------------------------ -----------------------------------------
timed_statistics boolean TRUE

Verify job_queue_processes is at least one.
$ sqlplus perfstat


SQL> show parameter job
NAME TYPE VALUE
------------------------------------ -----------------------------------------
job_queue_processes integer 10

Schedule the "snap" job with the PERFSTAT user. By default, this will schedule a level 5 snapshot. Snapping every 15 - 30 minutes is generally appropriate. Longer snap intervals (like an hour) often will not provide enough granular details. Install the job as the perfstat user.

$ sqlplus perfstat
SQL> variable jobNbr number;
begin
dbms_job.submit( :jobNbr, 'statspack.snap;', trunc(sysdate), 'sysdate+1/48');
end;
SQL> 2 3
4 /

PL/SQL procedure successfully completed.

SQL> col priv_user form a11
SQL> col schema_user form a11
SQL> col log_user form a11

SQL> select JOB, NEXT_DATE, NEXT_sec, INTERVAL, WHAT
from user_jobs
where job = :jobNbr
2 3
4 /

JOB NEXT_DATE NEXT_SEC
---------- ------------------- --------
INTERVAL
--------------------------------------------------------------------------------
WHAT
--------------------------------------------------------------------------------
2 2006-03-15 00:00:00 00:00:00
sysdate+1/48
statspack.snap;

Oracle provides an interactive script that allows choosing the instance and time frames for the report.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus '/ as sysdba' @spreport.sql

If you decide statspack information is too valuable and might put you out of a job then you may want to remove it. Simply drop the perfstat user.
$ sqlplus '/ as sysdba'
SQL> drop user perfstat cascade;
SQL> drop tablespace perfstat;

Snapshots are stored in the perfstat tablespace, so it will be necessary to remove old snapshots when the tablespace is nearly full. $ORACLE_HOME/rdbms/admin/sppurge.sql is an interactive removal script that prompts the user for a range of snaphot IDs.

==========================================================================
The following instructions may not be appropriate for you environment. Use at your own risk.
==========================================================================



Install custom PL/SQL package that contains purge logic. 10g statspack.purge reduces the needed DELETE SQL.

$ sqlplus perfstat
SQL> create or replace package statspackCustom
as

procedure purge ( p_daysBack in number );
end statspackcustom;
/
show err

create or replace package body statspackCustom as
procedure purge ( p_daysBack in number )
is
minsnap number;
maxsnap number;
snapshots_purged number;
ldbid number;
linst number;
begin

select case when min(snap_id) is null then 0 else min(snap_id) end
into minsnap
from stats$snapshot
where snap_time < sysdate - p_daysBack;

select case when max(snap_id) is null then 0 else max(snap_id) end
into maxsnap
from stats$snapshot
where snap_time < sysdate - p_daysBack;

/* 10g
snapshots_purged := statspack.purge( i_begin_snap => minsnap
, i_end_snap => maxsnap
, i_snap_range => true
, i_extended_purge => false);
*/

/* 9.2, SQL from $ORACLE_HOME/rdbms/admin/sppurge.sql.*/
select dbid
into ldbid
from v$database;
select instance_number
into linst
from v$instance;

delete from stats$snapshot
where instance_number = linst
and dbid = ldbid
and snap_id between minsnap and maxsnap;

delete
from stats$undostat us
where dbid = ldbid
and instance_number = linst
and begin_time < sysdate - p_daysBack;

delete from stats$database_instance di
where instance_number = linst
and dbid = ldbid
and not exists (select 1
from stats$snapshot s
where s.dbid = di.dbid
and s.instance_number = di.instance_number
and s.startup_time = di.startup_time);

delete from stats$statspack_parameter sp
where instance_number = linst
and dbid = ldbid
and not exists (select 1
from stats$snapshot s
where s.dbid = sp.dbid
and s.instance_number = sp.instance_number);

end purge;
end statspackcustom;
/
show err

Schedule the purge to retain the most recent fifteen days.
$ sqlplus perfstat
SQL> col priv_user form a11
SQL> col schema_user form a11
SQL> col log_user form a11

SQL> variable jobNbr number;
begin
dbms_job.submit( :jobNbr, 'statspackCustom.purge(15);', trunc(sysdate), 'trunc(sysdate)+1');
end;
SQL> 2 3
4 /

PL/SQL procedure successfully completed.


Check jobs. Broken or failed jobs should be corrected.
$ sqlplus /
SQL> col priv_user form a11
SQL> col schema_user form a11
SQL> col log_user form a11
1* select what,broken, failures from dba_jobs where schema_user='PERFSTAT'
SQL> /

WHAT
--------------------------------------------------------------------------------
B FAILURES
- ----------
statspackCustom.purge(15);
N 0

statspack.snap;
N 0





...UNDER CONSTRUCTION...

It can be useful to automatically create reports for every snap period and for every day. This is custom code you can develop.

The script that creates the statspack report is in bighost:/export/home/oracle/local/statspackSpreport.ora.ksh.

Place these line in oracle's crontab to schedule the script.
# Report for most recent snap period - not necessarily an hour. Run after snap job completes. 10,40 assumes 30 minute snap starting on hour.
10,40 * * * * WEBDB=/tmp; export WEBDB; ORACLE_HOME=/apps/oracle/product/9.2.0; export ORACLE_HOME; ORACLE_SID=DMPRD; export ORACLE_SID; /bin/umask 002; /export/home/oracle/local/statspackSpreport.ora.ksh $ORACLE_SID MOSTRECENT >> /tmp/$ORACLE_SID.statspackSpreport.mostrecent.out 2>&1

1 comment:

  1. We encountered a unique constraint violation. Setting cursor_sharing to exact is a fix.

    $ cat trigger.sql
    prompt -- First give permission: grant create trigger to perfstat;

    set define off
    create or replace trigger logontrigger after logon on schema
    declare
    p_sql varchar2(230);
    begin

    -- ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
    -- http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=305300.995
    -- bug 2784796
    --
    -- Set cursor_sharing to exact.

    p_sql:='alter session set cursor_sharing=exact';
    execute immediate p_sql;
    end;
    /

    ReplyDelete