StatsPack Fast Guide

We have a performance issue in database, and this database not have AWR license? no problem statspack is free and comes standard. Can create a report for example of 10-15 minutes in peek time for example what is waiting for dataase in this time, heavy querys,etc.

First, what is Statspack?

STATSPACK is a performance diagnosis tool, available since Oracle8i. STATSPACK can be considered BSTAT/ESTAT’s successor, incorporating many new features. STATSPACK is a diagnosis tool for instance-wide performance problems; it also supports application tuning activities by providing data which identifies high-load SQL statements. STATSPACK can be used both proactively to monitor the changing load on a system, and also reactively to investigate a performance problem.

1) Installation

create tablespace and user perfstat
ex:
create tablespace PERFSTAT datafile ‘+DATA’ size 150M autoextend on maxsize 2000M;

create user and tables: @?/rdbms/admin/spcreate

Or u can manual create user:
create user perfstat identified by perfstat default tablespace PERFSTAT;
grant connect,resource to perfstat;

2) Run manual snapshot:

with perfstat user:
exec statspack.snap;

3) Shedule auto snaps

with perfstat user execute next sql, by default is one snap each 1h

@$ORACLE_HOME/rdbms/admin/spauto.sql

we can modify for example if we have a issue with performance problem
to 15 minutes:

exec dbms_job.change(21,null,null,’SYSDATE +(1/144)’);

or maybe 30 min:

[ to_char(sysdate+(1/48) ]

or 3 hours:

exec dbms_job.change(4004,null,null,’SYSDATE +(3/24)’);

We can use if you want the DBMS_SCHEDULER to create the job
like this:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘snap_1’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘statspack.snap’,
repeat_interval => ‘FREQ=HOURLY; BYHOUR=9,12,15; BYMINUTE=5,15’,
auto_drop => FALSE,
enabled => TRUE,
comments => ‘Statspack automated snap’);
END;
A tip, update Statistics of the table if this is going to get frequent snaps:

exec dbms_stats.gather_schema_stats(ownname=>’perfstat’);

If u want stop job auto snaps:

exec SYS.DBMS_IJOB.BROKE(<job_num>,TRUE)

Want drop job:

exec SYS.DBMS_IJOB.REMOVE(<job_num>);

4) Want watch snapshots?

alter session set nls_date_format = ‘dd-mm-yyyy hh24:mi:ss’;
select SNAP_ID,SNAP_LEVEL, snap_time
from stats$snapshot
order by snap_id;

select name,snap_id,to_char(snap_time,’DD.MM.YYYY:HH24:MI:SS’) “Date/Time” from stats$snapshot,v$database;

 

5) Configure statspack
This are the diferent levels of snapshots:

Level 0: General performance statistics
Level 5: Add SQL Statements
Level 6: Add SQL plans
Level 7: Add segment level statistics,logical and physical reads, row lock, itl and buffer busy waits
Level 10: Add child latch statistics (only directed by Oracle technical support.)

You can read description by select:

select * from perfstat.stats$level_descriptio

For modify any option with for example the snap level:

execute statspack.modify_statspack_parameter(i_snap_level=>6);
level 6 snap with data about execution plans and sql querys.

This is the table with statpack options

Argument Name Type In/Out Default?
——————— ————- —— ——–
I_DBID NUMBER IN DEFAULT
I_INSTANCE_NUMBER NUMBER IN DEFAULT
I_SNAP_LEVEL NUMBER IN DEFAULT
I_SESSION_ID NUMBER IN DEFAULT
I_UCOMMENT VARCHAR2 IN DEFAULT
I_NUM_SQL NUMBER IN DEFAULT
I_EXECUTIONS_TH NUMBER IN DEFAULT
I_PARSE_CALLS_TH NUMBER IN DEFAULT
I_DISK_READS_TH NUMBER IN DEFAULT
I_BUFFER_GETS_TH NUMBER IN DEFAULT
I_SHARABLE_MEM_TH NUMBER IN DEFAULT
I_VERSION_COUNT_TH NUMBER IN DEFAULT
I_ALL_INIT VARCHAR2 IN DEFAULT
I_PIN_STATSPACK VARCHAR2 IN DEFAULT
I_MODIFY_PARAMETER VARCHAR2 IN DEFAULT

6) Delete snapshots

@?/rdbms/admin/sppurge;

If u have too much u can delete all:

@?/rdbms/admin/spdrop

if u want dellete all, u can recreate again all,
some time is faster drop and recreate all.

@?/rdbms/admin/spdrop.sql

7) Generate a Report

after install, configure and shedule statpack now lets see most imnportant part, generate report:

@?/rdbms/admin/spreport.sql

If want more details about a query, first take note of old_hash_value
of a sql query in last report, and run:

@?/rdbms/admin/sprepsql.sql

8) Report analyze

Long time ago exist this url, http://www.statspackanalyzer.com there u can use a tool to in a fast way take first look to report, now u can donwload full website and use the tool:

statspackanalyzer

 

