| Sequence引起的row cache lock | DBA之路：OoNiceDream |

Sequence引起的row cache lock

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/01/sequence-row-cache-lock.html

检查数据库发现有不少的row cache lock：

SYS@DB1> select Inst_id, event, username, sql_hash_value, count(*)
  2    from gv$session
  3   where Wait_class <> 'Idle'
  4   group by Inst_id, event, username, sql_hash_value
  5   order by 5;
 
INST_ID EVENT                                   USERNAME         SQL_HASH_VALUE   COUNT(*)
------- --------------------------------------- --------------- --------------- ----------
      1 latch free                              USER                 2266733057          1
      1 direct path write                       USER                 3348906585          1
      1 log file parallel write                                               0          1
      1 SQL*Net message to client               SYS                  2334880858          1
      1 log file sync                           USER                          0          1
      1 latch free                              USER                 3115510059          4
      1 latch: cache buffers chains             USER                 2266733057          5
      1 row cache lock                          USER                   24588433         22

查看相关语句信息，发现是Sequence的cache为０引起的：

SYS@DB1> @sql_hash
输入 hash_value 的值:  24588433
 
SQL_TEXT
---------------------------------------
select NAME_SEQ.nextval from dual      
 
SYS@DB1> select SEQUENCE_OWNER,SEQUENCE_NAME,cache_size
  2  from dba_sequences where sequence_name='NAME_SEQ';
 
SEQUENCE_OWNER       SEQUENCE_NAME        CACHE_SIZE
-------------------- -------------------- ----------
ISPP                 NAME_SEQ                  0
SYS@DB1> alter sequence ispp.NAME_SEQ cache 200;
SYS@DB1> select SEQUENCE_OWNER,SEQUENCE_NAME,cache_size 
  2  from dba_sequences where sequence_name='NAME_SEQ';
 
SEQUENCE_OWNER       SEQUENCE_NAME        CACHE_SIZE
-------------------- -------------------- ----------
ISPP                 NAME_SEQ                200

更改cache大小后，目前未观察到类似问题。

文档中关于CACHE描述：

CACHE
Specify how many values of the sequence Oracle preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:

(CEIL (MAXVALUE – MINVALUE)) / ABS (INCREMENT)

If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

— The End —

关键字: 故障案例