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
rem     Script:        my_stat.sql
rem     Author:        J.P.Lewis
rem     Dated:         12-Sep-1999
rem     Purpose:       Naming the 'per-session' statistics.
rem
rem     Use:
rem     ----
rem     This script should be run as SYS
 
create or replace view v$my_stats
as
select
        sn.name,
        ms.value
from
        v$mystat       ms,
        v$statname     sn
where
        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