René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Statspack in Oracle

Oracle's Statspack replaces utlbstat.sql and utlestat.sql.

Installation

As sysdba: 
@?/rdbms/admin/spcreate
This will create a user named perfstat and ask for its password. Additionally, it will ask for a default tablespace and a default temporary tablespace.
If there was an error while installing StatsPack (for example a typo entering the default tablespace), you can use @?/rdbms/admin/spdrop to drop StatsPack.

Taking a snapshot

begin
  statspack.snap;
end;
/

Altering the default statistic level

When taking a snapshot, a statistic level can be given that determines the amount of statistics gathered. The higher the statistic level, the more data is collected.
The following descriptions were extracted using select * from perfstat.stats$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
Additionally, level 5 and level 10 captures sql statement that exceed any of the following adjustable thresholds:
  • The number of executions of the SQL statement (adjustable with the i_executions_th parameter)
  • The number of disk reads the SQL statement performs (adjustable with the i_disk_reads_th parameter)
  • The number of parse calls the SQL statement performs (adjustable with the i_parse_calls_th parameter)
  • The number of buffer gets the SQL statement performs (adjustable with the i_buffer_gets_th parameter)
Defaults are stored in stats$statspack_parameters and can be changed with statspack.modify_statspack_parameter.
The statistic level is passed with the i_snap_level parameter: 
begin
  statspack.snap(i_snap_level=> 10);
end;
/

Collectiong statistics for a specific session

It is also possible to specify a session (more accuratly: a session id) for which statistics have to be collected: 
begin
  statspack.snap(i_session_id=>49);
end;
/

Printing a report

@?/rdbms/admin/spreport

Statistics to watch out

  • Memory Usage
    Ideally, this should be in the 70% range; if it is lower, memory is wasted, if it is higher, there is a congestion on memory and Oracle needs to age out components, resulting in a hard parse when the component is needed again.

Statspack and Oracle 10g

Statspack is getting a little, dare I say, oldfashioned in Oracle 10g. Its functionality is built into the database itself and can be visualized with the 10g Enterprise Manager (Em).