SQL> conn system/yang as sysdba
已连接。
SQL> show parameter workarea_size_policy;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL> show parameter sort_area_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536
SQL> select count(*) from t;
COUNT(*)
----------
69085
SQL> set autotrace traceonly;
SQL> set timing on;
SQL> select * from t order by object_id;
已选择69085行。
已用时间: 00: 00: 04.98
执行计划
----------------------------------------------------------
Plan hash value: 4247898483
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 69085 | 6814K| 1349 (1)| 00:0
0:17 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 69085 | 6814K| 1349 (1)| 00:0
0:17 |
| 2 | INDEX FULL SCAN | I_T_ID | 69085 | | 159 (1)| 00:0
0:02 |
--------------------------------------------------------------------------------
------
统计信息
----------------------------------------------------------
325 recursive calls
0 db block gets
10528 consistent gets
1015 physical reads
0 redo size
7894083 bytes sent via SQL*Net to client
51071 bytes received via SQL*Net from client
4607 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
69085 rows processed
SQL> alter session set workarea_size_policy=manual;
会话已更改。
已用时间: 00: 00: 00.04
SQL> alter session set sort_area_size =100000000;
会话已更改。
已用时间: 00: 00: 00.00
SQL> select * from t order by object_id;
已选择69085行。
已用时间: 00: 00: 07.93
执行计划
----------------------------------------------------------
Plan hash value: 961378228
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69085 | 6814K| 283 (2)| 00:00:04 |
| 1 | SORT ORDER BY | | 69085 | 6814K| 283 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T | 69085 | 6814K| 280 (1)| 00:00:04 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
11 recursive calls
372 db block gets
1024 consistent gets
4898 physical reads
0 redo size
3542598 bytes sent via SQL*Net to client
51071 bytes received via SQL*Net from client
4607 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
69085 rows processed
SQL> spool off
