Setting the Replay Timeout Action

This section describes how to set a timeout action for the workload replay. You can set a replay timeout action to abort user calls that are significantly slower during replay or cause a replay to hang. For example, you may want to set a replay timeout action to abort runaway queries caused by sub-optimal execution plans following a database upgrade.

When a replay timeout action is enabled, a user call will exit with an ORA-15569 error if it is delayed beyond the conditions specified by the replay timeout action. The aborted call and its error are reported as error divergence.

To set a replay timeout:

  • Use the SET_REPLAY_TIMEOUT procedure:

    BEGIN
      DBMS_WORKLOAD_REPLAY.SET_REPLAY_TIMEOUT (
                               enabled => TRUE,
                               min_delay => 20,
                               max_delay => 60,
                               delay_factor => 10);
    END;
    /
    

    In this example, the SET_REPLAY_TIMEOUT procedure defines a replay timeout action that will abort a user call if the delay during replay is more than 60 minutes, or if the delay during replay is over 20 minutes and the elapsed time is 10 times greater than the capture elapsed time.

    The SET_REPLAY_TIMEOUT procedure in this example uses the following parameters:

    • The enabled parameter specifies if the replay timeout action is enabled or disabled. The default value is TRUE.

    • The min_delay parameter defines the lower bound value of call delay in minutes. The replay timeout action is only activated when the delay is over this value. The default value is 10.

    • The max_delay parameter defines the upper bound value of call delay in minutes. The replay timeout action is activated and issues an error when the delay is over this value. The default value is 120.

    • The delay_factor parameter defines a factor for the call delays that are between the values of min_delay and max_delay. The replay timeout action issues an error when the current replay elapsed time is higher than the multiplication of the capture elapsed time and this value. The default value is 8.

To retrieve the replay timeout action setting:

  • Use the GET_REPLAY_TIMEOUT procedure:

    DECLARE
      enabled        BOOLEAN;
      min_delay      NUMBER;
      max_delay      NUMBER;
      delay_factor   NUMBER;
    BEGIN
      DBMS_WORKLOAD_REPLAY.GET_REPLAY_TIMEOUT(enabled, min_delay, max_delay,
                               delay_factor);
    END;
    /
    

    The GET_REPLAY_TIMEOUT procedure in this example returns the following parameters:

    • The enabled parameter returns whether the replay timeout action is enabled or disabled.

    • The min_delay parameter returns the lower bound value of call delay in minutes.

    • The max_delay parameter returns the upper bound value of call delay in minutes.

    • The delay_factor parameter returns the delay factor.