11.2.0.1 Statspack Report Contents

The following had to be cut from the “Expert Oracle Practices: Oracle Database Administration from the Oak Table” book, as it was about 4-5 pages of bulleted text. Someone might find this useful:

A level 7 Statspack report created by Oracle 11.2.0.1 includes the following sections (see the file spcpkg.sql in the rdbms/admin directory of the Oracle home).
• Database and host information – instance name, start up time, and Oracle release.
• Snapshot summary – starting and ending times for the statistics, number of sessions, elapsed time, CPU time used by the instance, and total waits with CPU time experienced by the sessions.
• Cache sizes – buffer cache size, shared pool size, standard block size, and log buffer size.
• Load profile – rates per second and per transaction for redo generation, physical reads/writes, parses, executes, rollbacks, DB time, and a handful of other statistics.
• Instance efficiency indicators – several ratios including execute to parse %, parse CPU to parse elapsed %, % non-parse CPU, and latch hit %.
• Top timed events – top five elapsed time wait events unioned to the CPU time.
• Host CPU – user mode CPU time, kernel mode CPU time, and unused CPU time accumulated across all processes running on the server.
• Instance CPU – available CPU time, busy system-wide CPU time, and CPU time used by the instance.
• Memory statistics – system memory, SGA, and PGA used in the start and end periods.
• Time model statistics – allocation of CPU time and elapsed time used by the instance (V$SYS_TIME_MODEL).
• Foreground wait events – wait time experienced by the end-user sessions.
• Background wait events – wait time encountered by Oracle’s background processes (DBWn, LGWR, PMON, SMON, etc.)
• Wait events (fg and bg) – wait time for all foreground and background processes.
• Wait event histogram – indicates the percentage of each wait on a wait event which completed in a time range (less than 1ms, between 1ms and 1.999ms, between 2ms and 3.999ms, etc), listing the delta values from V$EVENT_HISTOGRAM.
• SQL ordered by CPU – lists the top SQL statements consuming the greatest number of CPU seconds in the time period, with a default minimum of 1% of the DB CPU statistic. Statistics for SQL statements are grouped on the OLD_HASH_VALUE and ADDRESS columns, which allows the same SQL statement to appear multiple times in the Statspack report if the plan for the SQL statement changed between the selected start and end Statspack snapshots (this is true for the remaining SQL sections as well).
• SQL ordered by gets – lists the top SQL statements requiring the largest number of consistent gets in the time period, with a default minimum of 10,000 consistent gets.
• SQL ordered by reads – lists the top SQL statements requiring the largest number of physical block reads in the time period, with a default minimum of 1,000 physical block reads.
• SQL ordered by executions – lists the top SQL statements with the greatest number of executions in the time period, with a default minimum of 100 executions.
• SQL ordered by parse calls – lists the top SQL statements with the greatest number of hard or soft parse calls in the time period, with a default minimum of 1,000 parse calls.
• SQL ordered by sharable memory – lists the top SQL statements with the greatest SHARABLE_MEM summed over the OLD_HASH_VALUE and ADDRESS columns (V$SQL), with a default minimum of 1MB of sharable memory.
• SQL ordered by version count – lists the SQL statements with the greatest number of child cursors in the library cache, with a default minimum of 20 child cursors.
• Instance activity statistics – lists the delta values of changed system-wide statistics (V$SYSSTAT), showing the delta value over the time period, the average delta value per second, and the average delta value per transaction (defined as user commits + user rollbacks).
• Instance activity statistics (absolute values) – number of sessions connected to the instance, number of open cursors, number of cursors held open in the session cursor cache, and the approximate number of redo log switches.
• OS statistics – CPU usage and memory statistics for the server (V$OSSTAT).
• OS statistics detail – lists the server CPU usage statistics for intermediate Statspack snapshots between the start and end snap IDs.
• IO statistics by function – lists the delta values of the view V$IOSTAT_FUNCTION, which indicates disk I/O characteristics for various read and write type operations (ARCH, Buffer Cache Reads, Data Pump, DBWR, Direct Reads, Direct Writes, LGWR, RMAN, Recovery, Smart Scan, Streams AQ, XDB, and Others).
• Tablespace IO statistics – number of block reads and writes, and average performance by tablespace.
• File IO statistics – number of block reads and writes, and average performance per datafile and temp file, showing the delta values from V$FILESTAT and V$TEMPSTAT.
• File read histogram statistics – indicates the number of single block reads per datafile which completed in a time range (less than 2ms, between 2ms and 3.999ms, between 4ms and 7.999ms, etc), showing the delta values from V$FILE_HISTOGRAM.
• Instance recovery statistics – estimated time to recover from a shutdown abort.
• Memory dynamic components – amount of SGA memory used by components (buffer cache, shared pool, java pool, large pool) and the PGA_AGGREGATE_TARGET at the start and end snap IDs.
• Memory resize operations – indicates dynamic resizing events of objects in the SGA when the SGA_TARGET initialization parameter is set (V$MEMORY_RESIZE_OPS).
• Buffer pool advisory – provides estimated changes in the number of physical reads and read times with smaller and larger memory allocations for the DEFAULT, KEEP, and RECYCLE buffer pools (V$DB_CACHE_ADVICE).
• Buffer pool statistics – delta values from the view V$BUFFER_POOL_STATISTICS, showing the number of consistent gets, physical block reads, physical block writes, buffer busy waits, free buffer waits, and write complete waits by buffer pool (DEFAULT, KEEP, RECYCLE, etc.).
• Buffer wait statistics – delta values from the view V$WAITSTAT, showing by block class (data block, extent map, file header block, free list, segment header, undo block, etc.) the number of buffer busy waits and wait time.
• PGA aggregate target statistics – shows the amount of memory specified for the PGA_AGGREGATE_TARGET as well as the amount of memory actually used.
• PGA aggregate target histogram – number of in-memory optimal memory executions, as well as one pass, and multi-pass temp tablespace executions for various memory size ranges.
• PGA memory advisory – provides estimated changes in the amount of megabytes written to the temp tablespace with smaller and larger allocations for the PGA_AGGREGATE_TARGET (V$PGA_TARGET_ADVICE).
• Process memory summary statistics, and by component – PGA memory allocations for SQL processing, PL/SQL processing, and other activities for the beginning and ending snap IDs.
• Enqueue activity – lists enqueues (such as row lock contention, sequence cache, etc.) which impacted database instance performance in the collection period.
• Undo Segment Statistics – lists the number of transactions, maximum query execution time, and number of undo blocks for each of the intermediate snap IDs between the start and end snap ID.
• Latch activity – get requests, percentage of misses, and wait time for latches, showing delta values from V$LATCH (buffer pool, cache buffers chains, redo allocation, shared pool, etc.).
• Latch sleep breakdown – lists latches which could not be immediately acquired after spinning while waiting for thelatch to become available.
• Latch miss sources – number of sleeps and misses for latches, shows the delta values from V$LATCH_MISSES.
• Mutex sleep – lists sleeps and wait time for mutexes (Cursor Pin – kkslce, Cursor Pin – kksfbc, Cursor Pin – kksSetBindType, Library Cache – kglkhfs1 52, etc.), shows the delta values from V$MUTEX_SLEEP.
• Segments by logical reads – ranks the tables, indexes, and other objects by the number of logical reads experienced in the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 10,000 consistent gets.
• Segments by physical reads – ranks the tables, indexes, and other objects by the number of physical block reads experienced in the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 1,000 physical block reads.
• Segments by row lock waits – ranks the tables, indexes, and other objects by the number of row lock enqueues experienced in the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 100 row lock waits.
• Segments by buffer busy waits – ranks the tables, indexes, and other objects by the number of buffer busy wait experienced for blocks in the segment during the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 100 buffer busy waits.
• Dictionary cache statistics – delta values from V$ROWCACHE, showing statistics for types including dc_awr_control, dc_constraints, dc_database_links, dc_files, dc_free_extents, etc.
• Library cache activity – delta values from V$LIBRARYCACHE, showing summarized BODY, INDEX, OBJECT, SQL AREA, TRIGGER, etc. requests, misses, invalidations, and reloads.
• Shared pool advisory – provides estimated changes in the number of library cache hits and load times with smaller and larger memory allocations for the shared pool (V$SHARED_POOL_ADVICE).
• Cache size changes – lists the default buffer cache size and the shared pool size at each intermediate Statspack snapshot, which indicates the effectiveness of the value specified for the SGA_TARGET and the minimum values specified for the DB_CACHE_SIZE and SHARED_POOL_SIZE.
• SGA target advisory – provides estimated changes in the number of physical reads and DB time with smaller and larger memory allocations for the shared pool (V$SGA_TARGET_ADVICE).
• SGA memory summary – lists the starting and ending memory sizes for memory regions in the SGA including Database Buffers, Fixed Size, Redo Buffers, and Variable Size (V$SGA).
• SGA breakdown difference – lists the starting and ending memory sizes for the first 35 sub-memory regions sorted by pool and then sub-pool in the SGA including shared pool – row cache, shared pool – sql area, and buffer_cache (V$SGASTAT).
• SQL memory statistics – average memory utilization per cursor in the library cache, total number of unique SQL_IDs, and total number of SQL_IDs including child cursors (V$SQLSTATS).
• init.ora parameters – shows all non-default initialization parameters.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: