Today i want show you a comparison between Securefiles and Basicfiles. I know that there are some other sites out there which are showing how fast Securefiles can be, but in my Tests i will also try to show some more facts and benefits about the NOLOGGING Option which i have described in a earlier in my Blog article NOLOGGING What it is (and what it is not). For Basic Information look at my article Securefiles overview.
The default value of the initialization parameter DB_SECUREFILE has changed in 12c from
The tests were performed on a Oracle Exadata X2 (Patch level January 2014) running OEL 5 with a 220.127.116.11 Database. Your actual performance might vary depending on your test environment setup. My tests are done using the NOCACHE Options that is mandatory with the NOLOGGING Option.
All Test are done with the table which is described below.
CREATE TABLE lob_test (id raw(16) default sys_guid() primary key, lob1 BLOB, lob2 BLOB, lob3 BLOB, lob4 BLOB, lob5 BLOB) LOB(lob1) STORE AS SECUREFILE( NOCOMPRESS NOCACHE LOGGING ) LOB(lob2) STORE AS SECUREFILE( NOCOMPRESS NOCACHE NOLOGGING) LOB(lob3) STORE AS SECUREFILE( NOCOMPRESS NOCACHE FILESYSTEM_LIKE_LOGGING) LOB(lob4) STORE AS BASICFILE ( NOCACHE LOGGING ) LOB(lob5) STORE AS BASICFILE ( NOCACHE NOLOGGING);
What I have tested?
I have done a Load and a Unload Test with 5 Gigabyte of Data (500 Files) and every column is tested separately. I tested the NOLOGGING Option versus the LOGGING Option and the Basicfile versus the Securefile. I also Test the FILESYSTEM_LIKE_LOGGING Option which should have no or minimal difference in comparison to the NOLOGGING because the Documentation says….
In this case, if
NOLOGGINGis the default value, the SecureFile will default to
Also remember that this is not a Performance Benchmark, my Testcode simulates a normal Application. This should be a comparison to give you a picture of the Performance improvement that is possible on my Hardware.
Test #1 Execution Time
The Load Time means the Time the Database needs to load the 500 Files into the Table. The difference between lob2 and lob3 is pretty the same what makes sense. The Basicfile lob5 (NOLOGGING) needs 127 seconds to load the Data, on the other side lob3 only needs 14 seconds. In this case the Securefile is 9 times faster then the normal Basicfile.
Loading the 500 files from the Table to the Filesystem give us a very similar picture. The Securefiles needs around 550 seconds to Unload. The Basicfile lob5 needs 2351 seconds. Also in this scenario the Securefiles are 4 times faster then the Basicfiles.
Test #2 IO Performance
Test #3 NOLOGGING Performance
NOLOGGING Performance also means how many Redo Information is written. Securefile lob1 needs around 5100MB Redo which is similar to the size of data we load into the Database. The interesting thing is, that the Basicfile writes 176MB of Redo Data. The Securefile only writes 30MB of data that is around 6 times fewer.
If you are interested in test code or else have questions, please leave me a comment.