匯入資料使用引數sort_area_size加快索引建立

匯入資料使用引數sort_area_size加快索引建立

 

sort_area_size是當查詢需要排序的時候，資料庫會話將使用這部分記憶體進行排序，當記憶體大小不足的時候，使用臨時表空間進行磁碟排序。由於磁碟排序效率和記憶體排序效率相差好幾個數量級，所以這個引數的設定很重要。當出現大量排序時的磁碟I/O操作時，可以考慮增加sort_area_size的值。

sort_area_size是Oracle用於一次排序所需的最大記憶體數，在排序結束但是結果列返回之前，Oracle會釋放sort_area_size大小的記憶體，但是會保留sort_area_retained_size大小的記憶體，知道最後一行結果列返回以後，才釋放所有的記憶體。

 

會導致排序的操作有

Select DISTINCT

MINUS

INTERSECT

UNION

min()、max()、count()

建立索引等；

 

Dba要監控記憶體和硬碟的排序比率，最好使它小於 .10

SQL> Select name, value FROM v$sysstat Where name IN ('sorts (memory)','sorts (disk)');

 

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

sorts (memory)                                                         3774

sorts (disk)                                                              0

 

 

要使用sort_area_size引數還必須要考慮到其他的兩個引數：

Pga_aggregate_target

workarea_size_policy

 

PGA_AGGREGATE_TARGET是9i引入的，引數用於控制PGA的總體期望目標: 不過在Oracle9i中，PGA_AGGREGATE_TARGET引數僅對專用伺服器模式下（Dedicated Server）的專屬連線有效，對共享伺服器（Shared Server）連線無效；從Oracle10g開始PGA_AGGREGATE_TARGET對專用伺服器連線和共享伺服器連線同時生效。

 

workarea_size_policy的有兩種，auto和manual。這個引數的生效也和資料庫的版本以及伺服器連線種類有關。在9i版本中，共享服務模式下無論workarea_size_policy設定為何種值，sort_area_size都生效，而在專用伺服器模式下，只有workarea_size_policy設定為manual時，sort_area_size才生效。10g版本中這個引數的設定和9i中專用伺服器的方式一致。

 

靈活運用這三個引數在集中進行排序操作時可以有提速的作用。下面一段程式碼就是使用system使用者進行imp操作前對system使用者建立logon的trigger，使用者登入後會分配更大的記憶體排序區，以提高索引建立的速度。

 

CREATE OR REPLACE TRIGGER system.logon_system_tr

   after logon ON system.SCHEMA

   BEGIN

  Execute immediate 'alter session set workarea_size_policy= manual';

  Execute immediate 'alter session set sort_area_size= 800000000';

END;

/

 

經過測試，這種方法雖然可以提高索引建立的速度，但整體資料遷入速度不如只導資料後開並行重建索引，如果開並行重建索引時也使用引數sort_area_size的話，在io允許的情況下速度上應該會更快，只是需要整理建立索引的指令碼，比較麻煩。

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