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/，如需轉載，請註明出處，否則將追究法律責任。