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:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                              412          41.8
buffer busy waits                   415,575         299      1   30.4 Concurrenc
latch: cache buffers chains          39,045           2      0     .2 Concurrenc
latch free                            3,906           1      0     .1 Other
latch: enqueue hash chains            5,658           0      0     .0 Other

While the new/broken 11.2.0.4.2 stats are here:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                            Tota    Wait   % DB
Event                                 Waits Time Avg(ms)   time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
DB CPU                                        59           85.9
enq: TX - contention                    198  2.3      11    3.3 Other
buffer busy waits                    22,290  1.4       0    2.0 Concurrenc
local write wait                        292    1       3    1.4 User I/O
latch: object queue header ope          734   .2       0     .3 Other
latch: cache buffers chains           4,161   .1       0     .2 Concurrenc

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 :

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                            Tota    Wait   % DB
Event                                 Waits Time Avg(ms)   time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
DB CPU                                      34.8           94.4
log file switch (checkpoint in            6  1.7     275    4.5 Configurat
buffer busy waits                    18,247   .4       0    1.1 Concurrenc
latch: cache buffers chains           4,617   .1       0     .2 Concurrenc
log file switch completion               16   .1       4     .2 Configurat
enq: TX - index contention              246    0       0     .1 Concurrenc

as it shows that BASIC FILEs are faster on CPUs for this particular workload when you completley eliminate I/O ;)

Comments are closed.