Getting Most Out of Oracle 8i 9i Statspack

MRR and PLR Articles Pack

Below is a MRR and PLR article in category Computers Technology -> subcategory Software.

Maximizing the Benefits of Oracle 8i/9i Statspack

Summary:
Discover how to effectively use Oracle Statspack for performance monitoring and tuning in Oracle 8i and 9i environments.

---

Oracle Statspack is a powerful tool for monitoring and reporting on the performance of Oracle databases starting from version 8i. Administered by the user PERFSTAT, Statspack provides valuable insights into database performance metrics. For comprehensive documentation, refer to the file `$ORACLE_HOME/rdbms/admin/spdoc.txt`.

Installing Statspack

To install Statspack, navigate to the `ORACLE_HOME/rdbms/admin` directory. Use the SQL*Plus utility to connect as a user with SYSDBA privileges and execute the installation script SPCREATE.

UNIX (SunOS/HP UX/Linux)

```sql
SQL> CONNECT / AS SYSDBA
SQL> @?/rdbms/admin/spcreate
```

Windows (XP/NT/2000/2003)

```sql
SQL> CONNECT / AS SYSDBA
SQL> @%ORACLE_HOME%/rdbms/admin/spcreate
```

The SPCREATE script triggers three other scripts automatically, managing user creation, table setup, and package creation.

Configuring Statspack

Determine Statspack Level

You can identify the current Statspack level by checking the `PERFSTAT.STATS$SNAPSHOT` table or reviewing the `spreport.sql` output.

To change the capture level, use:
```sql
EXECUTE statspack.snap(i_snap_level => 7, i_modify_parameter => 'true');
```

- Levels ≥ 0: General Performance Statistics
- Levels ≥ 5: Includes SQL Statements
- Levels ≥ 6: Adds SQL Plans and Usage
- Levels ≥ 10: Captures Parent and Child Latches

Using Statspack to Gather Data

To capture a performance snapshot:
```sql
SQL> CONNECT perfstat
SQL> EXECUTE statspack.snap;
```

To list snapshots:
```sql
SQL> SELECT snap_id, TO_CHAR(snap_time, 'MON dd, yyyy hh24:mm:ss') snap_time FROM sp$snapshot;
```

Generating Performance Reports

Execute the following to run a Statspack report:
```sql
SQL> @?/rdbms/admin/spreport.sql
```

Identifying Resource-Intensive SQL

To retrieve substantial SQL data from the Statspack repository:

1. Log in as the PERFSTAT user.
2. Determine the DBID:
```sql
SELECT dbid FROM stats$sql_summary;
```
3. Find the start and end snapshot IDs:
```sql
SELECT MIN(snap_id), MAX(snap_id) FROM stats$snapshot WHERE TO_NUMBER(TO_CHAR(snap_time,'HH24')) BETWEEN 10 AND 13 AND TRUNC(snap_time) = TRUNC(SYSDATE);
```

Analyze SQL Statements by Logical Reads

Execute the query below to list SQL statements:
```sql
SELECT
e.hash_value "E.HASH_VALUE",
e.module "Module",
e.buffer_gets - NVL(b.buffer_gets,0) "Buffer Gets",
...
FROM stats$sql_summary e, stats$sql_summary b
WHERE ...
ORDER BY 3 DESC;
```

Retrieve SQL and Execution Plans

With Statspack set to level 7, use `sprepsql.sql` for detailed SQL report analysis:
```sql
SELECT * FROM STATS$SQLTEXT WHERE hash_value='%from stats pack%' ORDER BY piece;
```

To find an object’s PLAN details:
```sql
SELECT ...
FROM stats$SQL_PLAN a, STATS$SNAPSHOT b
WHERE object

='&&OBJECT_ID' AND a.snap_id=b.snap_id;

```

About the Author

Visit [Oracle DBA Support](http://OracleDbaSupport.co.uk) for more insights from Sagar Patil, an Oracle Certified RAC DBA with over a decade of expertise in Oracle databases. Specializing in RAC systems, backup strategies, Oracle architecture, and tuning methodologies, Sagar offers consulting services for Oracle strategy, design, and troubleshooting. For inquiries, visit [www.oracledbasupport.co.uk](http://www.oracledbasupport.co.uk).

---

Enhance your Oracle database performance monitoring with tailored Statspack insights and industry expertise.

You can find the original non-AI version of this article here: Getting Most Out of Oracle 8i 9i Statspack.

You can browse and read all the articles for free. If you want to use them and get PLR and MRR rights, you need to buy the pack. Learn more about this pack of over 100 000 MRR and PLR articles.

AI Generated Articles