Statspack 10-adjust the statspack collection threshold (post)

Statspack has two types of collection options:

Level: controls the types of collected data.

Threshold (threshold): set the threshold value of collected data.

 

1. level)

Statspack has three snapshot levels. The default value is 5.

A. Level 0: general performance statistics. Including wait events, system events, system statistics, rollback segment statistics, row cache, SGA, session, lock, buffer pool statistics, and so on.

B. Level 5: Add SQL statements. In addition to all content of level0, it also includes SQL statement collection, and the collection results are recorded in stats $ SQL _summary.

C. Level 10: Add sub-lock statistics. Includes all content of level5. The attached sub-locks are stored in stats $ lathc_children. Exercise caution when using this level. We recommend that you use this level under the guidance of Oracle Support.

You can use the statspack package to modify the default level settings.

SQL> execute statspack. Snap (I _snap_level => 0, I _modify_parameter => 'true ');

 

With this setting, the collection level will be 0 in the future.

If you only want to change the collection level this time, you can ignore the I _modify_parameter parameter.

 

SQL> execute statspack. Snap (I _snap_level => 10 );

 

2. snapshot threshold

 

The Snapshot threshold applies only to the SQL statements obtained from the stats $ SQL _summary table.

Because each snapshot collects a lot of data, and each row represents an SQL statement in the database when the snapshot is obtained, stats $ SQL _summary will soon become the largest table in Statspack.

 

The threshold value is stored in the stats $ statspack_parameter table. Let's take a look at the various thresholds:

A. executions_th this is the number of SQL statements executed (the default value is 100)

B. disk_reads_tn this is the number of disk reads executed by the SQL statement (default value: 1000)

C. parse_calls_th this is the number of resolution calls executed by SQL statements (the default value is 1000)

D. buffer_gets_th this is the number of buffers obtained by the SQL statement execution (the default value is 10000)

 

A record is generated when any of the above thresholds is exceeded.

By calling the statspack. modify_statspack_parameter function, we can change the default value of the threshold.

For example:

SQL> execute statspack. modify_statspack_parameter (I _buffer_gets_th = & gt; 100000, I _disk_reads_th = & gt; 100000;

 

From: http://www.eygle.com/statspack/statspack10.htm