oracle sql 排序優化

概念：    
    伺服器首先在sort_area_size指定大小的記憶體區域裡排序，如果所需的空間超過sort_area_size，排序會在臨時表空間裡進行。在專用伺服器模式下，排序空間在PGA中，在共享伺服器模式下，排序空間在UGA中。如果沒有建立large pool，UGA處於shared pool中，如果建立了large pool，UGA就處於large pool中，而PGA不在sga中，它是與每個程式對應單獨存在的。

     PGA：program global area,為單個程式（伺服器程式或後臺程式）儲存資料和控制資訊的記憶體區域。PGA與程式一一對應，且只能被起對應的程式讀寫，PGA在使用者登入資料庫建立會話的時候建立。

    有關排序空間自動管理的兩個引數：
    Pga_aggregate_target: 10M-4000G，等於分配給oracle instance的所有記憶體減去SGA後的大小。 
    Workarea_size_policy: auto/manual，只有Pga_aggregate_target已定義時才能設定為auto。

1.什麼導致排序：
  order by
  group by
  select distinct
  create index
  union/minus
 anlyze
  優化器呼叫排序合併連線
2． 排序在記憶體還是在磁碟中進行？
在記憶體執行的排序速度要比在磁碟執行的排序速度快14000倍。如果是專用連線，排序記憶體根據INIT.ORA的sort_area_size進行分配，如果是共享伺服器連線，排序記憶體根據large_pool_size進行分配。sort_area_size的增大可以減少磁碟排序，但是過大將使ORACLE效能降低，因為所用的連線回話都會分配到一個sort_area_size大小的記憶體，所以，為了提高有限的查詢速度，可能會浪費大量的記憶體。增加sort_multiblock_read_count的值使每次讀取更多的內容，減少執行次數，提高效能。
3.怎麼設定sort_area_size:每隔一段時間增加sort_area_size，並監控記憶體排序和磁碟排序數量。當sort_area_size的值的增加不在導致磁碟排序減少時，就合適了。
3.1、診斷和措施
   col name format a20
    Select * from v$sysstat where name like '%sort%';
    Sort(disk):要求Io去臨時表空間的排序數目
    Sort(memory)：完全在memory中完成的排序數目
    Sort(rows)：被排序的行數合計

    Sort（disk）/ Sort（memory）<5%,如果超過5％，增加sort_area_size的值。
    SELECT disk.Value disk,mem.Value mem,(disk.Value/mem.Value)*100 ratio FROM v$sysstat disk,v$sysstat mem WHERE mem.NAME='sorts (memory)' AND disk.NAME='sorts (disk)'; 

3.2、監控臨時表空間的使用情況及其配置
    Select tablespace_name,current_users,total_extents,used_extents,extent_hits,max_used_blocks,max_sort_blocks 

FROM v$sort_segment ;

    Column Description  [Page]
    CURRENT_USERS Number of active users 
    TOTAL_EXTENTS Total number of extents 
    USED_EXTENTS Extents currently allocated to sorts 
    EXTENT_HITS Number of times an unused extent was found in the pool 
    MAX_USED_BLOCKS Maximum number of used blocks 
    MAX_SORT_BLOCKS Maximum number of blocks used by an individual sort 

    臨時表空間的配置：
    A、initial/next設定為sort_area_size的整數倍，允許額外的一個block作為segment的header
    B、pctincrease=0
    C、基於不同的排序需要建立多個臨時表空間
    D、將臨時表空間檔案分散到多個磁碟上

4.sort_area_retain_size：決定磁碟排序完後保留記憶體的數量。
5.sort_multiblock_read_count，db_file_multiblock_read_count。
6。新增索引避免排序

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