實驗場景：

1 session 1 建立一個500000000萬行的表

2 session 1 建立索引在 id

3 session 2 update bigtab set object_name='ALIYUN';不提交

4 session 3 檢視 session 1 中被阻塞的table share lock 的 request

select a.*

from v$lock a,v$session b

where a.sid = b.sid and b.username ='YANG';

5 session 1 執行10704 ,10046 事件

alter session set events '10704 trace name context forever ,level 12';

alter session set events '10046 trace name context forever ,level 12';

重建索引 alter index bt_id_ind rebuiled online;

alter session set events '10704 trace name context off';

alter session set events '10046 trace name context off';

6 Session 2 提交前一個事務,使得rebuild online 過程繼續,並執行一個需要大量index欄位的更新操作.

--構造需要rebuild online在獲取下一次Table Share lock之前需要Merge的資料.

commit;

update bigtab set id = 1+id where rownum <=3e5;

commit;

==============================================================================

建立日誌表

*** 2011-02-21 21:30:51.684

ksqrcl: CU,73dc9cf0,1

ksqrcl: returns 0

=====================

PARSING IN CURSOR #6 len=41 dep=2 uid=0 ct=3 lid=0 '

=====================

PARSING IN CURSOR #12 len=159 dep=1 uid=0 ct=1 lid=0 tim=1298295051685451 hv=4130746007 ad='176c40a70'

sqlid='8yh6q4zv3cbnr'

create table "SYS"."SYS_JOURNAL_128845" (C0 NUMBER, opcode char(1), partno number, rid rowid, primary key( C0 , rid ))

organization index TABLESPACE "SYSTEM"

END OF STMT

註釋：索引 id_ind的object_id 是1F74D

表 bigtab的object_id 是1F74C；

--先獲取表bigtab的SS(Row-S) 行級共享鎖，其他物件只能查詢這些資料行。

*** 2011-02-21 21:30:51.671

ksqgtl *** TM-0001f74c-00000000 mode=2 flags=0x401 timeout=21474836 ***

ksqgtl: xcb=0x181849c90, ktcdix=2147483647, topxcb=0x181849c90

ktcipt(topxcb)=0x0

***

2011-02-21 21:30:51.671

ksucti: init session DID from txn DID:

ksqgtl:

ksqlkdid: 0001-0031-00000037

*** 2011-02-21 21:30:51.671

ksudidTrace: ksqgtl

ktcmydid(): 0001-0031-00000037

ksusesdi: 0001-0031-00000036

ksusetxn: 0001-0031-00000037

*** 2011-02-21 21:30:51.671

ksqcmi: TM,1f74c,0 mode=2 timeout=21474836

ksqcmi: returns 0

ksqgtl:

RETURNS 0

EXEC #18:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1298295051671730

CLOSE #18:c=0,e=5,dep=1,type=0,tim=1298295051671759

---對於原索引 id_ind

*** 2011-02-21 21:30:51.674

ksqgtl *** OD-0001f74d-00000000 mode=6 flags=0x10401 timeout=0 ***

ksqgtl: xcb=0x181849c90, ktcdix=2147483647, topxcb=0x181849c90

ktcipt(topxcb)=0x0

*** 2011-02-21 21:30:51.674

ksucti: init session DID from txn DID:

ksqgtl:

ksqlkdid: 0001-0031-00000037

*** 2011-02-21 21:30:51.674

*** ksudidTrace: ksqgtl

ktcmydid(): 0001-0031-00000037

ksusesdi: 0001-0031-00000036

ksusetxn: 0001-0031-00000037

*** 2011-02-21 21:30:51.674

ksqcmi: OD,1f74d,0 mode=6 timeout=0

WAIT #19: nam='ges message buffer allocation' ela= 4 pool=0 request=1 allocated=0 obj#=-1 tim=1298295051674185

WAIT #19: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=-1 tim=1298295051674249

WAIT #19: nam='enq: OD - Serializing DDLs' ela= 401 name|mode=1329856518 object #=128845 0=0 obj#=-1 tim=1298295051674693

ksqcmi: returns 0

ksqgtl: RETURNS 0

從上面的內容看，對原來的索引ID_IND 申請了mode 為6的鎖！Serializing DDLs 意思是 序列ddl，此時禁止對ID_IND 進行ddl操作。

*** 2011-02-21 21:30:51.675

ksqgtl *** DL-0001f74c-00000000 mode=3 flags=0x10011 timeout=0 ***

ksqgtl: xcb=0x181849c90, ktcdix=2147483647, topxcb=0x181849c90

