Using [BindTuning] parameters for performance

Consider the following points as you adjust your [BindTuning] parameters. These points cover general principles; the circumstances of your data and your environment can vary. These points assume that static SQL provides a performance benefit in your environment. 

Related topics

BindTuning-parameters

MaxExecuteImmediateDML

This assumption is most true when your input contains many repeated statements of the same distinct statement types.

  • Use the PackageUsage, StatementCount, and StatementUsage parameters as a group. All three parameters set thresholds that determine when the High-speed Apply Engine performs a bind action.
  • When you set a threshold higher, High-speed Apply Engine typically performs fewer bind actions, less frequently, with more distinct statement types in each bound package. Conversely, when you set a threshold lower, High-speed Apply Engine performs more bind actions, more frequently, with fewer distinct statement types in each bound package.
  • The MaxPackages parameter value should vary inversely with the PackageUsage, StatementCount, and StatementUsage parameters. When the thresholds are low, High-speed Apply Engine binds more packages and MaxPackages should be higher.
  • If an apply request includes a template file, the StatementCount and MaxPackages parameters also apply to the template file. Each entry in the template file counts as one occurrence of a distinct statement type. Any packages bound as High-speed Apply Engine processes the template file count against your MaxPackages limit.
  • To force High-speed Apply Engine to use static SQL for all input statements, set the Synchronous parameter to Yes and set the StatementCount parameter to 1. If you use this technique, ensure that the MaxPackages value is high enough to accommodate the total number of distinct statement types in your input file.

The following table summarizes the effects of changing values for these parameters.

StatementCount, StatementUsage, and PackageUsage settings

Effects / usage

Higher Values

Effects:

High-speed Apply Engine:

  • Performs fewer bind actions.
  • Performs less frequent bind actions.
  • Binds packages that contain more distinct statement types.
  • Binds packages that are larger.
  • Executes more dynamic SQL and less static SQL.
  • Performs bind actions and executes static SQL later in the apply request.

Usage:

  • MaxPackages can be set to a lower value (not required; no penalty with higher value).
  • Use higher values when the environment dictates more dynamic SQL (for example, if bind actions cause other activity to perform poorly) .

Lower Values

Effects:

High-speed Apply Engine

  • Performs more bind actions.
  • Performs more frequent bind actions.
  • Binds packages that contain fewer distinct statement types.
  • Binds packages that are smaller.
  • Executes more static SQL and less dynamic SQL.
  • Performs bind actions and executes static SQL sooner in the apply request.

Usage:

  • MaxPackages should be set to a higher value.
  • Use lower values when environment dictates static SQL (for example, if dynamic SQL performs poorly).
  • Use lower values when input contains fewer distinct statement types (set StatementCount lower).
  • Use lower values when input includes many repeated statements of the same distinct statement types (set StatementUsage and PackageUsage lower).


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*