|
X$BH
|
Last time, by performing the UPDATE query to update the block of the table and the index,
I got UNDO block address and UNDO entry record number from the transaction entry to examine
the UNDO entry.
Uba of the transaction entry is different between the table and the index.
For example, if the block is not updated at all, there will be no transaction entry
as follows.
*************************************************************
[Itl of table block]
Itl Xid Uba Flag Lck Scn/Fsc
[Itl of index block]
Itl Xid Uba Flag Lck Scn/Fsc
*************************************************************
|
If I perform the UPDATE query, the block will have the transaction entry.
*************************************************************
[Itl of table block]
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.009.00029f0a 0x00800cf8.098c.02 ---- 2 fsc 0x0000.00000000
[Itl of index block]
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.009.00029f0a 0x00800ca4.098c.04 ---- 4 fsc 0x0000.00000000
| |[Last UNDO record number (hexadecimals)]
[DBA of UNDO block (hexadecimals)]
*************************************************************
|
I only examined the transaction entry in the table last time.
Actually, Uba is different between the table and the index because
it is necessary to get the UNDO entry per block not per transaction
in order to create the CR block.
The location of the last UNDO entry can be found in V$TRANSACTION view.
*************************************************************
SQL> select UBAFIL , UBABLK, UBAREC from v$transaction;
UBAFIL UBABLK UBAREC
---------- ---------- ----------
2 3488 1
*************************************************************
|
Next, I will examine the UNDO entry when I perform the INSERT query.
In Oracle9i, Automatic Undo Management was introduced, which made the maintenance
of the UNDO segment a lot easier. However, when you managed UNDO in Oracle8i or earlier,
you may have encountered the following error.
ORA-01628: max # extents reached for rollback segment num
We may possibly encounter the same error when we perform the INSERT query even though
INSERT query DOES NOT require pre-updated data (i.e. data that needs to be rolled back).
Now, I insert 30,000 of data and examine the number of UNDO blocks to be created.
4MB size of data is actually created.
*************************************************************
Insert 30000 data and view UNDO information in v$transaction.
SQL> insert into temp_work select * from temp
SQL> select USED_UBLK from v$transaction;
USED_UBLK
----------
1158
1158 blocks * 2KB(oracle block size) = 2MB!!
*************************************************************
|
In fact, most of the data are index-related.
I delete the index and perform the INSERT query again. Surprisingly, the data size
is reduced to one tenth.
*************************************************************
After deleting the index, insert 30000 data. View the UNDO
information in v$transaction.
USED_UBLK
----------
102
102 blocks * 2KB(oracle block size) = 200KB!!
*************************************************************
|
Even a single index has such a difference in the data size.
It can be easily imagined that multiple indexes will make a huge difference.
The reason why the data size becomes so huge is because the updated UNDO data
are also created in addition to the pre-updated UNDO data.
If index is not used, updated UNDO entries are not created.
You have to be careful about one thing. The INSERT statement above inserts
the result in a table TEMP all at once. If you keep performing the INSERT queries
per row as an example below, a single UNDO entry is created for a single record, and
as a result, large amount of UNDO entries are created.
*************************************************************
Use POPSQL to loop the insert process.
CONNECT scott/tiger <-Connect to Oracle
SAMPLE SELECT ID FROM TEMP2; <-Open the cursor
LOOP(X=0;X<\SEL_CNT\;X++) <-Loop for the number of rows fetched
SQL insert into temp values(\ID[X]\); <-INSERT statement
ENDLOOP
*************************************************************
|
That's it for today.
Yoshihiro Uratsuji
|
|
