This article describes new features in the SAP HANA Plan Visualizer (PlanViz) in SQL Trace (transaction ST05). These new features are available from the following SAP_BASIS Releases / Support Packages:
Figure 1: Link to Execution Plan tool from ST05 trace display
The Execution Plan (Figure 1) is the tool of choice to understand which access path was selected by the DB optimizer to process an SQL statement. This tool is available for all DB platforms supported by SAP. Its behavior and capabilities are specific to the database. Unfortunately, for SAP HANA, the features of the Execution Plan are rather rudimentary and insufficient for complex statements. For these cases, the Plan Visualizer allows a very detailed analysis and is the preferred tool for this task.
Until recently, navigation from an SQL trace record in ST05 to the PlanViz involved many manual steps.
In the ST05 transaction, you can select an SQL statement, and choose the new button (Figure 2) to execute the PlanViz for the selected SQL statement.
Figure 2: Link to HANA Plan Visualizer from SQL trace (ST05)
Before it calls PlanViz, ST05 attempts to extract the values set for session variables from previously captured trace records corresponding to SET SESSION VARIABLE Statements (Figure 2).
When you execute the PlanViz using the new button, the following dialog box is displayed:
Figure 3: Dialog box to change the values for session variables
To change the values for the session variables, specify them in the dialog box (Figure 3).
To display an explanation of the session variables, choose Help for this screen in the dialog box. You can find this documentation in the appendix to this article.
Session variables make values of the ABAP user context available to ABAP Core Data Services (CDS) views. By default, the dialog box shown in Figure 3 has the session variables of the business application that was traced by ST05.
If you use these default values, the execution of the PlanViz will reflect the reality of the traced business application.
If you change these default values, the execution of the PlanViz will not necessarily reflect the reality of the traced business application.
When you confirm the dialog box, the PlanViz is executed.
With the non-default option HANA Plan Visualizer -> Without Session Variables, ST05 calls the PlanViz with empty values for the session variables. The execution plan is then determined with values that correspond to the situation at the time of the PlanViz call. This plan may be very different from the plan chosen by the DB optimizer at the statement’s run time.
By default, the result of the PlanViz is saved to a .PLV file on the user’s desktop front-end machine. You can open this file manually using SAP HANA Studio or HANA SQL Analyzer .
To open the PlanViz result automatically using SAP HANA Studio or HANA SQL Analyzer, two configuration settings are required:
SAP HANA Studio resp. HANA SQL Analyzer opens in its PlanViz perspective (Figure 4) and displays the Executed Plan for the statement with the specified session variables.
Figure 4: Display the Executed Plan in SAP HANA Studio’s PlanViz perspective
If the SAP system has been configured to call SAP HANA Studio resp. HANA SQL Analyzer for a user, but this Software is not available on the front-end machine where the user is working, a file save dialog pops up and the PlanViz .PLV file can be stored locally.
Business applications can develop SQL statements inside their coding in the following two ways:
In the SPs described in this article, SAP HANA PlanViz determines the execution plan for application A with literals. For application B, bind variables are used to find the execution plan. Therefore, the execution of the PlanViz reflects the reality of the traced business application.
In previous SPs, PlanViz used literals for both of the above scenarios, and therefore did not always reflect the reality of the traced business application.
Below is the documentation on session variables (from the dialog box shown in Figure 3).
This dialog box shows the session variables with values retrieved from the current SQL trace. You can change the values, if needed.
When you confirm, the selected SQL statement is analyzed using the specified session variables.
To create the execution plan, you can change the values of the following session variables:
If an SQL trace is incomplete, it may not be possible to retrieve values for the session variables. If values are missing, empty values are proposed, and you have the option to change them manually. If you do not know the correct values, record an SQL trace that covers the entire application.
Session variables make values of the ABAP user context available to ABAP Core Data Services (CDS) views. The PlanViz for a CDS statement can only be generated correctly if the correct values for the session variables are set.
Session variables are global variables for the current database. Depending on the values of these session variables, the same SQL statement can return different result sets and can have different performance.
Session variables can be specified using the ABAP statement SET SESSION VARIABLE. These statements and the values they inject into a session variable are visible in a trace.
When analyzing an SQL statement in ST05, and creating its execution plan, the values for the session variables are retrieved from the SET SESSION VARIABLE statements recorded together with the SQL statement that is being analyzed.
Additional documentation on session variables on the SAP Help Portal:
ABAP-Specific Session Variables in SAP HANA
- 7.52 SP3 (and higher)
- 7.53 All SPs
- 7.54 All SPs
Background
Figure 1: Link to Execution Plan tool from ST05 trace display
The Execution Plan (Figure 1) is the tool of choice to understand which access path was selected by the DB optimizer to process an SQL statement. This tool is available for all DB platforms supported by SAP. Its behavior and capabilities are specific to the database. Unfortunately, for SAP HANA, the features of the Execution Plan are rather rudimentary and insufficient for complex statements. For these cases, the Plan Visualizer allows a very detailed analysis and is the preferred tool for this task.
Until recently, navigation from an SQL trace record in ST05 to the PlanViz involved many manual steps.
New Feature: Button for the PlanViz
In the ST05 transaction, you can select an SQL statement, and choose the new button (Figure 2) to execute the PlanViz for the selected SQL statement.
Figure 2: Link to HANA Plan Visualizer from SQL trace (ST05)
New Feature: Including Session Variables
Before it calls PlanViz, ST05 attempts to extract the values set for session variables from previously captured trace records corresponding to SET SESSION VARIABLE Statements (Figure 2).
When you execute the PlanViz using the new button, the following dialog box is displayed:
Figure 3: Dialog box to change the values for session variables
To change the values for the session variables, specify them in the dialog box (Figure 3).
To display an explanation of the session variables, choose Help for this screen in the dialog box. You can find this documentation in the appendix to this article.
Session variables make values of the ABAP user context available to ABAP Core Data Services (CDS) views. By default, the dialog box shown in Figure 3 has the session variables of the business application that was traced by ST05.
If you use these default values, the execution of the PlanViz will reflect the reality of the traced business application.
If you change these default values, the execution of the PlanViz will not necessarily reflect the reality of the traced business application.
When you confirm the dialog box, the PlanViz is executed.
Note
With the non-default option HANA Plan Visualizer -> Without Session Variables, ST05 calls the PlanViz with empty values for the session variables. The execution plan is then determined with values that correspond to the situation at the time of the PlanViz call. This plan may be very different from the plan chosen by the DB optimizer at the statement’s run time.
New Feature: Navigate Directly to the PlanViz Result in SAP HANA Studio or in HANA SQL Analyzer
By default, the result of the PlanViz is saved to a .PLV file on the user’s desktop front-end machine. You can open this file manually using SAP HANA Studio or HANA SQL Analyzer .
To open the PlanViz result automatically using SAP HANA Studio or HANA SQL Analyzer, two configuration settings are required:
- On your local machine, the file type .PLV must be associated with program hdbstudio.exe respect. Code.exe.
To do this from Windows, choose Control Panel -> Programs -> Default Programs -> Associate a file type or protocol with a specific program. - In the SAP system, Set/Get parameter HDB_OPEN_STUDIO must be set to X.
To do this, choose System -> User Profile -> User Data and go to tab Parameters.
SAP HANA Studio resp. HANA SQL Analyzer opens in its PlanViz perspective (Figure 4) and displays the Executed Plan for the statement with the specified session variables.
Figure 4: Display the Executed Plan in SAP HANA Studio’s PlanViz perspective
If the SAP system has been configured to call SAP HANA Studio resp. HANA SQL Analyzer for a user, but this Software is not available on the front-end machine where the user is working, a file save dialog pops up and the PlanViz .PLV file can be stored locally.
New Feature: Including Account Bind Variables
Business applications can develop SQL statements inside their coding in the following two ways:
- Business application A may use literals inside the WHERE clause.
Example: SELECT * FROM sdbac WHERE shortnam = ‘SAVEALL’ - Business application B may use bind variables inside the WHERE clause.
Example: SELECT * FROM sdbac WHERE shortnam = ?
In the SPs described in this article, SAP HANA PlanViz determines the execution plan for application A with literals. For application B, bind variables are used to find the execution plan. Therefore, the execution of the PlanViz reflects the reality of the traced business application.
Note
In previous SPs, PlanViz used literals for both of the above scenarios, and therefore did not always reflect the reality of the traced business application.
Appendix: Session Variables
Below is the documentation on session variables (from the dialog box shown in Figure 3).
Specify Session Variables
This dialog box shows the session variables with values retrieved from the current SQL trace. You can change the values, if needed.
When you confirm, the selected SQL statement is analyzed using the specified session variables.
To create the execution plan, you can change the values of the following session variables:
- APPLICATIONUSER
The user specified in the ABAP system field SY-UNAME - SAP_SYSTEM_DATE
The system date specified in the ABAP system field SY-DATUM - CDS_CLIENT
The CDS client is set to the specified value when the database is accessed with an Open SQL statement with USING CLIENT. - LOCALE_SAP
A locale is a collection of location-specific user settings for language, formatting of date and time, numbers, and currencies. It is specified in ABAP system field SY-LANGU.
If an SQL trace is incomplete, it may not be possible to retrieve values for the session variables. If values are missing, empty values are proposed, and you have the option to change them manually. If you do not know the correct values, record an SQL trace that covers the entire application.
Session Variables
Session variables make values of the ABAP user context available to ABAP Core Data Services (CDS) views. The PlanViz for a CDS statement can only be generated correctly if the correct values for the session variables are set.
Session variables are global variables for the current database. Depending on the values of these session variables, the same SQL statement can return different result sets and can have different performance.
Session variables can be specified using the ABAP statement SET SESSION VARIABLE. These statements and the values they inject into a session variable are visible in a trace.
When analyzing an SQL statement in ST05, and creating its execution plan, the values for the session variables are retrieved from the SET SESSION VARIABLE statements recorded together with the SQL statement that is being analyzed.
Additional documentation on session variables on the SAP Help Portal:
ABAP-Specific Session Variables in SAP HANA
- SAP Managed Tags:
2 Comments
