As should be evident, the "direct read" may sometimes be slower (then a normal read through the buffer cache) if the table has undergone significant changes recently *if* such a read has to be performed repeatedly. Note : Christian Antognini has even presented his performance findings in one of the comments).
.
UPDATE :
11 comments:
Hello;
When I issue an update, itl entry is opened for this row.
next transaction see that itl is open for this row and goes to rollback segments to see if it is
committed
or not, if it is not committed, it will hang.
Regarding the insert statement to primay key column.
If a user insert a value to primary column and not commit, whenever another user tries to
insert the same value to that column it will hang.
How is the process in this scenerio??
Scofield,
I wonder why you bother thinking that you are a DBA. After months of postings on forums and reading many blog entries, you still use the word "hang".
Both sessions will wait on Rows Locks -- specifically "enq: TX - row lock contention"
Hemant
Hello;
I want to know about the pk locks:
Regarding the insert statement to primay key column.
If a user insert a value to primary column and not commit, whenever another user tries to
insert the same value to that column it will hang.
How is the lock process in this scenerio??
I know how itl lock works as I explained above
A PK by definition cannot allow the same value to be entered. The previous session has already added the value to the index, even if not yet committed. The next session will not be allowed to add the same value.
The session sees it as an "enq : TX row lock contention" wait but it isn't a row lock on a table row -- it is a constraint enforced by the PK definition.
Hemant
Thanks sir;
Is this also has a relation with
itl entries as I explained above?
This is where Im confused.
ITL entries are taken for all transactions on existing rows.
Hemant
I know that ,
itl entries are stored in block header.
If space needed can block header grow?
What else stored in block header?
is it fixed in size?
I know that ,
itl entries are stored in block header.
If space needed can block header grow?
What else stored in block header?
is it fixed in size?
The ITL entries are part of the variable header, not the fixed header. Thety can grow (i.e. add new entries), as long as there is free space.
Remember that there won't be more IT entries than the number of rows because no row would have more than one ITL entry associated with it.
Hemant
Hi
Does undo tablespace store both commited and uncommitted transactions?
Anonymous,
The undo information for transactions is written to undo. Whether the transaction is committed or not comes later. If the transaction gets committted, the undo blocks are "released" in the sense that they are marked for re-use by any other transaction (after the expiry of undo_retention).
If the transaction gets rolled back, this information is used to update the data blocks (table and index) to the pre-transaction image of the rows that were updated.
It is not whether "undo stores both committed and uncommitted transactions". It is that "undo stores information about changes made by transactions". At the point of time when it is written, Oracle (or even the application) does not know if the transaction will be committed or rolled back subsequently.
Hemant K Chitale
Post a Comment