Download SCRIPTS OR STATSPACK and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
Introduction to Statspack
The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.
How Statspack Works
When you run the Statspack installation script, the PERFSTAT user is created automatically. PERFSTAT owns all objects needed by the Statspack package and is
granted limited query-only privileges on the V$views required for performance tuning.
Installing Statspack
The first step in the installation is the creation of the PERFSTAT user, which owns all
PL/SQL code and database objects created, including the Statspack tables, constraints, and the Statspack package. During installation, you are prompted for the PERFSTAT user's password, default tablespace, and temporary tablespace. The default tablespace is used to create all Statspack objects, such as tables and indexes. The temporary tablespace is used for sort-type activities.
To install Statspack, perform the following:
- Start SQL*Plus.
- Connect as a user with SYSDBA privilege. For example:
- SQL> CONNECT / AS SYSDBA
- Run the SPCREATE.SQL script.
- Enter the following:
- SQL> @?/rdbms/admin/spcreate.sql
- Enter appropriate information when prompted for the PERFSTAT user's password, default tablespace, and temporary tablespace.
The SPCREATE.SQL install script runs the following scripts automatically:
- SPCUSR.SQL: Creates the user and grants privileges
- SPCTAB.SQL: Creates the tables
- SPCPKG.SQL: Creates the package
Using Statspack
Taking a Statspack Snapshot The simplest interactive way to take a snapshot is to login to SQL*Plus as the PERFSTAT user and run the procedure STATSPACK. SNAP.
SQL> connect perfstat/PERFSTAT Connected. SQL> EXECUTE statspack.snap; PL/SQL procedure successfully completed.
Taking such a snapshot stores the current values for the performance statistics in the Statspack tables. This snapshot can be used as a baseline for comparison with another snapshot taken at a later time.
to automate the gathering and reporting phases (during a benchmark, for example), you might need to know the snap_id of the snapshot just taken. To take a snapshot and display the snap_id, call the STATSPACK. SNAP function.
SQL> variable snap number; SQL> begin :snap := statspack.snap; end; 2 / PL/SQL procedure successfully completed. SQL> print snap SNAP
20 Running a Statspack Performance Report After snapshots are taken, you can generate performance reports. The SQL scripts that generate the reports prompts you for a beginning snapshot ID, an ending snapshot ID, and a report name.
To run a report , assign values to the SQL*Plus variables that specify the begin snap ID, the end snap ID, and the report name before running SPREPORT. The variables are:
- BEGIN_SNAP: Specifies the begin snapshot ID
- END_SNAP: Specifies the end snapshot ID
- (^) REPORT_NAME: Specifies the report output name
SQL> define begin_snap=
SQL> define end_snap=
SQL> define report_name=batch_run
SQL> @?/rdbms/admin/spreport
Running the SQL Report When you examine the instance report, you often find high-load SQL statements that you want to examine more closely. The SPREPSQL.SQL script can run the SQL report in batch mode. To run a report without being prompted, assign values to the SQL*Plus variables that specify the begin snap ID, the end snap ID, the hash value, and the report name before running the SPREPSQL.SQL script.
The variables are:
- SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>10, i_modify_parameter=>'true');
- Change the defaults immediately without taking a snapshot, using the STATSPACK.MODIFY_STATSPACK_PARAMETER procedure. For example, the following statement changes the snapshot level to 10 and modifies the SQL thresholds for BUFFER_GETS and DISK_READS:
- SQL> EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER
- (i_snap_level=>10, i_buffer_gets_th=>10000, i_disk_reads_th=>1000);
Specifying a Session ID If you want to gather session statistics and wait events for a particular session (in addition to the instance statistics and wait events), specify the session ID in the call to Statspack. For example: SQL> EXECUTE STATSPACK.SNAP(i_session_id=>3);
Time Units Used for Wait Events Oracle supports capturing certain performance data with microsecond granularity. Views that include microsecond timing include the following:
- V$SYSTEM_EVENT, V$SESSION_EVENT ( TIME_WAITED_MICRO column)
- V$SQL ( CPU_TIME, ELAPSED_TIME columns)
- V$LATCH ( WAIT_TIME column)
- V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE ( ACTIVE_TIME column) Event Timings If timings are available, the Statspack report orders wait events by time. If TIMED_STATISTICS is FALSE for the instance, but a subset of users or programs set TIMED_STATISTICS to TRUE dynamically, then the Statspack report output can look inconsistent, where some events have timings (those which the individual programs/users waited for) and some do not. Managing and Sharing Statspack Performance Data This section discusses the following topics:
- Sharing Data Through Export
- Removing Unnecessary Data
- Truncating All Statspack Data Sharing Data Through Export If you want to share data with other sites (for example, if Oracle Support requires the raw statistics), then you can export the PERFSTAT user. An export parameter file (SPUEXP. PAR) is supplied for this purpose. To use this file, supply the export command with the userid parameter, along with the export parameter file name.
For example:
exp userid=perfstat/ my_perfstat_password parfile=spuexp.par Removing Unnecessary Data Purge unnecessary data from the PERFSTAT schema using the SPPURGE.SQL script. This deletes snapshots that fall between the begin and end snapshot IDs you specify. Purging can require the use of a large rollback segment, because all data relating to each snapshot ID to be purged is deleted.
When you run SPPURGE.SQL, it displays the instance to which you are connected and the available snapshots. It then prompts you for the low snap ID and high snap ID. All snapshots that fall within this range are purged. SQL> CONNECT perfstat/ my_perfstat_password SQL> SET TRANSACTION USE ROLLBACK SEGMENT rbig; SQL> @?/rdbms/admin/sppurge Truncating All Statspack Data To truncate all performance data indiscriminately, use SPTRUNC. SQL. This script truncates all statistics data gathered.
SQL> CONNECT perfstat/ my_perfstat_password SQL> @?/rdbms/admin/sptrunc
Removing Statspack
To deinstall Statspack, connect as a user with SYSDBA privilege and run the following SPDROP script from SQL*Plus. SQL> CONNECT / AS SYSDBA SQL> @?/rdbms/admin/spdrop
The SPDROP.SQL script calls the following scripts:
- SPDTAB.SQL - drops tables and public synonyms
- SPDUSR.SQL - drops the user
