STATSPACK Collection Options Oracle Tips by Burleson Consulting

As we have already mentioned, STATSPACK has two types of collection options, level and threshold. The level parameter controls the type of data collected from Oracle, while the threshold parameter acts as a filter for the collection of SQL statements into the stats$sql_summary table.

There are three snapshot levels used in STATSPACK, and level 5 is the default:

·        Level 0: General Performance Statistics This level 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.

·        Level 5: Add SQL Statements This level includes all level 0 statistics plus SQL statements into the stats$sql_summary table.

·        Level 10: Add Child Latch Statistics The level 10 snapshot includes everything in the level 5 statistics plus the addition of child latches into the stats$latch_children table. You rarely, if ever, need this level of detail, and you should only do a level 10 snapshot when directed by Oracle technical support.

The default for a snapshot is always level 5, but you can use the STATSPACK package to change the default or request a special snapshot at a different level. You can change the default level of a snapshot with the statspack.snap function. In the example here, we remove all SQL collection from all future snapshots by specifying a default level 1 snapshot:

L 3-13

Once set, all future snapshots will be level 0. If you want a single snapshot at a different level without changing the default, you just omit the i_modify_parameter argument. In the next example, we take a single snapshot at level 10, while leaving the default at level 5 for all subsequent snapshots:

L 3-14

The snapshot thresholds only apply to the SQL statements that are captured in the stats$sql_summary table. The stats$sql_summary table can easily become the largest tables in the STATSPACK schema because each snapshot might collect several hundred rows, one for each SQL statement that was in the library cache at the time of the snapshot.

The thresholds are stored in the stats$statspack_parameter table. Let's take
a look at each threshold:

·        executions_th This is the number of executions of the SQL statement (default is 100).

·        disk_reads_th This is the number of disk reads performed by the SQL statement (default is 1,000).

·        parse_calls_th This is the number of parse calls performed by the SQL statement (default is 1,000).

·        buffer_gets_th This is the number of buffer gets performed by the SQL statement (default is 10,000).

In Oracle9i there are new threshold parameters, but they have not yet been activated in STATSPACK.

·        p_def_num_sql – This is a threshold for the number of SQL statements.

·        p_def_executions_th – This is a threshold for the number of SQL executions.

·        p_def_parse_calls_th  – This is a threshold for the number of SQL parse calls.

·        p_def_disk_reads_th    – This is a threshold for the number of disk reads.

·        p_def_buffer_gets_th  – This is a threshold for the number of SQL buffer gets.

·        p_def_sharable_mem_th  – This is a threshold for the SQL sharable memory.

·        p_def_version_count_th   – This is a threshold for the SQL child cursors.

It is important to understand that each SQL statement will be evaluated against all of these thresholds, and the SQL statement will be included in the stats$sql_summary table if any one of the thresholds is exceeded. In other words, these thresholds are not AND'ed together as we might expect, but they are OR'ed together such that any value exceeding any of the thresholds will cause a row to be populated.

The main purpose of these thresholds is to control the rapid growth of the stats$sql_summary table that will occur when a highly active database has hundreds of SQL statements in the library cache. In the next chapter, we will be discussing clean-up strategies for removing unwanted snapshots from the database.

You can change the threshold defaults by calling the statspack.modify_statspack_ parameter function. In the example here, we change the default threshold for buffer_gets and disk_reads to 100,000. In all subsequent snapshots, we will only see SQL that exceeds 100,000 buffer gets or disk reads.

L 3-15

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

---

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.