查看: 1851|回复: 0

[原创] oracle enq: TM & enq: TX 分析

[复制链接]
论坛徽章:
21
娜美
日期:2017-06-26 15:18:15火眼金睛
日期:2018-04-30 22:00:00目光如炬
日期:2018-07-29 22:00:00火眼金睛
日期:2018-08-31 22:00:00目光如炬
日期:2018-09-02 22:00:00目光如炬
日期:2018-09-16 22:00:01火眼金睛
日期:2018-09-30 22:00:00目光如炬
日期:2018-10-14 22:00:00火眼金睛
日期:2018-11-30 22:00:01目光如炬
日期:2018-04-29 22:00:00
发表于 2018-7-10 16:43 | 显示全部楼层 |阅读模式



oracle enq: TM & enq: TX 分析

作者简介:
----------------------------------------------------
@ 孙显鹏,海天起点oracle技术专家,十年从业经验
@ 精通oracle内部原理,擅长调优和解决疑难问题
@ 致力于帮助客户解决生产中的问题,提高生产效率。
@ 爱好:书法,周易,中医。微信:sunyunyi_sun
@ 易曰:精义入神,以致用也!
@ 调优乃燮理阴阳何其难也!
-----------------------------------------------------

现象:

TOP 等待:

1: 第一位:enq: TM - contention
2: 第三位:enq: TX - row lock contention

查询ASH:

