Oracle Statspack by Theory

=====================
General
=====================
Although AWR and ADDM (introduced in Oracle 10g) provide better statistics than STATSPACK, users that are not licensed to use the Enterprise Manager Diagnostic Pack, such as SE Oracle installations, should continue to use statspack.

=====================
Installation
=====================
To install statspack

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@spdrop.sql   -- Drop existing statspack schema.
@spcreate.sql -- Create Schema and Objects. Enter tablespace names when prompted.
@spauto.sql   -- Create a dbms_job that will execute a snapshot every hour

To run in batch mode, these parameter values must be defined:
define default_tablespace='TOOLS'
define temporary_tablespace='TEMP' 
define perfstat_password='my_perfstat_password'
SQL>  @?/rdbms/admin/spcreate.sql


Check each of the three output files produced (spcusr.lis, spctab.lis, spcpkg.lis) by the 
installation to ensure no errors were encountered.

PERFSTAT schema is created by the installation script, and owns all objects needed by this package.

=====================
Taking Snapshots
=====================
To manually create a snapshot:
sqlplus perfstat/perfstat_pass@orainst 
or
sqlplus / as sysdba

EXEC perfstat.statspack.snap;

The automation of the task of taking a snapshot, is done via DBMS_JOB.
The default is once every hour, by executing this script
sqlplus perfstat/perfstat_pass@orainst
@/rdbms/admin/spauto.sql


=====================
View Available Snapshots
=====================
See available snaphot IDs.
SELECT snap_id, snap_time FROM STATS$SNAPSHOT; 

Get the last snapshot_id
SELECT MAX(snap_id) FROM STATS$SNAPSHOT; 
Is same as using 
SQL> variable snap_id number;
SQL> begin :snap_id := statspack.snap;  end;
  2  /
PL/SQL procedure successfully completed.
SQL> print snap_id
      SNAP

----------
     34535

=====================
Jobs
=====================
This is example of defining perfstat jobs:
One job to gather stats which runs every hour.
Another job to purge data older than 14 days, which runs once a day.

SELECT schema_user, interval, next_date, what FROM USER_JOBS;

PERFSTAT   TRUNC(SYSDATE+1/24,'HH') 20160125 19:00:00
DECLARE snap NUMBER;BEGIN snap := STATSPACK.SNAP(i_snap_level=>7); END;


PERFSTAT   TRUNC(SYSDATE+1)+3/24    20160126 03:00:00
STATSPACK.PURGE(i_purge_before_date=>sysdate-14,i_extended_purge=>true);

=====================
Reporting
=====================
Once you have at least two snapshots created, you can run the performance reports.  
There are two reports available – an Instance report, and a SQL report.  
To run report without having a prompt for input values, need to define these variables:

sqlplus perfstat/my_perfstat_password 

SQL> define begin_snap=1 

SQL> define end_snap=2 

SQL> define report_name=batch_run 

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

And for SQL Report:

sqlplus perfstat/my_perfstat_password

SQL>  define begin_snap=39

SQL>  define end_snap=40

SQL   define hash_value=1988538571

SQL>  define report_name=batch_sql_run

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

=====================
Make your life easy
=====================

run_statspack.sh

#!/bin/bash
.  ~oracle/.set_profile
sqlplus perfstat/perfstat_pass@igt @${ORACLE_HOME}/rdbms/admin/spreport.sql


run_statspack_4sql.sh
#!/bin/bash
.  ~oracle/.set_profile
sqlplus perfstat/perfstat_pass@igt @${ORACLE_HOME}/rdbms/admin/sprepsql.sql

=====================

The Instance Report

=====================
The Instance Report (spreport.sql) is a general instance health report, covering all aspects of instance performance.  
It will show the top wait events, the top SQL queries, the busiest segments, the busiest datafiles, the database activity level and many, many other things.  
To run Instance Report:
@/rdbms/admin/spreport.sql

=====================
The SQL Report
=====================
The SQL Report (sprepsql.sql) is a report for a specific SQL statement.  
The SQL Report is usually run after examining the high-load SQL sections of the instance health report.  
The SQL Report provides detailed statistics and data for a single SQL statement, identified by the Hash Value.
To run SQL Report:
@/rdbms/admin/sprepsql.sql

=====================
Performance Tuning for Perfstat 
=====================