ktcipt(topxcb)=0x0

*** 2011-02-21 21:30:51.675

ksucti: init session DID from txn DID:

ksqgtl:

ksqlkdid: 0001-0031-00000037

*** 2011-02-21 21:30:51.675

*** ksudidTrace: ksqgtl

ktcmydid(): 0001-0031-00000037

ksusesdi: 0001-0031-00000036

ksusetxn: 0001-0031-00000037

*** 2011-02-21 21:30:51.675

ksqcmi: DL,1f74c,0 mode=3 timeout=0

ksqcmi: returns 0

ksqgtl: RETURNS 0

*** 2011-02-21 21:30:51.675

ksqgtl *** DL-0001f74c-00000000 mode=3 flags=0x10011 timeout=0 ***

ksqgtl: xcb=0x181849c90, ktcdix=2147483647, topxcb=0x181849c90

ktcipt(topxcb)=0x0

*** 2011-02-21 21:30:51.675

ksucti: init session DID from txn DID:

ksqgtl:

ksqlkdid: 0001-0031-00000037

*** 2011-02-21 21:30:51.675

*** ksudidTrace: ksqgtl

ktcmydid(): 0001-0031-00000037

ksusesdi: 0001-0031-00000036

ksusetxn: 0001-0031-00000037

*** 2011-02-21 21:30:51.675

ksqcmi: DL,1f74c,0 mode=3 timeout=0

ksqcmi: returns 0

ksqgtl: RETURNS 0

=====================

PARSING IN CURSOR #19 len=33 dep=0 uid=0 ct=9 lid=0 tim=1298295051675479 hv=3555973629 ad='176c3d790' sqlid='ca0sk839z7qgx'

alter index id_ind rebuild online 線上重建索引操作

END OF STMT

PARSE #19:c=8998,e=9153,p=0,cr=18,cu=0,mis=1,r=0,dep=0,og=1,plh=3822449708,tim=1298295051675478

=====================

PARSING IN CURSOR #15 len=5676 dep=1 uid=57 ct=47 lid=57 tim=1298295051675734 hv=3231638731 ad='176ec6710' sqlid='8cc6ny309xt6b'

declare

TYPE attrs_cur IS REF CURSOR;

m_cur attrs_cur;

m_event varchar2(512);

m_user varchar2(512);

m_owner varchar2(512);

m_user1 varchar2(512);

m_type varchar2(512);

m_stmt varchar2(512);

m_name varchar2(5120);

m_column varchar2(5120);

m_cnt NUMBER;

m_stmt1 varchar2(512);

m_var varchar2(512);

m_o_stmt VARCHAR2(5120);

PRAGMA AUTONOMOUS_TRANSACTION;

begin

m_stmt:='select sys.dbms_standard.dictionary_obj_type from dual';

execute immediate m_stmt into m_type;

if(not (m_type='TABLE' or m_type='TRIGGER' or m_type='USER'))

then

return;

end if;

m_stmt:='select sys.dbms_standard.sysevent from dual';

execute immediate m_stmt into m_event;

m_stmt:='select SYS_CONTEXT(''USERENV'',''SESSION_USER'') from dual';

execute immediate m_stmt into m_user;

m_stmt:='select SYS_CONTEXT(''USERENV'',''CURRENT_USER'') from dual';

execute immediate m_stmt into m_user1;

m_stmt:='select sys.dbms_standard.dictionary_obj_owner from dual';

execute immediate m_stmt into m_owner;

m_stmt:='select sys.dbms_standard.dictionary_obj_name from dual';

execute immediate m_stmt into m_name;

m_stmt:='select sdo_geor_def.getSqlText from dual';

execute immediate m_stmt into m_o_stmt;

if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='ALTER')

then

m_stmt:='select column_name from dba_tab_columns where wner=:1 and table_name=:2';

open m_cur for m_stmt using m_owner,m_name;

loop

fetch m_cur into m_column;

exit when m_cur%NOTFOUND;

m_stmt:='select sdo_geor_def.isDropColumn(:1) from dual';

execute immediate m_stmt into m_stmt1 using SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);

if (trim(m_stmt1)='TRUE')

then

m_stmt:='begin sdo_geor_def.doAlterDropColumn(:1,:2,:3); end;';

execute immediate m_stmt using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);

end if;

end loop;

end if;

if (m_event='DROP' and m_type='USER')

then

m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';

EXECUTE IMMEDIATE m_stmt;

commit;

end if;

if (m_event='DROP' and m_type='TABLE')

