RE: Large in memory DW table

  • From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
  • To: "Nigel Thomas" <nigel_cl_thomas@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 16 Nov 2007 11:18:45 -0600

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@xxxxxxxxx] 
Sent: Friday, November 16, 2007 11:16 AM
To: Ric Van Dyke; oracle-l@xxxxxxxxxxxxx
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 
//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@xxxxxxxxxx>

<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>

GIF image

Other related posts: