DELETE数据导致INSERT逻辑读增加
而大量会话的大量操作都要向日志表写入数据（插入一条数据，提交）
前台报告操作相当缓慢，已到了无法容忍的地步
客户中止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博客 ” ，链接：https://blog.itpub.net/8242091/viewspace-665882/ ，如需转载，请注明出处，否则将追究法律责任。