then

m_stmt:='select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2';

EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name;

if(m_cnt!=0)

then

m_stmt:='select count(*) from sdo_geor_ddl__table$$';

EXECUTE IMMEDIATE m_stmt into m_cnt;

if((m_cnt=0)and (m_user!='SYS' and m_user!='SYSTEM' and m_user!='MDSYS'and m_owner!='MDSYS' and m_owner!='SYS'))

then

m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''The referenced raster data table(RDT) cannot be dropped.'')';

execute immediate m_stmt;

end if;

m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';

EXECUTE IMMEDIATE m_stmt;

commit;

end if;

if(m_user='SYS' or m_user='SYSTEM' or m_user='MDSYS'

or m_owner='MDSYS' or m_owner='SYS')

then

return;

end if;

if (m_event='RENAME' and m_type='TABLE')

then

m_stmt:='select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2';

EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name;

if(m_cnt!=0)

then

m_stmt:='select count(*) from sdo_geor_ddl__table$$';

EXECUTE IMMEDIATE m_stmt into m_cnt;

if(m_cnt=0)

then

m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''The referenced raster data table(RDT) cannot be renamed directly.'')';

execute immediate m_stmt;

end if;

if (m_type='TRIGGER' and m_event='DROP')

then

m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual';

EXECUTE IMMEDIATE m_stmt into m_var using m_name;

if(m_var is null)

then

return;

end if;

m_stmt:='select count(*) from sdo_geor_ddl__table$$';

EXECUTE IMMEDIATE m_stmt into m_cnt;

if(m_cnt=0)

then

m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''GeoRaster DML triggers cannot be dropped.'')';

execute immediate m_stmt;

end if;

if (m_type='TRIGGER' and m_event='ALTER')

then

m_o_stmt:=upper(trim(m_o_stmt));

if(instr(m_o_stmt,' COMPILE ')>0 or instr(m_o_stmt,' ENABLE ')>0

or substr(m_o_stmt,length(m_o_stmt)-8,8)=' COMPILE' or substr(m_o_stmt,length(m_o_stmt)-7,7)=' ENABLE' )

then

return;

end if;

m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual';

EXECUTE IMMEDIATE m_stmt into m_var using m_name;

if(m_var is null)

then

return;

end if;

m_stmt:='select count(*) from sdo_geor_ddl__table$$';

EXECUTE IMMEDIATE m_stmt into m_cnt;

if(m_cnt=0)

then

m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''GeoRaster DML triggers cannot be altered.'')';

execute immediate m_stmt;

end if;

if (m_type='TRIGGER' and m_event='CREATE')

then

m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual';

EXECUTE IMMEDIATE m_stmt into m_var using m_name;

if(m_var is null)

then

return;

end if;

m_stmt:='select count(*) from sdo_geor_ddl__table$$';

EXECUTE IMMEDIATE m_stmt into m_cnt;

if(m_cnt=0)

then

m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''GeoRaster reserved names cannot be used to create regular triggers.'')';

execute immediate m_stmt;

end if;

Exception

when others then

if(sqlcode=-13391)

then

m_stmt:=sqlerrm;

m_stmt:=substr(m_stmt,11);

m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391,'''||m_stmt||''')';

execute immediate m_stmt;

end if;

end;

END OF STMT

PARSE #15:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=1298295051675732

=========================================================

PARSING IN CURSOR #18 len=214 dep=2 uid=0 ct=9 lid=0 tim=1298295051720031 hv=2123239887 ad='176c92630' sqlid='2z2p7ttz8w4fg'

CREATE UNIQUE INDEX "SYS"."SYS_IOT_TOP_128847"

on "SYS"."SYS_JOURNAL_128845"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE "SYSTEM"

STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) NOPARALLEL

END OF STMT

PARSE #18:c=1000,e=1264,p=0,cr=4,cu=0,mis=1,r=0,dep=2,og=4,plh=3858823975,tim=1298295051720030

請求表bigtab上的Share鎖.

*** 2011-02-21 21:30:51.678

ksqcmi: OD,1f74c,0 mode=4 timeout=0

WAIT #19: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1298295051678518

WAIT #19: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=-1 tim=1298295051678568

WAIT #19: nam='enq: OD - Serializing DDLs' ela= 378 name|mode=1329856516 object #=128844 0=0 obj#=-1 tim=1298295051678974

ksqcmi: returns 0

ksqgtl: RETURNS 0

