library cache lock--通过ddl、select模拟

在实际生产中，通常大表的DDL动作会持续较长的时间，这个时候对表的SELECT查询会收到影响，因此生产上的DDL动作要慎重，以下为模拟测试：

1.会话125执行一个对表的DDL动作

begin
    for i in 1 .. 10000
    loop
        execute immediate 'alter table test enable all triggers';
    end loop;
end;
/

2.会话24执行对表的select查询动作

declare
    v_value number;
begin 
    for idx in 1 .. 10000 loop
    select 1 into v_value from test where rownum=1;
    end loop;
end;
/

3.查询会话的等待事件如下：

       SID SQL_ID         EVENT                             P1            P2             P3
---------- ------------- -------------------------------    ---------- ---------- ----------------
        24 57fawfapp387u library cache lock                 2033061912 2032761176  374800321150978
       125 bhbabtmxf51xv library cache lock                 2033061912 2114251656  374800321150979

SQL> select sid,p1raw,p2raw,p3raw from v$session where event='library cache lock';

P1RAW             P2RAW           P3RAW
---------------- ---------------- ----------------
00000000792E1018 0000000079297958 000154E100010002  --24
00000000792E1018 000000007E051410 000154E100010003  --125

000154E100010002 对应的namespace为1，mode为2，即共享模式

000154E100010003 对应的namespace为1，mode为3，即独占模式

SQL> select distinct KGLHDNSP,KGLHDNSD from x$kglob order by 1;

  KGLHDNSP KGLHDNSD
---------- ----------------------------------------------------------------
     0 SQL AREA
     1 TABLE/PROCEDURE              --namespace为1的为表或者存储过程
     2 BODY
     3 TRIGGER
     4 INDEX
     5 CLUSTER
    10 QUEUE
    18 PUB SUB INTERNAL INFORMATION
    23 RULESET
    24 RESOURCE MANAGER
    28 SUBSCRIPTION

  KGLHDNSP KGLHDNSD
---------- ----------------------------------------------------------------
    34 TRANSFORMATION
    38 RULE EVALUATION CONTEXT
    45 MULTI-VERSION OBJECT FOR TABLE
    48 MULTI-VERSION OBJECT FOR INDEX
    51 SCHEDULER GLOBAL ATTRIBUTE
    52 SCHEDULER EARLIEST START TIME
    64 EDITION
    69 DBLINK
    73 SCHEMA
    74 DBINSTANCE
    75 SQL AREA STATS

  KGLHDNSP KGLHDNSD
---------- ----------------------------------------------------------------
    79 ACCOUNT_STATUS
    82 SQL AREA BUILD

24 rows selected.

 

转载于:https://www.cnblogs.com/dayu-liu/p/9604122.html