Index Leaf block split - 90-10 - 50-50 issue - monotonically increasing value

From: Vishnu Potukanuma <vishnupotukanuma_at_gmail.com>
Date: Fri, 29 Nov 2019 18:39:13 +0530
Message-ID: <CAP-Rywzqx2_joTbTySo6YACpM3KZRsBR62Gers2GxbzfLF8uQw_at_mail.gmail.com>



Hi,

We know that when the value of the index column is monotonically increasing as is in the case with column values populated by sequences, we have 90-10 splits..

This is true only when we insert data into the table using only session, in which case every eventual leaf block split is 90-10 and this is also true when when multiple sessions insert the data into the table when the database sequence is created with nocache and order. this is presents a unique issue with respect to index leaf block splits, when multiple sessions try to insert the data we end up with sequence related contention in which case it reduces the TPS that we get, to mitigate this we have to increase the cache size to make sure we avoid sequence related contention, and this is cache situation presents a unique situation, following a index block split (right most leaf blocks). which can be either 90-10 or 50-50 and resulting in space wastage, and the gap between 90-10 and 50-50 splits increases with the cache size and the number of sessions and increase in the value size. due to these splits 50-50 less entries are stored in the index leaf blocks. resulting in more space wastage later on as the size of the value increases. even though the growth of the index is right hand side, the 50-50 block splits results in space wastage.

but it is only when we reduce the cache size the situation chances to more 90-10 splits, but this also holds true when less number of sessions inserts the data, and when the session increase to a particular level, there are more 50-50 block splits, we know that the primary key value or unique key value increases monotonically the chances of updating a primary key column such as order_id never happens, and these index leaf blocks end up with more space wastage.

rebuilding an index to release the free space or shrinking will make sense when we have reserved enough INITRANS for further updates to same leaf block, which can only be deletes as updates to primary key value are very less in any application, and deletes are through deleting or row movement to a different partition (local partitioned indexes).... but rebuilding or shrinking space of an an primary key to save space may not make sense most of the times as it never affects the number of lookups required to find an entry based on the primary key lookup.

the case is a bit different when multiple sessions insert data into the table concurrently using an application sequence depending on the synchronization method used to generate these values, but the case goes weird only when we use synchronized method to serialize the access to a block between multiple sessions. in this case there is at least 50% more 50-50 block splits. Here is the code i used: public class oraSequence {
static volatile int value = 0;
synchronized static int nextVal() {
value++;
return value;
}

synchronized static int getval() {
return value;
}
}

Synchronized makes sure that the only one thread calls it and volatile protects the value with regards to multithreads. in this case due to its inherent behavior with synchronized where a thread may end up calling the function again and again, to mitigate this we can use other synchronization methods.. the gap can increase significantly even then the change of hitting a block with 90-10 with more rows so to cause it to split into 50-. as value grows, and we can see this as follows:

pool-1-thread-10 ---> 191548

pool-1-thread-10 ---> 191549

pool-1-thread-4 ---> 188987

pool-1-thread-7 ---> 188953

pool-1-thread-7 ---> 191552

The 50-50 block splits can increase when the length of the indexed data column increases... whether it is unique or not unique when the values are monotonically increasing. This may not present any challenge when the average row length is high for a table, as the size of the index is typically small in relation to a table size.

for an IOT this can even become a bigger issue in terms of space occupied as other columns can also be stored.

The other way to get around this problem is to increase the database block size, in which case the blocks contention increases as we are hitting the same block.... but using hash partitioning can alleviate the issue with regards to space wastage but undermines the queries with predicates containing ranges.

any technique to avoid fragmentation due to 50-50 block splits in this situation increase the chances of contention either at the leaf block due to buffer busy or contention at the sequence level there by reducing the efficiency of the index for inserts with monotonically increasing values resulting in right most block contention.

*can someone please tell me if there is any other way to get around this problem and find a perfect balance between the space wastage and the cost of rebuilding/shrink or coalesce such an index?*

This does not affect any select operations or joins involving the primary key columns, but the efficiency of the buffer cache decreases as the blocks are just 50% full, especially on only few edge cases or rare scenarios when index blocks are cached a lot... BTW while performing the tests i never used batchinsert as this has more potential to cause 50-50 splits with application sequences.

*I am not blaming Oracle or defaming it, i strongly support the use of oracle databases... other databases in these scenarios are even worst!!! *

the code that I used to test is as follows: void createTable() {
try {
Connection oraCon = DBConnection.getOraConn(); Statement stmt = oraCon.createStatement(); try {
String SQL = "drop table RandomLoad";
stmt.execute(SQL);
}

catch(Exception E) {
}

String SQL = " *create table randomload (roll number, name varchar2(20),  mark1 number , mark2 number, mark3 number, mark4 number, mark5 number, mark6 number)*";
stmt.execute(SQL);
//SQL = "create index randomload_idx on randomload(roll) "; SQL = "*create unique index randomload_pk on randomload(roll)*"; stmt.execute(SQL);
System.out.println("Created Tables and indexes, Starting Load");
}

catch(Exception E) {
E.printStackTrace();
}
}

 void loadTable() throws InterruptedException { ExecutorService asd = Executors.newFixedThreadPool(30); int i = 0;
while (i < 20) {
asd.submit(new InsertLoad());
i++;
}

i = 0 ;
System.out.println("Loading Data... Sleepin for 10 seconds"); asd.shutdown();
while(!asd.isShutdown()) {
Thread.currentThread().sleep(1000);
}

asd.shutdownNow();
}

class InsertLoad implements Runnable{
public void run() {
try {
System.out.println("Staring Insert Thread -->" + Thread.currentThread().getName());
Connection oraCon = DBConnection.getOraConn(); PreparedStatement pstmt = oraCon.prepareStatement("insert into RandomLoad (roll, name, mark1,mark2,mark3,mark4, mark5, mark6) values (*seq.nextval* ,?,?,?,?,?,?,?)");
int i = 0;
while (i < 30000000) {
// pstmt.setInt(1 , oraSequence.nextVal());

pstmt.setString(1, OraRandom.randomString(20));
pstmt.setInt(2, OraRandom.randomSkewInt(100));
pstmt.setInt(3,  OraRandom.randomSkewInt(200));
pstmt.setInt(4,  OraRandom.randomSkewInt(400));
pstmt.setInt(5, OraRandom.randomSkewInt(800));
pstmt.setInt(6,  OraRandom.randomUniformInt(1600));
pstmt.setInt(7,  OraRandom.randomUniformInt(3200));
pstmt.executeUpdate();

/* if (i%10000 == 0) {
pstmt.executeBatch();
System.out.println("loaded " + oraSequence.getval());
}*/

i++;
}

pstmt.close();
oraCon.close();
 DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");   LocalDateTime now = LocalDateTime.now();   System.out.println(dtf.format(now));
}

catch(Exception E) {
E.printStackTrace();
}
}
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 29 2019 - 14:09:13 CET

Original text of this message