ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用10046事件 +10704事件对索引在线重建的跟踪

使用10046事件 +10704事件对索引在线重建的跟踪

原创 Linux操作系统 作者:杨奇龙 时间:2011-02-24 21:54:46 0 删除 编辑

实验场景:

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_indobject_id 1F74D  

          bigtabobject_id 1F74C

--先获取表bigtabSS(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;

   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;

   end if;

 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;

 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;

 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;

 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

 

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

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7701973