RE: ora-08177 on selects with isolation level serializable

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of amit bansal [amit.bansal82_at_gmail.com] 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--
http://www.freelists.org/webpage/oracle-l Received on Wed Apr 03 2013 - 20:08:43 CEST