In order for the statspack code to complete fast, it is advisable to collect performance stats on objects owned by perfstat schema.

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'PERFSTAT',CASCADE=>TRUE);

=====================
Snapshot Levels
=====================

Possible values: 0, 5, 6, 7, 10
Default value: 5

In Short

Levels  0 and higher:  Collects General Instance Level Performance statistics.

Levels  5 and higher: Additional Data: SQL Statements

Levels  6 and higher: Additional Data: SQL Plans and SQL Plan Usage

Levels  7 and higher: Additional data: Segment Level Statistics

Levels  10 and higher:  Additional Statistics: Parent and Child Latches

Session specific:  Additional Statistics: Specific session statistics and wait events.

In Details

Level 0

Collects General Instance Level Performance statistics.

Such as: wait statistics, system events, system statistics, rollback segment data, row cache, SGA, background events, session events, lock statistics, buffer pool statistics, and parent latch statistics.

Levels  5 and higer

Collects Additional Data: SQL Statements

Such as SQL Statements Performance data.

The data is collected on top SQL statements with high resource usage. 

The SQL statements gathered by Statspack are those that exceed one of six predefined threshold parameters:

1. Number of executions of the SQL statement. The default 100.

2. Number of disk reads performed by the SQL statement. The default 1,000.

3. Number of parse calls performed by the SQL statement. The default 1,000.

4. Number of buffer gets performed by the SQL statement. The default 10,000.

5. Size of sharable memory used by the SQL statement. The default 1 Mb.

6. Version count for the SQL statement. The default 20.

If a SQL statement's resource usage exceeds any one of these threshold values, then it is captured during the snapshot.

The SQL threshold levels used are either those stored in the table STATS$STATSPACK_PARAMETER or by the thresholds specified when the snapshot is taken.

Levels  6 and higher

Collects Additional Data: SQL Plans and SQL Plan Usage

Such as SQL plans and plan usage data for each of the high-resource SQL statements captured.

A level 6 snapshot gathers valuable information for determining whether the execution plan used for a SQL statement has changed. Therefore, level 6 snapshots should be used whenever a plan might have changed.

To gather the plan for a SQL statement, the statement must be in the shared pool at the time the snapshot is taken, and it must exceed one of the SQL thresholds. 

To gather plans for all statements in the shared pool, specify the executions threshold to be zero (0) for those snapshots.

Levels  7 and higher

Collects Additional data: Segment Level Statistics

Such as performance data on highly used segments. 

RAC specific segment level statistics are also captured with level 7.

Level 7 includes the following segment statistics:

Logical reads

Db block changes

Physical reads

Physical writes

Physical reads direct

Physical writes direct

Global cache consistent read blocks served (RAC specific)

Global cache current blocks served (RAC specific)

Buffer busy waits

ITL waits

Row lock waits

Although Statspack captures all segment statistics, it reports only the following statistics that exceed one of the predefined threshold parameters:

1. Number of logical reads on the segment. The default is 10,000.

2. Number of physical reads on the segment. The default is 1,000.

3. Number of buffer busy waits on the segment. The default is 100.

4. Number of row lock waits on the segment. The default is 100.

5. Number of ITL waits on the segment. The default is 100.

6. Number of global cache consistent read blocks served (RAC only). The default is 1,000.

7. Number of global cache current blocks served (RAC only). The default is 1,000.

The values of the threshold parameters are used when deciding which segment statistics to collect. 

If a segment's statistic exceeds a threshold value, all statistics regarding this segment are captured during the snapshot.

The threshold levels used are either those stored in the table STATS$STATSPACK_PARAMETER.

Levels  10 and higher

Additional Statistics: Parent and Child Latches

This level includes all statistics gathered in the lower levels, as well as parent and child latch information. Sometimes data gathered at this level can cause the snapshot to take longer to complete. This level can be resource-intensive, and it should only be used when advised by Oracle personnel.

Session specific:  

Additional Statistics: Specific session statistics and wait events.

By default, there is to no collection of session level statistics.
The statistics gathered for the session include session statistics, session events, and lock activity. 
To enable Sessions Statistics Collection, need to provide sessions ID.
For example:

SQL>  EXECUTE STATSPACK.SNAP(i_session_id=>343);

=============================
Changing Statspack parameters
=============================

Statspack parameters can be changed for a specific snapshot, of for good.

