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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10678398/viewspace-715092/