WAIT #19: nam='DFS lock handle' ela= 1138 type|mode=1230372869 id1=1280262987 id2=26 obj#=-1 tim=1298295051680513

WAIT #19: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1298295051680710

WAIT #19: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=-1 tim=1298295051680746

WAIT #19: nam='DFS lock handle' ela= 545 type|mode=1230372869 id1=1398361667 id2=26 obj#=-1 tim=1298295051681315

讀取bigtab ，建立索引

WAIT #19: nam='db file scattered read' ela= 654 file#=1 block#=95593 blocks=7 obj#=128844 tim=1298295058804714

WAIT #19: nam='db file scattered read' ela= 521 file#=1 block#=95600 blocks=8 obj#=128844 tim=1298295058806050

WAIT #19: nam='db file scattered read' ela= 384 file#=1 block#=95608 blocks=4 obj#=128844 tim=1298295058807067

WAIT #19: nam='db file scattered read' ela= 351 file#=1 block#=95613 blocks=3 obj#=128844 tim=1298295058808071

WAIT #19: nam='db file scattered read' ela= 400 file#=1 block#=98432 blocks=4 obj#=128844 tim=1298295058808649

WAIT #19: nam='db file scattered read' ela= 368 file#=1 block#=98437 blocks=3 obj#=128844 tim=1298295058809212

WAIT #19: nam='db file scattered read' ela= 321 file#=1 block#=98440 blocks=2 obj#=128844 tim=1298295058809697

WAIT #19: nam='db file scattered read' ela= 363 file#=1 block#=98445 blocks=3 obj#=128844 tim=1298295058810659

WAIT #19: nam='db file scattered read' ela= 336 file#=1 block#=98448 blocks=2 obj#=128844 tim=1298295058811147

WAIT #19: nam='db file scattered read' ela= 364 file#=1 block#=98453 blocks=3 obj#=128844 tim=1298295058812138

WAIT #19: nam='db file scattered read' ela= 329 file#=1 block#=98456 blocks=2 obj#=128844 tim=1298295058812631

WAIT #19: nam='db file scattered read' ela= 354 file#=1 block#=98461 blocks=3 obj#=128844 tim=1298295058813586

===========================================================

WAIT #19: nam='ges message buffer allocation' ela= 4 pool=1 request=1 allocated=0 obj#=-1 tim=1298295058803591

WAIT #19: nam='gc cr multi block request' ela= 304 file#=1 block#=95599 class#=1 obj#=128844 tim=1298295058803978

WAIT #19: nam='db file scattered read' ela= 654 file#=1 block#=95593 blocks=7 obj#=128844 tim=1298295058804714

WAIT #19: nam='ges message buffer allocation' ela= 2 pool=1 request=1 allocated=0 obj#=128844 tim=1298295058804976

WAIT #19: nam='gc cr multi block request' ela= 12 file#=1 block#=95607 class#=1 obj#=128844 tim=1298295058805060

WAIT #19: nam='gc cr multi block request' ela= 386 file#=1 block#=95607 class#=1 obj#=128844 tim=1298295058805463

WAIT #19: nam='db file scattered read' ela= 521 file#=1 block#=95600 blocks=8 obj#=128844 tim=1298295058806050

WAIT #19: nam='ges message buffer allocation' ela= 2 pool=1 request=1 allocated=0 obj#=128844 tim=1298295058806294

索引排序，並寫入 temp，此過程中有大量的非同步io出現。

WAIT #19: nam='kfk: async disk IO' ela= 4 count=1 intr=0 timeout=4294967295 obj#=128844 tim=1298295066232809

WAIT #19: nam='kfk: async disk IO' ela= 3 count=1 intr=0 timeout=4294967295 obj#=128844 tim=1298295066232841

WAIT #19: nam='direct path write temp' ela= 3889 file number=203 first dba=256 block cnt=31 obj#=128844 tim=1298295066221604

...............

WAIT #19: nam='direct path write temp' ela= 3159 file number=203 first dba=628 block cnt=31 obj#=128844 tim=1298295066240741

WAIT #19: nam='direct path write temp' ela= 719 file number=203 first dba=690 block cnt=31 obj#=128844 tim=1298295066243167

WAIT #19: nam='direct path write temp' ela= 2392 file number=203 first dba=752 block cnt=31 obj#=128844 tim=1298295066247079

繼續讀基表，建立索引

WAIT #19: nam='db file scattered read' ela= 376 file#=1 block#=98624 blocks=4 obj#=128844 tim=1298295058830656

WAIT #19: nam='db file scattered read' ela= 334 file#=1 block#=98629 blocks=2 obj#=128844 tim=1298295058831152

