Wednesday, January 19, 2011

More on Interested Transaction Lists

How Oracle Locking Works
When a Lock is NOT a Lock!

In the last installment of this series "100 Thing you Probably Didn't Know About Oracle" you learned how Oracle locks the rows of a table. Here is what you learned in a nutshell:

(1) When a transaction modifies a record, the pre-change image is stored in the undo segments, which is required for various things; the most important of which is to provide a read consistent version of the row when another session wants it.

(2) The transaction is assigned a transaction identifier that shows the undo segment number, slot# and record of the undo information.

(3) The transaction locks the rows (since it did not commit) by placing a special type of data in the block header known as Interested Transaction List (ITL) entry. The ITL entry shows the transaction ID and other information.

(4) When a new transaction wants to update the same rows (locked by the previous transaction) it checks the ITL entries in the block first, to check if there is a lock.

(5) Since the lock information of rows is stored in the block itself, and the ITL entries in the block refer to the locks on the rows in that block alone, there is no need to have a central lock manager to dispense and handle the release of the locks. This makes the locking process not only immensely scalable but feasible as well since there is no theoretical limit to the number of locks.

[Updated Jan 22, 2011] [Thank you, Randolph Geist (info@www.sqltools-plusplus.org) for pointing it out. I follow his blog http://oracle-randolf.blogspot.com/, which is a treasure trove of information.
(6) The information that a row is locked is stored along with the row in the form of a lock byte.
[End of Update Jan 22, 2011]

While the article might have answered some of the vexing questions you may have had or needed some clarity on the concepts you were somewhat familiar with, I sincerely hope it has piqued you curiosity to learn even more about these concepts. If I was successful in explanation, now you should not be satisfied, you should have more questions. If you don’t have any, then I completely failed in my explanation.

So, what are the questions? For starters, how do you know what objects being locked in the transaction? It’s actually quite trivial. The view V$LOCK has provided that information for years, albeit in a convoluted form. A new view V$LOCKED_OBJECT is a bit more user-friendly. Let’s examine that with an example. First, update a row:

SQL> update itltest set col2 = 'CHANGED BY SESSION AGAIN' where col1 = 221
  2  /

1 row updated.

We can check the transaction ID:

SQL> select dbms_transaction.local_transaction_id from dual'

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
2.16.41316

1 row selected.

As you learned from the previous installment in this series, the transaction ID is a series of numbers denoting undo segment number, slot# and record# (also known as sequence#) respectively, separated by periods.

Now, check the view V$LOCKED_OBJECT:

SQL> select * from v$locked_object
  2  /

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID
---------- ---------- ---------- ---------- ----------
ORACLE_USERNAME                OS_USER_NAME
------------------------------ ------------------------------
PROCESS                  LOCKED_MODE
------------------------ -----------
         2         16      41316      95263         56
ARUP                           oracle
13181                              3

The view shows Undo Segment# (XIDUSN), Undo Slot# (XIDSLOT) and Undo Rec# (XIDSQN), which can be used to construct the transaction ID to be joined with the V$TRANSACTION to get the details. The view contains the column OBJECT_ID. Another important column is LOCKED_MODE, which shows the mode the rows are locked. In this case, it’s “3”, which means Row Exclusive. Here is a script that decodes the modes as well as reports the object name.

select
    owner               object_owner,
    object_name         object_name,
    session_id          oracle_sid,
    oracle_username     db_user,
    decode(LOCKED_MODE,
        0, 'None',
        1, 'Null',
        2, 'Row Share',
        3, 'Row Exclusive',
        4, 'Share',
        5, 'Sub Share Exclusive',
        6, 'Exclusive',
        locked_mode
    )                   locked_mode
    from v$locked_object lo,
        dba_objects do
    where
        (xidusn||'.'||xidslot||'.'||xidsqn)
            = ('&transid')
    and
        do.object_id = lo.object_id
/

Save this script and execute it when you need further details on the transaction. The script will ask for the transaction ID which you can pass in the format reported by dbms_transaction.local_transaction_id.

Next, you may draw my attention to the point #3 above. If there are 10 records in the block and a transaction updated (and therefore locked) all ten of them, how many ITL entries will be used – one or ten?

Good question (I have to say that, since I asked that :) I suppose you can answer that yourself. Ten ITL slots may be feasible; but what if the block has 10,000 records? Is it possible to have that many ITL slots in the block header? Let’s ponder on that for a second. There will be two big issues with that many ITL slots.

First, each ITL slot, by the way, is 24 bytes long. So, 10000 slots will take up 240,000 bytes or almost 22 KB. A typical Oracle block is 8KB (I know, it could be 2K, 4K or 16K; but suppose it is the default 8K). Of course it can’t accommodate 22KB.

Second, even if the total size of the ITL slots is less than the size of the block, where will be the room to hold data? In addition, there should be some space for the data block overhead; where will that space come from?

Obviously, these are genuine problems that make one ITL slot per row impractical. Therefore Oracle does not create an ITL entry for each locked row. Instead, it creates the ITL entry for each transaction, which may have updated a number of rows. Let me repeat that – each ITL slot in the block header actually refers to a transaction; not the individual rows. That is the reason why you will not find the rowid of the rows locked in the ITL slot.  Here is the ITL entry from the block header, again:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.019.00007c05  0x00c00288.1607.0e  ----    1  fsc 0x0000.00000000
0x02   0x0003.017.00009e24  0x00c00862.190a.0f  C---    0  scn 0x0000.02234e2b

There is a reference to a transaction ID; but not rowid. When a transaction wants to update a row in the block, it checks the ITL entries. If there is none, it means rows in that block are unlocked. However, if there are some ITL entries, does it mean that some rows in the block are locked? Not necessarily. It simply means that the rows the block were locked earlier; but that lock may or may not be active now. To check if a row is locked, the transaction checks for the lock byte stored along with the row.

That brings up an interesting question. If presence of an ITL slot does not mean a record in the block is locked, when does the ITL slot get cleared so that it can be reused, or when does that ITL slot disappear? Shouldn’t that ITL slot disappear when the transaction ends by commit or rollback? That should be the next burning question throbbing in your head right now.

Clearing of ITL Slots

To answer that question, consider this scenario: a transaction updates 10000 records, on 10000 different blocks. Naturally there will be 10000 ITL slots, one on each block, all pointing to the same transaction ID. The transaction commits; and the locks are released. Should Oracle revisit each block and remove the ITL entry corresponding to the transaction as a part of the commit operation?

If that were the processing logic, the commit would have taken a very long time. Acquiring the buffers of the 10000 blocks and updating the ITL entry will not be quick; it will take a very long time, prolonging the commit processing. From part 1 of the series, you learned that the commit processing is actually very quick, with a flush of the log buffer to redo logs and the writing of the commit marker in the redo stream. Even a checkpoint to the datafiles is not done as a part of commit processing – all the effort going towards making the process fast, very fast. Had Oracle added the logic of altering ITL slots, the commit processing would have been potentially long, very long. Therefore Oracle does not remove the ITL entries after that transaction ends (by committing, or rolling back); the slots are just left behind as artifacts.

The proof, as they say, is in the pudding. Let’s see with an example:

SQL> create table itltest (col1 number, col2 varchar2(200));

Table created.

SQL> begin
  2     for i in 1..1000 loop
  3             insert into itltest values (
  4                     i,'INITIAL VALUE OF COLUMN');
  5     end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

This inserts 1000 records. Let’s find out the file and block these records go to:

  1  select
  2     dbms_rowid.rowid_relative_fno(rowid) File#,
  3     dbms_rowid.rowid_block_number(rowid) Block#,
  4     count(1)
  5  from itltest
  6  group by
  7     dbms_rowid.rowid_relative_fno(rowid),
  8     dbms_rowid.rowid_block_number(rowid)
  9  order by
 10*    1,2
SQL> /

     FILE#     BLOCK#   COUNT(1)
---------- ---------- ----------
         7       4027        117
         7       4028        223
         7       4029        220
         7       4030        220
         7       4031        220

5 rows selected.

Let’s identify the rows in a specific block, block# 4028, for instance.

SQL> select min(col1), max(col1)
  2  from itltest
  3  where dbms_rowid.rowid_block_number(rowid) = 4028
SQL> /

 MIN(COL1)  MAX(COL1)
---------- ----------
1 223

1 row selected.

Block 4028 has the rows 1 through 223. That’s all we need to know for now. We will limit our activity to this block alone. We will need to update a single row in this block from a session:

SQL> update itltest set col2 = ‘Changed’ where col1 = 1;

Do NOT commit; just keep the session at this point. Open a different session, and update a different row, e.g. one with col1 = 2. Since this is a different row, there will be no lock contention. Similarly update 20 other rows on this block. There will be 20 different transactions on the rows of this table.

Let’s examine the innards of the block by dumping it. Before that, we should flush the block to the disk.

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 7 block min 4028 block max 4028;

System altered.

The information will be written to a tracefile. We have to know the SPID of the process to identify the tracefile:

SQL> select p.spid
  2  from v$session s, v$process p
  3  where s.sid = (select sid from v$mystat where rownum < 2)
  4* and p.addr = s.paddr
SQL> /

SPID
------------------------
9537

We will locate a file called D112D2_ora_9537.trc in the trace directory. Please note, this tracefile is named OracleSID_ora_ProcessID.trc; so the exact name will be different your system. Open the file and search for “Itl”. Here is an excerpt from the file:

Block header dump:  0x01c00fbc
 Object id on Block? Y
 seg/obj: 0x1741f  csc: 0x00.235a849  itc: 36  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1c00fb8 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.00d.0000a1eb  0x00c015d1.1d3c.28  ----    1  fsc 0x0005.00000000
0x02   0x0007.018.00007fab  0x00c01246.180b.21  ----    1  fsc 0x0005.00000000
0x03   0x0003.004.0000a1b0  0x00c005ef.1a18.07  ----    1  fsc 0x0005.00000000
0x04   0x0010.010.00000004  0x00c011ee.0001.10  ----    1  fsc 0x0005.00000000
0x05   0x000e.00e.00000003  0x00c011cb.0001.0f  ----    1  fsc 0x0005.00000000
0x06   0x000c.00e.00000003  0x00c011ab.0001.1b  ----    1  fsc 0x0005.00000000
0x07   0x0013.011.00000004  0x00c00f9c.0001.0f  ----    1  fsc 0x0005.00000000
0x08   0x0002.00a.0000a166  0x00c014d8.1c06.12  ----    1  fsc 0x0005.00000000
0x09   0x0001.010.00007f65  0x00c00cd3.16ae.14  ----    1  fsc 0x0005.00000000
0x0a   0x0014.01b.00000008  0x00c00faa.0003.67  ----    1  fsc 0x0005.00000000
0x0b   0x000f.00f.00000003  0x00c011db.0001.20  ----    1  fsc 0x0005.00000000
0x0c   0x000d.00f.00000004  0x00c011bb.0001.1d  ----    1  fsc 0x0005.00000000
0x0d   0x0012.010.00000003  0x00c00f8b.0001.1c  ----    1  fsc 0x0005.00000000
0x0e   0x000a.00c.00007f76  0x00c003d7.16ea.31  ----    1  fsc 0x0005.00000000
0x0f   0x0011.010.00000004  0x00c011fb.0001.10  ----    1  fsc 0x0005.00000000
0x10   0x0009.000.0000a236  0x00c00e91.1bbe.17  ----    1  fsc 0x0005.00000000
0x11   0x0006.00e.0000a1fc  0x00c0035c.1c24.2d  ----    1  fsc 0x0005.00000000
0x12   0x000b.012.00000003  0x00c01193.0001.1d  ----    1  fsc 0x0005.00000000
0x13   0x0004.00e.00007ff7  0x00c00d01.1771.0a  ----    1  fsc 0x0005.00000000
0x14   0x0005.002.0000a19f  0x00c00f1a.1bd6.1d  ----    1  fsc 0x0005.00000000
0x15   0x0015.000.00000002  0x00c00fba.0000.02  ----    1  fsc 0x0005.00000000
0x16   0x0016.000.00000002  0x00c00fca.0000.02  ----    1  fsc 0x0005.00000000
0x17   0x0017.000.00000002  0x00c00fda.0000.02  ----    1  fsc 0x0005.00000000
0x18   0x0018.000.00000002  0x00c00fea.0000.02  ----    1  fsc 0x0005.00000000
0x19   0x0019.000.00000002  0x00c00ffa.0000.02  ----    1  fsc 0x0005.00000000
0x1a   0x001a.000.00000002  0x00c0100a.0000.02  ----    1  fsc 0x0005.00000000
0x1b   0x001b.000.00000002  0x00c0101a.0000.02  ----    1  fsc 0x0005.00000000
0x1c   0x001c.000.00000002  0x00c0102a.0000.02  ----    1  fsc 0x0005.00000000
0x1d   0x001d.000.00000002  0x00c0103a.0000.02  ----    1  fsc 0x0005.00000000
0x1e   0x001e.002.00000002  0x00c0104a.0000.03  ----    1  fsc 0x0005.00000000
0x1f   0x001f.002.00000002  0x00c0105a.0000.03  ----    1  fsc 0x0005.00000000
0x20   0x0020.000.00000002  0x00c0106a.0000.02  ----    1  fsc 0x0005.00000000
0x21   0x0021.005.00000002  0x00c0107a.0000.08  ----    1  fsc 0x0000.00000000
0x22   0x0022.000.00000002  0x00c0108a.0000.02  ----    1  fsc 0x0005.00000000
0x23   0x0023.000.00000002  0x00c0109a.0000.02  ----    1  fsc 0x0005.00000000
0x24   0x0024.000.00000002  0x00c010aa.0000.02  ----    1  fsc 0x0005.00000000
bdba: 0x01c00fbc
data_block_dump,data header at 0xeb6994

Note the Itl entries – there is an entry for each transaction, marked by its transaction ID, as expected. When the block was created, there were two ITL slots. As the demand for locks increased, additional slots were created and used for these new transactions.

Now go to all these sessions and either commit or rollback to end the transactions. Dump the block and search for “Itl”. The ITL slots are still there, even though the transactions have ended and the locks released. Oracle does not update the ITL entries.

So, when does the ITL entry gets cleared? When block’s buffer is written to the disk, the unneeded ITL entries are checked and cleared out. Let’s force a block flushing:

SQL> alter system checkpoint;
Now dump the data block once again and examine the ITLs. Here is an excerpt from the tracefiles.

Object id on Block? Y
 seg/obj: 0x1741f  csc: 0x00.235a849  itc: 36  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1c00fb8 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0014.016.00000008  0x00c00fb3.0002.11  C---    0  scn 0x0000.0235a524
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x06   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x07   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x08   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x09   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0a   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0b   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0c   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0d   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0e   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0f   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x10   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x11   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x12   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x13   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x14   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x15   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x16   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x17   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x18   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x19   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1a   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1b   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1c   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1d   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1e   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1f   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x20   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x21   0x0021.002.00000002  0x00c0107a.0000.05  C---    0  scn 0x0000.0235a807
0x22   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x23   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x24   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01c00fbc
data_block_dump,data header at 0x484994

Note the Xid columns – the transaction Id, which shows 0’s, meaning there is no transaction using the ITL slots. These ITL slots are eligible for reuse. Update two rows from two different sessions, checkpoint and dump the block once again. Here is the ITL information again:

Itl           Xid                  Uba          Flag  Lck        Scn/Fsc
0x01   0x0014.016.00000008  0x00c00fb3.0002.11  C---    0  scn 0x0000.0235a524
0x02   0x0005.009.0000a1a5  0x00c00f21.1bd6.04  ----    1  fsc 0x0016.00000000
0x03   0x000a.002.00007f7a  0x00c003d8.16ea.13  ----    1  fsc 0x0016.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x06   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
... and so on ...

The first two Itl slots are now used. Note, only the ITL slots in this specific block will be created. All other blocks will continue to have the same number of ITL slots.

ITL Waits

From the part 2 of this series you learned that the ITL slots are not preallocated, at least not all of them. When a transaction needs to lock rows in the block, and it does not find an unused ITL slot, Oracle creates a new ITL slot for the transaction. Consider the figure below. There is no more room in the block for a new ITL entry. A new transaction comes in to update Record3. What will happen?



The transaction will have to wait. This is not the same wait as a row lock; because there is no lock on the row marked Record3.Instead, session will wait on a special wait event. You can check the wait event from the V$SESSION view.

SQL> select event
  2  from v$session
  3  where sid = 78
  4  /

EVENT
----------------------------------------------------------------
enq: TX - allocate ITL entry

The moment one of the transactions – from either Session1 or Session2 end by commit or rollback, the new transaction can grab that ITL slot and complete the locking operation. You will see that wait event disappear.

Since the ITL waits come and go, how do you capture them; or more specifically how will you know which objects are being subjected to this wait? It’s fairly trivial. Since Oracle 9.2 a new view – V$SEGMENT_STATSTICS – shows various segment related statistics on segments. Here is an example:

SQL> select statistic_name, value from v$segment_statistics
  2* where object_name = 'ITLTEST';

STATISTIC_NAME                                                        VALUE
---------------------------------------------------------------- ----------
logical reads                                                          7216
buffer busy waits                                                         3
gc buffer busy                                                            0
db block changes                                                       5600
physical reads                                                            0
physical writes                                                          39
physical read requests                                                    0
physical write requests                                                   9
physical reads direct                                                     0
physical writes direct                                                    0
optimized physical reads                                                  0
gc cr blocks received                                                     0
gc current blocks received                                                0
ITL waits                                                                 2
row lock waits                                                            1

Various stats on the segment named ITLTEST are listed here. Of the lot, the one interesting to our discussion here is “ITL waits”, which shows “2”. It means the table ITLTEST has waited 2 times for ITL waits (not for a legitimate row locking, which shown in the stats immediately afterwards).

Conversely, you may want to find out what have been subjected to ITL waits. The following query shows you that:

SQL> select owner, object_name
  2  from v$segment_statistics
  3  where statistic_name = 'ITL waits'
  4* and value > 0;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
ARUP                           ITLTEST

1 row selected.

The view has many more columns for making filtering easier:

SQL> desc v$segment_statistics
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 TS#                                                NUMBER
 OBJ#                                               NUMBER
 DATAOBJ#                                           NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 STATISTIC_NAME                                     VARCHAR2(64)
 STATISTIC#                                         NUMBER
 VALUE                                              NUMBER

Actually selecting from the above view is a bit expensive on the database. The base view is V$SEGSTAT, shown below:

SQL> desc v$segstat
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TS#                                                NUMBER
 OBJ#                                               NUMBER
 DATAOBJ#                                           NUMBER
 STATISTIC_NAME                                     VARCHAR2(64)
 STATISTIC#                                         NUMBER
 VALUE                                              NUMBER

While V$SEGMENT_STATISTICS show much more information, it’s a little slow due to all those joins. If you don’t need all that information, you may want to select instead from V$SEGSTAT, which is usually faster. The columns are self explanatory; but here they are in any case:

TS# - the tablespace number. You can use this to get the tablespace name from TS$ table joined by TS# column
OBJ# - the object_id, from dba_objects. You can get the rest of the details from that view
DATAOBJ# - the data_object_id, from dba_objects. This is usually the same as object_id; except in case of sub-objects such as partitions in which case they differ.

One important point about this view: like all V$ views, it shows information from the start of the instance. When the instance recycles, the values are reset to 0. To get a historical information, you should periodically select from this view and store in a regular table. If you have AWR enabled, you can check the historical records from there. Here is an example:

SQL> select snap_id, ITL_WAITS_TOTAL, ITL_WAITS_DELTA
  2  from DBA_HIST_SEG_STAT
  3  where obj# = 95263
  4* order by snap_id;

   SNAP_ID ITL_WAITS_TOTAL ITL_WAITS_DELTA
---------- --------------- ---------------
      5014               2               2

1 row selected.

Solution

Well, so far I talked about a problem. Is there a solution? Of course there is.

Remember, the cause of ITL waits is simply space inside a block. If there is no space inside the block to grow the ITL list to add more slots, the sessions will wait with the ITL wait event. So the solution is to reserve same space for that growth. There are two basic alternatives to solve the ITL wait problem:

(1) INITRANS.

Remember the little clause during table or index creation? Have you ever explicitly set it to its non-default value? Most likely you haven’t. It specifies the number of ITL slots that must be initially created on a block. If you specify 10, then 10 ITL slots are created on the block, guaranteeing the slot for 10 transactions. The 11th transaction will need to extend the ITL list; or wait if that is not possible.

To check for the INITRANS value of tables, use:

SQL> select ini_trans
  2  from dba_tables
  3  where table_name = 'T';

 INI_TRANS
----------
        10

(2) Less Space for Data

The other option is to make sure that you have less data inside a data block to allow the ITL sufficient free space. You can do it by several ways – by setting a high value of PCTFREE and by setting MINIMIZE_RECORDS_PER_BLOCK clause.

Obviously, both these options waste space inside the block; so you should use these only on those segments that experience high ITL waits, as you can see from AWR reports or your homegrown data collectors. To increase the INITRANS of an existing table, you should issue:

ALTER TABLE ITLTEST INITRANS 10;

Remember, the setting affects the new blocks only; not the existing ones. You can issue ALTER TABLE … MOVE command to relocate the blocks to new blocks, and thereby effecting the new settings.

What is the upper limit of the ITL slots? They are set by a parameter of the object called MAXTRANS. The default is 256. If you set it to 20, the ITL slots will go up to that much only. However, the parameter has no effect in Oracle 10gR2. It’s ignored and the ITL slots can go up to 256.

Takeaways

In this installment you learned:

(1) ITL itself does not say whether a row is locked or not. The lock byte stored in the row tells that.
(2) When a transaction ends, the corresponding ITL entry is not removed or altered. It gets cleared during flush to the disk.
(3) When the ITL can’t grow due to the lack of space in the block, the session waits will the event “enq: TX - allocate ITL entry
(4) You can identify the segments that have suffered from this wait by checking the view V$SEGSTAT.
(5) To reduce the possibility of these waits, you should have sufficient space inside the data block for ITL expansion, either by defining higher number of initial ITL slots, or forcing less data inside the blocks.


I hope you enjoyed this installment. As always, I will appreciate if you drop in a line on how you liked it.

274 comments:

«Oldest   ‹Older   201 – 274 of 274
Unknown said...

The world has enjoyed their Prime membership on larger-screens. www.amazon.com/mytv has made it easy. MyTV App registration is your best option for streaming all of the Amazon Prime shows to your Smart TVs. The above article will solve all your registration, login, verification problems. Now all you have to do is put on your comfy clothes and enjoy the theatre-like experience at home. Log in with your Amazon Prime Login account to access all your favorite web series and original web shows on your smart television.
amazon.com/mytv
amazon.com/redeem
amazon.com/mytv
amazon.com/redeem


coupon codes said...

Thanks for sharing.
drive.google/savingchief
sites.google/savingchief
google/savingchief
docs.google/savingchief
docs.google/savingchief
docs.google/savingchief
docs.google/savingchief
verfahrensd92.blogspot/savingchief

Gracia said...

Thanks for Informative blog, it is pretty good and impressed me a lot. Now Order Perdisco Assignment Help Expert with best price service. We are providing the best discount for students this season. Now order your assignment for getting MYOB assignment help. If you are looking for the best quality analysis of your assignment so you can get also experts with 24/7 support.

123hpcomsetup said...

My name is Timpaine from New York, US. I have good exposure in writing creative content about printers and their features. On my website, I provide instructions for printer setup, driver download, and troubleshooting issues. If interested, visit my site.
123.hp.com/setup.

oncasinosite said...

I’d really love to be a part of online community where I can get responses from other knowledgeable individuals that share the same interest. If you have any recommendations, please let me know. Appreciate it! Feel free to visit my website; 온라인카지노사이트

casinositelink said...

This type of article will be able to think and analyzed to the readers thats leads them in a bright future. I think this is one of the most significant information for me. And i’m glad reading your article. But should remark on some general things, The web site style is perfect, the articles is really great. Feel free to visit my website; 카지노사이트링크

oncasinositenet said...

Thank you so much for the post you do. I like your post and all you share with us is up to date and quite informative, i would like to bookmark the page so i can come here again to read you, as you have done a wonderful job. Feel free to visit my website; 온라인카지노사이트넷

bacarasite said...

Howdy! Would you mind if I share your blog with my facebook group? There’s a lot of folks that I think would really enjoy your content. Please let me know. Many thanks Feel free to surf to my webpage discuss. Feel free to visit my website; 바카라사이트닷컴

Edward said...

Go to office setup for office.com/setup. Sign In or Create a new Microsoft Account. Or go to
www.office.com/setup to download
Officecom which is best for online work. webroot.com/safe is best Installation Guide.The security package offered by this brand is incredibly easy to setup and install.

Mark Jubin said...

One can get a toll-free number for all kind of gadgets and online services from here.
Delta Airlines
Jet Airways
Printer help Center

SAVIOLA said...

You’re incredible! Thank you! This blog is a very informative place. I'll come by often. Also check what is unical cut off mark for computer science

adamparker said...

To learn How to Setup HP Deskjet Printer, here we explain the setup guidelines in detail. You can take out the Printer from the package. Then refer the setup manual to learn how to set up the Printer. If the guidelines are clear, locate the port on your Printer, Computer to connect the necessary cables. Now access the device control panel to select the network settings. WPS, Wireless setup wizard settings are compatible to use with HP DeskJet Printer. After selecting the settings, run the onscreen commands that appear on your screen to activate the network. Finally add Windows, Mac computer to start operating the Printer. Please dial the toll-free number provided on our website portal

Nyra Cuper said...

tramadol without prescriptionBuy tramadol online at +18056098797 directed by doctors, this medicine is used for moderate to severe pain.
silver singles loginusmatches you with people based on your personality, location, age, and desired relationship out comeses an algorithm that.

buy tramadol online
where to buy tramadol online

adamparker said...

How to Setup HP Printer?
To learn how to set up HP Printer, here we explain the Printer setup steps in detail. The steps include connecting the hardware, activating the network, selecting the language, display, and network settings. After selecting the required settings, install the Printer driver. Then add Windows, Mac computer to start operating the printer. If you need any help to setup HP printer, fix setup errors that pop up on your device display screen, please contact our Printer customer support team right away

adamparker said...

HOW TO SET UP HP Envy Printers?
To know how to set up HP Envy Printer, here we explain the Printer setup steps. You can unpack the Printer as the first step. Then collect the hardware requirements. After completing the hardware setup, select the required network settings. WPS, Wireless setup wizard methods are recommended to use with HP. You can choose the compatible method. Then run the onscreen commands that appear to complete HP Envy Printer network setup. Adding Windows, Mac computer is the next step. If the above step is complete, select the appropriate settings to perform the functions such as Print, Scan, Copy, and Fax. Please dial the toll-free number for setup assistance

HP Printer Assistant said...

If you are download and install the HP Support Solutions Frameworkapproaching the easy and quick steps given below. To complete the downloading process you need to connect your device. It's a whole pack of programs that allow HP users to easily access, install, and remove their system drivers, manage their hardware, and tweak BIOS or other Windows settings.

John mathew said...

Thank you so much for sharing this post, I appreciate your work. It was a great informative post. Go so many useful and informative links. Loved your writings also.
Best Salon in Delhi
Best Bridal Makeup Artist in Delhi
Best Salon in palam

BESTSITE said...

If you want to increase your knowledge only keep visiting this web page and be updated with the latest news update posted here 야한소설.

CLICKME said...

"Hey there! Do you use Twitter? I’d like to follow you if that would be okay 대딸방.
I’m absolutely enjoying your blog and look forward to new updates."

MY SITE said...

"I would like to thanks' 스포츠마사지 for the efforts you have put in writing this blog. I’m hoping the same high-grade blog post from you in the upcoming also. Actually your creative writing abilities has encouraged me to get my own web site now. Actually the blogging is spreading its wings quickly. Your write up is a good example of it."

VISIT ME said...

What an excellent you are 출장마사지. Your presentation was so good.

카지노슬롯 said...

You pointed out some good points. I did a Google search on the topic and found that most people would believe your blog. 카지노슬롯

marc monserrat said...

I have to thank you for the efforts you have put in penning this blog. I really hope to view the same high-grade content by you later on as well. In truth, your creative writing abilities has encouraged me to get my own website now

vinilos decorativos
vinilos BARTOP
vinilos decorativos TIENDAS
vinilos decorativos infantiles
vinilos decorativos frases
vinilos decorativos navidad
vinilos decorativos peluquerías
vinilos bartop - muebles bartop
vinilos decorativos frases
vinilos decorativos frases

john nicholas said...

hp officejet pro 8600 drivers is a multi-functional printer that is best for use in offices or homes. It comes with some great features like automatic document feeder and instant ink plan. So, simply you do not ever have to worry about empty ink cartridges. Other than this, the automatic document feeder will detect, scan and copy different documents on its own.

Sanjay Sharma said...

Our govt has been publishing many sarkarireasult.com these days for previous exams. Yesterday UPPSC published the outcome

ezequielfarrell said...

How do I change or factory reset epson printer driver settings (Windows)
The default driver settings that are used for the A4 inkjet printer comprise A4 paper size, plain papers, Port raito rientation, and the Text print quality mode. For Picture Mate printers The default settings are the size of a 4x6" (10x15 centimetres) and Epson Picture Mate Photo Paper, and Photo Print Quality.

You can alter the default settings according to your preference or return to default settings through the printer driver through in the folder Printers folder.

To set the driver for your printer to default settings it is recommended to close all open programs and follow the steps below.

Go to the Start menu Then click on the the Control Panel.
In the the Control Panel in Control Panel, open the folder for printers in Control Panel. Check out the following article for further assistance in this process: How to open the Devices and Printers folder in Windows
Within the folder Printers,, right-click on the Epson printer icon, then go to the menu, left-click on the Printing preferences.
Make sure you follow the section that is appropriate for the type of printer you have to modify the settings of the driver.

사설토토사이트 said...

This is the perfect post.사설토토사이트 It helped me a lot. If you have time, I hope you come to my site and share your opinions. Have a nice day.


Majortotosite Pro said...

Keep up the good work , I read few blog posts on this internet site and I believe that your site is really interesting and contains lots of fantastic info .
메이저사이트
토토

Racesite Pro said...

Wow, great post. Really looking forward to read more. Great.
경마사이트
경마

Oncasinosite Net said...

One of our visitors recently recommended the following website.
카지노사이트
카지노

Totopick Pro said...

Very informative blog article. Thanks Again. Keep writing.
메이저사이트
사설토토

Kale Johnson said...

There are various methods to set up your Tenda Router. Here is your stepwise guide on how to perform the Tenda AC1200 setup. First of all, unpack the router and connect it with the power adapter. Once done, establish a connection between the device WAN port and ISP using the ethernet cable, and it's done. Moreover, you can also set up your Tenda router through DHCP. Go to the wireless settings, select the DHCP option, and then select the password. Besides, you can also set up your Tenda AC1200 router with PPPoE mode by following these instructions. Go to your browser and visit 192.168.0.1. Then, enter the credentials, and in the password field, enter admin, which is the default password. Then, click the PPPoE option from the internet setup screen and enter th login details provided by ISP. Moreover, also enter the security key and then hit the OK button. Now, restart your Tenda router.

메이저사이트순위 said...

Hi there, I simply hopped over in your website by way of StumbleUpon. Now not one thing I’d typically learn, but I favored your emotions none the less. Thank you for making something worth reading. 메이저사이트순위

토토사이트추천 said...

This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value. Im glad to have found this post as its such an interesting one! I am always on the lookout for quality posts and articles so i suppose im lucky to have found this! I hope you will be adding more in the future. 토토사이트추천

racesite.pro said...

Your information was very useful to me. That's exactly what I've been looking for 경마사이트

reelgame.site said...

Very interesting information and i really glad to getting this information.파친코사이트

totosite365.info said...

After looking into a few of the blog posts on your site, I seriously appreciate your way of blogging. 토토

sand blasting machine said...

Sand impacting framework is find inside the tremendous assortment of the business it is use inside the all sort of the undertaking automatic sand blasting machine

john nicholas said...

Nice blog given by u thanks for it. Today i m going to tell about Epson printers so Epson printers are very popular all over world wide because of its performance and Epson is making there printers in many ranges it depends on there varient and it is making for home and business purposes but in thos printer some error is commonly occuring epson error code 0x97 but it is easy to resolve also so u can go through and check.

baccarat said...

It's the same topic , but I was quite surprised to see the opinions I didn't think of. My blog also has articles on these topics, so I look forward to your visit.baccarat

casinosite.one said...

Appreciating the commitment you put into your site and in depth information you offer 바카라사이트

davidshen said...

Thank for sharing this useful information.Need assistance an Epson printer setup, driver install, troubleshooting issues. Feel free to reach our website. Epson Printer Driver

Assignment Answers Online said...

Total Assignment Help gives best assignment answers online service here with experts specializing in a wide range of disciplines ensuring you get the assignments that score maximum grades.

adammil zampa said...

How to install HP smart app , How to find WPS Pin on HP Envy Photo 7855 Printer , Install HP Officejet 3830

Sand Blasting Machine said...

Are you looking for shot Blasting machine for your industry,Shots blasting machine
We are the leading manfacturer and provider of Shot Blasting Machine , Sand Blasting Machine, Abrasive materials equipments related to Airo shot blasting industry like SPray gun , Thermal SPray gun etc

Visit our website for more information

Thanks

Sand Blasting Machine said...

Are you looking for shot Blasting machine for your industry,Shots blasting machine
We are the leading manfacturer and provider of Shot Blasting Machine , Sand Blasting Machine, Abrasive materials equipments related to Airo shot blasting industry like SPray gun , Thermal SPray gun etc

Visit our website for more information.

Thanks

Anonymous said...

Hp officejet pro 8600 driver | Cox webmail login| Solutions.brother.com/windows | Pbs.org/activate

John Ruth said...

I liked your blog! Looking forward to more of your updates like this.

Ij.start.canon ts3122 | Ij.start.canon | 123.hp.com/laserjet |Garmin.com/express | Www.hp.com/go/wirelessprinting

Kaylee Brown said...

The majority of people believe that hiring writing pros is outrageously expensive. However, because the majority of consumers are students on a restricted budget, such services are extremely affordable. As a result, everyone in need of academic aid can afford the services. If you want high-quality help with paper, engage a professional writer from a legitimate and reputable writing service, and you will never be sorry.

Navnit said...

What is bitcoin mining ? How Bitcoin Mining is done.


Top 10 formal shoes brands in India

Ministry of Cleaning said...

We want to encourage you to contact the team at Cleaning Co. for site cleaning. We offer a wide variety of services that can fit your needs and accommodate any budget, and we guarantee that your satisfaction with our services is our top priority.

Contact Cleaning Company for site cleaning services today! With the wide range of services we offer, we can tailor something perfect for you and your budget.

Unknown said...

ij.start.canon
is the manufacturer site to download Canon printer drivers. Install and set up Canon Printer from https: //ij.start.canon and obtain high-quality printing documents straightforwardly.

https//ij.start.cannon is actually the official site of ij start canon that helps you install the latest printer drivers and software. Visiting http //ij.start.cannon
provides you a complete list of all canon printers where you’ve to select your printer model and download the correct setup file

Unknown said...

Canon IJ Network Tool is a toolkit software with the options to keep a check on most of your Canon printer network settings and adjust them according to your requirements.

Canon IJ Printer Utility is a complete software package meant to adjust and modify the configurations of your printer device as per the requirement. It is one of the essential utility software offered by Canon to ease your printer configuration alteration using a few clicks.


Canon.com/ijsetup/mg2500
is the Canon support link to get you the latest printer drivers and software to set up your PIXMA MG2500 or MG2520.

Unknown said...

Your opinion and feedbacks are valuable to us! Ping us about your opinion of the blog and we will instantly support you to assist regarding the Epson printer connect.

vrdigitalau said...

Online marketing Canberra is a great way to promote your business in Canberra. There are many options available, so it's important to find the right one for you. By using online marketing, you can reach a larger audience and get your message out there. Make sure to use a reputable company, and you'll be seeing results in no time!

text blogs said...

canon printer provides an answer for all type of canon printer problems by which you can undoubtedly figure out how to set up from canon and improve insight. canon.com/ijsetup | ij.start.canon

Techy Guy said...

Thanks for give us valuable information
Netgear Contact Number
Netgear Support

Unknown said...

Ij start canon and find out the best way to download Canon printer drivers.
Ij.start.cannon | Ij.start.canon | canon.com/ijsetup

Essien said...

Thanks a million and please keep up the effective work. This is my first time visiting and I'm gladding surfing from it. I found such a substantial number of interesting stuff in your blog. Much thanks for sharing. Click on: noun exam past questions and answers pdf format

Anonymous said...

ij.start canon is the manufacturer's site to download Canon printer drivers. You can easily download the driver according to your model as ij.start.canon is designed with a user-friendly interface . ij.start.canon

Anonymous said...

Canon IJ Network Tool is a toolkit software with the options to keep a check on most of your Canon printer network settings and adjust them according to your requirements. ij.start.canon helps to set up canon printer. It’s the online support platform to download and install canon printer drivers, firmware, and software Https //ij.start.cannon .

먹튀검증업체 said...

Hi there, I simply hopped over in your website by way of StumbleUpon. Now not one thing I’d typically learn, but I favored your emotions none the less. Thank you for making something worth reading. 먹튀검증업체

Sanjay Sharma said...

My sarkari result is one of the best job website in all over India. Our team provides you all the information about 100% genuine government jobs and government results. You have to visit our website to get all updates about all online sarkari exam, employment result, admit card, syllabus

sateesh said...

nice article seo services in hyderabad

Christian bale said...

Am Stephen, Technical guide for instant Support on Printer Hardware & Software content, technical & driver-oriented brochures, Web-related Technical content and more Printer blogs. To accomplish more knowledge, spare a few minutes on Our 123.hp.com Website.

UP Board result said...

I have read the blog and nice article. UP Board result




UP Board result 2022 kab tak aayega

Anonymous said...

Resulttank says: Nice Artical thanks for share it.

Urvashi said...

Health is just one aspect of a Healthy Life Human. It offers broad information on a variety of other topics, including astrology, entertainment, news, lifestyle, and travel. Healthy Life Human is changing the public's perception of fitness and health. This website provides trustworthy, dependable, and up-to-date information, and we did this by taking a practical approach to health, exercise, nutrition, well-being, food, and other themes.
Healthy Life Human

Family Office Singapore said...

VERY USEFUL CONTENT

Family Office Singapore said...

visit site

Singapore Citizenship said...

thanks

ISkillers said...

Nice Artical thanks for share it. Logo Design

Santosh Kumar said...

https://www.kaiseonline.com/ aims to provide detailed information on government jobs, which helps high school pass students to find latest recruitment opportunities. Fresher candidates can now make a better choice to reach the peak of their career.

los angeles said...

"Another way you can try to fix the Nighthawk app not logging in issue is to clear the cache and app data.
netgear nighthawk app not working"

«Oldest ‹Older   201 – 274 of 274   Newer› Newest»

Translate