| INITRANS parameter tuning. [message #60538] |
Fri, 13 February 2004 02:09  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Hi,
I've 2 big tables (20 millions of rows) with no delete on it, massive inserts and quite few updates just after the rows has been inserted. Knowing that, I was wondering if I had to increase my INITRANS parameter? Is there a drawback to increase this paramter too high? Is there a limitation to the value of INITRANS?
I gathered some informations about how to estimate and tune the INITRANS parameter.
-----------------------------------------------------------
TX: Row Transaction Lock:
.This lock is required in exclusive mode (mode 6: TX-6) to change data.
.One lock is acquired for each active transaction. It is released when the transaction ends due to a commit or rollback.
.If a block containing the row(s) to be changed does not have any ITL (interested transaction list) entries left, then the session requests the lock in shared mode (mode 4: TX-4). It is released when the session gets an ITL entry for the block.
.If any of the rows to be changed are locked by another session, then locking session's transaction lock is requested in exclusive mode. When the locking transaction ends, this request ends, and the rows are covered under the requesting session's existing TX lock.
.The lock point to the rollback segment and transaction table entries for the transaction.
Do the following to avoid contention on this enqueue:
.TX-6: To avoid contention on TX-6 enqueues, review the application.
.TX-4: To avoid contention on TX-4 enqueues, consider increasing INITRANS for the object.
-----------------------------------------------------------
What is an ITL (interested transaction list)? Is it corresponding to an INITRANS? What is the relationship between INITRANS and ITL? Any guidelines to set INITRANS?
Thank you very much for your tips and recommendations.
Regards,
Patrick Tahiri.
|
|
|
|
| Re: INITRANS parameter tuning. [message #60547 is a reply to message #60538] |
Fri, 13 February 2004 06:33  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
Lots of good questions ! Learning Oracle the right way :)
Each Block has a transaction header in it's block header section. The transaction header has this ITL array ( Interested transaction list) and the initial number of ITL slots is determined by INITRANS ( ie initial transaction slots) parameter. Thats the relationship you are asking for.
Since the ITL entry contains the Lock field ( apart from SCN,redo block address etc), you want to set the initial number of ITL slots to be the concurrent number of inserts in that block, which is hard to measure. Remember, FREELISTS should be set to the concurrent number of inserts/updates on the table.
Since Oracle can dynamically adjust the number of these transaction slots, you really do not need to adjust INITRANS in most of the situations, except when there is heavy amount of parallel dml.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
