v$mystat——是v$sesstat的一个子集，返回当前session的统计项！

查看一个sql执行过程中产生了多少次排序：

SQL> select * from v$mystat where STATISTIC# in(select STATISTIC# from v$statname where name like '%sort%');

       SID STATISTIC#      VALUE
---------- ---------- ----------
         1        565       1657
         1        566          0
         1        567       9647

SQL> set autotrace traceonly;
SQL> select * from t order by OWNER,OBJECT_NAME;

已选择71959行。


执行计划
----------------------------------------------------------
Plan hash value: 961378228

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 71959 |  7027K|       |  1922   (1)| 00:00:24 |
|   1 |  SORT ORDER BY     |      | 71959 |  7027K|  9608K|  1922   (1)| 00:00:24 |
|   2 |   TABLE ACCESS FULL| T    | 71959 |  7027K|       |   287   (1)| 00:00:04 |
-----------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1030  consistent gets
       1011  physical reads
          0  redo size
    4130801  bytes sent via SQL*Net to client
      53182  bytes received via SQL*Net from client
       4799  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      71959  rows processed

SQL> set autotrace off;
SQL> select * from v$mystat where STATISTIC# in(select STATISTIC# from v$statname where name like '%sort%');

       SID STATISTIC#      VALUE
---------- ---------- ----------
         1        565       1659
         1        566          0
         1        567      81609

可以看见这个sql总共做了81609 - 9647 = 71962次排序。

查看insert操作产生的redo log size：

SQL> select * from v$statname where name like 'redo size';

STATISTIC# NAME                                CLASS    STAT_ID
---------- ------------------------------ ---------- ----------
       169 redo size                               2 1236385760

SQL> select * from v$mystat where STATISTIC# = 169;

       SID STATISTIC#      VALUE
---------- ---------- ----------
         1        169     139692

SQL> insert into t select * from dba_objects;

已创建71963行。

SQL> /

已创建71963行。

SQL> commit;

提交完成。

SQL> select * from v$mystat where STATISTIC# = 169;

       SID STATISTIC#      VALUE
---------- ---------- ----------
         1        169   74304240

SQL> select (74304240-139692)/1024/1024 MB from dual;

        MB
----------
70.7288246