f Oracle RAC 不是OLTP 的萬靈丹 ~ 迪貝之家

Oracle RAC 不是OLTP 的萬靈丹

 

AP說起他們的程式發生timeout
問我能不能看一下資料庫端的狀況
只要你不是一付都是你啦~~ 你資料庫的問題
我當然是責無旁貸會去確認資料庫的狀況
這是一套Oracle RAC資料庫
會跑這東西的,當然都嘛是非同小可
能說不行嗎 ??
首先當是去看了一下ash 報表
因為AP就給了20分鐘的時間區間
(如果你熟Oracle,就會知道為何我先看ASH)

row lock contention

恩.....發生row lock contention
好...我再來看addm報表

ADDM

我再去調1nbv5wx5nx0g5它的執行數據
AWR SQL Report

instance 1 等了 84秒
instance 2 等了 52秒(inst 2的圖我就不貼了)
我初看就覺得納悶,怎麼會insert 造成lock
去metalink找到了Doc ID 873243.1
Troubleshooting 'enq: TX - index contention' Waits
它描述了在RAC環境下,大量insert會有什麼影響
原文如下 :
When running an OLTP systems, it is possible to see high TX enqueue contention on index associated with tables, which
are having high concurrency from the application. This usually happens when the application performs lot of INSERTs and
DELETEs concurrently. For RAC system, the concurrent INSERTs and DELETEs could happen from all the instances .
The reason for this is the index block splits while inserting a new row into the index. The transactions will have to wait for
TX lock in mode 4, until the session that is doing the block splits completes the operations.
A session will initiate a index block split, when it can't find space in an index block where it needs to insert a new row.
Before starting the split, it would clean out all the keys in the block to check whether there is enough sufficient space in
the block.deleted
Splitter has to do the following activities:
o Allocate a new block.
o Copy a percentage of rows to the new buffer.
o Add the new buffer to the index structure and commit the operation.
In RAC environments, this could be an expensive operation, due to the global cache operations included. The impact will
be more if the split is happening at a branch or root block level.
可是我的狀況是row lock contention
但依照這篇文件的指示
我再去看它所要求的AWR報表內
Segments by Row Lock Waits
的數據
我就恍然大悟
就是你了.....
它就是該表的索引
Segments by Row Lock Waits

文件裏頭所提出的最簡單的解法
就是改成reverse index
為了把東西賣出去
原廠都嘛跟你講不用改程式
對阿..用了你就知道.....
對...不用改...那才有鬼
不過...為了使用一個平台
你本來就該follow那個平台的coding規矩
不然夠本事,就不要用阿,自己寫一套囉~~
我最討厭那一種Programmer
因為framework,sql 語句是它自己產生的
104.................
那你用這個framework幹嘛哩~~
####2020/10/08#####
原來split page 的動作這麼大
這是sql server 2012 專書裏頭的描述
After SQL Server finds the new page, the original page must be split; half the rows (the first half based on the slot array on the page) are left on the original page, and the other half are moved to the new page, or as close to a 50/50 split as possible.
這還只是單一page split
當你大量匯入或者異動資料時
就是帶出一串粽子
難怪會有waiting