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

来源：互联网发布：淘宝双十一客服招聘编辑：程序博客网时间：2024/05/17 09:18

查看一个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       9647SQL> 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 processedSQL> 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 1236385760SQL> select * from v$mystat where STATISTIC# = 169;       SID STATISTIC#      VALUE---------- ---------- ----------         1        169     139692SQL> 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   74304240SQL> select (74304240-139692)/1024/1024 MB from dual;        MB----------70.7288246