JL Computer Consultancy

My own statistics

September 1999

Developers will often want to get some idea of the system statistics due to their activity, and for a long time the standard method for doing this was to look at the dynamic perfomance view v$sesstat - session statistics, joining it to the view v$statname - statistics names.

The v$sesstat view, however, is super-imposed on x$ksusesta, a rather large object in the SGA which holds statistics about ALL user sessions, both active and potential, so the overhead of using this object too casually can be quite significant.

Since Oracle 7.3, there has been a more efficient object - x$ksumysta, which is part of the PGA, and contains only the statistics for the current user session. This object has remarkably low visibility in Oracle, even to the extent that the autotrace facility uses v$sesstat rather than v$mystat.

As a minor convenience to developers, therefore, I tend to create a view to make v$mystat more visible, and more easily usable, with the following script which has to be run under the SYS account.

Back to Main Index of Topics

rem     Script:        my_stat.sql
rem     Author:        J.P.Lewis
rem     Dated:         12-Sep-1999
rem     Purpose:       Naming the 'per-session' statistics.
rem     Use:
rem     ----
rem     This script should be run as SYS
create or replace view v$my_stats
        v$mystat       ms,
        v$statname     sn
        ms.statistic# = sn.statistic#
drop public synonym v$my_stats;
create public synonym v$my_stats for v$my_stats;
grant select on v$my_stats to public;

Back to Main Index of Topics