update引起資料庫阻塞

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

--這條SQL只能看到排隊的SQL，不能看到源頭，不過能看到SQL文字內容

select p.spid pid,

s.sid,

s.SERIAL#,

s.username,

w.event,

w.p1,

w.P1TEXT,

w.p2,

w.P2TEXT,

w.p3,

w.P3TEXT,

sq.SQL_TEXT,

w.WAIT_TIME,

w.SECONDS_IN_WAIT,

w.STATE

from v$session_wait w, v$session s, v$process p, v$sql sq

where w.event not like 'SQL%' and w.sid = s.sid and s.paddr = p.addr and

s.SQL_ADDRESS = sq.ADDRESS and s.SQL_HASH_VALUE = sq.HASH_VALUE

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

--這條SQL能看到鎖住爭用的物件

select object_name as 物件名稱,s.sid,s.serial#,p.spid as 系統程式號

from v$locked_object l , dba_objects o , v$session s , v$process p

where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

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

--這個能看到機器的名稱，鎖的種類和型別

select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,

decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',

'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,

o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,

'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)

lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2

from sys.dba_objects o, ( select s.osuser, s.username, l.type,

l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,

v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner

<> 'SYS' order by o.owner, o.object_name

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

Tx 是行鎖

TM 是表鎖

昨天把會話編號拿出來殺的時候，已經沒有了，這裡看到的鎖的情況有可能是暫時的

也就是說這個鎖有可能只是暫時的，不一定是死鎖在那裡一直等待，而且出現以上情

況是update或者delete一張表的同一行時才會發生，不是同一行也不會發生，這裡已

經驗證過了。源頭沒有commit的SQL是已經執行完了的，在會話工具裡面的active裡面

也是看不到的。假設我沒有更新到這張表的被修改而沒有提交的那一行，而是對錶進行

了ddl操作，那麼一樣會出現等待，這就是TM表鎖在起作用，update一行資料時有兩個

鎖，獲得的TM鎖是共享鎖，Tx鎖是排他鎖

alter system kill session '164,13521' immediate;

批量刪除，這裡只需把sql弄出來，在兩邊加個冒號就可以了

Select ' alter system kill session ' || s.sid || ',' || s.serial# || ' immediate; '

from v$locked_object l , dba_objects o , v$session s , v$process p

where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

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

下面開始用huifu2做實驗，先檢視em工具，平均活動會話在o.2左右，單單模仿update同一行

不提交的情況試一下

我分別開啟4個視窗執行下面這句話，都不提交，會發現oem裡面的紅色部分變成方塊，application漲到了3.1左右

這樣也說明了圖形的高度和會話的多少沒有關係

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

Update t_to_order_info Set employee_id=1375 Where order_id=6007

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

OS_USER_NAME USER_NAME LOCK_TYPE OBJECT LOCK_MODE OWNER SID SERIAL_NUM ID1 ID2

Administrator HUIFU2 DML enqueue lock T_S_EMPLOYEE Row Share HUIFU2 501 29676 79433 0

Administrator HUIFU2 DML enqueue lock T_S_EMPLOYEE Row Share HUIFU2 503 17912 79433 0

Administrator HUIFU2 DML enqueue lock T_S_EMPLOYEE Row Share HUIFU2 494 8657 79433 0

Administrator HUIFU2 DML enqueue lock T_S_EMPLOYEE Row Share HUIFU2 499 10685 79433 0

Administrator HUIFU2 DML enqueue lock T_TO_ORDER_INFO Row Exclusive HUIFU2 494 8657 79486 0

Administrator HUIFU2 DML enqueue lock T_TO_ORDER_INFO Row Exclusive HUIFU2 503 17912 79486 0

Administrator HUIFU2 DML enqueue lock T_TO_ORDER_INFO Row Exclusive HUIFU2 501 29676 79486 0

Administrator HUIFU2 DML enqueue lock T_TO_ORDER_INFO Row Exclusive HUIFU2 499 10685 79486 0

看到這裡4個會話分別對這兩個物件鎖了四次，下面的lock_type非常明顯的顯示出了是dml enqueue Lock的排隊了

而lock_mode裡面有row share和row Exclusive ，這裡共享和排他都有了，值得注意的一個地方是我在這裡僅僅是

Update t_to_order_info 這張表，它確在t_s_employee表上面加了一個共享鎖，因為我在更改的employee_id是有

外來鍵約束的，他的外來鍵參考列是t_s_employee表，所以t_s_employee上面有共享鎖，我現在再開啟一個sql視窗，

對t_s_employee執行Update t_s_employee Set dept_id=1 Where employee_id=253，是沒有問題的,修改刪除都Ok,

現在對上面update中的employee_id=1375這行操作

Update t_s_employee Set dept_id=3 Where employee_id=1375 --這個是可以更改的，雖然定位到了employee_id=1375這行，但是沒有改到這個欄位

Update t_s_employee Set employee_id=1234 Where employee_id=1375 --這樣就又會出現等待的情況了，因為這個欄位有外來鍵並且正在使用

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

oem還是在3.1左右不動，已經成了一個平行的線，我現在再開啟3個會話，再看看OEM的變換情況

發現紅色方塊漲到5.1左右去了，後面我commit了一個SQL視窗，那麼紅塊就下來了一點，看來整

個紅塊的高度和會動會話的多少是有關係的

怎麼解決呢？我將源頭，也就是第一個發起update的sql視窗進行了commit,但是這樣依次排隊的

SQL只有視窗2能夠執行，而後面的SQL依然全部阻塞在那個地方，所以即使找到源頭的第一條SQL

解決了之後仍然不能解決整個阻塞的問題

alter system kill session '507,2509' immediate;

alter system kill session '490,3804' immediate;

alter system kill session '510,31794' immediate;

alter system kill session '503,17912' immediate;

alter system kill session '501,29676' immediate;

alter system kill session '499,10685' immediate;

alter system kill session '507,2509' immediate;

alter system kill session '490,3804' immediate;

alter system kill session '510,31794' immediate;

alter system kill session '503,17912' immediate;

alter system kill session '501,29676' immediate;

alter system kill session '499,10685' immediate;

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

將會話清楚後，我重新開啟7個SQL視窗，然後全部跑代收代付那條SQL，會發現藍色

的方塊直線上升，從中能夠發現上去的只有user I/O,這裡完全是把I/O和application

分開了，也就是說，在執行這種耗I/O和CPU的大SQL時，在OEM裡面發生變化的其實只有

藍色I/O和

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

回到當時阻塞的情況，我在sun2上面，鐵勇在sun1上面，我這邊剛剛做出批量殺掉鎖住

物件的時候，他那邊已經殺掉了一些會話，然後我這邊執行的時候已經沒有鎖住物件的

會話，資料庫基本上通了，那麼可不可以猜想整個資料庫的阻塞源頭是從sun1節點上面

發起的。當時檢視資料庫的會話的時候，看到絕大部分會話執行的都是下面這條SQL

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

Update T_S_TEL_RECORD set

TEL_RECORD_ID=:TEL_RECORD_ID,

EMPLOYEE_ID=:EMPLOYEE_ID,

CUSTOMER_ID=:CUSTOMER_ID,

CALLIN_NUMBER=:CALLIN_NUMBER,

CALIN_TIME=:CALIN_TIME,

RECALL_NUMBER=:RECALL_NUMBER,

EXTENSION=:EXTENSION,

OPERATION_TYPE=:OPERATION_TYPE,

RESULT=:THISRESULT,

CALLID=:CALLID,

OPERATOR_TYPE_ID=:OPERATOR_TYPE_ID,

OPERATION_DESC=:OPERATION_DESC

where

TEL_RECORD_ID = :TEL_RECORD_ID

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

單單就是一條update,後面和總監溝通後發現，說是這條SQL的程式就這麼一條，也就是

說在點選了按鈕之後，從程式發出去的SQL就只有這麼一條，再從上面做的實驗分析，也

許它僅僅是排隊等待的SQL,源頭是不是它現在還不好確定，只能說源頭是它的可能性比較

小，現在開始分析當時故障時的AWR報告。

--Top 5 Timed Events（快速的說明整個資料庫瓶頸）

sun1:

CPU time

db file sequential read User I/O

enq: TM - contention Application

db file scattered read User I/O

log file sync Commit

sun2:

enq: TM - contention Application

CPU time

db file sequential Read User I/O

db file scattered read User I/O

row cache Lock Concurrency

分析：

--CPU time

是系統消耗的CPU,初步懷疑SQL沒有充分繫結變數，過多的硬解析，導致庫緩衝產

生碎片。也就是說SQL效能不佳引起的，從這裡應該把方向對準TOP Sql

--db file sequential read

sequential read一次進行單個塊的讀取，聯絡到執行計劃可以得出sequential Read

一般發生在進行索引掃描時,並不意味著資料庫產生系統問題,基至它大量出現都不是

一件壞事.真正要引起注意的是像enqueue和latch free等待事件

--db file scattered read

它是多塊讀入，等待事件的發起者是執行對錶和索引全掃描操作的SQL語句，存在該事

件並不一定表示存在效能問題，但是如果該事件的等待時間比其他等待時間多得多，

則必須調查其原因。當SQL語句訪問物件中的大多數行時，使用db file scattered read很有用處。

--enq: TM - contention

執行DML 期間，為防止對與DML 相關的物件進行修改，執行DML 的程式必須對該

表獲得TM 鎖。若在獲取TM 鎖的過程中發生爭用，則等待enq:TM-contention 事件。

沒有索引的外來鍵列是TM 鎖爭用的主要原因

--log file sync

當一個使用者提交(commits)或者回滾(rollback),session的redo資訊需要寫出

到redo logfile中.使用者程式將通知LGWR執行寫出操作,LGWR完成任務以後會通

知使用者程式.這個等待事件就是指使用者程式等待LGWR的寫完成通知.

--ROW CACHE Lock

等待事件是一個共享池相關的等待事件。是由於對於字典緩衝的訪問造成的。

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

觀測一下TOP Sql

--SQL ordered by Elapsed Time（這是SQL執行時間的總合，而不是單次SQL的執行時間，Elapsed Time=CPU time+wait time）

我們把整個注意力應該放在sun1上面，因為通過SQL ordered by Elapsed Time發現

sun2節點上並沒用發現比較特殊的sql

但是在節點1上發現了事發時的這條SQL，非常的明顯,排名1,2的就是這兩條SQL

Insert Into t_s_Tel_Record

(Tel_Record_Id,

Employee_Id,

Customer_Id,

Callin_Number,

Calin_Time,

Recall_Number,

Extension,

Operation_Type,

Callid)

Values

(:Tel_Record_Id,

:Employee_Id,

:Customer_Id,

:Callin_Number,

:Calin_Time,

:Recall_Number,

:Extension,

:Operation_Type,

:Callid)

---

Update t_s_Tel_Record

Set Tel_Record_Id = :Tel_Record_Id,

Employee_Id = :Employee_Id,

Customer_Id = :Customer_Id,

Callin_Number = :Callin_Number,

Calin_Time = :Calin_Time,

Recall_Number = :Recall_Number,

Extension = :Extension,

Operation_Type = :Operation_Type,

Result = :Thisresult,

Callid = :Callid,

Operator_Type_Id = :Operator_Type_Id,

Operation_Desc = :Operation_Desc

Where Tel_Record_Id = :Tel_Record_Id

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

--SQL ordered by CPU Time（記錄了執行佔CPU時間總和時間最長的top sql）

--SQL ordered by gets(記錄了執行行佔總buffer gets(邏輯IO)的TOP SQL)

節點1按等待事件第一位的是cpu Time ,我們檢視一下SQL ordered by CPU Time的第一位

也是邏輯讀排第一的

--

Select r.Special_Req_Id, r.Content

From t_c_Special_Requirements r

Left Join t_c_Special_Req_Template t

On r.Temp_Id = t.Template_Id

Inner Join v_Customer_Info c

On c.Customer_Id = r.Customer_Id

Where 1 = 1

And Exists

(Select c.Customer_Id

From v_Customer_Info c

Where c.Customer_Memo Like '%' || r.Content || '%'

And c.Customer_Id = r.Customer_Id)

And r.Customer_Id = :Customer_Id

And t.Remain_Type = 1

And t.Isbasedonorder = 0

And t.Template_Id In

(Select Tp.Template_Id

From t_c_Special_Template_Page Tp

Where Tp.Page_Id In (Select p.Page_Id

From t_c_Special_Remain_Page p

Where p.Page_Url = :Page_Url))

--

這裡，我將變數替換成了實際的數字，跑著條SQL非常的快呀，而且次條SQL的執行計劃也

很不錯，這裡不應該在造成很大的影響呀

--SQL ordered by Reads（記錄了執行佔總磁碟物理讀的TOP SQL）

--SQL ordered by Cluster Wait Time(叢集等待時間的TOP SQL)

叢集等待和物理讀的第一名都是下面這條SQL，執行計劃也較好，而且執行時間非常快，沒有異常

--

Select t_To_Order_Info.Create_Time,

Expect_Out_Time,

t_Tp_Partner.Partner_Name,

t_To_Order_Info.Order_Flow_Id,

t_Td_Order_Status.Order_Status_Desc,

t_To_Order_Info.Customer_Id,

t_To_Order_Info.Customer_Card_Id,

t_To_Task.Pay_Type,

t_To_Task.Pay_Amount,

t_To_Order_Info.Order_Id,

Guest_Num,

Pnr,

Active_Code,

t_s_Employee.Employee_Name,

(Select Count(*)

From t_To_Company_Assure_Info

Where t_To_Company_Assure_Info.Order_Id = t_To_Order_Info.Order_Id) Isassure,

(Select Guest_Name

From t_To_Order_Tickets

Where t_To_Order_Tickets.Order_Id = t_To_Order_Info.Order_Id

And Rownum <= 1) || Case Guest_Num

When 1 Then

''

Else

' ...'

End Guest_Name,

(Select Flight

From t_To_Order_Tickets

Where t_To_Order_Tickets.Order_Id = t_To_Order_Info.Order_Id

And Rownum <= 1) Flight,

(Select Departure || Arrival

From t_To_Order_Tickets

Where t_To_Order_Tickets.Order_Id = t_To_Order_Info.Order_Id

And Rownum <= 1) Departure,

(Select Departure_Time

From t_To_Order_Tickets

Where t_To_Order_Tickets.Order_Id = t_To_Order_Info.Order_Id

And Rownum <= 1) Departure_Time,

(Select Operation_Time

From t_To_Order_Operation

Where Order_Id = t_To_Order_Info.Order_Id

And Ticket_Operation_Id = 18

And Rownum < 2) Assigntime,

t_f_Debit_Payment_Record.Pay_Status,

If_Ascend,

(Select Case Pymt_Type

When 1 Then

Null

When 2 Then

Ticketpartner.Partner_Name

When 3 Then

Paymentpartner.Partner_Name

End

From t_To_Consign_Pymt_Record

Inner Join t_Tp_Partner Ticketpartner

On Ticketpartner.Partner_Id =

t_To_Consign_Pymt_Record.Ticket_Partner_Id

Inner Join t_Tp_Partner Paymentpartner

On Paymentpartner.Partner_Id =

t_To_Consign_Pymt_Record.Partner_Id

Where t_To_Consign_Pymt_Record.Order_Id = t_To_Order_Info.Order_Id

And t_To_Consign_Pymt_Record.Consign_Pymt_Record_Id =

(Select Max(Consign_Pymt_Record_Id)

From t_To_Consign_Pymt_Record Tcr

Where Tcr.Order_Id = t_To_Order_Info.Order_Id)) Paymentparnter,

(Select Guest_Type

From t_To_Order_Tickets

Where t_To_Order_Tickets.Order_Id = t_To_Order_Info.Order_Id

And Rownum <= 1) Guest_Type,

t_f_Debit_Payment_Record.Transordid

From t_To_Task

Left Join t_To_Order_Info

On t_To_Task.Order_Id = t_To_Order_Info.Order_Id

Left Join t_s_Employee

On t_To_Order_Info.Employee_Id = t_s_Employee.Employee_Id

Left Join t_Td_Order_Status

On t_Td_Order_Status.Order_Status_Id = t_To_Order_Info.Order_Status_Id

Left Join t_f_Debit_Payment_Record

On t_To_Task.Order_Id = t_f_Debit_Payment_Record.Order_Id

Inner Join t_Tp_Partner

On t_Tp_Partner.Partner_Id = t_To_Order_Info.Partner_Id

Where (t_To_Task.Task_Type_Id = 6 Or t_To_Task.Task_Type_Id = 8)

And t_To_Task.Pay_Type = 8

And (t_To_Order_Info.Order_Status_Id In (2, 3, 5, 6))

And (t_To_Order_Info.Order_Flow_Id Is Null Or t_To_Order_Info.Order_Flow_Id = 3)

And t_To_Order_Info.Is_Debit_Card = 1

Order By t_To_Order_Info.Order_Id

--

--SQL ordered by Parse Calls（記錄了SQL的軟解析次數的TOP SQL）

update seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6, cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1

感覺還是和系統表裡面的序列有關係，問題也不在這裡

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

依然看不出任何有用的資訊，還是回到執行計劃上面了，單看故障時發生等待的那

兩條insert和update的執行計劃是非常非常小的，然後查閱了表T_S_TEL_RECORD的

統計資訊，是在11年5月最後一次做統計，現在收集一下統計資訊

SQL> exec dbms_stats.gather_table_stats('gc','T_S_TEL_RECORD');

然後，在把關注點放在這張表的外來鍵列上面來，發生enq: TM - contention等待的

情況是外來鍵列上沒有索引，但是這張表的兩個外來鍵都是有索引的，那麼是否是索引

表現的不好？再次重建並收集一下索引的統計資訊

SQL> select index_name,table_name,column_name

from user_ind_columns where table_name = 'T_S_TEL_RECORD';

INDEX_NAME TABLE_NAME COLUMN_NAME

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

PK_T_S_TEL_RECORD T_S_TEL_RECORD TEL_RECORD_ID

TEL_RECORD_CUSTOMER_FK T_S_TEL_RECORD CUSTOMER_ID

TEL_RECORD_EMPLOYEE_FK T_S_TEL_RECORD EMPLOYEE_ID

TEL_RECORD_CALLID T_S_TEL_RECORD CALLID

T_S_TEL_RECORD_CALIN_TIME T_S_TEL_RECORD CALIN_TIME

SQL> Select table_name, From user_constraints Where table_name ='T_S_TEL_CALLOUT_RECORD'

SQL> alter index TEL_RECORD_CUSTOMER_FK Rebuild Online

SQL> alter index TEL_RECORD_EMPLOYEE_FK Rebuild Online

SQL> exec dbms_stats.gather_index_stats('gc','TEL_RECORD_CUSTOMER_FK');

SQL> exec dbms_stats.gather_index_stats('gc','TEL_RECORD_EMPLOYEE_FK');

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

整個等待事件唯一值得關注的還是--enq: TM - contention

實際上，當我們在對employees表的資料進行更新時，不僅會在資料行的頭部記錄行級鎖，

而且還會在表的級別上新增一個表級鎖。那麼當D使用者要刪除表時，發現employees表上具

有一個表級鎖，於是等待。通過這種在表級別上新增鎖定的方式，我們就能夠比較容易並

且高效地（因為不需要掃描表裡的每一條記錄來判斷在表上是否有DML事務）對鎖定進行

管理了。表級鎖共具有五種模式，如下所示。

--行級排他鎖（Row Exclusive，簡稱RX鎖）

當我們進行DML時會自動在被更新的表上新增RX鎖，或者也可以通過執行lock命令顯式的

在表上新增RX鎖。在該鎖定模式下，允許其他的事務通過DML語句修改相同表裡的其他數

據行，或通過lock命令對相同表新增RX鎖定，但是不允許其他事務對相同的表新增排他鎖（X鎖）。

--行級共享鎖（Row Shared，簡稱RS鎖）

通常是通過select … from for update語句新增的，同時該方法也是我們用來手工鎖定某

些記錄的主要方法。比如，當我們在查詢某些記錄的過程中，不希望其他使用者對查詢的記

錄進行更新操作，則可以發出這樣的語句。當資料使用完畢以後，直接發出rollback命令

將鎖定解除。當表上新增了RS鎖定以後，不允許其他事務對相同的表新增排他鎖，但是允

許其他的事務通過DML語句或lock命令鎖定相同表裡的其他資料行。

--共享鎖（Share，簡稱S鎖）

通過lock table in share mode命令新增該S鎖。在該鎖定模式下，不允許任何使用者更新表。

但是允許其他使用者發出select …from for update命令對錶新增RS鎖。

--排他鎖（Exclusive，簡稱X鎖）

通過lock table in exclusive mode命令新增X鎖。在該鎖定模式下，其他使用者不能對錶進

行任何的DML和DDL操作，該表上只能進行查詢。

--共享行級排他鎖（Share Row Exclusive，簡稱SRX鎖）

通過lock table in share row exclusive mode命令新增SRX鎖。該鎖定模式比行級排他鎖

和共享鎖的級別都要高，這時不能對相同的表進行DML操作，也不能新增共享鎖。

首先是清楚這點

Select * from …… 觸發的是RS

下面3種DML語句觸發的RX

Insert into ……

Update ……

Delete from ……

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

update引起資料庫阻塞

相關文章