本节的关键字：sorts (memory) sorts (disk)
autotrace的方法,其中如果出现sorts(disk)有值，说明在磁盘中排序了，情况就要注意了。
Used-Mem statistics_level=all的方法中，如出现类似9118K (0)表示还没交换到磁盘，如果是9118K (1)就表示交换到磁盘
创建测试环境
SQL> set linesize 1000
SQL> set pagesize 2000
SQL> drop table t purge;
SQL> create table t as select * from dba_objects;
案例1
sort(memory) 有值，说明有排序了。
SQL> set autotrace traceonly
SQL> select * from t where object_id > 2 order by object_id;
执行计划
----------------------------------------------------------
Plan hash value: 961378228
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 81694 | 16M| | 3973 (1)| 00:00:48 |
| 1 | SORT ORDER BY | | 81694 | 16M| 19M| 3973 (1)| 00:00:48 |
|* 2 | TABLE ACCESS FULL| T | 81694 | 16M| | 293 (1)| 00:00:04 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1047 consistent gets
0 physical reads
0 redo size
3517144 bytes sent via SQL*Net to client
54051 bytes received via SQL*Net from client
4878 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
73155 rows processed
案例2
用到了排序
SQL> set autotrace off
SQL> alter session set statistics_level = all;
SQL> select * from t where object_id > 2 order by object_id;
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------
SQL_ID 7dv0pnqt14nqf, child number 1
-------------------------------------
select * from t where object_id > 2 order by object_id
Plan hash value: 961378228
-------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 73155 |00:00:00.23 | 1047 | | | |
| 1 | SORT ORDER BY | | 1 | 81694 | 73155 |00:00:00.23 | 1047 | 10M| 1234K| 9118K (0)|
|* 2 | TABLE ACCESS FULL| T | 1 | 81694 | 73155 |00:00:00.03 | 1047 | | | |
-------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement (level=2)
已选择23行。
案例3
在排序列有了索引后，消除排序。
SQL> create index idx_object_id on t(object_id);
SQL> set autotrace traceonly
SQL> select * from t where object_id>2 order by object_id;
执行计划
--------------------------------------------------
Plan hash value: 2041828949
--------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 81694 | 16M| 1303 (1)| 00:00:16 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 81694 | 16M| 1303 (1)| 00:00:16 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 81694 | | 177 (1)| 00:00:03 |
--------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
2 - access("OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
--------------------------------------------------
0 recursive calls
0 db block gets
10953 consistent gets
0 physical reads
0 redo size
3517144 bytes sent via SQL*Net to client
54051 bytes received via SQL*Net from client
4878 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
73155 rows processed
