Best practices for target DB and OS settings

Target database application settings

1. Oracle:

   1. Provision with 3 x 5GB online redo logs (minimum) to avoid pause when transaction logs wraparound.

   2. Provision in NOARCHIVELOG mode to reduce transaction log IO. Masking, Test, QA VDBs rarely need point-in-time rewind

   3. Always check initialization parameters inherited from parent, remove any expensive or irrelevant parameters.

      1. DB_CACHE_SIZE, SGA_TARGET: set based on target system being compared to.

      2. FILESYSTEMIO_OPTIONS to SETALL. Any other setting inherited from source is probably wrong.

      3. DB_BLOCK_CHECKSUM, DB_BLOCK_CHECKING, DB_LOST_WRITE_PROTECT, DB_ULTRA_SAFE: set to default values to minimize impact.

      4. PARALLEL_DEGREE_POLICY to AUTO, PARALLEL_MAX_SERVERS default, PARALLEL_EXECUTION_MESSAGE_SIZE to 32768 (maximum): improve PQ performance.

      5. FAST_START_MTTR_TARGET: drives steady write activity. Set based on target system being compared to.

      6. Consider non-durable commits for Masking, Test, QA, UAT: set COMMIT_WAIT = NOWAIT, COMMIT_LOGGING = BATCH

   4. Use Oracle Direct NFS (dNFS) for 11.2.0.4+ (unstableon older releases):

      1. Recommended documentation:

         • Oracle direct NFS configuration

         • Configuration examples and troubleshooting blog from Helmut Hutzler

         • Sample oranfstab to leverage multiple network paths for Delphix VDB

      2. Set DNFS_BATCH_SIZE = 128 (default is 4096). This is a good starting point and sufficient for most workloads.

      3. Tune TCP stack: set tcp_adv_win_scale = 2 due to workaround hard-coded Oracle dNFS TCP buffer size.

      4. Check Alert Log, V$DNFS_SERVERS, V$DNFS_FILES, V$DNFS_STATS to verify proper working (sample here).

   5. Create AWR snapshots around a reference customer workload, generate an AWR report.

      1. AWR snap before/after workload: SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

      2. AWR report between the snaps: SQL> @?/rdbms/admin/awrrpte

   6. Generate ASH report to diagnose bottlenecks while a workload is running.

      • SQL> @?/rdbms/admin/ashrpt

   7. Run synthetic benchmark sc-workload.

   8. Where db file scattered read (multiblock cached read) latency is high consult this Support KB: How to mitigate multi-block read performance on Oracle 10g

   9. Improve distributed query performance by modifying dblinks to use local IPs instead of SCAN IPs.

   10. NFS recommended mount options for Oracle RAC/SI: Oracle support note 359515.1

Target Host OS Settings

1. Existing documentation on Target OS practices: Target host configuration options for improved performance

2. HP-UX 11.31+

   • Async NFS direct I/O: HP-UX requires Oracle disk_asynch_io turned off for filesystems

3. IBM AIX:

   1. Consult IBM documentation on AIX TCP Tuning

   2. AIX TCP tuning prezo

4. Windows:

   1. Anti-virus programs can impact both performance and operation. Delphix recommends anti-virus scanning exclude folders where Delphix files are maintained, in addition to the normal exclusions put in place for MSSQL operation.

   2. Delphix Connector (aka DX Connector):

      1. Plan 3-5GB for the Delphix Connector installation.

      2. Windows does not yet have ssh, so Delphix developed the "DX Connector for Windows target host communication.

      3. The connector must be installed on all Target Windows hosts.

      4. The connector supports two modes – v1 and v2 both use the same application binaries.

      5. The connector v1 process is used to bootstrap the v2 process on a target. This opens a DSP session back to the Delphix Engine (The same thing is done via SSH on U*nix Targets)

      6. v2 mode is required to enable SQL hooks

      7. The connector can always be downloaded from a local Delphix Engine at: http://<delphix_engine>/connector/DelphixConnectorInstaller.exe.

      8. The connector is backwards compatible, so it is not always necessary to upgrade it during a Delphix upgrade.

   3. iSCSI connections:

      1. Read the following for general awareness of iSCSI limits

      2. In addition to the hard limits on iSCSI connections, consideration must be given to the RAM, CPU and Network to provide sufficient resources for the load on any Target or Staging host.

      3. To increase the iSCSI timeout on both Target and Staging hosts.

      4. In certain circumstances it's possible that iSCSI startup will not complete before the SQL Service attempts to start a database. In such circumstances, it can be helpful to ensure the SQL service depends on the iSCSI service.

         •  Example: c:\> sc config "MSSQLServer" depend="Microsoft iSCSI Initiator Service"

      5. Note that any changes to iSCSI are system-wide and could potentially impact other applications also leveraging that feature.

   4. Enable receive side scaling (RSS) on each network interface that Delphix will be connecting to.