Performance Tuning

Stats pack Report

SEP

SSTATSPACK

STEP 1: Connect to the SQL prompt as the SYSDBA and run the spcreate.sql script located in

your $ORACLE_HOME/rdbms/admin directory

SQL> connect / as sysdba

SQL>@spcreate.sql

STEP 2: Running this script on the database will create a user PERFSTAT and the STATSPACK

Schema

STEP 3: You will be prompted for the PERFSTAT password

You will be prompted for the default_tablespace

You will be prompted for the temporary tablespace

STEP 4: collect the snapshot of a database at any given time by using the PL/SQL procedure

STATSPACK.SNAP

SQL> execute STATSPACK.SNAP

You can execute this procedure at various times in order to compare the CPU

performance between those time intervals.

SQL> execute STATSPACK.SNAP

STEP 5: Collect the report of the performance between any two snapshots

SQL> spreport.sql

It will prompt for begin snap_ID:

It will prompt for end snap_ID:

It will prompt for value report_name :

STEP 6: We can make oracle to automatically take snapshots at a specified regular interval by

executing the script

SQL> spauto.sql

STEP 7: A set of snapshots that fall within a range can be purged using spurge.sql . When this

script is executed we will be prompted for low snap_ID and high snap_ID and all the

snapshots falling between this will be purged.

SQL> spurge.sql

In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR)

The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql)

the two reports give essential the same output but the awrrpti.sql allows you to select a single instance.

SQL> @awrrpt.sql

ADDM (Automatic Database Diagnostics Monitor):

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks.

The ADDM analysis includes:

• CPU load
• Memory usage
• I/O usage
• Resource intensive SQL
• Resource intensive PL/SQL and Java
• RAC issues
• Application issues
• Database configuration issues
• Concurrency issues
• Object contention

SQL> @addmrpt.sql