Using SAP HANA Capture and Replay

A workload in the context of SAP HANA can be described as a set of requests with common characteristics.

In the context of SAP HANA capture and replay, workload can mean any change to the database via SQL statements that come from SAP HANA client interfaces such as JDBC, ODBC, or DBSL. The workload can be created by applications or clients (for example, SAP NetWeaver or Analytic).

You can look at the details of a workload in several ways. Firstly, you can look at the source of requests and determine if applications or application users generate a high workload for the system. You can examine what kinds of SQL statements are generated. Are they simple or complex statements? Is there a prioritization of work done based on business importance? For example, does one part of the business need to have more access at peak times? You can then look at what kind of service level objectives the business has in terms of response times and throughput.

• WORKLOAD CAPTURE ADMIN – Required for capturing workloads.

• WORKLOAD REPLAY ADMIN – Required for prepocessing, replaying workloads, and viewing the load chart in the replay report.

• WORKLOAD ANALYZE ADMIN – Required for viewing the load chart in the replay report.

1. On the Overview page, choose the Capture Workload card. The Capture Management page opens. If you have already captured workload with SAP HANA capture and replay, you see the captured workload on the current system.

2. To change INI configuration parameters, choose Configure Capture.

   In the Configure Capture screen, you can change the Capture Destination. By default, the captured workload file is stored in the $SAP_RETRIEVAL_PATH/trace directory. Because the default trace directory generally resides in the same storage area as data and log volumes, capturing workloads may affect the performance across the entire system over time. Enter a different destination for the captured workload file to have a better distribution of the disk I/O between the data and log volumes, and the captured workload file.

3. To start configuring the new capture, on the Capture Management page, choose New Capture.

   On the Configure New Capture page, it is mandatory to enter the name of the new capture. You can customize other optional settings before you start the capture.

4. Choose Start Capture.

   The Capture Monitor opens, displaying monitoring information such as duration, the number of captured statements, or disk space. You can stop the capture or you can let it run for as long as you wish. If you did not create a backup when starting the capture, you can also start a full backup from the Capture Monitor page.

After the capture is complete, the new captured workload has the status Captured. By choosing the new captured workload, the Capture Report opens, displaying information about the captured workload. You can continue analyzing the captured workload. For more information, see Analyze a Captured Workload.

You can customize several optional settings before you start capturing a workload. The following list provides an overview of the optional settings that can be customized.

• Description: Enter a description of the capture for future reference. You can use this description to identify different scenarios in the same system.

• Schedule: Schedule the capture by specifying the start and end time.

• Overwrite Capture When Time Exceeds: Turn it on and enter a time to remove the captured workload segment files that are older than the specified time you entered. Only closed segments are deleted. The currently active captured workload segment file is not affected.

• Overwrite Capture When Disk Usage Exceeds: Turn it on and select a ratio to remove the old captured workload segment files when the disk usage exceeds the specified percentage. Only closed segments are deleted. The currently active captured workload segment file is not affected.

• Collect Explain Plan: Turn this on to collect the output of the EXPLAIN PLAN command for the captured statements. You can use this information for analysis after the replay.

• Collect Workload Details: Turn this on to collect additional information for the instrumentation-based workload analyzer such as application source, involved threads, network statistics, or related objects.

  If this option is disabled, the captured workload file can still be viewed using the instrumentation-based workload analyzer, but less information is available for the review.

• Abstract SQL Plan: Turn this on to collect additional information on the SQL Plan Stability. Information related to the physical execution plan is collected and stored as Abstract SQL Plan (ASP) to the persistent storage for future reference.

• Optional Filter: Select additional filters to capture only desired aspects of the workload. Filters can include different aspects such as Application Name, Database User Name, and Statement Hash.

• Create Full Backup: Turn this on to automatically create a full database backup after starting the capture

  Hint

  To ensure that the source system and the target system are in a consistent state for capture and replay, we recommend performing a full database backup after starting the capture. A full database backup is only required the first time, because incremental backups can be used once the system has been initialized. For more information, see SAP HANA Backup and Recovery.

You have the system privilege WORKLOAD ANALYZE ADMIN.

1. To analyze the captured workload from a source system, use the Capture Workload card, then select the capture you want to analyze.

2. To load the workload into the SAP HANA database, choose Load Workload.
3. When the data is loaded the Analyze Workload button appears.

4. In the Workload Analysis page, several analysis charts and information tables are displayed.

In the target system, you need a user account to control the replayer. This user account requires the WORKLOAD REPLAY ADMIN system privilege. Store the logon credentials in the secure store of the control system.

As <sid>adm on the control system, execute the following command:

