|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Statspack in Oracle
Oracle's Statspack replaces utlbstat.sql and utlestat.sql.
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:
Additionally, level 5 and level 10 captures sql statement that exceed any of the following adjustable thresholds:
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
Statistics to watch out
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).