9) Managing deletes

Auto purge, example procedures for 14 days

first check:
select * from stats$snapshot where snap_time < sysdate – 14;

then this proceducers

CREATE OR REPLACE PACKAGE STATSPACK_WRAPPER AS
procedure PURGE
( i_num_days IN number
, i_extended_purge IN boolean default FALSE
, i_dbid IN number default null
, i_instance_number IN number default null
);
END STATSPACK_WRAPPER;
/

create or replace PACKAGE BODY STATSPACK_WRAPPER AS
procedure PURGE
( i_num_days IN number
, i_extended_purge IN boolean default FALSE
, i_dbid IN number default null
, i_instance_number IN number default null
) IS
BEGIN
STATSPACK.PURGE
( i_num_days
, i_extended_purge
, i_dbid
, i_instance_number
);
END PURGE;
END STATSPACK_WRAPPER;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘snap_purge_1’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘statspack_wrapper.purge’,
number_of_arguments => 1,
repeat_interval => ‘FREQ=DAILY; BYHOUR=19; BYMINUTE=00’,
auto_drop => FALSE,
comments => ‘Statspack automated purge’);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => ‘snap_purge_1’,
argument_position => 1,
argument_value => ’14’); <—– DIAS
DBMS_SCHEDULER.ENABLE(‘snap_purge_1’);
END;
/

10) All statspack scripts:

@?/rdbms/admin/sppurge.sql – Purge a range of Snapshot Id’s between the specified begin and end Snap Id’s
@?/rdbms/admin/spauto.sql – Schedule a dbms_job to automate the collection of STATPACK statistics
@?/rdbms/admin/spcreate.sql – Installs the STATSPACK user, tables and package on a database (Run as SYS).
@?/rdbms/admin/spdrop.sql – Deinstall STATSPACK from database (Run as SYS)
@?/rdbms/admin/sppurge.sql – Delete a range of Snapshot Ids from the database
@?/rdbms/admin/spreport.sql – Report on differences between values recorded in two snapshots
@?/rdbms/admin/sptrunc.sql – Truncates all data in Statspack tables
Statspack – the basic source of information is $ORACLE_HOME/rdbms/admin/spdoc.txt
@?/rdbms/admin/spreport.sql -> Generates a Statspack Instance report
@?/rdbms/admin/sprepins.sql -> Generates a Statspack Instance report for the database and instance specified
@?/rdbms/admin/sprepsql.sql -> Generates a Statspack SQL report for the SQL Hash Value specified
@?/rdbms/admin/sprsqins.sql -> Generates a Statspack SQL report for the SQL Hash Value specified, for the database and instance specified
@?/rdbms/admin/sppurge.sql -> Purges a limited range of Snapshot Ids for a given database instance
@?/rdbms/admin/sptrunc.sql -> Truncates all Performance data in Statspack tables
@?/rdbms/admin/spuexp.par -> An export parameter file supplied for exporting the whole PERFSTAT user

Standby Statspack – the basic source of information is $ORACLE_HOME/rdbms/admin/sbdoc.txt

@?/rdbms/admin/sbreport.sql – Create a report
@?/rdbms/admin/sbaddins.sql – Add a standby database instance to the configuration
@?/rdbms/admin/sblisins.sql – List instances in the standby configuration
@?/rdbms/admin/sbdelins.sql – Delete an instance from the standby configuration
@?/rdbms/admin/sbpurge.sql – Purge a set of snapshots

11) What STATSPACK Data is Best for Trend Analysis?

With hundreds of statistics available in STATSPACK, the question arises as to what metrics are the most useful?

-Sorts: amount of sorting in the database, especially in the TEMP tablespace (sorts (disk)).

-Physical disk reads
The reduction of I/O is the primary goal of Oracle tuning, so a trend report showing hourly disk reads can be very useful for measuring the effect of table reorganizations the re-sequence rows into index order.

-Physical disk writes
The amount of disk writes has a great impact on the overall throughput of the Oracle database, and is especially important when rollback segment activity is critical.

-I/O waits
This is a very important metric that can be used to identify and correct I/O contention. High I/O waits occur when two or more data files are being accessed simultaneously and the read-write head movement of the DASD causes the disk to wait for the completion of I/O.

-Buffer Busy Waits
A buffer bust wait occurs when a Oracle data block resides in a buffer, but the requesting program must wait to retrieve the data block. Buffer Busy Waits can commonly occur when a table has concurrent ‘UPDATE’ or ‘INSERT DML’ and only one freelist is defined for the table.

-Redo log space requests
Redo Log space requests occur when the log buffer is too small to hold the volume of redo log activity.

-Latch pin hit ratio
The pin hit ratios for the database instance give a good idea of the sizing of the shared_pool_size parameter in the init.ora file.

-Table Fetch Continued Row
This metric give a general indication of database migrated/chained rows. When chaining becomes excessive, additional I/O is required to service a query, and the DBA should increase PCTFREE for the table and reorganize the table to remove the chains.

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s