Overview

If you use Datical DB to order, execute and track DML scripts with other database changes, there are some special considerations when those files become very large. If large DML files are not handled appropriately in Datical deployment performance can slow drastically and errors could occur should Datical exhaust available memory on the system from which changes are being deployed. The information below is intended to help you avoid such outcomes.

General Guidance for Optimizing Performance of Large DML Scripts

Maximize Memory Allocation

Running out of available memory is the most common issue caused by large DML scripts in Datical for any supported database platform. Therefore it is recommended that you allocate at least 8 GB (8192 MB) of memory to the Datical run time environment. See https://datical-cs.atlassian.net/wiki/spaces/DDOC/pages/896570566 for more details on managing available memory settings.

Split Large Files into Multiple Smaller Files

Another way to prevent performance degradation is to split the large file into several smaller files of less than 10,000 distinct DML statements (Update, Insert, Delete).

Maintaining Order of Execution when Splitting Files

To preserve the order of execution for the statements in the original file, add a numeric indicator to each filename. For example, a large file named myLargeDMLFile.sql would become myLargeDMLFile_1.sql, myLargeDMLFile_2.sql, myLargeDMLFile_3.sql, myLargeDMLFile_4.sql To ensure that the file set is appropriately ordered in the resulting change log:

• Manually Creating Change Sets or Using the Change Set Wizard: Create a change set for each file and order them appropriately in the change log
  
  

• Creating Change Sets Using the Deployment Packager in SCM Mode: To maintain execution order of the files based on alphanumeric ordering of their names, simply include all files in the same source code control commit to your SQL code repository

Avoid Forecasting Large DML Scripts (Oracle only)

DML Forecasting Project Setting (Oracle only)

Datical’s DML Forecasting feature for users of Oracle Database will run DML scripts in a transaction that is rolled back so that the user can determine what will happen when that script is applied to a given database. This feature can only be used for scripts that don’t contain explicit or implicit commits, which Datical confirms prior to attempting the Forecast. The additional processing associated with the DML Forecasting feature can exacerbate the performance issues observed with very large DML scripts. As a result it is recommended that DML Forecast be disabled for large DML scripts.

Disabling DML Forecast for the Project

You can disable DML Forecasting in the project’s Settings tab in the Datical DB GUI, or by removing forecastDML=”true” attribute from datical.project file, or by using the forecastDML subcommand of the hammer set command from the CLI

Disabling DML Forecast for an Individual Change Set

The DML Forecast feature will only run against scripts that are in the Resources\data_dml directory of your project. You can disable DML Forecast for specific scripts when it is enabled for the project by following the steps below.

Disabling DML Forecasting for Scripts Processed by Deployment Packager

To disable DML Forecast for a specific script using the Deployment Packager, check the script into a directory in the SQL code repo with packagemethod=direct if using flexible folders. (DML Forecast is enabled by packagemethod=data_dml).

Manually Updating a Change Set to Skip DML Parsing

Moving the DML script out of the Resources\data_dml directory and updating the reference to the script accordingly

SQL Parser forecasting (Oracle only)

SQL Parser for Oracle is not intended to be applied to large DML scripts because parsing each DML statement can cause performance slowdown.

Folders to use for large DML scripts in regards to SQL Parser

If you are using SQL Parser for Oracle in your project, we recommend only putting large DML scripts in:

• the folder called “data_dml” in fixed folder name configurations

• or folders with other flexible folder names that have packageMethod=”data_dml” set in the metadata.properties

• or in folders that have disableSqlParser=”true” set in the metadata.properties

If you are using SQL Parser for Oracle in your project, do not put large DML scripts in folders that will use the parser, such as:

• the folders called “ddl_direct”, “sql_direct”, or “sql” in fixed folder name configurations

• or folders with other flexible folder names that have packageMethod=”ddl_direct”, packageMethod=”direct”, or packageMethod=”sqlfile” set in the metadata.properties

• or in folders that have disableSqlParser=”false” set in the metadata.properties

Disabling SQL Parser at the Folder level

You can selectively disable the SQL Parser after it has been enabled, either at the folder level or on the change set level.

You can disable SQL Parser at the folder level either temporarily or permanently in the metadata.properties file for that folder:

Set disableSqlParser=true in the metadata.properties file for any folders that you do not want SQL Parser to process. 

Disabling SQL Parser at the Change Set level

If SQL Parser was applied to a large DML script during packaging, that may cause performance issues during deployment to higher environments. You can disable SQL Parser for a specific change set by manually editing the changelog.xml to add disableSqlParser="true":

Add disableSqlParser=true in the appdba:sqlplus section of the change set for a specific change set you do not want SQL Parser to process.