sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124原因分析

时间:2016-07-09 22:17   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 运维DBA反映在巡检Oracle数据库时发现ckpt进程的trace文件出现类似类此9430ms (rw) file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124原因分析

1.ckpt进程的trace文件中报如下错误

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /oradb/11.2.0/db
System name:	AIX
Node name:	offondb2
Release:	1
Version:	6
Machine:	00CC83664C00
Instance name: offon2
Redo thread mounted by this instance: 2
Oracle process number: 20
Unix process pid: 19660878, image: oracle@offondb2 (CKPT)


*** 2012-10-26 13:25:00.971
  1: 9430ms (rw) file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124
  2: 1777ms (rw) file: krse.c line: 1800 count: 164 total: 122140ms time: 364176
  3: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117
  4: 1230ms (rw) file: kcrf.c line: 10012 count: 135 total: 90995ms time: 2630737
  5: 1173ms (ro) file: kcrr.c line: 3525 count: 13 total: 8842ms time: 3645916
  6: 890ms (rw) file: kcrf.c line: 9959 count: 8 total: 4812ms time: 3578222
  7: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116
  8: 677ms (rw) file: kcv.c line: 11783 count: 8 total: 4499ms time: 416869
Control file enqueue hold time tracking dump at time: 2092019

*** 2012-10-26 15:25:14.789
  1: 9430ms (rw) file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124
  2: 1777ms (rw) file: krse.c line: 1800 count: 165 total: 122832ms time: 364176
  3: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117
  4: 1230ms (rw) file: kcrf.c line: 10012 count: 135 total: 90995ms time: 2630737
  5: 1173ms (ro) file: kcrr.c line: 3525 count: 13 total: 8842ms time: 3645916
  6: 890ms (rw) file: kcrf.c line: 9959 count: 8 total: 4812ms time: 3578222
  7: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116
  8: 677ms (rw) file: kcv.c line: 11783 count: 8 total: 4499ms time: 416869

2.原因分析并解决

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

To disable the trace set _controlfile_enqueue_holding_time_tracking_size to 0:

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

- pfile
_controlfile_enqueue_holding_time_tracking_size=0

A database restart is required.

查询_controlfile_enqueue_holding_time_tracking_size

SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> set linesize 150
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('%¶m%')
  7  order by name
  8  /
Enter value for param: _controlfile_enqueue_holding_time_tracking_size
old   6:    and upper(a.ksppinm) LIKE upper('%¶m%')
new   6:    and upper(a.ksppinm) LIKE upper('%_controlfile_enqueue_holding_time_tracking_size%')

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

3.补充MOS说明[791417.1]

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].

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124原因分析

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/773.html

Oracle研究中心

关键词:

ckpt进程的trace文件9430ms (rw) file报错

file: kct.c line: 7800 count: 13 total: 26874ms time