|
|
Statspack is a performance tuning tool provided by Oracle with the Oracle9i database distribution. With minimal effort, it can be installed on any Oracle9i database to quickly gather detailed analysis of the performance of that database instance. This appendix describes in brief: Installing the Statspack, Collecting Snapshots, Generating Reports, and identifying the Top WLI Database Bottlenecks.
Installation of the Oracle Statspack tool is a relatively simple process. The following is a step-by-step guide to the process of installing Oracle Statspack on a UNIX system.
$ORACLE_HOME/rdbms/admin directory as follows:# cd $ORACLE_HOME/rdbms/admin/
spcreate.sql, as follows:# sqlplus "/ as sysdba" @spcreate.sql
SQL> exit
Once the Oracle Statspack tool is installed, snapshots must be collected to evaluate database performance. Snapshots are moment-in-time collections of all of the database statistics that the Oracle database continuously collects. Once two snapshots are collected, they can be compared to identify the activity that occurred during the interval between the two snapshots.
Snapshots can be collected a various levels, each increasing level collecting a greater amount of information about the database. As the levels go higher, each level is inclusive of the information collected at the levels below it.
Oracle Statspack comes with a comprehensive reporting script called spreport.sql. When this script is run, it outputs a list of available snapshots, asks the user for two snapshot IDs and a name for the report, and then outputs a text report of the results.
Oracle Statspack is capable of identifying all of the common database performance bottlenecks that have been observed with WLI. This section describes the top WLI database performance bottlenecks and how they are identified in the Oracle Statspack report, and provides recommendations to work around them
Enqueues are local locks that serialize access to various resources. 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.
There are three types of locks that predominantly cause enqueue waits - TX, TM, and ST.
Enqueue waits and their types can be identified by looking at the “Enqueue activity” section of the Statspack report.
For the WLI application, enqueue waits are primarily found for indexed monotonic keys and data block access on the WLI_PROCESS_INSTANCE_INFO table. Enqueue waits can be reduced on these objects by using reverse-key indexes and by partitioning the WLI_PROCESS_INSTANCE_INFO table. See WLI Schema Tuning for more information on using reverse-key indexes and partitioning.
When a user session COMMITs (or rolls back), session REDO information needs to be flushed to the REDO log file. The user session will post the log writer (LGWR) to write all REDO information required from the log buffer to the REDO log file. When the LGWR has finished, it posts the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all the REDO changes are safely on disk.
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. See WLI Schema Tuning for more information on using reverse-key indexes and partitioning.
Log file parallel write waits occur when waiting for writes of REDO records to the REDO log files to complete. The wait occurs in log writer (LGWR) as part of normal activity of copying records from the REDO log buffer to the current online log.
The actual wait time is the time taken for all the outstanding I/O requests to complete. Even though the writes may be issued in parallel, LGWR needs to wait for the last I/O to be on disk before the parallel write is considered complete. 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.
Log file parallel write waits can be reduced by moving log files to the faster disks and/or separate disks where there will be less contention.
DB file sequential read waits signify a wait for an I/O read request to complete. This call differs from ‘DB file scattered reads’ in that a sequential read reads data into contiguous memory (whereas a scattered read reads multiple blocks and scatters them into different buffers in the SGA). If the time spent waiting for reads is significant, then it can be helpful to determine which segments Oracle is performing the reads against.
Segments that are excessive on reads can be identified by looking at the “Top 5 Physical Reads per Segment” and “SQL ordered by Reads” sections of the Statspack report.
Block reads are fairly inevitable so the aim should be to minimize unnecessary I/O. I/O for sequential reads can be reduced by tuning SQL calls that result in full table scans and using the partitioning option for large tables.
DB file scattered read waits happens when a session is waiting for a multi-block I/O to complete. This typically occurs during full table scans or index fast full scans.
Segments that are excessive on reads can be identified by looking at the “Top 5 Physical Reads per Segment” and “SQL ordered by Reads” sections of the Statspack report.
Ideally, applications should not repeatedly perform full table scans of the online portions of application data when there is a faster and more selective way to retrieve the data. Query tuning should be used to optimize online SQL to use indexes.
The buffer hit ratio metric shows how often processes are finding data blocks in memory vs. retrieving them from disk.
Buffer hit ratio can be found in the “Instance Efficiency Percentages” section for the Statspack report.
The exact value of the buffer hit ratio is of less importance than the ability to monitor it over time and notice any significant changes in the profile of activity on the database. If the ratio falls below 80%, then more memory should be allocated to the database by increasing the value of the DB_CACHE_SIZE parameter.
In some cases, the ratio can be low due to poorly performing SQL statements. In this case, the buffer hit ratio may not increase after increasing DB_CACHE_SIZE. These SQL statements should be tuned to avoid excessive physical I/O.
Row lock waits occur when a process requests an incompatible lock for a row that is currently locked by another process. These lock waits can usually be attributed to high volume inserts on a table with a primary key index.
Segments where performance suffers from excessive row lock waits can be identified in the “Top 5 Row Lock Waits per Segment” section of the Statspack report.
These waits can be avoided by partitioning tables or by using reverse-key indexes. For WLI, these waits can be found on the WLI_PROCESS_INSTANCE_INFO table and on the primary key index of this tables. See WLI Schema Tuning for more information on using reverse-key indexes and partitioning.
The library cache hit ratio indicates how often Oracle retrieves a parsed SQL or PL/SQL statement from the library cache. When an application makes a SQL or stored procedure call, Oracle checks the library cache to determine if a parsed version of the statement is already stored there. If the parsed statement is stored in the library cache, Oracle executes the statement immediately. If not, Oracle parses the statement and allocates a shared SQL area within the library cache for it. A low library cache hit ratio can result in additional parsing, which decreases performance and increases CPU consumption for the database.
The library hit ratio can be found in the “Instance Efficiency Percentages” section of the Statspack report.
If this ratio falls below 80%, increasing the size of shared pool area can help. This can be done by changing the value of the SHARED_POOL_SIZE parameter.
|