Explore topic-wise InterviewSolutions in .

• To check whether tuning measures have the expected effect.
• To check the effects of changes in the configuration ( profile parameters, tablespace layout).
• To check whether a higher workload requires an adjustment of profile parameters.
• To obtain COMPARISON METRICS for the performance behavior before a scheduled change in the system (NEW platform, new storage, software upgrade, new Oracle release).
• To check system USER complaints about poor response times and to determine the causes for the poor performance.
• To recognize trends, that is, slow changes over a longer period, for example, an increasing workload, an increasing I/O load or an increasing CPU workload
• To determine load PEAKS, bottlenecks and their causes.
• To determine the SQL statements with the highest resource consumption (top SQL).

In general, we recommend that you set the SNAPSHOT level to the value 7 in the CASE of RAC. This means that the most heavily loaded SEGMENTS are recognized at segment level. These statistics help you to localize hot spots.

To GENERATE a snapshot in an RAC environment, you must log on to the RAC instance for which you WANT to generate statistics.

To set up automatic generation of snapshots in an RAC environment with the spauto.sql script for each RAC instance, you must run this script once on each instance.

To create an performance report in an RAC environment, you must log on to the RAC instance for which you want to generate a performance report.

In general, we recommend that you set the snapshot level to the value 7 in the case of RAC. This means that the most heavily loaded segments are recognized at segment level. These statistics help you to localize hot spots.

To generate a snapshot in an RAC environment, you must log on to the RAC instance for which you want to generate statistics.

To set up automatic generation of snapshots in an RAC environment with the spauto.sql script for each RAC instance, you must run this script once on each instance.

To create an performance report in an RAC environment, you must log on to the RAC instance for which you want to generate a performance report.

$sqlplus

SQL>connect perfstat/perfstat

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

The performance report calculates differences in the statistical numbers of two snapshots (final snapshot MINUS the start snapshot). To ensure that these calculated values can be USEFULLY interpreted, the instance between the two snapshots must not have been stopped. The reason for this is that a snapshot determines its FIGURES from the V$ tables, which are reset to 0 each time an instance starts.

$sqlplus

SQL>connect perfstat/perfstat

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

The performance report calculates differences in the statistical numbers of two snapshots (final snapshot minus the start snapshot). To ensure that these calculated values can be usefully interpreted, the instance between the two snapshots must not have been stopped. The reason for this is that a snapshot determines its figures from the V$ tables, which are reset to 0 each time an instance starts.

There are different types of snapshot levels. A snapshot LEVEL determines which performance DATA is collected. The HIGHER the level, the more data is collected. The default value of 5 is generally sufficient. For RAC, you should generate snapshots with at least level 7.

Temporarily changing the snapshot level (for ONE snapshot only):

SQL>execute STATSPACK.SNAP(i_snap_level=>7);

Changing the snapshot level and saving it as a new default:

SQL>execute statspack.modify_statspack_parameter(i_snap_level=>7);

For simultaneous generation of a snapshot:

SQL>execute STATSPACK.SNAP(i_snap_level=>7,

i_modify_parameter= >'true');

For details regarding the individual snapshot levels, see spdoc.txt.

There are different types of snapshot levels. A snapshot level determines which performance data is collected. The higher the level, the more data is collected. The default value of 5 is generally sufficient. For RAC, you should generate snapshots with at least level 7.

Temporarily changing the snapshot level (for one snapshot only):

SQL>execute STATSPACK.SNAP(i_snap_level=>7);

Changing the snapshot level and saving it as a new default:

SQL>execute statspack.modify_statspack_parameter(i_snap_level=>7);

For simultaneous generation of a snapshot:

SQL>execute STATSPACK.SNAP(i_snap_level=>7,

i_modify_parameter= >'true');

For details regarding the individual snapshot levels, see spdoc.txt.

Check the files for errors during the installation.

sqlplus

SQL>connect perfstat/perfstat

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

Prerequisite: job_queue_processes > 0

This SETS up an automatic JOB in the database that generates a SNAPSHOT every HOUR.

Check the files for errors during the installation.

sqlplus

SQL>connect perfstat/perfstat

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

