PLSQL_效能最佳化系列08_Oracle Insert / Direct Insert效能最佳化

標籤：des   style   blog   color   io   os   使用   ar   strong   

2014-09-25 BaoXinjian

一、Insert 效能影響

應用設計不合理導致的session之間的互鎖(enqueue)是影響程式可擴充性最常見的原因。此外，一些共用資源的爭用，也會導致效能下降。

本篇介紹兩個由並發insert操作導致的等待事件(wait event)，以及如何通過最佳化實體設計來進行改善。

普通Insert操作本身產生的是行鎖，因此進程相互之間不會鎖住(enqueue)，但當很多進程insert同一張表時，會有資源上衝突。

 

以下是兩個例子：

1. Buffer busy wait

批量進程insert test_table表，當進程多時（超過100個）速度明顯下降。在awr報告中看到（等待事件佔了93%,cpu佔0.95%。也就是說全都在等了，沒人在幹活…）。

進一步查到等待的object是PK_TEST_TABLE，是test_table表的主鍵。

Test_table表上建了hash分區（16），而PK_TEST_TABLE卻沒有建分區，導致修改同一index block，這會導致TX-index contention和buffer busy waits。

重建pk_test_table為hash分區索引後buffer busy waits和enq:TX-index contention就不在top events裡了。

 

 

2. 很多進程insert,報ora-00060 Dead lock錯誤

insert/update/delete操作時，雖然是行封鎖，但進程會在修改的block上佔一個slot，當修改同一block的進程數量超過initrans時，可能導致60錯誤。見下文

Its possible to get an ora-60 deadlock when more than one transaction does DML on a block if INITRANS was set too low. If there are more simultaneous transactions than slots then the transaction layer needs to grow from INITRANS towards MAXTRANS, but it can‘t if the rest of the block is packed with data. In this case the transaction that could not get a slot will either hang or get an ora-60 depending on the particular circumstances. This is an issue with tables, indexes, and clusters.

以上兩種效能問題可以table/index的實體設計最佳化來解決。有幾種方法：

(1). 建表時將表、索引的initrans值設大；

(2). 將表、索引建成hash分區，降低單個block上的衝突；

(3). 將索引建成反向索引。由於很多主鍵使用sequence的值，因此insert都是按sequence值排序的，insert時會在索引的同一block上產生衝突。使用反向鍵索引可以消除這一問題。有興趣的可以在google上查相關概念。

 

 二、Insert Direct效能影響

資料庫運行出賬程式時，出現enq: TM - contention等待事件，

主要原因是多個進程在insert bill_invoice_*時，使用了insert /*+ append*/這一方式，本來想通過append即direct insert方式提升速度，結果產生表級鎖。

這樣開帳進程相互等待，實際上是變成串列的操作了，反而影響了速度。

更嚴重的是，如果是生產系統，還會導致賬 單表上所有修改操作都無法進行，影響其他重要業務，這個後果就很嚴重了。

Direct insert這一用法有其速度快的優點，但也有其缺點，必須注意適用場合。大家想一下，為什麼oracle不預設用append呢？

這裡總結一下，direct-load insert(及append，direct方式sqlldr等)

適合於：

(1). 大量記錄的insert時使用會提升速度，因為繞過databuffer直接存取資料檔案，且不用掃描原有block上哪些有剩餘空間，而直接分配新空間；

(2). 單個進程，通常是資料維護時，或者臨時倒資料等；

這兩種情況下，使用direct-load insert不僅速度快，還減少了資料庫data buffer的使用，對資料庫上的其他應用產生的影響也較小；

不適合：

(1). 少量記錄insert。因為每次insert /*+append */後會進行索引維護，少量記錄insert使用append反而慢；

(2). 多使用者，OLTP環境。因為append會產生表級鎖。OLTP系統除了一些批處理操作，大多數應用都不應該使用append；

 

 

********************鮑建立********************

 

PLSQL_效能最佳化系列08_Oracle Insert / Direct Insert效能最佳化