Lob字段，您用对了吗？

作者：刘娜

说起lob字段，不少人都被坑过，这不，小编就遇到了这样的麻烦。
系统突然交易缓慢了，作为dba，根据以往经验，数据库方面如果有问题，很可能是有等待事件，赶紧用如下语句验证一下：

select sql_id, event,count(*)
from dba_hist_active_sess_history
group by sql_id, event
order by 3;

果然，发现存在大量的enq: CF - contention等待事件，而且等待该事件的SQLID基本都是163ydjrbgxxxx，查看该SQL的具体内容：

select sql_text
from v$sql
where sql_id=’ 163ydjrbgxxxx’;

查询到的语句如下：

update plat_yyyyy_xxx
set a=:1 , b=:2 , c=:3 , d=:4 , e=:5 , f=:6 , pox=:7 , gex=:8 , f=:9
where g=:10
and h=:11
and i=:12
and j = :13
and k=:14 

而阻塞它的是什么呢？我们用如下语句找到了阻塞源头的信息，发现源头会话执行的SQL也是类似的DML语句，等待事件是control file parallel write，多次执行如下语句会发现，源头会话在不断变化。

select d.sid
      ,d.event
      ,a.sql_text
from v$sql a,
     v$locked_object b,
     dba_blockers    c,
     v$session       d
where a.sql_id=decode(d.sql_id,null,d.prev_sql_id,d.sql_id)
and   b.session_id=c.holding_session
and   c.holding_session=d.sid
and   d.sid not in (select distinct waiting_session from dba_waiters);

为什么会产生enq: CF - contention事件？我们先来看看enq: CF - contention等待事件是什么？
任何需要更新控制文件的动作都需要拿到CF锁，用于保证控制文件事务一致性，enq: CF - contention等待事件就是在多个会话同时更新控制文件时在获得CF锁的过程中发生的。根据oracle官方对该等待事件的描述，当nologging属性对象的DML操作并发时就会等待enq: CF – contention事件，而LOB字段上的UPDATE操作是一个典型的nologging模式的问题场景，该SQL是一个UPDATE操作，于是查看表上是否存在LOB字段，并查看LOB字段的logging属性：

desc NCISDBAU. plat_yyyyy_xxx
Name  Type
 ----- --------
POX    BLOB
GEX    BLOB
select table_name,SEGMENT_NAME,LOGGING from dba_lobs where table_name=' plat_yyyyy_xxx ' order by 2;
TABLE_NAME                     Segment                   LOGGING
------------------------------ ------------------------- -------
plat_yyyyy_xxx                 SYS_LOB12$$               NO
plat_yyyyy_xxx                 SYS_LOB13$$               NO

果然LOB字段的logging属性为nologging，中招了！
我们不禁会问：logging/nologging属性之间有什么区别，为什么会产生问题？LOB字段在创建时可以设置为logging/nologging属性，设置为logging时会把对LOB字段的修改记录到redo log中，定期由后台进程完成与控制文件同步，从而降低控制文件访问，避免大面积的CF-contention冲突。若设置为nologging，虽然可以减少产生redo log的日志量，但是oracle会在控制文件中记录不可恢复SCN号，需要拿到CF锁，如果有大量的DML并发，就会带来enq: CF-contention的问题。
而该系统LOB字段在设置为nologging属性的同时，对应表有大量DML操作，从而引起大量enq: CF-contention等待事件争用，导致了业务缓慢。因此，紧急将LOB字段设置为logging模式，之后等待事件消失了，系统恢复正常。
alter table NXXXXYYY. plat_yyyyy_xxx modify lob(PXXXYYY) (nocache logging);
小编在此提醒各位：您的LOB字段会不会有这样的隐患呢？不妨去检查一下nologging的LOB对象吧！

select owner,table_name,column_name
from dba_lobs
where logging ='NO'
and   PARTITIONED='NO'
and   owner='<USERNAME>'
UNION
select table_owner,table_name,column_name
from dba_lob_partitions
where logging='NO'
and table_owner='<USERNAME>'
UNION
select table_owner,table_name,column_name
from dba_lob_subpartitions
where logging='NO'
and table_owner='<USERNAME>';