Oracle Database best practices

The vast majority of configuration settings for Oracle Database will remain unchanged. However, you may want to adhere to the following suggestions for best performance.

Install the appropriate Oracle JDBC driver into your application server to allow NexJ applications to communicate with the database.

The following settings are required for a production environment:
  • When running the script oracle_create_database.sql, if you encounter error ORA-12638, comment out the line SQLNET.AUTHENTICATON_SERVICES=(NTS) in ORACLE_HOME\network\admin\sqlnet.ora.
  • Ensure that datasource, environment, and connection files are up to date, and set any additional parameters through NexJ Studio. Unset parameters will use template defaults where available.
  • Set the memory size to 15% of the total development desktop capacity.
  • In a production environment, database users should only be members of the njdbuser database role, which provides create, read, update and delete (CRUD) access to the data contained in NexJ tables. No other database roles are required.
The following settings are recommended for a production environment:
  • Configure the size of redo log files to improve performance. Determine the optimal size by querying the OPTIMAL_LOGFILE_SIZE column in the V$INSTANCE_RECOVERY view. Log files ranging from 100 MB to a few gigabytes are considered reasonable.
  • If your application is designed for an older release of Oracle Database and you are installing a later version, set the COMPATIBLE initialization parameter to the older release. This setting allows you to use the maintenance improvements of the new release in the production environment without having to test the new functionality.
  • Set the block size to 8192. A setting of 8192 is typical for transaction processing systems, and larger for database warehouse systems.
  • Configure tablespaces to use automatic segment-space management. Oracle will automatically manage segment space for best performance.
  • All databases can benefit from a higher open_cursors setting. It is recommended that you set the value to the relational database connection's statement cache size + 10. There is no extra cost to setting the open_cursors higher, as cursors are allocated only when needed. This setting simply sets an increased upper limit.

The following settings are best practices for development:

  • Set the memory percentage to 50%.
  • Set the connection mode to Dedicated Server Mode.
Note: Documentation for Oracle Database can be found here: http://docs.oracle.com.