oracle筆記整理13——效能調優之SQL優化

1) 3種遊標迴圈效能

a) 單條處理

declare
        cursor 遊標 is
begin
open 遊標;
LOOP  
FETCH 遊標 INTO 變數;
EXIT WHEN  條件;
END LOOP;
CLOSE 遊標;
end;

逐條處理，效率之低，不敢想象。

b) 批量處理

declare
        cursor 遊標 is
begin
open 遊標;
FETCH 遊標 BULK COLLECT INTO 變數;
EXIT WHEN  條件;
CLOSE 遊標;
end;

一次FETCH出來.一次處理所有行，效率最高。

declare
  cursor c is
    select *
      from table1;
  type c_type is table of c%rowtype;
  v_type c_type;
begin
  open c;
  loop
    fetch c bulk collect
      into v_type limit 100000;
    exit when c%notfound;
    <<inner1>>
    begin
    forall i in 1 .. v_type.count
      insert /*+append*/
      into table2
      values v_type
        (i);
    commit;
    EXCEPTION    ----出錯後忽略繼續執行
      WHEN OTHERS THEN
        null;
    end inner1;
  end loop;
  close c;
  commit;
end;
--刪除
delete from table2 where read_id = v_type(i);

c) 遊標for迴圈

for c in (sql語句) loop
……
end loop;

效率非常接近於批量處理，這種隱式迴圈的遊標語句,其實也是一種批量處理的過程,它每次讀取了近100行資料到快取.

d) 結論：在不使用集合變數(bulk collect into)的情況下，選擇隱式遊標，既簡潔又高效。

http://www.2cto.com/database/201307/224636.html

2) 多用commit

在確保完整性的情況下多用commit提交，可以釋放事務所佔的資源，包括redo快取、undo表空間、鎖、管理這些資源的內部花費。
大表進行insert，update，delete時可以分批進行，小批量提交。

3) exists與in

a) 示例：select * from A where exists (select 1 from B where A.id = B.id)

b) 原理：通過使用EXISTS，Oracle會首先檢查主查詢，然後執行子查詢直到它找到第一個匹配項；Oracle在執行IN時，首先執行子查詢，並將獲得的結果存放臨時表中，再執行主查詢。

c) 在選擇了合理的驅動表情況下，基於CBO時，A、B兩表資料量差別越大，exists的效能越好。在RBO/11g時代，exists與in在效能上是一樣的，經過cost最低轉換之後都採用了hash join的連線方式，影響效能的關鍵因素在於能否選擇合適的驅動表。

d) 結論：在RBO/11g中還是儘量採用exists，RBO是基於統計資訊的，一旦統計資訊沒有及時更新，就會出現效能問題。

4) not in和not exists

a) 在RBO/11g中，都採用hash join方式，子查詢的資料量越大，not exists的效率越高，not exists子查詢中可以使用索引，但是 not in的子查詢中無法使用索引，在都不使用索引的情況下，not in與not exists無效能差別。

b) 空值null的影響：當外層表存在空值時，not in直接忽略此空值，not exists不會受影響，正常資料。當內層表為空時，not in導致返回的最終查詢結果為空，not exists則不受影響。

c) 結論：不管是出於效能方面，還是防止null值干擾輸出結果方面，建議儘量使用not exists

5) nologging

只有在如下情況下，nologging才會生效，普通的DML語句無效。

a)  direct load (SQL*Loader)(load檔案、load命令形式)
b)  direct load INSERT (using APPEND hint)(模擬直接載入)
c)  create table XXX nologging as select * from ……;
d)  insert /*+ APPEND */ into xxx nologging ……；
e)  create index …… nologging;
f)  create table name as select與insert into在nologging狀態下效能相當，但是在正常情況下，create由於其為DDL操作，日誌量很少，故效能最好，因此可能的情況下，儘量選擇create。
g)  一般情況nologging、append、parallel同時使用會大幅提高效能。

6) 減少遠端訪問及次數

7) 先刪索引，再插資料，最後重建索引(建並行索引，之後再alter parallel為1)。