Re: Single row table and LIO

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Tue, 09 Nov 2004 20:43:59 GMT
Message-ID: <j_9kd.30514$K7.19319@news-server.bigpond.net.au>

"dejan" <dejan.colja_at_siol.net> wrote in message news:107f8a43.0411090743.79e25949_at_posting.google.com...
> Hi
> --
> I have problem with LIO on small sigle row tables.
> Table was created in tablespace LMT/ASSM/Uniform and we are using
> Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production on unix.
> --
> Tablespace DATA_S have 8k block and extent = 128k (16 blocks)
> --
> Table with single record was created in this tablespace, Average
> record size computed by DBMS_STATS = 178 bytes.
> This means that at least 5 such records should stand in one block.
> --
> Immediately after inserting 1 row HWM goes up to block 15 ??
> Full table scan all the time result in 15 LIO blocks. ?? !!!! The
> most frustrating is fact that in production database we need just 3
> LIO
> for the same table (exactly the same tablespace definition and query)
> and I don't know why. (it could be opposite !!)
> --
> Thanks for any explanation or hint.
>

Hi Dejan

It's all due to ASSM and the manner in which it deals with the Low HWMs and High HWM. Some of the details can be found in the Oracle doco. Also check out Tanel Poder's excellent presentation (http://integrid.info/Poder_Freelists_vs_ASSM.ppt).

Perhaps one reason for not using ASSM with tiny tables.

Cheers

Richard Received on Tue Nov 09 2004 - 14:43:59 CST