set lines 1200 pages 1200
col evnet for a30
col p1 for a23
col block_sess for a9
select h.session_id,h.sql_id,h.event,h.p1text||'='||to_char(h.p1,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') p1,h.p2,
blocking_inst_id||blocking_session block_sess,time_waited/100000 wait
from dba_hist_active_sess_history h
where h.sample_time>=to_date('2018-07-04:16:00','yyyy-mm-dd-hh24:mi:ss')
      and h.sample_time<=to_date('2018-07-04:16:30','yyyy-mm-dd-hh24:mi:ss')
      and h.event like 'enq%'


      6524 dw8sgchczzj16 enq: TX - row lock contention                                    name|mode=                  458779 16656              0
                                                                                                       54580006
---大量的  dw8sgchczzj16 enq: TX - row lock contention --这里不显示了,TX 如何处理后面附件有说明
下面是TM 锁信息:

note: BLOCK_SES 列 16364 前面的1代表实例号,应该写成1-6364,着急忘记标记!

SESSION_ID SQL_ID        EVENT                                                            P1                              P2 BLOCK_SES       WAIT
---------- ------------- ---------------------------------------------------------------- ----------------------- ---------- --------- ----------
      5297 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      1284 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      3641 4j9ya06f6f55d enq: TM - contention                                             name|mode=                  364840                    0
                                                                                                       544D0003

      5244 4j9ya06f6f55d enq: TM - contention                                             name|mode=                  364840                    0
                                                                                                       544D0003

      5297 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      5932 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      6798 4j9ya06f6f55d enq: TM - contention                                             name|mode=                  364840                    0
                                                                                                       544D0003

       908 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      1284 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      3641 4j9ya06f6f55d enq: TM - contention                                             name|mode=                  364840                    0
                                                                                                       544D0003

      5297 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      6798 4j9ya06f6f55d enq: TM - contention                                             name|mode=                  364840                    0
                                                                                                       544D0003

       908 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      1284 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      5297 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      6798 4j9ya06f6f55d enq: TM - contention                                             name|mode=                  364840                    0
                                                                                                       544D0003

       908 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

       908 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      5297 c6qn2qxs46qfz enq: TM - contention                                             name|mode=                  364840 16364              0
                                                                                                       544D0004

      3169 atqmkk25d9kpg enq: TX - contention                                             name|mode=                 3670034 13337              0
                                                                                                       54580004

      5976 7w586m0dhr2cd enq: TX - contention                                             name|mode=                 5308448 15896         .10316
                                                                                                       54580004

      1460 2nububwzrnc9k enq: IV -  contention                                            type|mode=              1280262987               .09024
                                                                                                       49560005

      3906 34n2zhhqmsunb enq: IV -  contention                                            type|mode=              1280262987               .01777
                                                                                                       49560005

      2608               enq: IV -  contention                                            type|mode=              1398361667               .01438
                                                                                                       49560003

      3906 34n2zhhqmsunb enq: IV -  contention                                            type|mode=              1280262987                .0097
                                                                                                       49560005

      2608               enq: IV -  contention                                            type|mode=              1398361667               .00714
                                                                                                       49560003


425 rows selected.

TM 分析:

相关SQL:

TM-03 ----sx--ROW EXCLUSIVE WAIT
Schema Name: ACCT
SQL ID     : 4j9ya06f6f55d
SQL Text   :  INSERT INTO ACCT_BALANCE (        ACCT_BALANCE_ID,        BALANCE_TYPE_ID,
                EFF_DATE,       EXP_DATE,       BALANCE,        CYCLE_UPPER,    CYCLE_LOWER,
                CYCLE_UPPER_TYPE,       CYCLE_LOWER_TYPE,       BANK_ACCT,      STATE,
                STATE_DATE,     ACCT_ID,        SERV_ID,        ITEM_GROUP_ID,  OBJECT_TYPE_ID
             ) VALUES ( :lAcctBalanceID,        :iBalanceTypeID,
                TRUNC(TO_DATE((DECODE(:sEffDate, :"SYS_B_00", NULL,
             :"SYS_B_01",NULL, :sEffDate)), :"SYS_B_02")),
                TRUNC(TO_DATE((DECODE(:sExpDate, :"SYS_B_03", NULL,
             :"SYS_B_04",NULL, :sExpDate)), :"SYS_B_05")),      :lBalance,
                DECODE(:lCycleUpper, -:"SYS_B_06", NULL, :lCycleUpper),
                DECODE(:lCycleLower, -:"SYS_B_07", NULL, :lCycleLower),
                DECODE(:sCycleUpperType, :"SYS_B_08", NULL, :sCycleUpperType),
                DECODE(:sCycleLowerType, :"SYS_B_09", NULL, :sCycleLowerType),
                DECODE(:sBankAcct, :"SYS_B_10", NULL, :sBankAcct),      :sState,
                TO_DATE(:sStateDate, :"SYS_B_11"),      :lAcctID,       DECODE(:lServID,
             -:"SYS_B_12", NULL, :lServID),     DECODE(:iAcctItemGroupID,
             -:"SYS_B_13", NULL, :lAcctItemGroupID),    :lObjectTypeID )


c6qn2qxs46qfz tm-04-S -请求S mode,等待,阻塞者 6364
-------------------------------------------------------------------------------
Schema Name: ACCT
SQL ID     : c6qn2qxs46qfz
SQL Text   :  DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID
             =:ACCT_BALANCE_ID
Bind Variables :
1 -  (NUMBER):46471271


对象分析:

desc ACCT.ACCT_BALANCE


CREATE TABLE "ACCT"."ACCT_BALANCE"
   (    "ACCT_BALANCE_ID" NUMBER(9,0) NOT NULL ENABLE,
        "BALANCE_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,
        "EFF_DATE" DATE,
        "EXP_DATE" DATE,
        "BALANCE" NUMBER(16,5) NOT NULL ENABLE,
        "CYCLE_UPPER" NUMBER(16,5),
        "CYCLE_LOWER" NUMBER(16,5),
        "CYCLE_UPPER_TYPE" VARCHAR2(3),
        "CYCLE_LOWER_TYPE" VARCHAR2(3),
        "BANK_ACCT" VARCHAR2(30),
        "STATE" VARCHAR2(3) NOT NULL ENABLE,
        "STATE_DATE" DATE NOT NULL ENABLE,
        "ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,
        "SERV_ID" NUMBER(12,0),
        "ITEM_GROUP_ID" NUMBER(9,0),
        "OBJECT_TYPE_ID" NUMBER(5,0),
        "UPDATE_DATE" DATE,
         CONSTRAINT "PK_P_ACCT_BALANCE" PRIMARY KEY ("ACCT_BALANCE_ID")
  USING INDEX PCTFREE 10 INITRANS 100 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 31457280 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ACCT_INDEX01"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ACCT_DATA01"
  PARTITION BY HASH ("ACCT_ID")
(PARTITION "P_ACCT_BALANCE_1" SEGMENT CREATION IMMEDIATE
  TABLESPACE "ACCT_DATA01"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_2" SEGMENT CREATION IMMEDIATE
  TABLESPACE "ACCT_DATA02"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_3" SEGMENT CREATION IMMEDIATE
  TABLESPACE "ACCT_DATA03"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_4" SEGMENT CREATION IMMEDIATE
  TABLESPACE "ACCT_DATA04"
NOCOMPRESS )



疑惑不解:

DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID =:ACCT_BALANCE_ID

这个DELETE 操作怎么请求TM-4-Smode 锁呢?
一般DML操作请求TM-SX锁,SX和SX是兼容的,SX和S是不兼容的,这种情况是外键没有索引的情况下,DELETE主表会给子表添加TM-S锁,
由INSRRT 语句P2和delete语句P2相同,那么DELETE操作就直接请求该表的TM-S锁,这个P2=364840是什么对象?存在外键无索引?


今天让客户查询了对象364840:
sys@ACCTDB>select object_name,subobject_name from dba_objects where object_id=364840;

OBJECT_NAME SUBOBJECT_NAME

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

BALANCE_SHARE_RULE


经查BALANCE_SHARE_RULE 是ACCT_BALANCE 表的子表。--好吧还是外键无索引导致!!


在自己本机测试:


CREATE TABLE "WOLF"."ACCT_BALANCE"
   (    "ACCT_BALANCE_ID" NUMBER(9,0) NOT NULL ENABLE,
        "BALANCE_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,
        "EFF_DATE" DATE,
        "EXP_DATE" DATE,
        "BALANCE" NUMBER(16,5) NOT NULL ENABLE,
        "CYCLE_UPPER" NUMBER(16,5),
        "CYCLE_LOWER" NUMBER(16,5),
        "CYCLE_UPPER_TYPE" VARCHAR2(3),
        "CYCLE_LOWER_TYPE" VARCHAR2(3),
        "BANK_ACCT" VARCHAR2(30),
        "STATE" VARCHAR2(3) NOT NULL ENABLE,
        "STATE_DATE" DATE NOT NULL ENABLE,
        "ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,
        "SERV_ID" NUMBER(12,0),
        "ITEM_GROUP_ID" NUMBER(9,0),
        "OBJECT_TYPE_ID" NUMBER(5,0),
        "UPDATE_DATE" DATE,
         CONSTRAINT "PK_P_ACCT_BALANCE" PRIMARY KEY ("ACCT_BALANCE_ID")
  USING INDEX PCTFREE 10 INITRANS 100 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 31457280 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST"
  PARTITION BY HASH ("ACCT_ID")
(PARTITION "P_ACCT_BALANCE_1" SEGMENT CREATION IMMEDIATE
  TABLESPACE "TEST"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_2" SEGMENT CREATION IMMEDIATE
  TABLESPACE "TEST"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_3" SEGMENT CREATION IMMEDIATE
  TABLESPACE "TEST"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_4" SEGMENT CREATION IMMEDIATE
  TABLESPACE "TEST"
NOCOMPRESS )



INSERT INTO ACCT_BALANCE values (46471271,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate)
commit;

DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471271
/


SQL>  select sid,type,id1,id2,lmode,request from v$lock where type='TM';

    SID TYPE            ID1        ID2      LMODE    REQUEST
------- -------- ---------- ---------- ---------- ----------
     25 TM            86358          0          3          0
     25 TM            86360          0          3          0  --TM-SX 3 级锁



添加一个外键表,且无索引:

CREATE TABLE "WOLF"."ACCT_BALANCE_REF"
   (    "ACCT_BALANCE_ID" NUMBER(9,0)  NULL references ACCT_BALANCE(ACCT_BALANCE_ID),
        "BALANCE_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,
        "EFF_DATE" DATE,
        "EXP_DATE" DATE,
        "BALANCE" NUMBER(16,5) NOT NULL ENABLE,
        "CYCLE_UPPER" NUMBER(16,5),
        "CYCLE_LOWER" NUMBER(16,5),
        "CYCLE_UPPER_TYPE" VARCHAR2(3),
        "CYCLE_LOWER_TYPE" VARCHAR2(3),
        "BANK_ACCT" VARCHAR2(30),
        "STATE" VARCHAR2(3) NOT NULL ENABLE,
        "STATE_DATE" DATE NOT NULL ENABLE,
        "ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,
        "SERV_ID" NUMBER(12,0),
        "ITEM_GROUP_ID" NUMBER(9,0),
        "OBJECT_TYPE_ID" NUMBER(5,0),
        "UPDATE_DATE" DATE )
TABLESPACE "TEST"
/


DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471271

ERROR at line 1:
ORA-02292: integrity constraint (WOLF.SYS_C0013150) violated - child record found

这种情况不不允许的,因为外键存在!

先删除外键,再删除主键:
session 1:
DELETE FROM ACCT_BALANCE_REF WHERE ACCT_BALANCE_ID=46471271
/

session 2:
DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471271
/



    SID TYPE            ID1        ID2      LMODE    REQUEST
------- -------- ---------- ---------- ---------- ----------
      1 TM            86358          0          2          0
     25 TM            86358          0          3          0
      1 TM            86365          0          3          0
     25 TM            86365          0          0          4    ---session 2 请求TM-S 4级锁



col object_name for a20
col subobject_name for a20
select l.sid,l.type,o.object_name,l.id1,l.id2,lmode,l.request from v$lock l,dba_objects o where l.id1=o.object_id and l.type='TM';



    SID TYPE     OBJECT_NAME                 ID1        ID2      LMODE    REQUEST
------- -------- -------------------- ---------- ---------- ---------- ----------
      1 TM       ACCT_BALANCE              86358          0          2          0
     25 TM       ACCT_BALANCE              86358          0          3          0
      1 TM       ACCT_BALANCE_REF          86365          0          3          0
     25 TM       ACCT_BALANCE_REF          86365          0          0          4


删除主表的时候需要给子表添加4级锁,此时子表由于delete已经持有3级锁,4级和3级锁不兼容


USERNAME        SID SQL_ID          SQL_TEXT                     EVENT                        MODULE     STATE
------------ ------ --------------- ---------------------------- ---------------------------- ---------- ------------
SYS              67 517qpp3b76vrc   select /*+ all_rows*/        SQL*Net message to client    sqlplus@se WAITED SHORT
WOLF             25 5q8rsb0an7j4t   DELETE FROM ACCT_BALANCE WHE enq: TM - contention         SQL*Plus   WAITING


等待事件:
enq: TM - contention


set lines 1200 pages 1200
col evnet for a30
col p1 for a23
col block_sess for a9
select h.sid,h.sql_id,h.event,h.p1text||'='||to_char(h.p1,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') p1,
BLOCKING_INSTANCE||blocking_session block_sess,SECONDS_IN_WAIT
from v$session h
where  h.event like 'enq%'



       SID SQL_ID                                  EVENT                P1                             BLOCK_SES SECONDS_IN_WAIT
---------- --------------------------------------- -------------------- ------------------------------ --------- ---------------
        25 5q8rsb0an7j4t                           enq: TM - contention name|mode=                     11                    837
                                                                              544D0004


删除没有记录呢?
DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=0

    SID TYPE     OBJECT_NAME                 ID1        ID2      LMODE    REQUEST
------- -------- -------------------- ---------- ---------- ---------- ----------
     25 TM       ACCT_BALANCE              86358          0          3          0


删除正在插入的数据呢?
sess1:
INSERT INTO ACCT_BALANCE values (46471273,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate);
INSERT INTO ACCT_BALANCE_REF values (46471273,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate);

sess2:
DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471273


    SID TYPE     OBJECT_NAME                 ID1        ID2      LMODE    REQUEST
------- -------- -------------------- ---------- ---------- ---------- ----------
      1 TM       ACCT_BALANCE              86360          0          3          0
      1 TM       ACCT_BALANCE              86358          0          3          0
     25 TM       ACCT_BALANCE              86358          0          3          0
      1 TM       ACCT_BALANCE_REF          86365          0          3          0
     25 TM       ACCT_BALANCE_REF          86365          0          0          4 -- 一样的



关于TM-S四级锁就明白了。

还有一个问题,为什么insert 主表给子表却添加了TM-SX 3级锁,应该TM 2才对呀? 如上面ASH信息,
那么只有一种可能就是现在主表插入,继而在子表插入锁从TM2级升级为TM3级:

测试如下:

session 1:

SQL> INSERT INTO ACCT_BALANCE values (46471274,46471272,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate)
  2  /

1 row created.

    SID TYPE     OBJECT_NAME                 ID1        ID2      LMODE    REQUEST
------- -------- -------------------- ---------- ---------- ---------- ----------
     24 TM       ACCT_BALANCE              86360          0          3          0
     24 TM       ACCT_BALANCE              86358          0          3          0
     24 TM       ACCT_BALANCE_REF          86365          0          2          0  --子表持有TM 2级锁

session 2:

SQL> INSERT INTO ACCT_BALANCE_REF values (46471274,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate)
  2  
SQL> /

1 row created.

    SID TYPE     OBJECT_NAME                 ID1        ID2      LMODE    REQUEST
------- -------- -------------------- ---------- ---------- ---------- ----------
     24 TM       ACCT_BALANCE              86360          0          3          0
     24 TM       ACCT_BALANCE              86358          0          3          0
     24 TM       ACCT_BALANCE_REF          86365          0          3          0  ---子表持有TM 3级锁


到此问题全部搞明白!

处理方案:
1:dump 进程6364,分析6364为什么阻塞了后面的进程,由于事故发生后处理没有得到dump信息
2:kill 6364 进程,释放资源
3:给存在外键的子表BALANCE_SHARE_RULE 外键添加索引,添加索引后delete主表会请求子表的TM SX 3级锁,SX和SX是兼容的就不会出现TM 锁等待。

为了防止系统存在外键无索引问题,可执行下面脚本进行检查: (建议执行第一个脚本)

a:执行oracle 提供脚本FKCHLK.SQL可从MOS下载---脚本太长,这里不贴了
b:执行下面脚本:


SET ECHO off
REM NAME: CHKFK.SQL
REM USAGE:"@path/chkfk"
REM --------------------------------------------------------------------------  
REM This file checks the ALL users Foreign Keys no index col
REM -------------------------------------------------------------------------
REM Main text of script follows:

set lines 1200 pages 1200
col owner for a30
col table_name for a30
col CONSTRAINT_NAME for a30
col COLUMNS for a30

SELECT OWNER,
       TABLE_NAME,
       CONSTRAINT_NAME,
       CNAME1 ||
       NVL2(CNAME2, ',' || CNAME2, NULL) ||
       NVL2(CNAME3, ',' || CNAME3, NULL) ||
       NVL2(CNAME4, ',' || CNAME4, NULL) ||
       NVL2(CNAME5, ',' || CNAME5, NULL) ||
       NVL2(CNAME6, ',' || CNAME6, NULL) ||
       NVL2(CNAME7, ',' || CNAME7, NULL) ||
       NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
FROM (SELECT   B.OWNER,
               B.TABLE_NAME,
               B.CONSTRAINT_NAME,
               MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
               MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
               MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
               MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
               MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
               MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
               MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
               MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
               COUNT(*) COL_CNT
          FROM (SELECT OWNER,
                       SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
                       SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
                       SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
                       POSITION
                  FROM DBA_CONS_COLUMNS) A,
               DBA_CONSTRAINTS B
         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.OWNER=B.OWNER
               AND A.OWNER NOT IN ('SYS','SYSTEM','SYSMAN','OLAPSYS','MDSYS','ORDDATA','EXFSYS','SCOTT','DBSNMP')
           AND B.CONSTRAINT_TYPE = 'R'
         GROUP BY B.OWNER,B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
WHERE COL_CNT > ALL
(SELECT COUNT(*)
          FROM DBA_IND_COLUMNS I
         WHERE I.TABLE_NAME = CONS.TABLE_NAME and I.INDEX_OWNER =CONS.OWNER
           AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
                CNAME6, CNAME7, CNAME8)
           AND I.COLUMN_POSITION <= CONS.COL_CNT
         GROUP BY I.INDEX_NAME)
order by 1,2
/



附:


锁的介绍:

1:enq: TM - contention
关于 enq: TM - contention 锁结合下面信息:

This wait indicates time spent waiting for a TM lock. TM locks are used to coordinate activities for many base table / partition operations.
Individual Waits:
  Parameters:
P1 = name|mode
P2 = object #
P3 = table/partition
name|mode
The lock name and requested mode are encoded into P1 as "name<<16 | mode". This is best seen in P1RAW (or you can convert P1 to hexadecimal).
For this wait:
name is always the ASCII for "TM" = 0x544d. --结合上面查询ASH信息对照
mode is the mode that the TM lock is being requested in.
TM locks may be requested in several different modes:
"2" = RS = Row Share wait - also known as sub-share table lock (SS)
"3" = RX = Row Exclusive wait - also known as sub-exclusive table lock (SX)
"4" = S = Share mode wait.
"5" = SRX = Share Row Exclusive wait - also known as share-subexclusive table lock (SSX)
"6" = X = eXclusive mode wait

eg: P1RAW of 544D0006 = eXclusive mode TM wait, P1RAW of 544D0004 = Shared mode TM wait.
object # (dictionary object id)
This is the dictionary object id of the object that the TM lock protects.
ie: The dictionary object with OBJECT_ID = P2
P2RAW corresponds to the ID1 part of the TM lock itself.
table/partition
Indicates ID2 of the TM lock being waited on:
"0" = wait for the normal table / partition lock
"1" = wait for the partition-wait lock
This wait thus indicates a wait for the lock "TM-<P2RAW>-<P3RAW>"


2:enq: TX - row lock contention

关于 enq: TX - row lock contention 是由于应用设计问题导致,但是我们需要检查:
1:redo 日志写和切换正常,不要由于redo 写问题导致前台事务时间经历较长时间,加重TX锁
2:enq: TX - row lock contention 等待时间不能太长,太长说明SQL可能需要优化提高SQL执行效率
3:enq: TX - row lock contention 等待时间非常短,但是存在大量enq: TX - row lock contention说明系统事务频繁操作,需要应用控制事务!







您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表