28 | 03 | 2024
Latest Articles
Popular Articles

Performance Tuning

Statspack at session level

User Rating:  / 0
PoorBest 

Session level Statspack

 

I still use statspack since it is free.
Whereas interpretation of wait events, hit ratio' s, troubleshooting expensive sql, sql tuning can become complicated and require experience, taking some statspack snapshots and some statspack reports is as easy as the installation.
Not that many people know statspack can easily be used at the session level. I' ll show it here.

 

 

If I wanna have a reporting for session with session id 9 ( v$session.sid ) I can  proceed in this way

SQL> exec perfstat.statspack.snap(i_snap_level=>7,i_session_id=>9);

after some time - the dba is sometimes allowed to drink a coffee - I take another snapshot of the performance statistics

SQL> exec perfstat.statspack.snap(i_snap_level=>7,i_session_id=>9);

 

Report generation is done is identical as for statspack at the instance level