DELETE資料導致INSERT邏輯讀增加

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE在業務繁忙時間，客戶開始清空一張日誌表的資料（DELETE）

而大量會話的大量操作都要向日志表寫入資料（插入一條資料，提交）

前臺報告操作相當緩慢，已到了無法容忍的地步

客戶中止DELETE操作，回滾完成後，INSERT操作正常，取到STATSPACK

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value

--------------- ------------ -------------- ------ -------- --------- ----------

1,089,863,890 415,786 2,621.2 96.8 ######## ######### 2559501436

正常時間段：

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value

--------------- ------------ -------------- ------ -------- --------- ----------

7,465,349 613,081 12.2 23.5 18.67 722.46 2559501436

BUFFER GETS比正常時間段突增了200多倍

主要等待為LATCH，確認是undo global data，這個latch不熟悉，只能看到和UNDO有關，附帶的cache buffers chains，說明可能存在併發訪問的增用。應該是DELETE和INSERT共同的結果

在自己機器上測試了下：

正常情況下INSERT資料

SQL> INSERT INTO ctais2.test VALUES(10000,'aaa');

1 row created.

Statistics

----------------------------------------------------------

0 recursive calls

3 db block gets

1 consistent gets

0 physical reads

436 redo size

783 bytes sent via SQL*Net to client

816 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

在另一個SESSION “delete test”，不提交的情況下INSERT資料

SQL> INSERT INTO ctais2.test VALUES(10000,'aaa');

1 row created.

Statistics

----------------------------------------------------------

0 recursive calls

459 db block gets

379 consistent gets

0 physical reads

436 redo size

787 bytes sent via SQL*Net to client

816 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

邏輯讀增加的很多，多次執行邏輯讀數量基本不變

本來想用10200 EVENT看看訪問了那些塊，但是沒TRC生成

邏輯讀增加+UNDO相關的LATCH等待高，猜想段點陣圖不像資料塊一樣，有前映象，DELETE資料後，不管是否提交，在點陣圖中塊都釋放為可以插入資料。那麼ORACLE可能將這個塊分配給INSERT的會話插入資料，而該會話找到該塊，構建一致性映象後，發現更本無法插入資料，又接著尋找，直到找到合適塊。這樣就會導致邏輯讀升高

為了證實這個想法，多個SESSION INSERT大量資料，進行過程中DELETE整個表

INSERT指令碼

BEGIN

for i in 1..1000 loop

INSERT INTO ctais2.test VALUES(i,'aaa');

COMMIT;

dbms_lock.sleep(1);

end loop;

END;

/

查詢v$bh，點陣圖塊各只有1個在BUFFER中，是個髒塊

SQL> SELECT FILE#, BLOCK#, DIRTY, TEMP, PING, STALE, DIRECT, NEW

2 FROM V$BH

3 WHERE FILE# = 5

4 AND BLOCK# IN (9, 10);

FILE# BLOCK# D T P S D N

---------- ---------- - - - - - -

5 9 Y N N N N N

5 10 Y N N N N N

所有會話提交後，其還是DIRTY的

有show_space來分析塊的空閒程度

DELETE前

DELETE後COMMIT前

COMMIT後

SQL> exec show_space('TEST',p_owner_1 => 'CTAIS2')

Total Blocks............................9216

Total Bytes.............................75497472

Unused Blocks...........................0

Unused Bytes............................0

Last Used Ext FileId....................5

Last Used Ext BlockId...................8329

Last Used Block.........................1024

***********************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............13

50% -- 75% free space bytes.............106496

75% -- 100% free space blocks...........7880

75% -- 100% free space bytes............64552960

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................1184

Total bytes.............................9699328

SQL> exec show_space('TEST',p_owner_1 => 'CTAIS2')

Total Blocks............................9216

Total Bytes.............................75497472

Unused Blocks...........................0

Unused Bytes............................0

Last Used Ext FileId....................5

Last Used Ext BlockId...................8329

Last Used Block.........................1024

***********************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............1

25% -- 50% free space bytes.............8192

50% -- 75% free space blocks............1

50% -- 75% free space bytes.............8192

75% -- 100% free space blocks...........8326

75% -- 100% free space bytes............68206592

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................749

Total bytes.............................6135808

SQL> exec show_space('TEST',p_owner_1 => 'CTAIS2')

Total Blocks............................9216

Total Bytes.............................75497472

Unused Blocks...........................0

Unused Bytes............................0

Last Used Ext FileId....................5

Last Used Ext BlockId...................8329

Last Used Block.........................1024

***********************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............1

25% -- 50% free space bytes.............8192

50% -- 75% free space blocks............1

50% -- 75% free space bytes.............8192

75% -- 100% free space blocks...........8326

75% -- 100% free space bytes............68206592

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................749

Total bytes.............................6135808

在commit前後資料塊空閒空間無變化，在DELETE釋放後就已經變化了

基本可以證明推斷：

ORACLE的段點陣圖不會去維護前映象，因為併發的修改不多，也沒有必要維護前映象。

只要空間被釋放到一定量，不管是否提交，都立刻在點陣圖中改變該塊的狀態。

INSERT的會話被按照一個方式分配使用一個塊，其構建該塊的一致性映象後，發現該塊更本無法插入資料

接著其又被分配一個新塊，繼續

最終找到一個合適的塊，插入資料

LATCH：undo global data

The undo global data Oracle metric latch serializes the access to the Undo (also known as Rollback) segment information in the SGA. Each time a session wants to know about the state of the Undo Segments, it has to get this latch

多個會話因為要尋找適當的塊插入資料，需要訪問相同的/相當多的UNDO塊，從而導致在undo global data，cache buffers chains LATCH上爭用。

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

DELETE資料導致INSERT邏輯讀增加

相關文章