1
hdbuserstore SET <User key><Target system@Target tenant><User name><Password>

The replayer is a service at the operating system level that must be running before starting the replay.

• A user with the WORKLOAD REPLAY ADMIN system privilege to control the replayer.

• Store the logon credentials in the secure store.

• When using multiple replayers, distribute and divide all involved components (for example, target instance, control instance, and one or more replayers) on different hosts and systems.

• A user with WORKLOAD REPLAY ADMIN system privilege.

• You have captured workloads using the Capture Workload card.

• Copy the captured workload file from the production system to the target system in the trace directory. If you use a control system that is different from the target system, copy the captured workload file to the control system.

1. On the Overview page, choose the Replay Workload card. The Replay Management page opens displaying an overview of the replay candidates located on the current system.

2. Check the status of the captured workload that you want to preprocess. The status should be Not Preprocessed.

3. Optional: To change INI configuration parameters, choose Configure Replay.

4. Choose the Start link below the Not Preprocessed status.

5. The preprocessing starts. The runtime depends on the size of the captured workload. You can manually refresh the screen by using the refresh button at the top right of the screen.

6. As soon as the preprocessing is done, the status changes to Preprocessed.

Preprocess Destination: After the preprocessing is complete, the preprocessed workload file is stored by default in the $SAP_RETRIEVAL_PATH/trace directory. Because the default trace directory generally resides in the same storage area with data and log volumes, preprocessing workloads may affect the performance across the entire system. Enter a different destination to have a better distribution of the disk I/O between the data and log volumes, and the preprocessed files.

• A user with WORKLOAD REPLAY ADMIN and WORKLOAD ANALYZE ADMIN system privileges.

• The target system meets the same security and privacy prerequisites as the source system. Since the target system processes the same data as the source system, it should meet an appropriate security level depending on data criticality.

• You have preprocessed the captured workloads using the Replay Workload card.

• The replayer is running.

1. On the Overview page, choose the Replay Workload card. The Replay Management page opens displaying the captured workload.

2. Choose a replay candidate with the status Preprocessed to start configuring it for the replay. The Replay Configuration page opens, allowing you to configure various mandatory and optional settings.

3. To start the replay, choose Start Replay.

If a database backup is available, restore the database before starting the replay in the target system. When running a replay on a target system that has been restored using a backup taken automatically during the capture process, activate the Synchronize Replay with Backup.

If no or only out-dated database backups are available, you can still restore the database or manually export parts of the data before starting the replay in the target system. When running a replay on a target system that was restored using old backups or contains only smaller manual exports of data, deactivate the Synchronize Replay with Backup option.

The Replay Management screen opens displaying in the Replay List tab for the workloads that are being replayed. To access the Replay Monitor, choose the running replay. The monitoring view provides information such as duration, number of statements, size, and other details about the replay in progress. You can navigate away from the monitoring view using the arrow at the top right and you can return anytime.

If you have already replayed preprocessed workloads, you can generate comparison reports for further analysis. For more information, see Generating Comparison Reports.

The Replay Configuration allows you to set various parameters to best suit how the replay is to be processed. The General Information page allows you to customize the following mandatory and optional settings:

• Replay Name: Enter a name for the replay. By default, this field has the same name as the initial captured file.

• Description: Enter a description for the replay for your future reference. This information can be used when changing settings for different replays.

• Host: Enter the target host name (for example, hana01) where the capture is replayed.

• Instance Number: Enter the target instance number (for example, 42) where the capture is replayed.

• Database Mode: Choose between Single Container or Multiple Containers.

• User Name: Enter a database user who has WORKLOAD REPLAY ADMIN privilege and is used for the final preparation steps in the target instance.

• Password: Enter the database user password.

• Request Rate: Modify the rate at which the statements are replayed.

  You can decrease the wait time between statements during the replay. For example, statement B starts one second after statement A has been triggered. When setting the request rate from 1x to 2x, this difference is only 0.5 seconds.

• Consistent with Backup: This option allows you to synchronize the replay with an existing database backup.

  The option is turned on by default allowing the replayer to compare each statement with the database backup. This option makes it possible to check if there are no duplicate inserts and if the backup and replay are aligned. A backup is required for this option to work correctly.

  If the option is turned off, the replayer replays statements, even if no backup is present. This is important for scenarios in which you use only single tables, or smaller data exports, which are not considered a complete backup.

• Collect Explain Plan: Collect the output of the EXPLAIN PLAN command for captured statements. You can use this information for comparison after the replay.

• Transactional Replay: This option enables guaranteed transactional consistency during a replay.

  Caution

  Enabling this option may cause overhead to query runtime as transactional consistency needs to be checked constantly.

