v$statname、v$mystat说明

两个简单的动态视图：v$statname、v$mystat的一点说明

2008-04-09 21:15

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     ....    314 OTC commit optimization hits                                       1       28 4064864054      315 OTC commit optimization failure - setup                       1       28 3633344886 v$mystat字面来说就是当前用户的各种统计信息，sid就是session的id(也就是当前用户),STATISTIC#就是统计量的编号(用来唯一确定统计量 的名称)，value是统计量的值。 sys@OCP10G> show user USER 为 "SYS" sys@OCP10G> select sid,username from v$session where username='SYS';        SID USERNAME ---------- -----------------        138 SYS sys@OCP10G> desc v$mystat; 名称                                                  是否为空? 类型 ----------------------------------------------------- -------- ------------------------------------ SID                                                             NUMBER STATISTIC#                                                     NUMBER VALUE                                                           NUMBER sys@OCP10G> select * from v$mystat where rownum<10;        SID STATISTIC#      VALUE ---------- ---------- ----------        138          0           1        138          1         1        138          2       54        138          3         1         ...        138        314          0        138        315          0 小结： 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