Statspack was used before Oracle 10g. In Oracle 10g version AWR and ADDM are used to monitor the performance of database.
The default level of collection, level 5, is adequate for most applications. At this level the normal performance statistics are captured along with the high-resource-usage SQL statements. Parameters can be used to set the limits for the SQL statement collection, which will be highly system dependent.
It is also possible to capture statistics from an individual session as part of a snapshot by using the i_session_id parameter to the procedure. The example below will capture session level statistics for the session with a session id (Oracle sid) of 32.
SQL> execute statspack.snap(i_session_id=>32);
There are currently five different levels of statspack snapshots, defined as follows in the table stats$level_description (9i version):
SNAP_LEVEL DESCRIPTION
---------- -----------------------------------------------------------
0 This level captures general statistics, including rollback
segment, row cache, SGA, system events, background events,
session events, system statistics, wait statistics, lock
statistics, and Latch information
5 This level includes capturing high resource usage SQL
Statements, along with all data captured by lower levels
6 This level includes capturing SQL plan and SQL plan usage
information for high resource usage SQL Statements, along
with all data captured by lower levels
7 This level captures segment level statistics, including
logical and physical reads, row lock, itl and buffer busy
waits, along with all data captured by lower levels
10 This level includes capturing Child Latch statistics, along
with all data captured by lower levels
Using Statspack: Performance monitoring tool----------------------------------------------
1) Run the scripts to install statspack
SQL> @?/rdbms/admin/spcreate.sql
2) Enter password for perfstat, enter default_tablespace (USERS), enter temporary tablespace (TEMP)
3) conn perfstat/perfstat
4) execute statspack.snap
5) Again repeat step 4 after sometime to get another snap
6) @?/rdbms/admin/spreport.sql
7) Enter a value for begin_snap and end_snap
8) Give a name for the report
9) host vi
.lst to view the report
Resolving Your Wait Events
DB File Scattered Read
This generally indicates waits related to full table scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing or suppressed indexes. Although it may be more efficient in your situation to perform a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list.
DB File Sequential Read
This event generally indicates a single block read (an index read, for example). A large number of waits here could indicate poor joining orders of tables, or unselective indexing. It is normal for this number to be large for a high-transaction, well-tuned system, but it can indicate problems in some circumstances. You should correlate this wait statistic with other known issues within the Statspack report, such as inefficient SQL. Check to ensure that index scans are necessary, and check join orders for multiple table joins. The DB_CACHE_SIZE will also be a determining factor in how often these waits show up. Problematic hash-area joins should show up in the PGA memory, but they're also memory hogs that could cause high wait numbers for sequential reads. They can also show up as direct path read/write waits.
Free Buffer
This indicates your system is waiting for a buffer in memory, because none is currently available. Waits in this category may indicate that you need to increase the DB_BUFFER_CACHE, if all your SQL is tuned. Free buffer waits could also indicate that unselective SQL is causing data to flood the buffer cache with index blocks, leaving none for this particular statement that is waiting for the system to process. This normally indicates that there is a substantial amount of DML (insert/update/delete) being done and that the Database Writer (DBWR) is not writing quickly enough; the buffer cache could be full of multiple versions of the same buffer, causing great inefficiency. To address this, you may want to consider accelerating incremental checkpointing, using more DBWR processes, or increasing the number of physical disks.
Buffer Busy
This is a wait for a buffer that is being used in an unshareable way or is being read into the buffer cache. Buffer busy waits should not be greater than 1 percent. Check the Buffer Wait Statistics section (or V$WAITSTAT) to find out if the wait is on a segment header. If this is the case, increase the freelist groups or increase the pctused to pctfree gap. If the wait is on an undo header, you can address this by adding rollback segments; if it's on an undo block, you need to reduce the data density on the table driving this consistent read or increase the DB_CACHE_SIZE. If the wait is on a data block, you can move data to another block to avoid this hot block, increase the freelists on the table, or use Locally Managed Tablespaces (LMTs). If it's on an index block, you should rebuild the index, partition the index, or use a reverse key index. To prevent buffer busy waits related to data blocks, you can also use a smaller block size: fewer records fall within a single block in this case, so it's not as "hot." When a DML (insert/update/ delete) occurs, Oracle Database writes information into the block, including all users who are "interested" in the state of the block (Interested Transaction List, ITL). To decrease waits in this area, you can increase the initrans, which will create the space in the block to allow multiple ITL slots. You can also increase the pctfree on the table where this block exists (this writes the ITL information up to the number specified by maxtrans, when there are not enough slots built with the initrans that is specified).
Uninstall Statspack:
---------------------
1) Drop the user perfstat
drop user perfstat cascade; [cascade will drop all objects in user's schema]
2) @?/rdbms/admin/spdrop.sql;
