11GR2 Control file enqueue hold time tracking dump

如果你比较心细,可能在11.2的数据库中发现alert文件中存在存在类此下面的记录

Errors in file /oradb/diag/rdbms/offon/offon2/trace/offon2_ckpt_19660878.trc:

查看trace文件发现

*** 2012-08-01 03:36:03.520
  1: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117
  2: 890ms (rw) file: kcrf.c line: 10012 count: 6 total: 4266ms time: 1820928
  3: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116
  4: 530ms (rw) file: kcv.c line: 11783 count: 1 total: 530ms time: 3207607
Control file enqueue hold time tracking dump at time: 3376956

*** 2012-08-03 02:14:38.714
  1: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117
  2: 890ms (rw) file: kcrf.c line: 10012 count: 7 total: 4953ms time: 1820928
  3: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116
  4: 530ms (rw) file: kcv.c line: 11783 count: 1 total: 530ms time: 3207607
Control file enqueue hold time tracking dump at time: 3384212

这个类此我们在10g中看到的lgwr的警告类此,见Warning: log write time 560ms, size 3KB,其实也就是oracle对lgwr进程写入日志慢的时候的一个trace功能记录下来的.
我们这里遇到的是因为oracle对ckpt进程的trace,当control file enqueue holding time tracking size超过10的时候，就会记录到trace文件中,oracle 内部文档对于该问题的一些描述如下:

About the issue, this is the expected behavior on 11.2. 

New controlfile enqueue hold time tracking statistics have been added in 11.2 to 
aid diagnosis of controlfile transaction related performance related issues:

Control File Enqueue AWR Statistics:

* max cf enq hold time - The maximum amount of time in milliseconds a client has held the control file enqueue.
* total cf enq hold time - The total amount of time in milliseconds all clients have held the control file enqueue.
* total number of cf enq holders - The total number of times clients have held the control file enqueue.

Periodically, the CKPT process dumps statistics for the top N control file enqueue holders. 
N defaults to 10, but can be modified with the static hidden parameter:
_controlfile_enqueue_holding_time_tracking_size.The dump looks like the following:

Preface: "Control file enqueue hold time tracking dump at time: [relative time]".

* a. Time the client has held the control file enqueue.
* b. Type of client's control file enqueue transaction - rw or ro.
* c. File name where the client obtained control file enqueue.
* d. Line number where the client obtained control file enqueue.
* e. Number of times the client has held the control file enqueue since it became a member of the top N.
* f. Total time the client has held the control file in all those times from [e].
* g. Relative time the client obtained the control file enqueue from [a].

查询数据库默认值

SQL> col value for a24
SQL> col description for a70
SQL> set linesize 180
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3    where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _controlfile_enqueue_holding_time_tracking_size
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_controlfile_enqueue_holding_time_tracking_size%')

NAME                                               VALUE    DESCRIPTION
-------------------------------------------------- -------- ------------------------------------------------
_controlfile_enqueue_holding_time_tracking_size    10       control file enqueue holding time tracking size

虽然在alert日志中使用Error的形式显示该错误,但是这只是一个oracle作为诊断数据库Control File Enqueue性能的一个依据,大部分情况下,我们可以选择忽略或者关闭该诊断功能.屏蔽该提示方法如下:

The way to shut off is set _controlefile_enqueue_holding_time_tracking_size = 0 then restart the database

-- spfile
alter system set "_controlfile_enqueue_holding_time_tracking_size"=0 scope=spfile;

-- pfile
_controlfile_enqueue_holding_time_tracking_size=0

Restart database