WAIT #19: nam='db file scattered read' ela= 652 file#=1 block#=98632 blocks=6 obj#=128844 tim=1298295058831989

.........

WAIT #19: nam='db file scattered read' ela= 348 file#=1 block#=98661 blocks=2 obj#=128844 tim=1298295058835458

WAIT #19: nam='db file scattered read' ela= 456 file#=1 block#=98664 blocks=6 obj#=128844 tim=1298295058836102

WAIT #19: nam='db file scattered read' ela= 480 file#=1 block#=98672 blocks=7 obj#=128844 tim=1298295058836865

WAIT #19: nam='db file scattered read' ela= 421 file#=1 block#=98682 blocks=5 obj#=128844 tim=1298295058838026

從temp檔案裡讀取排好順序的索引：

WAIT #19: nam='direct path read temp' ela= 513 file number=203 first dba=256 block cnt=7 obj#=128844 tim=1298295120553800

WAIT #19: nam='direct path read temp' ela= 689 file number=203 first dba=270 block cnt=1 obj#=128845 tim=1298295120565252

............

WAIT #19: nam='direct path read temp' ela= 1014 file number=203 first dba=336 block cnt=1 obj#=128845 tim=1298295120599464

WAIT #19: nam='direct path read temp' ela= 1086 file number=203 first dba=337 block cnt=7 obj#=128845 tim=1298295120600841

=====寫入索引段，擴充索引段

select file# from file$ where ts#=:1

select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3

insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapranges) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0,NULL,:17),:18,:19)

=====移動高水位，對索引段進行擴充套件

*** 2011-02-21 21:32:00.572

ksqcmi: HW,0,4d10e0 mode=6 timeout=21474836

WAIT #19: nam='ges message buffer allocation' ela= 3 pool=0 request=1 allocated=0 obj#=-1 tim=1298295120572685

WAIT #19: nam='enq: HW - contention' ela= 294 name|mode=1213661190 table space #=0 block=5050592 obj#=-1 tim=1298295120573024

ksqcmi: returns 0

ksqgtl: RETURNS 0

WAIT #19: nam='row cache lock' ela= 99 cache id=2 mode=0 request=5 obj#=-1 tim=1298295120573189

========結束排序輸出，direct path write 寫入索引

WAIT #19: nam='kfk: async disk IO' ela= 2 count=1 intr=0 timeout=4294967295 obj#=128845 tim=1298295173598667

WAIT #19: nam='asynch descriptor resize' ela= 2 outstanding #aio=3 current aio limit=2016 new aio limit=2112 obj#=128845 tim=1298295173599331

WAIT #19: nam='kfk: async disk IO' ela= 8 count=1 intr=0 timeout=4294967295 obj#=128845 tim=1298295173599605

WAIT #19: nam='kfk: async disk IO' ela= 3 count=1 intr=0 timeout=4294967295 obj#=128845 tim=1298295173599633

WAIT #19: nam='kfk: async disk IO' ela= 4 count=1 intr=0 timeout=4294967295 obj#=128845 tim=1298295173599751

WAIT #19: nam='direct path write' ela= 1377 file number=1 first dba=977532 block cnt=4 obj#=128845 tim=1298295173601152

WAIT #19: nam='kfk: async disk IO' ela= 6 count=1 intr=0 timeout=4294967295 obj#=128845 tim=1298295173601193

=====這裡是猜測是更新資料庫字典的關於索引的相關資訊

update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,flags=:7,property=:8,pctfree$=:9,initrans=:10,maxtrans=:11,blevel=:12,leafcnt=:13,distkey=:14,lblkkey=:15,dblkkey=:16,clufac=:17,cols=:18,analyzetime=:19,samplesize=:20,dataobj#=:21,degree=decode(:22,1,null,:22),instances=decode(:23,1,null,:23),rowcnt=:24,pctthres$=:31*256+:25, indmethod#=:26, trunccnt=:27,spare4=:29,spare2=:30,spare6=:32where obj#=:1

====

UPDATE ind_online$ SET flags=:2 WHERE obj#=:1

====更新索引段資訊

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3

END OF STMT

=====

WAIT #19: nam='db file sequential read' ela= 347 file#=1 block#=858659 blocks=1 obj#=128845 tim=1298295327247530

WAIT #19: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=128845 tim=1298295327247638

WAIT #19: nam='gc current grant 2-way' ela= 398 p1=1 p2=856290 p3=33554433 obj#=128845 tim=1298295327248085

