Re: ora-08177 on selects with isolation level serializable
Date: Thu, 4 Apr 2013 16:42:31 +0530
Message-ID: <CAB8JqBaCxGBE_O4qM+ZKspDJ5t1HeeBOy+v8T97eHYcuZgSEXA_at_mail.gmail.com>
Thanks Jonathan/Mark for your updates. We had taken errorstack for ora-08177 and provided block dump of this table and Oracle analyst updated SR with following information
============From the trace block dumps:
(List of Blocks) next index = 1
index itli buffer hint rdba savepoint
0 2 0x7afd3408 0x499de3 0xc01
^using 2nd ITL slot. (possible a small ITL since it is not a high slot number)
Block header dump: 0x0241275f
Object id on Block? Y
seg/obj: 0x18c0a csc: 0xa09.fbfe3ab5 itc: 169 flg: E typ: 1 - DATA
…
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0014.016.0002a9e6 0x00c0012a.2ef6.52 C--- 0 scn 0x0a09.fbfd6975
…
0xa9 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
^ translation: 169 slots - not 255
Many other blocks with ITLs of 169 slots. The trace also implies there might be an few indices out there with an ITL of 2, but I’m less sure of that as I’m not as familiar with redo dump translation. Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x800c52 block=0x008186a9
Our db_block_size is 8kb and I took a block dump of this recreated table (with inittrans of 255) and we still get 169 slots confirming what you already mentioned above
0xa9 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
I have updated the SR and asked dev to explain why we were asked to recreate table with 255 inittrans when they already know that db_block_size is 8k (we provided RDA :) )
This table is part of payment system and our dev team is doing regression testing which involves so many concurrent transaction. We know in reality we might not hit this situation but dev wants us to follow up with oracle and get patch. I took errorstack for this error and it matched Bug 2728408 : ORA-08177 ON SELECTING DATA WITH SERIALIZABLE which was reported in Oracle 8 and fixed.
Regards
Amit
On Wed, Apr 3, 2013 at 11:38 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:
>
> 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
> > >
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 04 2013 - 13:12:31 CEST