• Replayer List: Select a running replayer that is used to connect to the target system and facilitates the replay.

• User Authentication: Enter the password or the securestore key for the database users captured in the source system. For a realistic replay, all users that are part of the workload which you have chosen to replay, must be authenticated.

  To reset the password for the database users captured in the source system, select Reset Password, then choose the user. This can be helpful when you do not know the actual password of each user. On the Reset Password window, set a new password for all selected users, and choose Confirm. All selected user passwords in the defined target system are changed as defined in this step.

You can open a comparison report by choosing a replay from the Replay List. When opening a comparison report directly from the Replay List, the report shown always compares values from the original captured workload with values from the replay. This comparison report also allows you to export the replay results to store them outside the database. Choose the arrow at top right to export the replay results. Back in the Replay List, you can import a replay at the top right.

You can compare two or more replayed workloads with each other based on the same initial captured workload. When using the Compare Replays button, the report shown always compares different replays with each other based on the same initial captured workload.

• A user with WORKLOAD REPLAY ADMIN and WORKLOAD ANALYZE ADMIN system privileges.

• You have replayed preprocessed workloads using the Replay Workload card.

You can start the comparison of the replayed workloads from the Replay List in the Replay Management.

1. On the Replay List tab, choose Compare Replays. The Select Baseline Replay dialog opens, allowing you to select the replayed workload that you want to compare. Use the Target <SID>information to distinguish between the replays.

2. Select one entry from the displayed list and choose Close. The Select Target Replay dialog opens, allowing you to select the replayed workload that you want to compare with the previously selected workload. The list displays replayed workloads based on the same initial captured workload.

3. Select one or more entries from the displayed list and choose Compare Replays. The Comparison Report opens, displaying a comparison of the selected replayed workloads.

The Overview tab displays an overall comparison of the SQL statements involved in the capturing and replaying process in the following blocks:

• Result Comparison: In a result-based comparison, you get an overview of the statements with identical or different results. Choose the block to open directly the Result Comparison tab.

• Performance Comparison: In a performance-based comparison, you get an overview of the statements based on a comparison of runtimes. Choose the block to open directly the Performance Comparison tab.

• Different Statements: Displays the top SQL statements that have different results from the selected baseline in descending order. You can choose each row to open the Execution Detail page for the selected SQL statement. Use the drop-down arrow to filter the statements by time or by the number of records that have different results.

• Slower Statements: Displays the top SQL statements that have a different performance ordered by the difference in execution time. You can choose each row to open the Execution Detail page for the selected SQL statement. To view KPI details for each statement, choose the icon to the right.

• Verification Skipped: Displays the distribution of reasons for statements with skipped result comparison.

• Replay Failed Statements: Displays the distribution of reasons for the statements, which failed during replay. Use the drop-down arrow to filter the statements by time or error code.

• Capture Information: Displays information on the capture system, capture options, and the properties of the capture file.

• Replay Information: Displays information on the replay system and the replay options. If the comparison was made between two replays, the information is displayed in a Baseline Replay Information block and in a Target Replay Information block.

The Load tab includes load charts comparing both the captured and the replayed workloads after a capture-replay comparison, or the baseline and the target workloads after a replay-replay comparison. The KPIs can be toggled independently for both the capture and replay aspects, making it easier to compare them with each other. Additional KPIs can be added using the Show More KPIs button at the top right of the load chart.

The result-based comparison provides an overview of statements with, for example, identical or different results.

The result-based replay report also includes a classification of statement types based on the content of those statements being either deterministic or non-deterministic. Deterministic statements should always deliver the same results during a replay. Non-deterministic statements are expected to deliver different results (for example, because they do not contain an explicit sorting of results).

The list below the graphic displays a detailed view of the statements that were selected using the categories at the top of the report. To open the execution details for a specific statement, choose the statement from the list.

The performance-based comparison provides an overview of statements compared by runtime.

Based on tolerance ratio, the statements are classified as comparable, when they have a similar runtime within the defined tolerance ratio, Faster, Slower, or Failed. For more information about the tolerance ratio, see Replay Report Elapsed Time Threshold in Preprocess a Captured Workload.

The list below the graphic displays a detailed view on the statements that were selected using the categories at the top of the report. To open the execution details for a specific statement, choose the statement from the list.

You can use the detailed execution level of both report types to compare the EXPLAIN PLAN results between the initial captured and replayed workloads, or between the baseline and target workloads. Comparing the plans can provide guidance and pointers for further statement-level investigation. This is only possible if the Collect Explain Plan setting was activated for both the capture and the replay during the configuration steps. For more information about this setting, see Capture Configuration Settings and Replay Configuration Settings.