ITPub博客

首页 > 数据库 > Oracle > control file sequential read等待事件

control file sequential read等待事件

Oracle 作者:东方友诚 时间:2015-11-05 09:14:47 0 删除 编辑


文章版权所有 Jusin Hao(luckyfriends) ,支持原创,转载请注明

control file sequential read等待事件

------20140715

1. 问题现象:

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

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

clip_image002[4]

2. 问题分析

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

clip_image004[4]

clip_image006[4]

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

clip_image008[4]

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>

clip_image010[4]

查看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

该表对应的表空间:

clip_image012[4]

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

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/erpsitclip_image013[6]/tech_st/11.2.0clip_image013[7]s/MISSING00022'

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

FND_FILE 中日志消息结束

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

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

clip_image015[4]

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

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;

clip_image017[4]

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/30036720/viewspace-1822895/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-11-24

  • 博文量
    144
  • 访问量
    257894