How to set FILESYSTEMIO_OPTIONS for use with RSA Identity Governance and Lifecycle on a remote Oracle database where Automatic Storage Management (ASM) is not being used for better storage performance 

Article Number

000035132

Applies To

RSA Product Set: RSA Identity Governance and Lifecycle
RSA Version/Condition: All
Platform (Other): Non-ASM Oracle database instances (soft-appliance and/or remote databases)
 

Issue

For non-ASM (Oracle Automatic Storage Management) database implementations, RSA recommends the explicit setting of the FILESYSTEMIO_OPTIONS initialization parameter to SETALL in order to enable direct and asynchronous disk I/O. It can make a huge difference in storage performance. This is for non-ASM Oracle database implementations only.

To determine if your Oracle database is an ASM implementation:

Login as SYSDBA and run the following SQL command:

$ sqlplus / as SYSDBA
SQL> SELECT * FROM v$asm_client:

If this view returns no rows, then your system is a non-ASM Oracle implementation.


For more information on the FILESYSTEMIO_OPTIONS initialization parameter, visit the Oracle web site.  

• Click for information on Oracle 11g
• Click for more information on Oracle 12c.

Task

Check the current value of this parameter to see if it needs to be set. The default value is typically none.

Login as sysdba, then run the following:

SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS;

If the value is something other than SETALL, it needs to be modified.  For example,

SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none

Resolution

Modify the FILESYSTEMIO_OPTIONS setting to SETALL, restart Oracle, and check that the setting has been modified:

1. Modify the setting:

SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

2. Restart Oracle

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

3. Check that the setting has been updated:

SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

For example,

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 4 09:53:56 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SET LINE 300
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
filesystemio_options                 string                           none
SQL>
SQL>
SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

System altered.

SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> STARTUP
ORACLE instance started.

Total System Global Area 7063207936 bytes
Fixed Size                  2940568 bytes
Variable Size            1308623208 bytes
Database Buffers         5737807872 bytes
Redo Buffers               13836288 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
filesystemio_options                 string                           SETALL
SQL>