What is statspack

You have the following options:. 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, you could take snapshots at 9 a. SQL script once on each instance in the cluster. After snapshots are taken, you can generate performance reports. The Statspack package includes two reports. It is not correct to specify begin and end snapshots where the begin snapshot and end snapshot were taken from different instance startups.

In other words, the instance must not have been shutdown between the times that the begin and end snapshots were taken. This is necessary because the database's dynamic performance tables, which Statspack queries to gather the data, reside in memory.

Hence, shutting down the database resets the values in the performance tables to 0. Because Statspack subtracts the begin-snapshot statistics from the end-snapshot statistics, the resulting output is invalid. If begin and end snapshots taken between shutdowns are specified in the report, then the report shows an appropriate error to indicate this. Because data gathering is separate from report production, you have flexibility to base a report on any data points you select. For example, as DBA you might want 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, all you have to do is specify the required start point and end point when running the report.

In an Oracle Real Application Clusters environment, you must connect to the instance on which you want to report. The blank lines thus identify begin and end snapshots that cannot be used together when running a Statspack report. Example shows the SQL commands to run the report and an example of the partial report output.

When you examine the instance report, you often find high-load SQL statements that you want to examine more closely. SQL , displays statistics, the complete SQL text, and if a 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. SQL script. Both the snapshot level and the thresholds specified affect the amount of data Statspack captures. You can change the amount of information gathered by specifying a different snapshot level.

The higher the snapshot level, the more data is gathered. The default level set at 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 you can configure, 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.

You can change the default parameters used for taking snapshots so that they are tailored to the instance's workload. To temporarily use a snapshot level or threshold that is different from the instance's default snapshot values, you 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. These thresholds are used for all subsequent snapshots.

Only the snapshot taken at that point uses the specified values. 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 wait event indicates a wait for a lock that is held by another session or sessions in an incompatible mode to the requested mode. The action to take to reduce enqueue waits depends on the lock type that is causing the wait. Enqueue waits and their types can be identified by looking at the "Enqueue activity" section of the Statspack report.

When the LGWR has finished, it posts the user session. Waits on log file sync can be identified by looking at the "Top 5 Timed Events" or "Wait Events" section of the Statspack report. These waits can be reduced by moving log files to the faster disks or by reducing COMMIT frequency by performing batch transactions. Buffer busy waits happen when a session needs to access a database block in the buffer cache but cannot, because the buffer is "busy".

The two main cases where this can occur are:. Segments with high buffer busy waits can be identified by looking in the "Top 5 Buf. Busy Waits per Segment" section of the Statspack report. Buffer busy waits can be reduced by using reverse-key indexes for busy indexes and by partitioning busy tables.

Hence the wait time depends on the time it takes the OS to complete all requests. Waits for log file parallel writes can be identified by looking at the "Top 5 Timed Events" or "Wait Events" section of the Statspack report. If the time spent waiting for reads is significant, then it can be helpful to determine which segments Oracle is performing the reads against.

If the latch is not available, a latch free miss is recorded. Most latch problems are related to the failure to use bind variables library cache latch , redo generation issues redo allocation latch , buffer cache contention issues cache buffers LRU chain , and hot blocks in the buffer cache cache buffers chain. There are also latch waits related to bugs; check MetaLink for bug reports if you suspect this is the case. When latch miss ratios are greater than 0.

An enqueue is a lock that protects a shared resource. Locks protect shared resources, such as data in a record, to prevent two people from updating the same data at the same time.

An enqueue includes a queuing mechanism, which is FIFO first in, first out. The ST enqueue is used for space management and allocation for dictionary-managed tablespaces.

Use LMTs, or try to preallocate extents or at least make the next extent larger for problematic dictionary-managed tablespaces.

HW enqueues are used with the high-water mark of a segment; manually allocating the extents can circumvent this wait. TX4s are the most common enqueue waits. TX4 enqueue waits are usually the result of one of three issues. The second is multiple updates to the same bitmap index fragment. Since a single bitmap fragment may contain multiple rowids, you need to issue a commit or rollback to free the enqueue when multiple users are trying to update the same fragment.

The third and most likely issue is when multiple users are updating the same block. If there are no free ITL slots, a block-level lock could occur. If you have foreign keys, be sure to index them to avoid this general locking issue. This wait occurs because you are writing the log buffer faster than LGWR can write it to the redo logs, or because log switches are too slow. To address this problem, increase the size of the log files, or increase the size of the log buffer, or get faster disks to write to.

You might even consider using solid-state disks, for their high speed. All commit requests are waiting for "logfile switch archiving needed " or "logfile switch Checkpoint. You may need to add more or larger redo logs, and you may potentially need to add database writers if the DBWR is the problem. When a user commits or rolls back data, the LGWR flushes the session's redo from the log buffer to the redo logs. The log file sync process must wait for this to successfully complete.

To reduce wait events here, try to commit more records try to commit a batch of 50 instead of one at a time, for example. Put redo logs on a faster disk, or alternate redo logs on different physical disks, to reduce the archiving effect on LGWR. Information about the installtion and usage of statspack can be found in the following document. This is achieved by running the following script as SYS.

At a later time you can take another system snapshot, giving you a potential start and end point for your analysis.