DELETE資料導致INSERT邏輯讀增加

westzq1984發表於2010-06-22
delete
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章