sparse index/block splits & structure

hiya folks,

dealing w/a sparse index issue and have a couple of questions. first the facts:

• 8.1.7.3.0 64 bit, sun solaris, 3rd party app, oltp , 8k block size, btree idxs
• itl slots taking up about half the space of most of the blocks i dump (about 169 entries)
• leaf node splits kicking about all over the dang place
• idx horribly bloated
• deletes are an issue, but in many of the blocks (but not all), the number of rows flagged for delete is not over 50%
• lots of inserts and updates too (dba_tab_modifications shows a roughly equal split between I, U and D for delta processing since the last stats collection)

so i dare say in sparse idx characteristics, its probably pretty normal. no range scans on the thing so i don't have a perf problem, just one of space control (in a month it'll bloat to 400% the size of the table). a reverse key could assist w/the concurrency but it ain't my app, so careful must i tread ... coalescing will quite likely assist, but the dumps i have seen indicate i will still bloat fairly quickly (re 50% comment above) ... so i focused a little more on the block splits and quickly realised it's not too easy to identify the object upon which the splits are occurring.

so here are the direct questions:

• is the kdxcosdc structure the split/delete count? if so, can i use that to identify blocks that have split? (assumption here is i can dump away and isolate block splits to {x} index(s) ...
• is the kdxcoavs structure the free space and is it accurate (some blocks it seems to make sense, others no)?
• what, if any, relationship does the kdxlebksz structure have w/the db block size?
• for those of you who have faced and nailed such a situation, did larger values for pctfree assist?

hope that makes some sense!

any feedback much appreciated,

cheers,

casey ...

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Casey Dyke
  INET: cdyke_at_corp.home.nl

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).