Re: Enqueue TX level 4 wait -- blocks dump

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 3 May 2004 19:53:36 +0100
Message-ID: <006401c4313f$f4c924a0$7102a8c0@Primary>

Note in-line

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Optimising Oracle Seminar
http://www.jlcomp.demon.co.uk/seminar.html

June 2004 UK Manchester
July 2004 Iceland
July 2004 USA California
Aug 2004 USA North Carolina
Sept 2004 UK Manchester
Sept 2004 USA NYC
Oct 2004 USA Boston

Original Message ----- From: "Diego Cutrone" <diegocutrone_at_yahoo.com.ar> To: <oracle-l_at_freelists.org> Sent: Monday, May 03, 2004 10:56 PM Subject: Re: Enqueue TX level 4 wait -- blocks dump Let me tell you what I tried to do. For indexes for example, in many blocks all the ITLs had been used, wouldn't that mean that at some point in time there were 11 simultaneous transactions active? (counting also the recursive ones)

[jl] No. Although indexes can be a little funny in their use of ITL
[jl] entries, the basic principle is that a new transaction will use
[jl] the itl entry that has the oldes commit scn in it. So this means
[jl] that you will tend to see all 11 ITLs used.

Index example.

Block header dump: 0x55802364
Object id on Block? Y
seg/obj: 0xb456 csc: 0x00.d86ff6c itc: 11 flg: - typ: 2 - INDEX

fsl: 0 fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01 xid: 0x0002.012.00019480 uba: 0x10c0170e.240b.02 C--- 0 scn 0x00
00.06a5a457
0x02 xid: 0x0006.00d.000335f6 uba: 0x10c06d67.464b.0e --U- 1 fsc 0x00
00.0d96cc25
0x03 xid: 0x0001.031.00032a0e uba: 0x10c00b0f.456c.0a --U- 1 fsc 0x00
00.0d96cc29
0x04 xid: 0x0003.041.00033018 uba: 0x10c03b0c.44db.0c --U- 1 fsc 0x00
00.0d96cc3b
0x05 xid: 0x0003.002.00033015 uba: 0x10c03b0f.44db.19 --U- 1 fsc 0x00
00.0d96cde5
0x06 xid: 0x0003.039.00032c31 uba: 0x10c0cd44.447e.06 --U- 10 fsc 0x00
00.0d86ff6d
0x07 xid: 0x0003.021.00033019 uba: 0x10c03b0b.44db.0e --U- 1 fsc 0x00
00.0d96cc21
0x08 xid: 0x0007.026.000330af uba: 0x10c07ea9.43bd.2e --U- 1 fsc 0x00
00.0d8ed98e
0x09 xid: 0x0002.035.00031143 uba: 0x59401d01.4483.1b --U- 1 fsc 0x00
00.0d8ed992
0x0a xid: 0x0006.010.000335f7 uba: 0x10c06d66.464b.11 --U- 1 fsc 0x00
00.0d96cc19
0x0b xid: 0x0001.00d.00032a27 uba: 0x10c00b0e.456c.0d --U- 1 fsc 0x00
00.0d96cc1d

Leaf block dump

header address 9223372041150438708=0x80000001000a9d34 kdxcolev 0
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 3
kdxcosdc 1
kdxconro 514
kdxcofbo 1064=0x428
kdxcofeo 1118=0x45e
kdxcoavs 54
kdxlespl 0
kdxlende 0
kdxlenxt 1434461029=0x55802365
kdxleprv 1434456775=0x558012c7
kdxledsz 0
kdxlebksz 7800

There are some index blocks which have all the ITL slots used and some of them that don't. They have only 2 slots used. For the table, all ITLs have been used for almost all the table's blocks.

[jl] There are some oddities with indexes and ITLs. It is possible
[jl] that the blocks with only two slots used are branch blocks.
[jl] There is also the oddity that when the first block created for the
[jl] index (which is both a leaf and branch) splits, both the leaf blocks
[jl] inherit the ITL count from the parent. Since the first block created
[jl] is a branch block, it starts with only 2 ITL slots - whatever you
[jl] specify for initrans.

By checking the ITC value I was trying to find an ITC higher than 11, if I was lucky and found something like that, this would have meant that at some point in time there were more than 11 transactions going on and that an additional ITL slot was needed and was allocated (as maxtrans and pctfree allowed it). am I correct?

[jl] Yes - but if itc was greater than 11, you would see more than 11
[jl] slots in the ITL - slots do not get reclaimed.

I really appreciate your comments.
Thank you.
Regards,
Diego.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon May 03 2004 - 13:50:43 CDT