WAIT #19: nam='db file sequential read' ela= 343 file#=1 block#=856290 blocks=1 obj#=128845 tim=1298295327248479

==在session 3中檢視：sort output 結束

SID SERIAL# TARGET SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME

195 105 Sort Output 147493 147493 Blocks Feb 21 2011 21:31:59 Feb 21 2011 21:32:53

===讀取Journal表上的變更,,將變更Merge到新的索引上.

--從10046 的traced Event的角度看,新的索引檔案寫完成,開始讀取Journal表的內容,以merge新索引.

WAIT #6: nam='kfk: async disk IO' ela= 3 count=1 intr=0 timeout=4294967295 obj#=-1 tim=1298296248201542

WAIT #6: nam='direct path write' ela= 346 file number=2 first dba=241938 block cnt=1 obj#=-1 tim=1298296248201913

WAIT #6: nam='asynch descriptor resize' ela= 1 outstanding #aio=0 current aio limit=2208 new aio limit=2160 obj#=-1 tim=1298296248201936

*** 2011-02-21 21:50:48.202

ksqgtl *** TX-00070007-00060f35 mode=6 flags=0x481 timeout=0 ***

WAIT #6: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1298296248202223

WAIT #6: nam='gc current grant 2-way' ela= 254 p1=2 p2=306 p3=16777220 obj#=212 tim=1298296248202528

WAIT #6: nam='db file sequential read' ela= 320 file#=2 block#=306 blocks=1 obj#=212 tim=1298296248202902

WAIT #6: nam='db file sequential read' ela= 306 file#=2 block#=305 blocks=1 obj#=212 tim=1298296248203536WAIT #6: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=212 tim=1298296248203623

--結束新索引的Merge工作.

WAIT #13: nam='db file sequential read' ela= 291 file#=2 block#=127450 blocks=1 obj#=206 tim=1298296143507248

WAIT #13: nam='db file sequential read' ela= 305 file#=2 block#=356348 blocks=1 obj#=206 tim=1298296143507884

WAIT #18: nam='db file sequential read' ela= 339 file#=2 block#=241935 blocks=1 obj#=208 tim=1298296143512189

WAIT #19: nam='db file sequential read' ela= 576 file#=2 block#=356351 blocks=1 obj#=206 tim=1298296248188493

====刪除日誌表

drop table "SYS"."SYS_JOURNAL_128845" purge

====更新物件

update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null

END OF STMT

====最後更新物件segment

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3

END OF STMT

使用 grep 抓取的關於 bigtab (object_id =1f74c)，id_ind (object_id=1f74d) 鎖的情況~！從這裡可以看出在索引重建過程中對於表 bigtab 加的鎖的情況。

存在事務對索引欄位進行更新時，最高是mode為 4的鎖。

oracle@rac1:rac1 /home/oracle>more /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_11801.trc | grep 1f74d | more

ksqgtl *** OD-0001f74d-00000000 mode=6 flags=0x10401 timeout=0 ***

ksqcmi: OD,1f74d,0 mode=6 timeout=0

ksqrcl: OD,1f74d,0

ksqcmi: OD,1f74d,0 mode=0 timeout=0

oracle@rac1:rac1 /home/oracle>more /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_11801.trc | grep 1f74c | more

ksqgtl *** TM-0001f74c-00000000 mode=2 flags=0x401 timeout=21474836 ***

ksqcmi: TM,1f74c,0 mode=2 timeout=21474836

ksqgtl *** DL-0001f74c-00000000 mode=3 flags=0x10011 timeout=0 ***

ksqcmi: DL,1f74c,0 mode=3 timeout=0

ksqgtl *** DL-0001f74c-00000000 mode=3 flags=0x10011 timeout=0 ***

ksqcmi: DL,1f74c,0 mode=3 timeout=0

ksqgtl *** OD-0001f74c-00000000 mode=4 flags=0x10401 timeout=0 ***

ksqcmi: OD,1f74c,0 mode=4 timeout=0

ksqrcl: DL,1f74c,0

ksqcmi: DL,1f74c,0 mode=0 timeout=0

ksqrcl: DL,1f74c,0

ksqcmi: DL,1f74c,0 mode=0 timeout=0

ksqrcl: OD,1f74c,0

ksqcmi: OD,1f74c,0 mode=0 timeout=0

ksqrcl: TM,1f74c,0

ksqcmi: TM,1f74c,0 mode=0 timeout=0

使用10046事件 +10704事件對索引線上重建的跟蹤

相關文章