Use Statspack for snapping stats in logon/logoff triggers

Just posted a comment on Arup Nanda's blog



Hi Arup,

Wouldn't it be easier to just use Statspack for the collection? The post-logon trigger would simply call
statspack.snap(
i_session_id=>sys_context('USERENV','SID'),
i_ucomment=>'START or better SID', i_snap_level=>0)
instead of the INSERT-SELECT.
The pre-logoff trigger would make the same call with a different comment value to enable matching of snapshots made by the same session.

Advantages I see over your approach:
1. Statspack is supplied by and supported by Oracle which makes it easier for the production DBA or consultant to convince management to install it if not already installed.
2. Amount of data captured is easily and extensively configurable (snapshot levels, thresholds)
3. Reporting tools are available from Oracle (spreport.sql, sprepsql.sql) and many third parties, since the schema is well-known.
4. It is easy to manually take intermediate snapshots while a session is running to help concentrating on parts of the app processing.

Drawbacks I see:
1. Increased disk space usage as Statspack captures much more data even at level 0.
2. Increased resource usage, especially for applications with short-lived sessions or prone to cause logon storms.
The second drawback is very significant, but when used sparingly, this method provides far greater flexibility.

Cheers!
Flado