oracle實驗記錄 （sort_area_size與 cpu_time）

基於成本優化書上介紹 說 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會佔用長時間.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-608740/，如需轉載，請註明出處，否則將追究法律責任。