oracle 排序記憶體

我在http://zhidao.baidu.com/question/123262452.html?fr=msg　提的問題，整理到這裡　非常感謝 zjwssg
的回答

 

排序記憶體涉及到PGA。
什麼時候使用自動PGA記憶體管理？什麼時候使用手動PGA記憶體管理？

  白天系統正常運行時適合使用自動PGA記憶體管理，讓Oracle根據當前負載自動管理、分配PGA記憶體。

  夜裡使用者數少、進行維護的時候可以設定當前會話使用手動PGA記憶體管理，讓當前的維護操作獲得儘可能多的記憶體，加快執行速度。

  如：伺服器平時運行在自動PGA記憶體管理員模式下，夜裡有個任務要大表進行排序串連後更新，就可以在該操作session中臨時更改為手動PGA記憶體管理，然後分配大的SORT_AREA_SIZE和HASH_AREA_SIZE（50%甚至80%記憶體，要確保無其他使用者使用），這樣能大大加快系統運行速度，又不影響白天高峰期對系統造成的影響。

 操作命令

 
  會話級更改

  ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；

  ALTER SESSION SET SORT_AREA_SIZE = 65536；

  ALTER SESSION SET HASH_AREA_SIZE = 65536；

 學以致用

  1，排序區：

  pga_aggregate_target為100MB，單個查詢能用到5%也就是5MB時排序所需時間

  SQL> create table sorttable as select * from all_objects;

  表已建立。

  SQL> insert into sorttable (select * from sorttable);

  已建立49735行。

  SQL> insert into sorttable (select * from sorttable);

  已建立99470行。

  SQL> set timing on;

SQL> set autotrace traceonly;

  SQL> select * from sorttable order by object_id;

  已選擇198940行。

  經過時間:  00: 00: 50.49

  Session級修改排序區為30mb所需時間

  SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

  會話已更改。

  經過時間:  00: 00: 00.02

  SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;

  會話已更改。

  經過時間:  00: 00: 00.01

  SQL> select * from sorttable order by object_id;

  已選擇198940行。

  經過時間:  00: 00: 10.76

  可以看到所需時間從50.49秒減少到10.31秒，速度提升很明顯。

  2，散列區：

  pga_aggregate_target為100MB，單個查詢能用到5%也就是5MB時表串連所需時間

  SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;

  已選擇49735行。

  經過時間:  00: 00: 40.50

  Session級修改散列區為30mb所需時間

  SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

  會話已更改。

  經過時間:  00: 00: 00.01

  SQL> ALTER SESSION SET HASH_AREA_SIZE = 30000000;

  會話已更改。

  經過時間:  00: 00: 00.01

  SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;

  已選擇49735行。

  經過時間:  00: 00: 04.47

  所需時間由40.50秒提升到4.47秒，效果同樣很明顯。

  備忘：以上實驗皆執行全表掃描保證相關表讀入緩衝區中，避免因資料沒讀入緩衝造成誤差。

---另一個網址的：
總結一下：
1。 使用並行參數，8個CPU, 可以用parallel 6 ,最多佔用6個CPU, 正常情況下沒問題（前提是其他應用沒有佔超過2個CPU的資源）
2。 nologging, 絕對應該使用，會使速度大幅上升。（減少大量redo log)
3。 PGA, 普通的auto pga最大才100M, 顯然沒有達到最好效能，應該使用manual pga
alter session set workarea_size_policy=manual;
alter session set  hash_area_size=100000; -- hash_area_size 預設情況下會自動根據sort_area_size*2來調，導致sort_area_size不能超過1G, 手動設了就沒問題了。
alter session set sort_area_size=2000000000; -- 在系統可用記憶體足夠的情況下，最大可以到2G
4。 設定高一點的db_file_multiblock_read_count 也有利於提高I/O效能。保證足夠大的db_cache_size可以避免free_buffer_wait的出現
5。 可以通過v$session_longops 來監視進度