查看一个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