Re: leaf node splits

From: Dom Brooks <dombrooks_at_hotmail.com>
Date: Fri, 27 Jan 2012 16:27:23 +0000
Message-ID: <BLU0-SMTP3750D270D29BF25681EE81CA18E0_at_phx.gbl>

Perhaps what I should have asked is as index splits are recursive transactions unaffected by my rollback, why do I consistently see the same number of 90-10 splits in these subsequent transactions?    

>How many list subpartitions in each partition ?
3 subpartitions in each of 64 partitions  

>Are you using ASSM or freelist management on the tables or indexes ?
ASSM.  
>Do any of the indexes have very repetitious key values ?
Yes.  

One of these global indexes is a unique composite index with 6 columns. 17 million rows in table.

Column 1 - 3786 distinct values
Column 2 - 1000862 distinct values
Column 3 - 2 distinct values
Column 4 - the partition date column - 64 distinct values
Column 5 - 2 distinct values
Column 6 - 2 distinct values
 

(From an index maintenance perspective, the date column would probably be better at position 1 but some queries might suffer as a result - something to investigate)  

Two other non-unique local indexes - one on column 2 above; one composite on the subpartition key (140 distinct values), column 1, column 2.    

>Does the insert include a "sequence.nextval" and if so is that column indexed ?
Yes. Another global unique index on the primary key populated by sequence number.  

>Is the volume of data (including indexes) large relative to the size of the cache ?
Table - 3G
Indexes - 3G
Buffer Cache - 16G

Cheers,
Dominic--
http://www.freelists.org/webpage/oracle-l Received on Fri Jan 27 2012 - 10:27:23 CST