增大sort_area_size 後 排序時間增長

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