RE: Large in memory DW table

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Fri, 16 Nov 2007 11:18:45 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CFDA2BDF@WIN02.hotsos.com>

Actually that is "new". With Indexes that was ALWAYS true, but with tables that stared in 9 something-er-other.

Ric Van Dyke

Hotsos Enterprises

From: Nigel Thomas [mailto:nigel_cl_thomas_at_yahoo.com] Sent: Friday, November 16, 2007 11:16 AM To: Ric Van Dyke; oracle-l_at_freelists.org Subject: Re: Large in memory DW table

Ric, unless things have changed recently you *always* get at least 2 slots in the ITL, even when you specify just one.

See earlier threads eg
http://www.freelists.org/archives/oracle-l/03-2005/msg00811.html.

I repeated Mark Powell's test (create table ... initrans 1) on XE 10.2.0.1.0 and my block dump looks like this:

Block header dump: 0x0040da0a

Object id on Block? Y

seg/obj: 0x5fb8 csc: 0x00.63166d itc: 2 flg: - typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0009.016.00000c35 0x008002e9.0cf9.03 C--- 0 scn 0x0000.0063166c

0x02 0x0001.010.0000095b 0x00801519.0bbf.0f --U- 25 fsc 0x0000.0063166e

Regards Nigel

Original Message ---- From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>

<snip>

ITLs are not used for a SELECT, however they are checked by a SELECT (or any other statement that touches a block) to see if there are still valid transactions in the block. The disadvantage to forcing only one ITL slot is that there can never be more then one transaction ever doing DML in the block. I think setting it to at least 2 might be a better idea, just gives you some options for the future.

<snip>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 16 2007 - 11:18:45 CST