查看: 8123|回復: 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
跳轉到指定樓層
1#
發表于 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:
[email protected]>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    [email protected] 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說明系統事務頻繁操作,需要應用控制事務!







您需要登錄后才可以回帖 登錄 | 注冊

本版積分規則 發表回復

DTCC2020中國數據庫技術大會 限時8.5折

【架構革新 高效可控】2020年9月21日~23日第十一屆中國數據庫技術大會將在北京隆重召開。

大會設置2大主會場,20+技術專場,將邀請超百位行業專家,重點圍繞數據架構、AI與大數據、傳統企業數據庫實踐和國產開源數據庫等內容展開分享和探討,為廣大數據領域從業人士提供一場年度盛會和交流平臺。

http://dtcc.it168.com


大會官網>>
30岁的男人干啥赚钱快赚钱多 云南省十一选五走势 河南彩票十一选五 516棋牌游戏大厅手机版 秒速赛车官方网站开奖 美股实时指数行情 浙江11选5走势 nba有多少支球队 现在开什么店最赚钱 中国重工股票行情 巨牛赢家
TOP技術積分榜 社區積分榜 徽章 團隊 統計 知識索引樹 積分競拍 文本模式 幫助
  ITPUB首頁 | ITPUB論壇 | 數據庫技術 | 企業信息化 | 開發技術 | 微軟技術 | 軟件工程與項目管理 | IBM技術園地 | 行業縱向討論 | IT招聘 | IT文檔
  ChinaUnix | ChinaUnix博客 | ChinaUnix論壇
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓優訊信息技術有限公司版權所有 聯系我們 
京ICP備09055130號-4  北京市公安局海淀分局網監中心備案編號:11010802021510 廣播電視節目制作經營許可證:編號(京)字第1149號
  
快速回復 返回頂部 返回列表
云南省十一选五走势 河南彩票十一选五 516棋牌游戏大厅手机版 秒速赛车官方网站开奖 美股实时指数行情 浙江11选5走势 nba有多少支球队 现在开什么店最赚钱 中国重工股票行情 巨牛赢家