Sql最佳化(十) 程式的可擴充套件性—sequence上的競爭

提高程式可擴充套件性一定要減少各類資源衝突

某批處理程式中有一個步驟，單程式執行10分鐘，10個程式時執行超過3小時，即使使用parallel引數也無法提高速讀，是cpu資源不足嗎？語句如下：

[@more@]

create table test_table as
select
to_number(substr(bpid,1, 11)) ACCOUNT_NO,
bill_ref_no,
statement_date,
seq_bid_row.nextval BILL_INVOICE_ROW,
prd_inst_id SUBSCR_NO,
main_devno USER_ID,
…
from BILL_TABLE; （千萬記錄級別）

透過監控發現系統資源並沒有非常緊張，而是產生大量row cache lock的等待事件，進一步發現這個等待事件和sequence有關。因為取sequence值會訪問資料字典，當大批次取sequence值時會引起資源競爭。例如上面例子中bill_table是一張大表，因此會取大量sequence。如果是多程式就更嚴重，如果是RAC會'更更'嚴重…上面批處理程式就滿足了大批次、多程式的特徵。
減少這種資源瓶頸的方法是將sequence值cache到記憶體中，
alter sequence seq_bid_row cache 100;
修改之後，row cache lock大大降低，速度也上去了。

建sequence時，有兩個引數和效能相關，需要注意：
1. CACHE
如果不指定CACHE/NOCACHE，預設是CACHE ，cache size為20。這在大多數系統中是足夠的。如果大批次取，也可設大為100，也可以設更大。但要注意，很多工具生成的指令碼卻是NOCACHE。

NOCACHE在很多OLTP系統中也不是什麼問題，但批次取效能較差，RAC中就更有問題了。
單Instance資料庫單會話迴圈不間斷去1-4萬個值 結果如下：
nocache： 37.7s 10000　　　
cache ：20 4.31s 10000

RAC兩個會話分別處於不同node同時併發迴圈間斷去取4萬個值 ：
nocache： 　　 2100s 40000
cache ：1000 　　 55s 40000

設定Cache，如果db重啟，就會丟失這些sequence值，個數最多為cache size，當然這不是什麼大問題。

2. Order
預設是noorder。 Order對單例項資料庫沒有影響，在RAC情況下，多個節點會需要同步sequence值，因此有一定開銷，除非必要，否則不要使用ORDER。

ORDER NOCACHE這個組合效能最差，要慎用。