CU锁的问题

saup007

在给表加 非空 约束时，会阻塞其他session的DML或select，但在v$lock看不到阻塞SESSION A sid=135

11:57:04 ttt@TEST10G> alter table T1 modify OBJECT_NAME not null;

Table altered.

11:57:22 ttt@TEST10G>


session B  sid=136

10:29:39 ttt@TEST10G> select * from t1 where object_id=100;

--结果没有写出来，太多了，下午是执行完的时间，与alter table 改为not null时间一致

11:57:22 ttt@TEST10G>


在执行时锁的情况：request都为0，block也看没到阻塞啊。

1. 10:29:16 sys@TEST10G> select * from v$lock where sid in (135,136);
   
2. 
   
3. ADDR                 KADDR                         SID TY        ID1          ID2           LMODE    REQUEST         CTIME            BLOCK
   
4. ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
   
5. 00000000784DC720 00000000784DC740         136 CU 1949120480            0               6          0             0                0
   
6. 00000000787A04A8 00000000787A04D0         135 TM      59231            0               6          0             2                0
   
7. 00000000774472E0 0000000077447318         135 TX     131110         3161               6          0             3                0
   
8. 
   
9. 10:29:31 sys@TEST10G>

复制代码

修改not null执行完成后锁的情况
没有，因为select完了。

问题：
v$lock看不到阻塞，实际上却是阻塞了。

一般如果大数据量改not null，都在线重定义做了。
小的就直接改了，如果是11g有新特性就比较好的

[ 本帖最后由 saup007 于 2012-8-2 15:30 编辑 ]

Maclean Liu(刘相兵

CU LOCK CU

Cursor bind  

This document explains about the CU enqueue. This is called bind enqueue.

This enqueue is used when we determine that we have a cursor in the library cache that we can possibly share, but we need to check / set the bind types appropriately. The enqueue is used to protect manipulation of the shared cursor since there is a potential issue with pinning the library cache pin at this point in the code because the code path can then go on to try to pin in either shared or exclusive modes and we have to be careful that we do not have more than one process trying to pin in different modes.

Solution

Reducing Contention:

Normally we would not expect contention with this enqueue but if it is seen then the problem
probably lies somewhere within the library cache. It would not cause any impact as it would go on
its own.

Try to increase shared pool size to avoid the contention.

This may also happen sometimes due to the optimizer features. In higher versions of Oracle database, there are many new features got introduced with optimizer which make the CBO more bulkier and that would lead to increased parse time. So this could have also caused the CU lock during parsing as it takes long time.

Setting the optimizer_features_enable parameter to a lower value in the higher version database would also help.

Ex: optimizer_features_enable='10.1.0.4' in 10.2.0.3 database.




你在修改constraint  即修改表的定义，  sid  136 应当在等 library cache pin之类的等待 而非 enqueue lock的 TX或TM ，所以在V$LOCK没记录很正常

saup007

11g新特性，加字段，设置not null，default，是往ecol$插入一条数据，所以很快。

但11g，修改表中原来字段为not null，也很快啊。

15:34:38 ttt@TEST11G> select count(1) from t1;

  COUNT(1)
----------
   1174240

15:34:50 ttt@TEST11G> alter table T1 modify STATUS not  null;

Table altered.

Elapsed: 00:00:00.25