RE: ora-08177 on selects with isolation level serializable
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: oracle-l L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Apr 2013 18:08:43 +0000
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of amit bansal [amit.bansal82@xxxxxxxxx]
Sent: 03 April 2013 05:55
To: oracle-l L
Subject: ora-08177 on selects with isolation level serializable
|So our application launches test and executes following sql
|
|Set transaction isolation level serializable;
|select * from metadata_table where id>0;
|
|We saw there were 169 ITL transactions in this block.
Which block ?
And how can you tell that "this" block is the one that is causing the ORA-08177
to appear ?
||Oracle is saying this is not bug and have asked to reproduce this issue by
recreating table with initrans of 255 for this table.
It may be a coincidence in this case, but since 169 is the maximum number of
ITL entries you can get in an 8KB block I'm going to guess that that's your
block size and that Oracle's advice is therefore a waste of breath.
It is unusual, though, to get to the limit - very few applications would be
engineered to get 169 active transactions in a single block: for a start you'd
have to have 169 rows in the block, which means very short rows (ca. 18 bytes
each for an 8KB block).
This looks more like a side effect of an Oracle bug: I can think of one
relating to row migration - which should have been patched in your version -
and another relating to index leaf block splits). It's possible that the
serializable transaction needs a new ITL entry (despite being a select) and
can't get one because the block has reached its limit and the 169 existing
entries are unavailable because of the way the anomaly caused the extreme
number to appear. (This is all pure speculation at present.)
Regards
Jonathan Lewis--
//www.freelists.org/webpage/oracle-l
Other related posts:
