Part 4: Oracle SecureFile LOB is better than BasicFile, isn’t it?

In part 3 we have shown that SecureFiles were having much lower space utilization (they reused space) on 11.2.0.3.8. I’ve wanted to try the same workload on 11.2.0.4.2 (also on ramdisks, same ammount of concurrent jobs performing INSERTs and DELETEs, same ammount of data/variables for loops), the results are more or less VERY interesting:

Click to see in better resolution

Clearly there is some regression when it comes down to MB used afterwards (at the end of test “SELECT count(1) FROM t_blob” returns just 21 rows! so you really shouldn’t have ~700MB lost). The collected AWR snapshots also reveal lack of high “buffer busy waits” on 11.2.0.4.2 for securefiles when they were present on 11.2.0.3.8. AWR 11.2.0.3.8 is below:

As been metnioned before I/O has been elimiated by using ram disk (with filesystem_options=NONE due to the lack of proper O_DIRECT support on tmpfs). Much lower CPU utilization (412s to 59s) is very interesting, but as you can see this comes at price. Even more interesting is the old BASIC FILE AWR report on 11.2.0.4.2 :