Oracle Statspack

Oracle STATSPACK (Statistics Package) is a performance diagnostic tool provided by Oracle to collect, store, and analyze performance data of an Oracle database. It is especially useful for monitoring and diagnosing database performance over time.

Key Features of STATSPACK

1. Snapshot Collection:
2. Report Generation:
3. Historical Performance Data:

Setting Up STATSPACK :-

Prerequisites : - Ensure you are using Standard Edition or not using Diagnostics Pack on your enterprise edition Database

Creating the STATSPACK User and Schema:

Run the spcreate.sql script located in the ORACLE_HOME/rdbms/admin directory to create the STATSPACK schema and user.

Taking Snapshots:

• Snapshots can be taken manually or scheduled using Oracle's job scheduler (DBMS_JOB or DBMS_SCHEDULER).

Manual snapshot: -

EXEC STATSPACK.SNAP;

Scheduled snapshots :-

VARIABLE jobno NUMBER; BEGIN DBMS_JOB.SUBMIT(:jobno, 'STATSPACK.SNAP;', SYSDATE, 'SYSDATE + 1/24'); COMMIT; END;

Generating STATSPACK Reports

To generate a STATSPACK report, you need two snapshots: a start snapshot and an end snapshot.

1. Generating the Report:Run the spreport.sql script located in the ORACLE_HOME/rdbms/admin directory. You will be prompted to enter the start and end snapshot IDs, as well as the name of the output file.

Analyzing STATSPACK Reports

A STATSPACK report contains several key sections that provide valuable insights into database performance:

1. Instance Efficiency Percentages:
2. Top 5 Wait Events:
3. SQL Statistics:
4. Instance Activity Stats:
5. Tablespace and File I/O Stats:

Example Queries for STATSPACK Analysis

1. List All Snapshots:

SELECT SNAP_ID, SNAP_TIME, SNAP_LEVEL FROM STATS$SNAPSHOT ORDER BY SNAP_ID;

2. Top 5 Wait Events:

SELECT * FROM (SELECT event, total_waits, time_waited FROM stats$system_eventWHERE snap_id = :snap_id ORDER BY time_waited DESC)

WHERE ROWNUM <= 5;

3. SQL Statistics:

SELECT sql_text, executions, buffer_gets, disk_reads, cpu_time, elapsed_time FROM stats$sql_summary WHERE snap_id = :snap_id ORDER BY buffer_gets DESC;