Boost your Database Performance with SecureFiles


oracle_m1

Introduction

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 PERMITTED to PREFERRED

Test Environment

The tests were performed on a Oracle Exadata X2 (Patch level January 2014) running OEL 5 with a 11.2.0.4 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 NOLOGGING is the default value, the SecureFile will default to FILESYSTEM_LIKE_LOGGING.

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

lob_load_timeThe 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.

lob_unload_timeLoading 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

lob_ioThe IO Throughput is measured in MB/s from the Load into the Table. The Securefiles reach 358 MB/s from the local Server Harddisks. The Basicfiles only reach 39 MB/s in NOLOGGING mode.

Test #3 NOLOGGING Performance

lob_redo_sizeNOLOGGING 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.

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s