Yaping's Weblog

August 30, 2008

V$TRANSACTION.USED_UREC

Filed under: Oracle — Yaping @ 4:20 am
Tags:

We know that v$transaction.USED_UREC records the transaction’s changed records, including table’s rows numbers and associated indexes’ entries numbers.
After test, there’re some puzzle on bitmap index I can’t make it clear.
All test tables/indexes use manual segment space management tablespace.

Oracle 9208
session 1
@>conn test/test
Connected.
@>drop table t1;
Table dropped.
@>create table t1(id number,id2 number);
Table created.
@>create index ind_t1_id on t1(id);
Index created.
@>insert into t1 values (1,1);
1 row created.

Now I expect there’re 2 records in v$transaction.USED_UREC.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
2

session 1
@>commit;
Commit complete.
@>update t1 set id=2;
1 row updated.

Indexes update includes one delete and one insert, so there should be 3 records.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
3

The above result accords with my expectation.
Now what will happen after bitmap index instead of B*Tree index?

session 1
@>drop table t1;
Table dropped.
@>create table t1(id number,id2 number);
Table created.
@>create bitmap index ind_t1_id2 on t1(id2);
Index created.
@>insert into t1 values (1,1);
1 row created.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
4

I expect it should be 2, but it is 4, where do the additional two records come from?

@>alter system checkpoint;
System altered.
@>@exts ind_t1_id2
OWNER EXTENT_ID FILE_ID BLOCK_ID BLOCKS
—————————— ———- ———- ———- ———-
TEST 0 3 17 8
@>@dumpf 3 17 18
PL/SQL procedure successfully completed.
@>
@>@trace
… …
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×000a.010.000000b8 0×0080009c.00d0.15 —- 2 fsc 0×0008.00000000
… …
row#0[8005] flag: —–, lock: 2
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 1; (1): 00
row#1[8026] flag: —D-, lock: 2
col 0; NULL
col 1; NULL
col 2; NULL
col 3; NULL
—– end of leaf block dump —–

There’s one delete entry and its value is NULL, how does it be there? Now there’s still one record not found where it comes from. I guess the another one record is insertion the NULL value, finally the NULL value is deleted.

session 1
@>commit;
Commit complete.
@>insert into t1 values (2,1);
1 row created.

Bitmap index entry update will delete old entry and recreate new one.
So there should be 3 records.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
3

session 1

@>commit;
Commit complete.
@>insert into t1 values (3,3);
1 row created.

Bitmap index entry changes will effect its near value index entry be locked, so I think there maybe be 3 records.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
3
@>alter system checkpoint;
System altered.
@>@exts ind_t1_id2
old 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=upper(’&1′)
new 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=upper(’ind_t1_id2′)
OWNER EXTENT_ID FILE_ID BLOCK_ID BLOCKS
—————————— ———- ———- ———- ———-
TEST 0 3 17 8
@>@dumpf 3 17 18
PL/SQL procedure successfully completed.
@>@trace
/opt/app/oracle/admin/chen/udump/chen_ora_2819.trc
… …
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×000a.029.000000b8 0×0080009c.00d0.10 —- 2 fsc 0×0000.00000000
… …
row#0[7983] flag: —–, lock: 2
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 2; (2): c8 03
row#1[7962] flag: —–, lock: 2
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 1; (1): 02
—– end of leaf block dump —–

From above dump data, we can find that indexed value 1, its entry is locked.

session 1
@>commit;
Commit complete.
@>update t1 set id2=0 where id=3;
1 row updated.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
5
@>
@>alter system checkpoint;
System altered.
@>
@>@dumpf 3 17 18
PL/SQL procedure successfully completed.
… …
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×0009.011.000000b5 0×00800090.0250.0c —- 3 fsc 0×001f.00000000
… …
row#0[7936] flag: —–, lock: 2
col 0; len 1; (1): 80
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 1; (1): 02
row#1[7983] flag: —–, lock: 0
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 2; (2): c8 03
row#2[7962] flag: —D-, lock: 2
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 1; (1): 02
row#3[7956] flag: —D-, lock: 2
col 0; NULL
col 1; NULL
col 2; NULL
col 3; NULL
—– end of leaf block dump —–

There’s still one record I can’t know it comes from.
Bitmap index management has changed very much in Oracle 10g. What will be different?

10g
session 1
test@TEST>drop table t1;
Table dropped.
test@TEST>
test@TEST>create table t1 (id number,id2 number);
Table created.
test@TEST>create bitmap index idx_t1_id2 on t1(id2);
Index created.
test@TEST>insert into t1 values (1,1);
1 row created.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
3
@>alter system checkpoint;
System altered.
@>@exts idx_t1_id2
old 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=upper(’&1′)
new 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=upper(’idx_t1_id2′)
OWNER EXTENT_ID FILE_ID BLOCK_ID BLOCKS
—————————— ———- ———- ———- ———-
TEST 0 5 49 8
sys@TEST>@dumpf 5 49 51
PL/SQL procedure successfully completed.
sys@TEST>@trace
/opt/app/oracle/admin/test/udump/test_ora_5304.trc
… …
Itl Xid Uba Flag Lck Scn/Fsc

0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×0002.007.00000374 0×00800072.0388.2f —- 1 fsc 0×0000.00000000
… …
row#0[7986] flag: ——, lock: 2, len=26
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 00 00 00 00 00
col 2; len 6; (6): 01 40 00 2a 00 07
col 3; len 6; (6): c0 aa 9e 80 a2 07
—– end of leaf block dump —–

From the above result, there’s one record in Oracle 10g less than in Oracle 9i.

Advertisements

1 Comment »

  1. Hello webmaster
    I would like to share with you a link to your site
    write me here preonrelt@mail.ru

    Comment by Alexwebmaster — March 3, 2009 @ 1:20 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: