ITLife365 生活 互动学习空间 - 互动学习空间

v$statname是对统计信息的说明，STATISTIC#相当于编号，NAME就是统计量的名称。
sys@OCP10G> desc v$statname;
名称                                                  是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
STATISTIC#                                                     NUMBER
NAME                                                           VARCHAR2(64)
CLASS                                                          NUMBER
STAT_ID                                                        NUMBER

sys@OCP10G> select * from v$statname;
STATISTIC# NAME                                                                  CLASS    STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
         0 logons cumulative                                                         1       2666645286
         1 logons current                                                              1       3080465522
         2 opened cursors cumulative                                          1     85052502
         3 opened cursors current                                               1 2301954928
    ....
v$mystat字面来说就是当前用户的各种统计信息，sid就是session的id(也就是当前用户),STATISTIC#就是统计量的编号(用来唯一确定统计量的名称)，value是统计量的值。

SQL> show user
USER 为 "SYS"
SQL> select sid,username from v$session where username ='SYS'
  2  ;

       SID USERNAME
---------- ------------------------------------------------------------
       146 SYS

SQL> DESC V$MYSTAT;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 SID                                                NUMBER
 STATISTIC#                                         NUMBER
 VALUE                                              NUMBER

SQL> SELECT * FROM V$Mystat where rownum=1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       146          0          1

SQL>
小结：
v$statname视图获取系统的统计量的说明
v$mystat视图获取当前用户的在v$statname中的每个统计量的值
两个视图按STATISTIC#连接，可以得到当前用户的sid,统计量的编号,统计量的名称,和统计值.

select b.sid,a.STATISTIC#,a.name,b.value
from v$statname a,v$mystat b
where a.STATISTIC#=b.STATISTIC#;

       SID STATISTIC# NAME                                                                  VALUE
---------- ---------- ---------------------------------------------------------------- ----------
       138          0 logons cumulative                                                         1
       138          1 logons current                                                             1
       138          2 opened cursors cumulative                                           56
       138          3 opened cursors current                                                 1
        ...
       138        314 OTC commit optimization hits                                       0
       138        315 OTC commit optimization failure - setup                       0

http://hi.baidu.com/johnson_lff/blog/item/504e155c3f995746faf2c06f.html