Re: Index rebuilds

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 09 Oct 2003 13:53:58 GMT
Message-ID: <WLdhb.143404$bo1.139470@news-server.bigpond.net.au>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bm329u$h43$1$8300dec7_at_news.demon.co.uk...
>
> Where did you get the line about the last block
> never being filled more than 100-PCTFREE ?
> I've not seen it before. It's fairly easy to prove
> that it's not (always) true - but I'd be interested
> to see a test case that demonstrates it.
>
> It is true that Oracle reports a statistics called
> something like:
> index leaf splits 90/10
>
> But to date, I've never seen a 90/10 split,
> only things that are roughly 50/50 splits,
> and things that could be called 100/0 splits -
> the latter being things that then record under
> the 90/10 statistic.
>

Hi Jonathan,

I'm with you (guess you're a bit worried now eh ;)

Oracle reports 'leaf node splits', which I've always interpreted as the 50-50 split.

Oracle reports 'leaf node 90-10 splits' which I've always interpreted as the 100-0 split, and

Oracle reports 'branch node splits' which I've always interpreted as the, well I guess branch node split

Having not much to do in sunny Canberra this evening (rugby world cup doesn't start until tomorrow), I thought I'll dump a leaf index block that has just "split" due to a maximum index entry just being inserted and yes, the behaviour is still the same on 9.2:

Start dump data blocks tsn: 39 file#: 16 minblk 482 maxblk 482 buffer tsn: 39 rdba: 0x040001e2 (16/482) scn: 0x0000.03750e66 seq: 0x01 flg: 0x00 tail: 0x0e660601 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Block header dump: 0x040001e2
 Object id on Block? Y
 seg/obj: 0x7aa0 csc: 0x00.3750e66 itc: 2 flg: O typ: 2 - INDEX

     fsl: 0 fnx: 0x40001e3 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01 0x000f.009.0000480e 0x0340016a.08fa.01 CB-- 0 scn 0x0000.03750e63
0x02 0x000f.00c.0000480e 0x03400047.08f9.4e C--- 0 scn 0x0000.03750e65

Leaf block dump

---

header address 96211036=0x5bc105c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 7999=0x1f3f
kdxcoavs 7961
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 67109344=0x40001e0
kdxledsz 0
kdxlebksz 8036
row#0[7999] flag: -----, lock: 0
col 0; len 27; (27):
 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a  7a 69
col 1; len 6; (6): 02 40 38 7b 00 27
----- end of leaf block dump -----
End dump data blocks tsn: 39 file#: 16 minblk 482 maxblk 482

This new index block only contains the one and only maximum index entry (the 100-almost 0 split).

I posted in the past what a 50-50 split looks like but I too have not seen the illusive 90-10 split.

If anyone newish wants me to post how I performed the above test, just let me know.

Cheers

Richard Received on Thu Oct 09 2003 - 08:53:58 CDT