Andy Kerber (@dbakerber), Senior Consultant

In this blog I will examine the effects ‘setall’ and ‘none’ on the Oracle initialization parameter filesystemio_options. This setting is used to set I/O parameters for the Oracle database when the data files are stored on file system rather than ASM.

Test Environment

The Oracle database version is 12.1.0.2, and the OS version is RHEL 7.6. The VMware Workstation and Oracle VirtualBox versions are the most recent available – VirtualBox version 5.2.12r122591 and VMware Workstation Pro version 12.5.7 build-5813279. The NMON analyzer version is 5.21 and SLOB version is 2.4.0.

Explanation of filesystemio_options

I/O operations in UNIX and Linux systems typically go through the file system cache. Although this doesn’t represent a problem in itself, this extra processing does require resources. Bypassing the file system cache reduces CPU requirements, and frees up the file system cache for other non-database related file operations. Operations against raw devices automatically bypass the file system cache.

When a synchronous I/O request is submitted to the operating system, the writing process blocks until the write is complete before continuing to process. With asynchronous I/O, processing continues while the I/O request is submitted and processed. This allows asynchronous I/O to bypass some of the performance bottlenecks associated with I/O operations.

Oracle can take advantage of direct I/O and asynchronous I/O on supported platforms using the FILESYSTEMIO_OPTIONS parameter, the possible values for which are listed below.

• ASYNCH – enables asynchronous I/O where possible
• DIRECTIO – enables direct I/O where possible
• SETALL – enables both direct I/O and asynchronous I/O where possible
• NONE – disables both direct I/O and asynchronous I/O

Configuration

Both VMs were created as new VMs, and each VMDK was pre-allocated to full size. The VM (on both VirtualBox and VMware Workstation) were created with 16G RAM and 1 CPU. Swap size was set to 16G also. A second VMDK was added for the Oracle datafiles, size 80G (once again, pre-allocated). This was mounted on /u02. In each, an Oracle database was created SID iotest, and disk_asynch_io=TRUE.

The SLOB tablespace was created with the tablespace name slob, and pre-allocated to 32G in size in order to eliminate waits on file extension. We created 64 slob schemas, and tested with 32 schemas using this command: ./runit.sh 32

Testing Process

In the testing process, filesystemio_options was set to setall in the spfile, the system rebooted and the database started. Each time the filesystemio_options setting was changed, the system was rebooted in order to make sure there was no caching interfering with the process. Each test ran for one hour. It was expected that the filesystemio_options=setall would produce measurably, though not substantially better, I/O performance than filesystemio_options=none.

Results

However, the results of the test were surprising, and consistent on both VirtualBox and VMware Workstation.

Below is a typical graph from NMON analyzer with filesystemIO_options=SETALL:

In the AWR reports we see this for the top foreground wait events:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                       Total Wait  Wait     % DB    Wait
Event                            Waits Time (sec)  Avg(ms)  time    Class
------------------------------ ------- ---------- -------- ----- --------
db file sequential read          55,702     11.1K   199.49  74.1 User I/O
db file parallel read             7,101    3890.4   547.86  25.9 User I/O
DB CPU                                       17.9             .1
cursor: pin S wait on X              53       2.9    55.09    .0 Concurre
library cache load lock              93       2.3    24.25    .0 Concurre
db file scattered read               47         2    41.62    .0 User I/O
read by other session               107       1.6    15.19    .0 User I/O
log file sync                        27       1.5    55.75    .0 Commit
Disk file operations I/O             63       1.3    21.14    .0 User I/O
control file sequential read        123       1.1     9.20    .0 System I



Wait Classes by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Avg             Avg
                            Total Wait    Wait   % DB   Active
Wait Class         Waits    Time (sec)    (ms)   time Sessions
-------------- ---------- ------------ ------- ------ --------
User I/O           71,514       15,558  217.55 103.7     8.3
System I/O         14,235        1,071   75.23   7.1     0.6
DB CPU                                            .1     0.0
Concurrency           203            6   29.23    .0     0.0
Commit                 38            2   49.41    .0     0.0
Other                 526            2    3.04    .0     0.0
Network                80            0    0.00    .0     0.0

Below is typical output when filesystemIO_options=NONE.

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                            Total  
                                             Wait           
Event                              Waits     Time     Wait  % DB     Wait
                                            (sec)  Avg(ms)  time    Class
------------------------------ ---------- ------- -------- ----- --------
free buffer waits               2,528,630   26.2K    10.35  90.0 Configur
db file parallel read             226,393    1569     6.93   5.4 User I/O
db file sequential read         1,603,321   320.2     0.20   1.1 User I/O
buffer exterminate                 19,919   200.4    10.06    .7 Other
DB CPU                                        138             .5
write complete waits                   47   136.9  2912.46    .5 Configur
latch: cache buffers chains        16,208     135     8.33    .5 Concurre
latch: cache buffers lru chain     26,913   116.8     4.34    .4 Other
db file scattered read              1,513      49    32.39    .2 User I/O
log buffer space                       99    23.5   237.62    .1 Configur




Wait Classes by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                    Avg             Avg
                                  Total Wait       Wait   % DB   Active
Wait Class            Waits       Time (sec)       (ms)   time Sessions
-------------- ------------ ---------------- ---------- ------ --------
Configuration     2,792,212          29,067       10.41  100.0     8.1
System I/O           23,147           3,611      155.99   12.4     1.0
User I/O          1,867,661           1,953        1.05    6.7     0.5
Other                53,904             396        7.34    1.4     0.1
Concurrency          18,577             229       12.32     .8     0.1
DB CPU                                  138                 .5     0.0
Commit                   50               1       13.11     .0     0.0
Network                  72               0        0.00     .0     0.0

As you can see, it appears that filesystemIO_options=SETALL is being choked by the CPU resource, while filesystemIO_options=NONE has plenty of CPU available. Most likely the choke point is simply the I/O bandwidth.

At one of our recent clients, setting the filesystemio_options=setall in an Oracle database running in vSphere virtualization on enterprise level storage caused a roughly 30 percent improvement in the I/O performance of the database.

Conclusions

What are the implications of this? Most obviously, the filesystemIO_options setting on a VM probably needs to be evaluated on an individual basis when using filesystem. Additionally, it may imply that the ASM is the better choice in a virtualized environment. While the results of this experiment have been interesting, I plan to follow up with more research in more powerful environments in the future in order to determine how much the underlying hardware affects the recommendations for this setting.