基于成本优化书上介绍 说 workarea manual时候
sort_area_size越大 其cpU_time越长 占用的CPU越多,理由总结为sort机制为2叉树 ,树高度为log2(行数)，内存越大，放的行数越多，高度越高，cpu遍历起来越费时间(有可能现在最近版本已经用heap机制sort了)
特此验证下

cpu_time/1000000 = (s)单位

分别实验 sort_area_size  1M,5M,10M, 30M(optimial sort)
workarea_size_policy                 string      MANUAL
sort_area_size                       integer     1048576

分别用2个session一个执行,一个查SQL语句的cpu_time

SQL> set autotrace traceonly statistics;
SQL> select sortcode from t1 order by sortcode;

1048576 rows selected.

Elapsed: 00:00:14.17

另一个session查
SQL> select cpu_time,elapsed_time,sql_id from v$sqlarea where sql_text='select s
ortcode from t1 order by sortcode';

  CPU_TIME ELAPSED_TIME SQL_ID
---------- ------------ -------------
   5351684      6713273 fasuzt0qcxhcx
SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
SQL> alter session set sort_area_size = 5242880;
SQL> set autotrace traceonly statistics;
SQL> select sortcode from t1 order by sortcode;

1048576 rows selected.

Elapsed: 00:00:12.86

SQL> select cpu_time,elapsed_time,sql_id from v$sqlarea where sql_text='select s
ortcode from t1 order by sortcode';

  CPU_TIME ELAPSED_TIME SQL_ID
---------- ------------ -------------
   3746109      5152772 fasuzt0qcxhcx

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> alter session set sort_area_size =10485760 ;

Session altered.

Elapsed: 00:00:00.00
SQL> set autotrace traceonly statistics;
SQL> select sortcode from t1 order by sortcode;
Elapsed: 00:00:12.89

SQL> select cpu_time,elapsed_time,sql_id from v$sqlarea where sql_text='select s
ortcode from t1 order by sortcode';

  CPU_TIME ELAPSED_TIME SQL_ID
---------- ------------ -------------
   7947986     10131213 fasuzt0qcxhcx

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> set autotrace off;
SQL> alter session set sort_area_size = 31457280;

Session altered.

Elapsed: 00:00:00.00
SQL> set autotrace traceonly statistics;
SQL> select sortcode from t1 order by sortcode;
Elapsed: 00:00:12.96
 2  sorts (memory)
 0  sorts (disk)~~~~~~~~~~~~~~~已经是最优sort了

SQL> select cpu_time,elapsed_time,sql_id from v$sqlarea where sql_text='select s
ortcode from t1 order by sortcode';

  CPU_TIME ELAPSED_TIME SQL_ID
---------- ------------ -------------
  12538560     15271482 fasuzt0qcxhcx

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> alter session set sort_area_size = 5242880;

Session altered.

Elapsed: 00:00:00.00
SQL> set autotrace traceonly statistics;
SQL> select sortcode from t1 order by sortcode;

1048576 rows selected.

Elapsed: 00:00:11.81

SQL> select cpu_time,elapsed_time,sql_id from v$sqlarea where sql_text='select s
ortcode from t1 order by sortcode';

  CPU_TIME ELAPSED_TIME SQL_ID
---------- ------------ -------------
   3753378      4312063 fasuzt0qcxhcx~~~~~~~~~~~~~~~~~~~~~~~~~~~~~为 5M时候 cpu time 最小

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> set autotrace off;
SQL> alter session set sort_area_size =10485760 ;

Session altered.

Elapsed: 00:00:00.00
SQL> set autotrace traceonly statistics;
SQL> select sortcode from t1 order by sortcode;

SQL> select cpu_time,elapsed_time,sql_id from v$sqlarea where sql_text='select s
ortcode from t1 order by sortcode';

  CPU_TIME ELAPSED_TIME SQL_ID
---------- ------------ -------------
   7935026      9117286 fasuzt0qcxhcx

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

很奇怪,经过反复测试~有时候sort_area_size变大cpu_time会变大 但有时候也会变小,总体来说 还是基本随着sort_area_size变大而变大,尤其在最优sort时明显

也许测试方法不正确,也许版本有BUG,也许oracle改变了sort机制不用2叉数了~那样就不存在高度了.结论:在R2之前 肯定是用2叉数,切cpu_time会占用长时间.