文章版权所有 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/