使用10046事件 +10704事件对索引在线重建的跟踪
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!=MDSYSand 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
记一次线上频繁FGC的事件和解决方式 1.大量的请求,调用的地方要注意是否会导致内存的大量消耗,尽可能使用池化技术,单例等,减少创建,销毁的系统开销;2.CMS 的几个缺点,可以参考《深入java虚拟机》,对CPU占用会比较高,无法处理浮动垃圾,还有就是CMS使用的是标记-清除算法,会导致大量的空间碎片,碎片过多的话,导致分配大对象很困难,所以不得不进行FGC,也可能是这个原因导致了本文说的一直FGC的问题。
BSTestRunner增加历史执行记录展示和重试功能 之前对于用例的失败重试,和用例的历史测试记录存储展示做了很多的描述呢,但是都是基于各个项目呢,不方便使用,为了更好的使用,我们对这里进行抽离,抽离出来一个单独的模块,集成到BSTestRunner中,以后我们使用BSTestRunner直接就可以使用里面的失败重试和展示历史记录了。
相关文章
- ios事件-触摸事件2(手势 和 pointInSide()、hitTest()、touchesBegan()、touchesMoved()、touchesEnded()的关系)
- 9.26VR行业大事件:苹果或取消10月发布会;Rec Room联合Unity推出PC端创作工具
- 9.28VR行业大事件:E3 2023将于6月13日-16日在洛杉矶举办;Nreal Air登陆美国亚马逊
- 10.19VR行业大事件:AR眼镜智能终端Rokid Station发布;李未可发布AR眼镜Meta Lens
- vue源码分析-事件机制
- 选中和取消选中事件on
- 【IOC 控制反转】Android 事件依赖注入 ( 事件依赖注入具体的操作细节 | 获取 Activity 中的所有方法 | 获取方法上的注解 | 获取注解上的注解 | 通过注解属性获取事件信息 )
- Oracle 等待事件 Redo Transport Ping 官方解释,作用,如何使用及优化方法
- 解锁 Redis 事件监听之旅(redis事件监听)
- JS页面事件(非常详细)
- 事件视界望远镜拍摄超大质量黑洞喷射物特写图像
- jquery事件对象属性小结
- (跨浏览器基础事件/浏览器检测/判断浏览器)经验代码分享
- javascript中注册和移除事件的4种方式
- js导航栏单击事件背景变换示例代码
- ASP.NET页面按钮单击事件失效的解决方法
- 在RowCommand事件中获取索引值示例代码
- ZendFramework2.0事件管理器(TheEventManager)入门教程
- C#中委托和事件的区别实例解析
- C#控制台程序中处理2个关闭事件的代码实例