Skip to Main Content
Oracle in a Nutshell
book

Oracle in a Nutshell

by Rick Greenwald, David C. Kreines
December 2002
Intermediate to advanced content levelIntermediate to advanced
928 pages
85h 29m
English
O'Reilly Media, Inc.
Content preview from Oracle in a Nutshell

Statspack levels and thresholds

You can adjust the amount of information collected by Statspack by specifying a level or adjusting the threshold for data collection. A SQL statement must surpass the specified threshold for a statistic before it is captured by Statspack.

Statspack supports the following levels:

  • 0or greater collects general statistics such as wait and system statistics, rollback segment data, row cache and SGA information, system, background, and session events, as well as lock, buffer pool, and parent latch statistics

  • 5 or greater also includes performance data on SQL statements that exceed the thresholds specified

  • 6 or greater also includes information about whether the execution plan has changed.

  • 10 or greater also includes information about child and parent latches

The thresholds that can be specified for a statement, with their defaults, are:

I_EXECUTIONS_TH

Number of times executed (100)

I_DISK_READS_TH

Number of disk reads (1000)

I_PARSE_CALLS_TH

Number of parse calls (1000)

I_BUFFER_GETS_TH

Number of buffer gets (10,000)

I_SHARABLE_MEM_TH

Amount of sharable memory (1,048,576)

I_VERSION_COUNT_TH

Number of versions (20)

To change a parameter for an individual session, include the threshold or level number identifier followed by => and the new value as a parameter to the SNAP procedure. To set the level, you use the parameter name I_SNAP_LEVEL. You can also include I_UCOMMENT to add a user comment and I_SESSION_ID for the ID of the session to use to gather session-specific ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Internals

Oracle Internals

Donald K. Burleson
Oracle PL/SQL

Oracle PL/SQL

Lewis Cunningham
Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Rick Greenwald, Robert Stackowiak, Jonathan Stern

Publisher Resources

ISBN: 0596003366Errata Page