oracle ITL TX MODE 4問題
SQL> conn xh/a831115
Connected.
SQL> create table t1 (a int,b varchar2(10)) pctfree 0 initrans 1;
Table created.
SQL> SQL> declare
2 begin
for i in 1..1000 loop
3 4 insert into t1 values(i,'a');
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats('xh','t1');
PL/SQL procedure successfully completed.
SQL> select distinct block# from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from t1);
BLOCK#
----------
3639
3640
SQL> select pct_free,pct_used,ini_trans from user_tables where table_name='T1';
PCT_FREE PCT_USED INI_TRANS
---------- ---------- ----------
0 1
SQL> update t1 set b='aa' where a=1;
1 row updated.
SQL> update t1 set b='aaa' where a=2;
1 row updated.
SQL> select distinct sid from v$mystat;
SID
----------
128
SQL> update t1 set b='aaaa' where a=3;
SQL> select event from v$session where sid=128;
EVENT
---------------------------------------------------------------- TX MODE =4
enq: TX - allocate ITL entry
SQL>
SQL> alter system dump datafile 4 block 3639;
System altered.
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/home/oracle/diag/rdbms/xh/xh/trace/xh_ora_4290.trc
seg/obj: 0x115e6 csc: 0x00.2fadfd itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000e31 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.020.00000339 0x00c00515.0272.6e C--- 0 scn 0x0000.002fad58
0x02 0x0004.00f.000002ec 0x00c013a2.027c.2b ---- 1 fsc 0x0000.00000000
bdba: 0x01000e37
SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('1000e37','xxxxxxxxxx'));
PL/SQL procedure successfully completed.
SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('1000e37','xxxxxxxxx'));
PL/SQL procedure successfully completed.
SQL> print file#
FILE#
----------
4
SQL> print blk#
BLK#
----------
3639
Flag :標記共4位c---表示已經提交,----表示未提交
flag:--u-表示使用者已經提交,之後 如果與該塊有關的事務再提交 則 這個itl flag變為c---,以前c---的變為----
關於塊中itl
SQL> create table t2 (a int, b int);
表已建立。
1 declare
2 begin
3 for i in 1..20 loop
4 insert into t2 values(i,i+1);
5 end loop;
6 commit;
7* end;
SQL> /
PL/SQL 過程已成功完成。
SQL> select distinct block# from (select dbms_rowid.rowid_relative_fno(rowid) fi
le#,dbms_rowid.rowid_block_number(rowid) block# from xh.t2);
BLOCK#
----------
2632
SQL>
SQL> update xh.t2 set b=23 where a=1;
已更新 1 行。
SQL> update xh.t2 set b=24 where a=2;
已更新 1 行。
SQL> update xh.t2 set b=25 where a=3;
已更新 1 行。
SQL> alter system dump datafile 4 block 2632;
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.002.000004b1 0x00800989.04b0.06 ---- 1 fsc 0x0000.00000000
0x02 0x0006.028.00000604 0x00800a20.03fc.26 ---- 1 fsc 0x0000.00000000
0x03 0x0008.00f.000005e5 0x00800093.058f.20 ---- 1 fsc 0x0000.00000000
3個不同的session更新 產生3個 事務 且都未提交oracle會自動擴充套件itl
SQL> select max_trans from all_tables where table_name='T2';
MAX_TRANS
----------
255
最多可以擴充套件到255個itl ,可以 擴充套件但會對效能有影響,可以建表時指定 多個 create table name (id number) initrans 3;
SQL> create table t2 (a int ) initrans 3;
表已建立。
SQL> insert into t2 values(1);
已建立 1 行。
SQL> commit;
提交完成。
SQL> select file#,block# from (select dbms_rowid.rowid_relative_fno(rowid) file#
,dbms_rowid.rowid_block_number(rowid) block# from t2);
FILE# BLOCK#
---------- ----------
1 64554
SQL> alter system dump datafile 1 block 64554;
系統已更改。
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.027.000005f5 0x00800aba.0388.34 --U- 1 fsc 0x0000.0053d9b9
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12020513/viewspace-671717/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 關於enq: TX - allocate ITL entry的問題分析ENQ
- 模擬mode=4 and name='TX'的enqueueENQ
- enq: TX - allocate ITL entryENQ
- TX:ITL LOCK(INITRANS,MAXINTRANS)
- enq: TX - allocate ITL entry等待事件分析ENQ事件
- 關於enq: TX - allocate ITL entry等待事件ENQ事件
- 排查 “Detected Tx Unit Hang”問題
- [20150721]enq TX - allocate ITL entryENQ
- oracle ITL槽Oracle
- 【MOS】 Troubleshooting waits for enq: TX - allocate ITL entry(1472175.1)AIENQ
- 【故障處理】佇列等待之TX - allocate ITL entry案例佇列
- Segments by ITL Waits 問題及解決AI
- [20231026]enq TX - allocate ITL entry的測試4.txtENQ
- ORACLE TX鎖Oracle
- 關於ITL以及UNDO SEGMENT HEADER 事物表(tx table)闡述Header
- Sql Mode及相關問題SQL
- Oracle初學者問題4(轉)Oracle
- enq: TX - allocate ITL entry等待過多導致全域性死鎖ENQ
- GES:Potential blocker on resource TX問題的處理BloC
- TX 面試題目面試題
- [20140130]關於enq TX-allocate ITL entryENQ
- oracle-tom-dmllock-txOracle
- 關於FILE_MODE值的問題,!!!!!
- oracle time modeOracle
- [20120224]itl的問題.txt
- 【故障處理】佇列等待之TX - allocate ITL entry引起的死鎖處理佇列
- Oracle TX鎖的處理Oracle
- oracle的TM鎖、TX鎖Oracle
- ORACLE LOCK MODE 1.2.3.4.5.6Oracle
- [20120601]ITL的問題補充.txt
- maven “Generating project in Batch mode“問題的解決MavenProjectBAT
- mysql 5.7sql_mode=only_full_group_by問題MySql
- Oracle ITL (Interested Transaction List) - 事務槽OracleREST
- oracle block資料塊itl小記OracleBloC
- 什麼是Interested Transaction List(ITL)--Oracle?RESTOracle
- 一個linux as4 U4 + oracle 10.2.0.3的問題LinuxOracle
- MySQL儲存過程中的sql_mode問題MySql儲存過程
- ITL