Prerequisite: job_queue_processes > 0

This sets up an automatic job in the database that generates a snapshot every hour.

$SQLPLUS

SQL>CONNECT perfstat/perfstat

SQL>EXECUTE statspack.snap;

$sqlplus

SQL>connect perfstat/perfstat

SQL>execute statspack.snap;

The USE of the statspack to PERFORM a once-off collection of performance DATA is called a snapshot, that is, a snapshot of the current system status. You can use two snapshots to create a performance REPORT.

The use of the statspack to perform a once-off collection of performance data is called a snapshot, that is, a snapshot of the current system status. You can use two snapshots to create a performance report.

Yes, you must set the following PARAMETERS to the values SPECIFIED to ensure that you RECEIVE useful statistics:

SQL>show parameter statistics

statistics_level=TYPICAL

timed_os_statistics=0

timed_statistics=TRUE

To automate the generation of STATSPACK snapshots, you must set the parameter job_queue_processes > 0.

Yes, you must set the following parameters to the values specified to ensure that you receive useful statistics:

SQL>show parameter statistics

statistics_level=TYPICAL

timed_os_statistics=0

timed_statistics=TRUE

To automate the generation of statspack snapshots, you must set the parameter job_queue_processes > 0.

No, not up to now.

No, not up to now.

APART from the tablespace naming convention during CREATION with brspace, there are no SAP SPECIFICATIONS.

Apart from the tablespace naming convention during creation with brspace, there are no SAP specifications.

Installing the statspack in a separate tablespace USING a dedicated database user means that the statspack is kept COMPLETELY separate from the SAP schema. You can install, configure or uninstall the statspack during SAP operation without affecting the SAP system. The amount of statspack data should generally by small in relation to the overall SIZE of the database. The collection and analysis of statistical data has only a minimal effect on system performance, since only those statistics that are contained in the MEMORY (SGA) are being accessed.

The installation and use of the statspack is therefore largely independent of and transparent for the SAP System.

Installing the statspack in a separate tablespace using a dedicated database user means that the statspack is kept completely separate from the SAP schema. You can install, configure or uninstall the statspack during SAP operation without affecting the SAP system. The amount of statspack data should generally by small in relation to the overall size of the database. The collection and analysis of statistical data has only a minimal effect on system performance, since only those statistics that are contained in the memory (SGA) are being accessed.

The installation and use of the statspack is therefore largely independent of and transparent for the SAP System.

Step 1: DELETE the ENTERED statspack jobs -

Log on as the USER under which the statspack jobs run (usually PERFSTAT):

SQL>connect PERFSTAT/

SQL>select job, what from user_jobs;

This displays a list of the jobs that are currently entered under the user PERFSTAT.

You can delete these jobs using DBMS_JOB.REMOVE

SQL>connect PERFSTAT/

Execute the following SQL statement for all jobs you want to delete that have the print job number :

SQL>execute dbms_job.remove( <job> );

Step 2: Delete the PERFSTAT objects and the PERFSTAT schema -

SQL>connect / as sysdba

SQL>@?/rdbms/admin/spdrop

As a result, the tables of the PERFSTAT user and, after that, the user PERFSTAT are deleted.

Step 3: Delete the PERFSTAT tablespace -

Since the PERFSTAT tablespace was created specifically for the PERFSTAT user and the statspack DATA, it should be empty once you have deleted the PERFSTAT user. You can check this using

SQL>select * from dba_segments where tablespace_name = 'PERFSTAT';

If the tablespace is empty, you can delete it.

Step 1: Delete the entered statspack jobs -

Log on as the user under which the statspack jobs run (usually PERFSTAT):

SQL>connect PERFSTAT/

SQL>select job, what from user_jobs;

This displays a list of the jobs that are currently entered under the user PERFSTAT.

You can delete these jobs using DBMS_JOB.REMOVE

SQL>connect PERFSTAT/

Execute the following SQL statement for all jobs you want to delete that have the print job number :

SQL>execute dbms_job.remove( <job> );

Step 2: Delete the PERFSTAT objects and the PERFSTAT schema -

SQL>connect / as sysdba

SQL>@?/rdbms/admin/spdrop

