Release 1 (9.0.1)
Part Number A87503-02
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Database Performance Guide and Reference Release 1 (9.0.1) Part Number A87503-02 |
|
This chapter explains how to install and configure Statspack.
This chapter contains the following sections:
Statspack differs from the existing UTLBSTAT/UTLESTAT performance scripts in the following ways:
BSTAT/ESTAT).
PERFSTAT store performance statistics. Instead of creating/dropping tables each time, data is inserted into the preexisting tables. This makes historical data comparisons easier.
DBMS_JOB or an operating system utility.
COMMIT or a ROLLBACK, and so calculates the number of transactions as 'user commits' + 'user rollbacks.' BSTAT/ESTAT considers a transaction to complete with a COMMIT only, and so assumes that transactions = 'user commits.' For this reason, comparing per transaction statistics between Statspack and BSTAT/ESTAT can result in significantly different per transaction ratios.
Statspack is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. A user is automatically created by the installation script. This user, PERFSTAT, owns all objects needed by this package. This user is granted limited query-only privileges on the V$ views required for performance tuning.
Statspack users become familiar with the concept of a snapshot, a single collection of performance data. Each snapshot taken is identified by a snapshot ID, which is a unique number generated at the time the snapshot is taken. Each time a new collection is taken, a new SNAP_ID is generated.
The SNAP_ID, along with the database identifier (DBID) and instance number (INSTANCE_NUMBER), comprise the unique key for a snapshot (using this unique combination allows storage of multiple instances of an Oracle Real Application Clusters database in the same tables).
After snapshots are taken, it is possible to run the performance report. The performance report prompts for the two snapshot IDs the report will process. The report produced calculates the activity on the instance between the two snapshot periods specified, similar to the BSTAT/ESTAT report. To compare, the first SNAP_ID supplied can be considered the equivalent of running BSTAT; the second SNAP_ID specified can be considered the equivalent of ESTAT. Unlike BSTAT/ESTAT, which can by it's nature only compare two static data points, the report can compare any two snapshots specified.
The amount of database space required by the package varies considerably based on the frequency of snapshots, the size of the database and instance, and the amount of data collected (which is configurable). It is, therefore, difficult to provide general storage clauses and space utilization predictions that are accurate at each site.
If you install the package in a dictionary-managed tablespace, then monitor the space used by the objects created and, if required, adjust the storage clauses of the segments.
If you install the package in a locally-managed tablespace, then storage clauses are not required, because the storage characteristics are automatically managed.
There are two ways to install Statspack:
Batch mode is useful when you do not want to be prompted for the PERFSTAT user's default and temporary tablespaces.
The first step is to create 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 default and temporary tablespaces. 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 the package, either change to the ORACLE_HOME rdbms/admin directory, or fully specify the ORACLE_HOME/rdbms/admin directory when calling the installation script, SPCREATE.
To run the installation script, you must use SQL*Plus and connect as a user with SYSDBA privilege. For example, start SQL*Plus, then:
On Unix:
SQL> CONNECT / AS SYSDBA SQL> @?/rdbms/admin/spcreate
On NT:
SQL> CONNECT / AS SYSDBA SQL> @%ORACLE_HOME%\rdbms\admin\spcreate
The SPCREATE install script runs three other scripts. These scripts are called automatically, so you do not need to run them:
SPCUSR: Creates the user and grants privileges
SPCTAB: Creates the tables
SPCPKG: Creates the package
Check each of the three output files produced (SPCUSR.LIS, SPCTAB.LIS, SPCPKG.LIS) by the installation to ensure that no errors were encountered.
To install in batch mode, you must assign values to the SQL*Plus variables that specify the default and temporary tablespaces before running SPCREATE. The variables are:
For example, on Unix:
SQL> CONNECT / AS SYSDBA SQL> define default_tablespace='tools' SQL> define temporary_tablespace='temp' SQL> @?/rdbms/admin/spcreate
SPCREATE no longer prompts for the above information.
The simplest interactive way to take a snapshot is to login to SQL*Plus as the PERFSTAT user and run the procedure STATSPACK.SNAP. For example:
SQL> CONNECT perfstat/perfstat SQL> EXECUTE statspack.snap;
This stores the current values for the performance statistics in the Statspack tables, and can be used as a baseline snapshot for comparison with another snapshot taken at a later time.
For better performance analysis, set the initialization parameter TIMED_STATISTICS to true. This way, Statspack data collected includes important timing information. The TIMED_STATISTICS parameter can be dynamically changed using the ALTER SYSTEM statement. Timing data is important and ususally is required by Oracle support to diagnose performance problems.
Typically, to automate the gathering and reporting phases (such as during a benchmark), 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. Below is an example of calling the snap function using an anonymous PL/SQL block in SQL*Plus:
SQL> variable snap number; SQL> begin :snap := statspack.snap; end; 2 / PL/SQL procedure successfully completed. SQL> print snap SNAP ---------- 12
To make performance comparisons from one day, week, or year to the next, there must be multiple snapshots taken over a period of time. The best method to gather snapshots is to automate the collection on a regular time interval. There are the following options:
DBMS_JOB procedure to schedule snapshots.
To use an Oracle-automated method for collecting statistics, use the DBMS_JOB package. A sample script on how to do this is supplied in SPAUTO.SQL, which schedules a snapshot every hour, on the hour.
You might want to schedule snapshots at regular times each day to reflect your system's OLTP or batch peak loads. For example, take snapshots at 9am, 10am, 11am, 12 midday, and 6pm for the OLTP load, then a snapshot at 12 midnight and another at 6am for the batch window.
In order to use DBMS_JOB to schedule snapshots, the JOB_QUEUE_PROCESSES initialization parameter must be set to greater than 0 in the init.ora file for the job to be run automatically.
Example of an initialization entry:
# Set to enable the job queue process to start. This allowsDBMS_JOB to# schedule automatic statistics collection using StatspackJOB_QUEUE PROCESSES=1
If using SPAUTO.SQL in an Oracle Real Application Clusters environment, the SPAUTO.SQL script must be run once on each instance in the cluster. Similarly, the JOB_QUEUE_PROCESSES parameter must also be set for each instance.
Use the DBMS_JOB.INTERVAL procedure to change the interval of statistics collection. For example:
EXECUTE DBMS_JOB.INTERVAL(1,'SYSDATE+(1/48)');
Where 'SYSDATE+(1/48)' results in the statistics being gathered each 1/48 hours (that is, every half hour).
To force the job to run immediately:
EXECUTE DBMS_JOB.RUN(<job number>);
To remove the autocollect job:
EXECUTE DBMS_JOB.REMOVE(<job number>);
After snapshots are taken, it is possible to generate a performance report. The SQL script that generates the report prompts for the two snapshot ID's to be processed.
There are two reports. The first (spreport.sql) is a general instance health report, covering all aspects of instance performance. The second report, a SQL report, usually is run after examining the first report. The SQL report only reports on a single SQL statement (as identified by the hash value). Both reports prompt for the beginning snapshot ID, the ending snapshot ID, and the report name. The instance report then calculates and prints ratios, increases, and so on for all statistics between the two snapshot periods, similar to the BSTAT/ESTAT report. The SQL report only reports on data relating to the single SQL statement.
Separating the phase of data gathering from producing a report allows the flexibility of basing a report on any data points selected. For example, it might be reasonable for the DBA to use the supplied automation script to automate data collection every hour, on the hour. If, at some later point, a performance issue arose that might be better investigated by looking at a three hour data window rather than an hour's worth of data, then all the DBA needs to do is specify the required start point and end point when running the report.
To examine the change in instance-wide statistics between two time periods, the SPREPORT.SQL file is run while connected to the PERFSTAT user. The SPREPORT.SQL command file is located in the rdbms/admin directory of the Oracle home.
You are prompted for the following:
For example, on Unix:
SQL> connect perfstat/perfstat SQL> @?/rdbms/admin/spreport
For example, on NT:
SQL> connect perfstat/perfstat SQL> @%ORACLE_HOME%\rdbms\admin\spreport
Example output:
SQL> connect perfstat/perfstat Connected. SQL> @spreport
DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 2618106428 PRD1 1 prd1 Completed Snapshots Snap Snap Instance DB Name Id Snap Started Level Comment ------------ ------------ ----- ----------------- ----- ---------------- prd1 PRD1 1 11 May 2000 12:07 5 2 11 May 2000 12:08 5Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 1 Begin Snapshot Id specified: 1 Enter value for end_snap: 2 End Snapshot Id specified: 2Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_1_2 To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: <press return or enter a new name> Using the report name sp_1_2
The report now scrolls past and is also written to the file specified (for example, sp_1_2.lis).
To run a report without being prompted, 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
For example, on Unix:
SQL> connect perfstat/perfstat SQL> define begin_snap=1 SQL> define end_snap=2 SQL> define report_name=batch_run SQL> @?/rdbms/admin/spreport
SPREPORT no longer prompts for the above information.
After the instance report has been examined, often there are high-load SQL statements when should be examined more closely. The SQL report sprepsql.sql, displays statistics, the complete SQL text, and (if level six snapshot has been taken), information on any SQL plan(s) associated with that statement.
The SQL statement to be reported on is identified by a hash value, which is a numerical representation of the statement's SQL text. The hash value for each statement is displayed for each statement in the SQL sections of the instance report.
The sprepsql.sql file is executed while connected to the PERFSTAT user. It is located in the rdbms/admin directory of the Oracle home.
You are prompted for the following:
Example output:
SQL> connect perfstat/perfstat Connected. SQL> @spreport
DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 2618106428 PRD1 1 prd1
Completed Snapshots Snap Snap Instance DB Name Id Snap Started Level Comment ------------ ------------ ----- ----------------- ----- ---------------------- prd1 PRD1 37 02 Mar 2001 11:01 6 38 02 Mar 2001 12:01 6 39 08 Mar 2001 09:01 5 40 08 Mar 2001 10:02 5 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 39 Begin Snapshot Id specified: 39 Enter value for end_snap: 40 End Snapshot Id specified: 40 Specify the Hash Value ~~~~~~~~~~~~~~~~~~~~~~ Enter value for hash_value: 1988538571 Hash Value specified is: 1988538571 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_39_40_1988538571. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: Using the report name sp_39_40_1988538571 The report will scroll past, and also be written to the file specified (e.g. sp_39_40_1988538571.lis).
Similar to spreport.sql, the SQL report can be run 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, and the report name before running spreport.
The variables are:
BEGIN_SNAP: specifies the begin snapshot ID
END_SNAP: specifies the end snapshot ID
HASH_VALUE: specifies the hash value
REPORT_NAME: specifies the report output name
For example:
SQL> connect perfstat/perfstat SQL> define begin_snap=39 SQL> define end_snap=40 SQL define hash_value=1988538571 SQL> define report_name=batch_sql_run SQL> @sprepsql
SPREPSQL no longer prompts for the above information.
For best performance when running the performance reports, collect optimizer statistics for tables and indexes owned by PERFSTAT. This should be performed whenever there is significant change in data volumes in PERFSTAT's tables. To do this, use DBMS_STATS or DBMS_UTILITY, and specify the PERFSTAT user: For example:
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'PERFSTAT',CASCADE=>TRUE);
or
EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('PERFSTAT','COMPUTE');
Both the snapshot level and the thresholds specified affect the amount of data Statspack captures.
It is possible to change the amount of information gathered by the package by specifying a different snapshot 'level'. In other words, the level chosen (or defaulted) decides the amount of data collected. The higher the snapshot level, the more data is gathered. The default level set by the installation is level 5.
For typical usage, level 5 snapshot is effective on most sites. There are certain situations when using a level 6 snapshot is beneficial. These include the following:
There are other parameters that can be configured in addition to the snapshot level. These parameters are used as thresholds when collecting data on SQL statements; data is captured on any SQL statements that breach the specified thresholds. Snapshot level and threshold information used by the package is stored in the STATS$STATSPACK_PARAMETER table.
You can change the default parameters used for taking snapshots so that they are tailored to the instance's workload. The full list of parameters that can be passed into the MODIFY_STATSPACK_PARAMETER procedure are the same as those for the SNAP procedure.
To temporarily use a snapshot level or threshold that is different than the instance's default snapshot values, simply specify the required threshold or snapshot level when taking the snapshot. This value is used only for the immediate snapshot taken; the new value is not saved as the default.
For example, take a single level 6 snapshot (do not save level 6 as the default):
SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>6);
You can save the new value as the instance's default in two ways:
STATSPACK.SNAP the I_MODIFY_PARAMETER input variable).
SQL> EXECUTE STATSPACK.SNAP - (i_snap_level=>10, i_modify_parameter=>'true');
Setting the I_MODIFY_PARAMETER value to true saves the new thresholds in the STATS$STATSPACK_PARAMETER table. These thresholds are used for all subsequent snapshots.
If the I_MODIFY_PARAMETER was set to false or if it were omitted, then the new parameter values would not be saved. Only the snapshot taken at that point uses the specified values. Any subsequent snapshots use the preexisting values in the STATS$STATSPACK_PARAMETER table.
STATSPACK.MODIFY_STATSPACK_PARAMETER procedure. For example, to change the snapshot level to 10, and the SQL thresholds for BUFFER_GETS and DISK_READS, the following statement can be issued:
SQL> EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER - i_snap_level=>10, i_buffer_gets_th=>10000, i_disk_reads_th=>1000);
This procedure changes the values permanently, but does not take a snapshot.
This level and any level greater than 0 collects general performance statistics, such as wait statistics, system events, system statistics, rollback segment data, row cache, SGA, background events, session events, lock statistics, buffer pool statistics, and parent latch statistics.
This level includes all statistics gathered in the lower level(s), and additionally gathers the performance data on high resource usage SQL statements. In a level 5 snapshot (or above), the time required for the snapshot to complete is dependant on the SHARED_POOL_SIZE and on the number of SQL statements in the shared pool at the time the snapshot is taken. The larger the shared pool, the longer the time taken to complete the snapshot.
The SQL statements gathered by Statspack are those that exceed one of six predefined threshold parameters:
The values of each of these threshold parameters are used when deciding which SQL statements to collect. If a SQL statement's resource usage exceeds any one of the above threshold values, then it is captured during the snapshot.
The SQL threshold levels used are either those stored in the table STATS$STATSPACK_PARAMETER or by the thresholds specified when the snapshot is taken.
This level includes all statistics gathered in the lower level(s). Additionally, it gathers SQL plans and plan usage data for each of the high resource usage SQL statements captured.
A level 6 snapshot gathers valuable information for determining whether the execution plan used for a SQL statement has changed. Therefore, level 6 snapshots should be used whenever there is the possibility that a plan may change.
To gather the plan for a SQL statement, the statement must be in the shared pool at the time the snapshot is taken, and it must exceed one of the SQL thresholds. To gather plans for all statements in the shared pool, specify the executions threshold to be zero (0) for those snapshots.
|
See Also:
"Changing the Default Values for Snapshot Levels and SQL Thresholds" for information on how to do this |
This level includes all statistics gathered in the lower levels. Additionally, it gathers parent and child latch information. Sometimes, data gathered at this level can cause the snapshot to take longer to complete. This level can be resource-intensive, and it should only be used when advised by Oracle personnel.
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. The statistics gathered for the session include session statistics, session events, and lock activity. The default behavior is to not gather session level statistics.
For example:
SQL> EXECUTE STATSPACK.SNAP(i_session_id=>3);
Parameters able to be passed in to the STATSPACK.SNAP and STATSPACK.MODIFY_STATSPACK_PARAMETER procedures are as follows:
Range of Default Parameter Name Valid Values Value Meaning ------------------ ------------ ------- ----------------------------------- i_snap_level 0, 5, 6, 10 5 Snapshot Level i_ucomment Text Blank Comment to be stored with Snapshot i_executions_th Integer >=0 100 SQL Threshold: number of times the statement was executed i_disk_reads_th Integer >=0 1,000 SQL Threshold: number of disk reads the statement made i_parse_calls_th Integer >=0 1,000 SQL Threshold: number of parse calls the statement made i_buffer_gets_th Integer >=0 10,000 SQL Threshold: number of buffer gets the statement made i_sharable_mem_th Integer >=0 1048576 SQL Threshold: amount of sharable memory i_version_count_th Integer >=0 20 SQL Threshold: number of versions of a SQL statement i_session_id Valid sid 0 (no Session Id of the Oracle Session from session) to capture session granular v$session statistics for i_modify_parameter True,False False Save the parameters specified for future snapshots?
Oracle supports capturing certain performance data with microsecond granularity. Views that include microsecond timing include the following:
V$SESSION_WAIT, 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)
Because microsecond timing might not be appropriate for rolled-up data, such as that displayed by Statspack, Statspack displays most times in seconds, and average times in milliseconds (for easier comparison with operating system monitoring utilities which often report timings in milliseconds).
For clarity, the time units used are specified in the column headings of each timed column in the Statspack report. The following convention used is:
(s) - a second
(cs) - a centisecond (100th of a second)
(ms) - a millisecond (1,000th of a second)
(us) - a microsecond (1,000,000th of a second)
If timings are available, the Statspack report orders wait events by time (in the Top-5 and background and foreground wait events sections).
If TIMED_STATISTICS is false for the instance, but a subset of users or programs set TIMED_STATISTICS (set 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. The Top-5 section also looks unusual in this situation.
Optimally, TIMED_STATISTICS should be set to true at the instance level for ease of diagnosing performance problems.
If you want to share data with other sites (for example, if Oracle Support requires the raw statistics), then it is possible to 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/perfstat parfile=spuexp.par
This creates a file called SPUEXP.DMP and the log file SPUEXP.LOG. If you want to load the data into another database, use the import command.
Purge unnecessary data from the PERFSTAT schema using the SPPURGE.SQL script. This deletes snapshots that fall between the begin and end range of the snapshot ID's specified.
Purging can require the use of a large rollback segment, because all data relating to each snapshot ID to be purged is deleted. To avoid rollback segment extension errors, explicitly use a large rollback segment. This can be done by executing the SET TRANSACTION USE ROLLBACK SEGMENT statement before running the SPPURGE.SQL script. Alternatively, to avoid rollback segment extension errors, specify a smaller range of snapshot ID's to purge.
When SPPURGE is run, the instance currently connected to and the available snapshots are displayed. The DBA is then prompted for the low snap ID and high snap ID. All snapshots that fall within this range are purged.
For example, connect to PERFSTAT using SQL*Plus, then:
SQL> CONNECT perfstat/perfstat SQL> SET TRANSACTION USE ROLLBACK SEGMENT rbig; SQL> @sppurge Database Instance currently connected to ======================================== Instance DB Id DB Name Inst Num Name ----------- ---------- -------- ---------- 720559826 PERF 1 perf Snapshots for this database instance ==================================== Snap Snap Id Level Snapshot Started Host Comment ---------- ----- --------------------- --------------- ------------------- 1 5 30 Feb 2000 10:00:01 perfhost 2 5 30 Feb 2000 12:00:06 perfhost 3 5 01 Mar 2000 02:00:01 perfhost 4 5 01 Mar 2000 06:00:01 perfhost
Specify the Low Snap ID and High Snap ID range to purge ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for losnapid: 1 Using 1 for lower bound. Enter value for hisnapid: 2 Using 2 for upper bound. Deleting snapshots 1 - 2
Purge of specified snapshot range complete. If you want to rollback the purge, it is still possible to do so. Exiting from SQL*Plus automatically commits the purge.
SQL> -- end of example output
To purge in batch mode, you must assign values to the SQL*Plus variables that specify the low and high snapshot IDs to purge. The variables are:
For example:
SQL> CONNECT perfstat/perfstat SQL> DEFINE losnapid=1 SQL> DEFINE hisnapid=2 SQL> @sppurge
SPPURGE no longer prompts for the above information.
If you want to truncate all performance data indiscriminately, use SPTRUNC.SQL. This script truncates all statistics data gathered.
For example, connect to PERFSTAT using SQL*Plus, then:
SQL> CONNECT perfstat/perfstat SQL> @sptrunc ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you would like to continue, enter any string, followed by <return>. Enter value for anystring: entered - starting truncate operation Table truncated. <etc> Truncate operation complete.
The unique identifiers for a database instance used by Statspack are the DBID and the INSTANCE_NUMBER. When using Oracle Real Application Clusters, the INSTANCE_NUMBER could change between startups (either because the INSTANCE_NUMBER parameter is set in the initialization file or because the instances are started in a different order).
In this case, because Statspack uses the INSTANCE_NUMBER and the DBID to identify the instance's snapshot preferences, this could inadvertently result in a different set of levels or thresholds being used when snapshotting an instance.
There are three conditions that must be met for this to occur:
The only way the parameters differ is if the parameters have been explicitly modified by the DBA after installation, either by saving the specified values or by using the MODIFY_STATSPACK_PARAMETER procedure. Check whether any of the Statspack snapshot parameters are different for the instances by querying the STATS$STATSPACK_PARAMETER table.
To deinstall Statspack, connect as a user with SYSDBA privilege and run the following SPDROP script from SQL*Plus. For example:
SQL> CONNECT / AS SYSDBA SQL> @spdrop
This script calls two other scripts:
Check each of two output files produced (SPDTAB.LIS, SPDUSR.LIS) to ensure that the package was completely deinstalled.
The following must be run as a user with SYSDBA privilege:
SPCREATE.SQL: Creates entire Statspack environment (calls SPCUSR.SQL, SPCTAB.SQL, SPCPKG.SQL)
SPDROP.SQL: Drops entire Statspack environment (calls SPDTAB.SQL, SPDUSR.SQL)
The following are run as a user with SYSDBA privilege by the calling scripts (above):
The following are run as PERFSTAT by the calling scripts (above):
SPCUSR.SQL: Creates the Statspack user (PERFSTAT)
SPCTAB.SQL: Creates Statspack tables
SPCPKG.SQL: Creates the Statspack package
The following must be run as PERFSTAT:
SPREPORT.SQL: Generates a Statspack report
SPREPSQL.SQL: Generates a Statspack SQL report for the specific SQL hash value specified
SPREPINS.SQL: Generates a Statspack report for the database and instance specified
SPAUTO.SQL: Automates Statspack statistics collection (using DBMS_JOB)
The following must be run as PERFSTAT:
SPUP817.SQL: Converts data from the 8.1.7 schema to the 9.0 schema. Backup the existing schema before running the upgrade. If upgrading from Statspack 8.1.6, SPUP816.SQL must be run first.
The following must be run as PERFSTAT:
SPPURGE.SQL: Purges a limited range of Snapshot ID's for a given database instance.
SPTRUNC.SQL: Truncates all performance data in Statspack tables
SPUEXP.PAR: An export parameter file supplied for exporting the whole PERFSTAT user.
The SPDOC.TXT file contains instructions and documentation on the Statspack package.
Statspack is not supported with releases earlier than 8.1.6.
Storing data from multiple databases in one PERFSTAT user account is currently not supported.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|