Analyzing executions with the SQL Analyzer

Objective

After completing this lesson, you will be able to analyze executions with the SQL Analyzer

Introducing the SQL Analyzer

What is the Purpose of the SQL Analyzer?

Although your calculation view may appear well designed, ultimately it is the performance of the calculation view that really counts. You have seen how to display the generated SQL in a calculation view using the Debug Query mode. While this is helpful to see how filters are pushed down and data is pruned, this does not tell us how the SQL actually performs. We really need to know the answers to the following questions:

  • What was the total execution time?

  • How long did each step take?

  • Are there any steps that are taking significantly longer than other steps?

  • Are there any bottlenecks?

  • How many records did each step process?

  • In what sequence are the steps carried out?

  • Which operators are being called?

  • To what extent was SAP HANA Cloud able to parallelize the query?

  • Which processing engines are being invoked?

  • Were all the query plan steps unfolded?

To help us learn more about the performance of the SQL, we can use the SAP Business Application Studio view HANA SQL Analyzer.

Note

To enable this view, when creating your SAP Business Application Studio development space, or later on, you must add the extension SAP HANA Performance Tools to this space.

Screen capture of SAP Business Application Studio. The HANA SQL Analyzer view is displayed. The Overview Tab shows key information about the execution: Time elapsed, memory used, operators, data usage, network traffic and other system information.

The SQL Analyzer is a set of views, tables, graphs that you can use to analyze any SQL Query. You can drill-down in a graph execution, analyze the timeline of query compilation and execution, visualize how many tables, and which tables, were used, and so on.

In addition to analyzing the SQL generated by the calculation views (either the standard query or a modified query), it can be used wherever SQL is defined, for example in table functions.

How to Get the Query Statement?

To generate the plan file of an SQL query, you must put it in an SQL Console. You do not need to execute it.

For a calculation view, generating the SQL query can be done in the following ways:

  • In the SAP HANA Database Explorer view of SAP Business Application Studio, select the HDI container, right-click the column view and choose Generate SQL Statement.

    This is the easiest way, because from within the embedded SAP HANA Database Explorer view, the generated plan file will open directly in the HANA SQL Analyzer view.

  • In the Explorer view, navigate to the design-time file of the calculation view, right-click and choose SQL Editor. You can also choose Data Preview and, in the Raw Data tab, choose the Edit SQL Statement in SQL Console icon.
  • In the external SAP Database Explorer, select the HDI container, select the column view, right-click and choose Generate SQL Statement.

    The two last methods are a bit less straightforward, as they require to download the SQL plan file (.plv) from the SQL Console and upload it to the HANA SQL Analyzer view in Business Application Studio.

Note

In any scenario, it is always possible to modify the default query. For example, removing some columns to make sure node/data source pruning works as expected. It is also possible to create the SQL query on top of a calculation view from scratch.

Generating the SQL Analyzer Plan File

  1. To get started, you generate a file that collects the run-time information of your SQL query. Use the menu option AnalyzeGenerate SQL Analyzer Plan File. Choose a file name prefix for the plan file, and choose Save. The location is already set for you and you cannot change it at this stage.

    Screen Capture of the Business Application Studio SQL Console. A SELECT statement is displayed and the Analyze menu drop down list shows the Generate SQL Analyzer Plan File option.
  2. If you have generated the SQL plan file from an SQL Console opened from the SAP HANA Database Explorer view of SAP Business Application Studio, the plan file opens immediately in the HANA SQL Analyzer view.

    Screen Capture of the Business Application Studio HANA SQL Analyzer view. The generated file from previous step is selected.

    You can then proceed to the last step of this procedure.

  3. If you have generated the SQL plan file from another tool, such as the Data Preview of SAP Business Application Studio, or from the external SAP HANA Database Explorer (outside of SAP Business Application Studio), you need to download the SQL plan file to your computer. Choose Download, and this is then handled by your web browser. Choose a convenient location to store the SQL plan file.

    Screen Capture of the SAP HANA DATABASE EXPLORER. The Database Diagnostic Files folder is expanded and the Other folder is selected. The content of the folder is displayed and you can right click on the PLV file and choose to Download it to your computer.
  4. The next stage is to upload the SQL analyzer plan file to the Explorer view of SAP Business Application Studio. Upload the plan file to any project folder by using the context menu Upload.

    Set of screen captures showing the process to use an external PLV file. First upload the file to Business Application Studio project, then switch to HANA SQL Analyzer view and add the plan file using the + icon.
  5. Switch to the SQL Analyzer.

    Note

    In order to see the HANA SQL Analyzer view, remember to add the extension SAP HANA Performance Tools to your development space. This can only be done when the development space is stopped.

  6. Add the plan file to HANA SQL Analyzer.

    You must first locate the plan file in the folder where you uploaded it.

  7. Select the plan file to display the results in the main window.
    Screen capture of the SAP Business Application Studio HANA SQL Analyzer view. The PLV file is selected and the PLAN GRAPH tab is displayed showing the SQL statement execution plan in graphical mode.

How to Analyze a Generated SQL Plan File Later on?

In case you want to generate plan files first, and access the generated SQL plan files at a later time, you can proceed as follows, depending on how you generated the plan files:

  • Embedded SAP HANA Database Explorer (in SAP Business Application Studio):

    You access these plan files from the Explorer view, in the folder /home/user/.vscode/data/User/globalStorage/sapse.hana-database-explorer/. They can be opened directly from this location or, If relevant, you can move the plan files to your project.

  • External SAP HANA Database Explorer and other views of SAP Business Application Studio:

    You access these plan files from the External SAP HANA DB Explorer, within the HDI container, in the section CatalogDatabase Diagnostic Files<DB Instance ID>other. They must be downloaded, and then uploaded to SAP Business Application Studio.

Which Privileges are Needed to Run SQL Analyzer?

Analyzing an SQL plan file with SQL Analyzer can be performed either from the technical user <container_schema_name>...RT of your HDI container, or a classic database user.

In both cases, the user analyzing the query needs the two following SYSTEM privileges:

  • TRACE ADMIN
  • INIFILE ADMIN

Note

You will learn more about users, roles and privilege management in a dedicated lesson.

Log in to track your progress & complete quizzes