As a result, the tables of the PERFSTAT user and, after that, the user PERFSTAT are deleted.

Step 3: Delete the PERFSTAT tablespace -

Since the PERFSTAT tablespace was created specifically for the PERFSTAT user and the statspack data, it should be empty once you have deleted the PERFSTAT user. You can check this using

SQL>select * from dba_segments where tablespace_name = 'PERFSTAT';

If the tablespace is empty, you can delete it.

The BACKGROUND installation proceeds as follows:

$sqlplus

SQL>connect / as sysdba

SQL>define DEFAULT tablespace='PERFSTAT'

SQL>define temporary tablespace='PSAPTEMP'

SQL>define perfstat password=perfstat

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

The PERFSTAT tablespace must already have been created.

SYSTEM is not allowed as a default tablespace.

Interactive installation:

$sqlplus

SQL>connect / as sysdba

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

The interactive installation requires that you enter the password, the default tablespace as well as the temporary tablespace of the PERFSTAT database user.

The EXECUTION of the spcreate.sql script executes three scripts one after the other (spcusr.sql, spctab.sql, spcpkg.sql). The system creates three log FILES (spcusr.lis, spctab.lis, spcpkg.lis) in the current directory. You should check these for the occurrence of errors during the installation. After that, you can delete them.

After you install the statspack, we recommend that you change the password for the PERFSTAT user.

SQL>alter user perfstat identified by

The background installation proceeds as follows:

$sqlplus

SQL>connect / as sysdba

SQL>define default tablespace='PERFSTAT'

SQL>define temporary tablespace='PSAPTEMP'

SQL>define perfstat password=perfstat

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

The PERFSTAT tablespace must already have been created.

SYSTEM is not allowed as a default tablespace.

Interactive installation:

$sqlplus

SQL>connect / as sysdba

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

The interactive installation requires that you enter the password, the default tablespace as well as the temporary tablespace of the PERFSTAT database user.

The execution of the spcreate.sql script executes three scripts one after the other (spcusr.sql, spctab.sql, spcpkg.sql). The system creates three log files (spcusr.lis, spctab.lis, spcpkg.lis) in the current directory. You should check these for the occurrence of errors during the installation. After that, you can delete them.

After you install the statspack, we recommend that you change the password for the PERFSTAT user.

SQL>alter user perfstat identified by

At the beginning, approximately 100 MB are required for the installation.

The future space requirements of the STATSPACK depend on how often you are generating snapshots, the size of the database and the size of the instance. In ADDITION, they are also INFLUENCED by the snapshot level which determines the amount of data that is collected in a snapshot. Therefore, we cannot give you any general RECOMMENDATIONS about the size of the statspack. We only recommend that you OBSERVE the expansion of the statspack, especially if you are generating snapshots automatically, so that you can make an estimate about future space requirements.

At the beginning, approximately 100 MB are required for the installation.

The future space requirements of the statspack depend on how often you are generating snapshots, the size of the database and the size of the instance. In addition, they are also influenced by the snapshot level which determines the amount of data that is collected in a snapshot. Therefore, we cannot give you any general recommendations about the size of the statspack. We only recommend that you observe the expansion of the statspack, especially if you are generating snapshots automatically, so that you can make an estimate about future space requirements.

When you install the statspack, the PERFSTAT statspack database user is CREATED AUTOMATICALLY with the minimum ACCESS authorizations for the ORACLE dictionary (V$-Views). The statspack and all required tables, indexes, and so on belong to the PERFSTAT user. You collect the statistics, write the collected data to the database and evaluate the data during the creation of performance REPORTS under this user.

When you install the statspack, the PERFSTAT statspack database user is created automatically with the minimum access authorizations for the Oracle dictionary (V$-Views). The statspack and all required tables, indexes, and so on belong to the PERFSTAT user. You collect the statistics, write the collected data to the database and evaluate the data during the creation of performance reports under this user.

Yes, we recommend that you install the statspack in a separate tablespace called PERFSTAT. You should create the tablespace as follows:

$SQLPLUS

SQL&GT;CREATE TABLESPACE "PERFSTAT"

DATAFILE '<sap data>/perfstat 1/PERFSTAT.data1'

SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE 2000M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

brspace only SUPPORTS the creation of data tablespaces for SAP data, but not for non-SAP data. Therefore, you should use sqlplus, not brspace, to create the PERFSTAT tablespace.

Yes, we recommend that you install the statspack in a separate tablespace called PERFSTAT. You should create the tablespace as follows:

$sqlplus

SQL>CREATE TABLESPACE "PERFSTAT"

DATAFILE '<sap data>/perfstat 1/PERFSTAT.data1'

SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE 2000M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

brspace only supports the creation of data tablespaces for SAP data, but not for non-SAP data. Therefore, you should use sqlplus, not brspace, to create the PERFSTAT tablespace.

The data collected by the statspack is more extensive and is saved in tables in the ORACLE database so that you can use it LATER to diagnose performance problems or to create trend analyses. The overall diagnosis generated by the statspack INCLUDES an overview over the instance status (instance HEALTH), a load profile, the MAIN SQL statements with the highest resource consumption and information about queues, events and profile parameters.

The data collected by the statspack is more extensive and is saved in tables in the Oracle database so that you can use it later to diagnose performance problems or to create trend analyses. The overall diagnosis generated by the statspack includes an overview over the instance status (instance health), a load profile, the main SQL statements with the highest resource consumption and information about queues, events and profile parameters.

The statspack is available as of ORACLE 8i (8.1.6) and was ENHANCED with ORACLE9I. This NOTE only REFERS to Oracle9i Release 2 (9.2.0).

The statspack is available as of Oracle 8i (8.1.6) and was enhanced with Oracle9i. This note only refers to Oracle9i Release 2 (9.2.0).

- Oracle9i Database Performance Tuning GUIDE and REFERENCE

Chapter 21: Using STATSPACK

- Statistics Package (Statspack) README: spdoc.txt

This readme is located under:

$ORACLE_HOME/RDBMS/admin spdoc.txt (UNIX) or

%ORACLe_HOME%rdbms admin spdoc.txt (Windows)

- Oracle9i Database Performance Tuning Guide and Reference

Chapter 21: Using Statspack

- Statistics Package (Statspack) README: spdoc.txt

This readme is located under:

$ORACLE_HOME/rdbms/admin spdoc.txt (UNIX) or

%ORACLe_HOME%rdbms admin spdoc.txt (Windows)

The statspack scripts are installed automatically in the $ORACLE_HOME/rdbms/admin directory (Unix) or the %ORACLE_HOME%rdbms admin directory (WINDOWS) when you install the ORACLE9I database software.

The most important scripts are:

• spcreate.sql: INSTALLS the statspack;
• spdrop.sql : Uninstalls the statspack;
• spauto.sql : Automates the collection of statistics;
• spreport.sql: Creates a performance report;
• spdoc.txt : Provides documentation for the statspack;

spcreate.sql and spdrop.sql MUST be executed with the SYSDBA PRIVILEGE.

spauto.sql and spreport.sql should be executed under the PERFSTAT user.

The statspack scripts are installed automatically in the $ORACLE_HOME/rdbms/admin directory (Unix) or the %ORACLE_HOME%rdbms admin directory (Windows) when you install the Oracle9i database software.

The most important scripts are:

spcreate.sql and spdrop.sql must be executed with the SYSDBA privilege.

spauto.sql and spreport.sql should be executed under the PERFSTAT user.

Statspack is the abbreviation for statistics package. The statspack consists of a collection of SQL, PL/SQL and SQL*Plus scripts. The statspack helps you to collect, STORE, compare, FORMAT and analyze performance data for an Oracle instance.

With the statspack, you can, for example, determine the cause of performance problems or determine and quantify the effects of profile parameter CHANGES. Refer to the section about TYPICAL usage SCENARIOS for more information.

Statspack is the abbreviation for statistics package. The statspack consists of a collection of SQL, PL/SQL and SQL*Plus scripts. The statspack helps you to collect, store, compare, format and analyze performance data for an Oracle instance.

With the statspack, you can, for example, determine the cause of performance problems or determine and quantify the effects of profile parameter changes. Refer to the section about typical usage scenarios for more information.