control file sequential read等待事件

文章版权所有 Jusin Hao（luckyfriends） ，支持原创，转载请注明

control file sequential read等待事件

------20140715

1. 问题现象：

提交并发请求发现长时间无法完成，对应大请求要插入到JS_REVENUE_BALANCE_IFACES里大约100万数据（按批处理，每1万条提交一次）；

发现请求等时间为control file sequential read，执行如下insert 插入操作。

2. 问题分析

查看数据库对应进程cpu较高

SQL> select * from V$SESSION_WAIT a where a.sid='446';

SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO

--- -------------------- --------------------------

446 7902 control file sequential read file# 0 00 block# 93 000000000000005D blocks 1 0000000000000001 4108307767 9 System I/O -1 0 WAITED SHORT TIME 4 11741

select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'control file sequential read';

SQL>

查看alert日志

Thread 1 cannot allocate new log, sequence 145

Private strand flush not complete

Current log# 3 seq# 144 mem# 0: /data/erpsit/db/apps_st/data/log03a.dbf

Current log# 3 seq# 144 mem# 1: /data/erpsit/db/apps_st/data/log03b.dbf

Beginning log switch checkpoint up to RBA [0x91.2.10], SCN: 5981262270864

Thread 1 advanced to log sequence 145 (LGWR switch)

Current log# 1 seq# 145 mem# 0: /data/erpsit/db/apps_st/data/log01a.dbf

Current log# 1 seq# 145 mem# 1: /data/erpsit/db/apps_st/data/log01b.dbf

Tue Jul 15 16:28:46 2014

Completed checkpoint up to RBA [0x91.2.10], SCN: 5981262270864

Tue Jul 15 16:33:46 2014

Incremental checkpoint up to RBA [0x91.19e58.0], current log tail at RBA [0x91.3248d.0]

Tue Jul 15 16:35:38 2014

Thread 1 cannot allocate new log, sequence 146

Private strand flush not complete

Current log# 1 seq# 145 mem# 0: /data/erpsit/db/apps_st/data/log01a.dbf

Current log# 1 seq# 145 mem# 1: /data/erpsit/db/apps_st/data/log01b.dbf

Beginning log switch checkpoint up to RBA [0x92.2.10], SCN: 5981262442799

Thread 1 advanced to log sequence 146 (LGWR switch)

Current log# 2 seq# 146 mem# 0: /data/erpsit/db/apps_st/data/log02a.dbf

Current log# 2 seq# 146 mem# 1: /data/erpsit/db/apps_st/data/log02b.dbf

该表对应的表空间：

发现对应的数据文件并没有任何增长；

select * from dba_data_files a where a.tablespace_name='CUX_DATA' OR a.tablespace_name='CUX_INX';

输出0，表示没有插入任何数据。

终止并发请求。

3. 尝试解决

3.1. resize数据文件

alter database datafile 35 resize 20G;

修改自定义的并发请求的包，使其只插入一条数据；

发现并没有成功插入，如下输出0；

select * from dba_data_files a where a.tablespace_name='CUX_DATA' OR a.tablespace_name='CUX_INX';

查看并发请求输出和日志，有异常：

**Starts**15-07-2014 16:49:16

**Ends**15-07-2014 16:49:18

+---------------------------------------------------------------------------+

FND_FILE 中日志消息开始

+---------------------------------------------------------------------------+

**************异常,org_code100,period_name2013-12,item_number37ORA-00376: 此时无法读取文件 22

ORA-01111: 数据文件 22 名称未知 - 请重命名以更正文件

ORA-01110: 数据文件 22: '/data/erpsit/tech_st/11.2.0s/MISSING00022'

+---------------------------------------------------------------------------+

FND_FILE 中日志消息结束

+---------------------------------------------------------------------------+

该数据文件在clone的时候并没有恢复

因此怀疑是该表的索引创建到了这个表空间上：

create index JS_REVENUE_BALANCE_N1 onJS_REVENUE_BALANCE_IFACES

(PERIOD_NAME, ITEM_NUMBER)   tablespace CUX_INX

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    next 1M

    minextents 1

    maxextents unlimited

  );

3.2. 解决方法：

create index JS_REVENUE_BALANCE_N1 on JS_REVENUE_BALANCE_IFACES (PERIOD_NAME, ITEM_NUMBER) tablespace CUX_SMALL;

4. 模拟测试

4.1. 测试1

----创建测试表

create table t_hao1 as select * from dba_objects where 1<>1;

select * from t_hao1;

create index i_t_hao1 on t_hao1(owner) tablespace cux_inx;

表空间不存在，但是索引正常创建；

4.2. 测试2

drop index i_t_hao1

-- Create table

create table T_HAO2

(

OWNER VARCHAR2(30),

OBJECT_NAME VARCHAR2(128),

SUBOBJECT_NAME VARCHAR2(30),

OBJECT_ID NUMBER,

DATA_OBJECT_ID NUMBER,

OBJECT_TYPE VARCHAR2(19),

CREATED DATE,

LAST_DDL_TIME DATE,

TIMESTAMP VARCHAR2(19),

STATUS VARCHAR2(7),

TEMPORARY VARCHAR2(1),

GENERATED VARCHAR2(1),

SECONDARY VARCHAR2(1),

NAMESPACE NUMBER,

EDITION_NAME VARCHAR2(30)

)

tablespace cux_data;

pctfree 10

initrans 1

maxtrans 255;

发现插入数据的时候报错：

insert into t_hao2 select * from dba_objects where rownum <2;

http://www.eygle.com/archives/2011/11/oracle_io_tuning.html

http://blog.csdn.net/zq9017197/article/details/6929152

http://www.2cto.com/database/201110/107268.html

http://blog.csdn.net/47522341/article/details/3290919

http://book.51cto.com/art/201205/335465.htm

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14710393/viewspace-1219967/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14710393/viewspace-1219967/