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

增大sort_area_size 後排序時間增長

相關文章

增大sort_area_size 後 排序時間增長

相關文章

增大sort_area_size 後排序時間增長