For a specific snapshot:
EXECUTE STATSPACK.snap(i_ucomment=>'temporary commment');

Permenently. 
This effectively changes the default value:

The new thresholds values are updated in STATS$STATSPACK_PARAMETER table.

EXECUTE STATSPACK.snap(i_snap_level=>10,i_modify_parameter=>'true');

Or

EXECUTE STATSPACK.modify_statspack_parameter(i_ucomment=>'this is a commment that is saved');

The List of STATSPACK parameters that can be changed:

Parameter Name     Possible Values Default Value Parameter meaning
------------------ --------------- ------------- ----------------------------------------

i_snap_level       0, 5, 6, 7, 10  5             Snapshot level
i_ucomment         Text            Blank         Free Text
i_executions_th    Integer >=0     100           Threshold for SQL statement executions
i_disk_reads_th    Integer >=0     1000          Threshold for SQL disk reads
i_parse_calls_th   Integer >=0     1000          Threshold for SQL number of parse calls
i_buffer_gets_th   Integer >=0     10000         Threshold for SQL number of buffer gets
i_sharable_mem_th  Integer >=0     1048576       Threshold for SQL shareable memory
i_version_count_th Integer >=0     20            Threshold for SQL versions
i_seg_phy_reads_th Integer >=0     1000          Threshold for Segment physical reads
i_seg_log_reads_th Integer >=0     10000         Threshold for Segment logical reads
i_seg_buff_busy_th Integer >=0     100           Threshold for Segment buffer busy
i_seg_rowlock_w_th Integer >=0     100           Threshold for Segment row lock waits
i_seg_itl_waits_th Integer >=0     100           Threshold for Segment ITL waits
i_seg_cr_bks_sd_th Integer >=0     1000          Threshold for Segment consistent reads 
i_seg_cu_bks_sd_th Integer >=0     1000          Threshold for Segment current blocks
                                                 served by the instance
i_session_id       V$SESSION.sid   0             Session ID for which capture statistics
i_modify_parameter TRUE, FALSE     FALSE         Determines whether the parameters 
                                                 specified are used for future snapshots

=====================
Enable statistics collection
=====================
In order for STATSPACK to work, Oracle has to collect statistics data.
This feature is configured by two parameters: TIMED_STATISTICS and STATISTICS_LEVEL

TIMED_STATISTICS
TIMED_STATISTICS specifies whether or not statistics related to time are collected.

Possible Values: TRUE and FALSE

TRUE:  The statistics are collected and stored, and displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views.


FALSE: The value of all time-related statistics is set to zero. This setting lets Oracle avoid the overhead of requesting the time from the operating system.

TIMED_STATISTICS must be set to TRUE

ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

STATISTICS_LEVEL
STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. 
Possible Values: TYPICAL, ALL, BASIC
Default Value: TYPICAL

TYPICAL, Collection of all major statistics and provides best overall performance. This value  should be adequate for most environments.

ALL, additional statistics are added, the additional statistics are timed OS statistics and plan execution statistics.

BASIC, Effectively disables the collection of many of the important statistics 

=====================
Statspack scripts
=====================
Installation
spdrop.sql   - Uninstall Statspack from database (Run as SYS)
spcreate.sql - Installs the Statspack user, tables and package on a database (Run as SYS).

Reports
spauto.sql   - Automates Statspack statistics collection (using DBMS_JOB)
spreport.sql - Generates a Statspack report.
sprepsql.sql - Generates a Statspack SQL report for the specific SQL hash value. 
sprepins.sql - Generates a Statspack report for the database and instance specified.

Clean Up

sppurge.sql  - Purge a range of Snapshot Id's. 
sptrunc.sql  - Truncates all data in Statspack tables.


=====================
Known Issues
=====================

Some statistics may only be reported on completion of a query. 

If a query runs for 12 hours, its processing won't be reported during any of the snapshots taken while the query was busy executing.

If queries are aged out of the shared pool, the stats from V$SQL are reset. 
This can throw off the delta calculations and even make it negative. 
For example, query A has 10,000 buffer_gets at snapshot 1, but at snapshot #2, it has been aged out of the pool and reloaded and now shows only 1,000 buffer_gets. So, when you run spreport.sql from snapshot 1 to 2, you'll get 1,000-10,000 = -9,000 for this query.