Target Database Application Settings
Provision with 3 x 5GB online redo logs (minimum) to avoid pause when transaction logs wraparound.
Provision in NOARCHIVELOG mode to reduce transaction log IO. Masking, Test, QA VDBs rarely need point-in-time rewind
Always check initialization parameters inherited from parent, remove any expensive or irrelevant parameters.
DB_CACHE_SIZE, SGA_TARGET: set based on target system being compared to.
SETALL. Any other setting inherited from source is probably wrong.
DB_BLOCK_CHECKSUM, DB_BLOCK_CHECKING, DB_LOST_WRITE_PROTECT, DB_ULTRA_SAFE: set to default values to minimize impact.
PARALLEL_EXECUTION_MESSAGE_SIZEto 32768 (maximum): improve PQ performance.
FAST_START_MTTR_TARGET:drives steady write activity. Set based on target system being compared to.
Consider non-durable commits for Masking, Test, QA, UAT: set
COMMIT_WAIT = NOWAIT, COMMIT_LOGGING = BATCH
Use Oracle Direct NFS (dNFS) for 18.104.22.168+ (unstable on older releases):
Set DNFS_BATCH_SIZE = 128 (default is 4096). This is a good starting point and sufficient for most workloads.
- Tune TCP stack: set
tcp_adv_win_scale= 2 due to workaround hard-coded Oracle dNFS TCP buffer size.
- Check Alert Log, V$DNFS_SERVERS, V$DNFS_FILES, V$DNFS_STATS to verify proper working (sample here).
- Create AWR snapshots around a reference customer workload, generate an AWR report.
AWR snap before/after workload:
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
AWR report between the snaps:
Generate ASH report to diagnose bottlenecks while a workload is running.
Run synthetic benchmark
db file scattered read(multiblock cached read) latency is high consult this Support KB: How to Mitigate Multi-Block Read Performance on Oracle 10g
Improve distributed query performance by modifying dblinks to use local IPs instead of SCAN IPs.
- NFS recommended mount options for Oracle RAC/SI: Oracle Support Note 359515.1.
Target Host OS Settings
Existing documentation on Target OS practices: Target Host Configuration Options for Improved Performance
Async NFS Direct I/O: HP-UX requires Oracle
disk_asynch_ioturned off for filesystems
Consult IBM documentation on AIX TCP Tuning
- 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.
- Delphix Connector (aka DX Connector):
- Plan 3-5GB for the Delphix Connector installation.
- Windows does not yet have ssh, so Delphix developed the "DX Connector for Windows target host communication.
- The connector must be installed on all Target Windows hosts.
- The connector supports two modes – v1 and v2 both use the same application binaries.
- 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)
- v2 mode is required to enable SQL hooks
- The connector can always be downloaded from a local Delphix Engine at: http://<delphix_engine>/connector/DelphixConnectorInstaller.msi.
- The connector is backwards compatible, so it is not always necessary to upgrade it during a Delphix upgrade.
Read the following for general awareness of iSCSI limits
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.
To increase the iSCSI timeout on both Target and Staging hosts.
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"
Note that any changes to iSCSI are system-wide and could potentially impact other applications also leveraging that feature.
- Enable Receive Side Scaling (RSS) on each network interface that Delphix will be connecting to.