关于会话的statistics

 oracle 对于这些statistics 指标是有记录的，它主要分了session 和instance 2个层面。 v$sesstat ，v$sysstat 分别记录了会话级别和instance 级别各个 statistics 指标的 当前累计值。当然，还有个 v$mystat 是表示当前会话的 statistics 值，它是v$sesstat 结果的真子集。

1.  对于各种记录, v$statname 中有他们的信息。

select *  from v$statname where name like '%call%';

STATISTIC# NAME                                                                  CLASS    STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
         6 user calls                                                                1 2882015696
         7 recursive calls                                                           1 2656001462
        11 CPU used when call started                                              128  572264820
       103 commit cleanout failures: callback failure                                8 3086716748
       128 calls to kcmgcs                                                         128 2775936802
       129 calls to kcmgrs                                                         128 4210206824
       130 calls to kcmgas                                                         128 4072914524
       131 calls to get snapshot scn: kcmgss                                        32 3128650623
       193 cleanout - number of ktugct calls                                       128 2577316246
       199 drop segment calls in space pressure                                    128 1500291109
       255 rows fetched via callback                                                64 3299365556
       281 java call heap total size                                                 1 1649812842
       282 java call heap total size max                                             1  580075922
       283 java call heap used size                                                  1  187277223
       284 java call heap used size max                                              1  625960724
       285 java call heap live size                                                  1 2431389341
       286 java call heap live size max                                              1 1298432410
       287 java call heap object count                                               1 3472185722
       288 java call heap object count max                                           1 1862536587
       289 java call heap live object count                                          1  632577394

STATISTIC# NAME                                                                  CLASS    STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
       290 java call heap live object count max                                      1 2964139762
       291 java call heap gc count                                                   1 2284697213
       292 java call heap collected count                                            1 2510746206
       293 java call heap collected bytes                                            1 3585877838

24 rows selected

SQL> select *  from v$statname where name like '%db block%';

STATISTIC# NAME                                                                  CLASS    STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
        47 db block gets                                                             8 1480709069
        48 db block gets from cache                                                  8 4017839461
        49 db block gets direct                                                      8   95128520
        59 db block changes                                                          8  916801489

SQL>


2. 通过  statistics name 的对应id ，找出它的当前累计值。

SQL> select *  from v$statname where name like '%sort%';

STATISTIC# NAME                                                                  CLASS    STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
       341 sorts (memory)                                                           64 2091983730
       342 sorts (disk)                                                             64 2533123502
       343 sorts (rows)                                                             64 3757672740


SQL> select *  from v$mystat where statistic#=341;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       142        341        185

SQL> select *  from v$sesstat where statistic#=341 and sid=142;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       142        341        185



SQL> select *  from v$sySstat where statistic#=341 ;

STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
       341 sorts (memory)                                                           64      33039 2091983730

SQL>