How to Use SQL Explain Plans in SAP HANA Studio – Step-by-Step Guide

SQL Explain plans are essentially data tables that store information in a very detailed manner about the instances when an SQL query will hit the SAP HANA database. They are useful for troubleshooting and performance optimization of SQL statements.

In this tutorial, we will be learning about the SQL Explain plans and their use in SAP HANA scripting. Here, we will start with learning the basics of explain plans and proceed with the steps to use explain plans in SAP HANA Studio (Modeler).

What are SQL Explain Plans?

SQL Explain plans evaluate and present a detailed analysis of the order of SQL statements execution and all the statistics involved to carry out statement execution.

Using the output of explain plans, you can assess the accuracy and efficiency of your SQL statement and make suitable changes in the logic of the SQL script.

For instance, if something is causing a delay in executing the statement and fetching the results, you can go back to the SQL script and edit it so that it executes faster.

The statements of the SQL script must be in Data Manipulation Language (DML) for the explain plan statement to work on it. The SQL Explain Plans cannot be used on DDL or DCL SQL statements.

Some commonly used DML statement are:

• SELECT statement used for retrieving data from a database.
• INSERT statement to insert data into a table.
• UPDATE statement to update the data in an existing table.

Syntax of Explain Plan statements

To create an explain plan table, enter:

EXPLAIN PLAN [SET STATEMENT_NAME = <statement_name>] FOR <SQL DML statement>

The statement_name is the name of the explain plan statement.

SQL DML statement or explain_plan_entry is the SQL script that is to be evaluated and obtain output in the form of explain plan table.

To view, the values in an explain plan table, enter:

SELECT <Operator_Name, Operator_ID>
FROM explain_plan_table
WHERE statement_name = <statement_name>;

To delete a statement in explain plan table, enter:

DELETE FROM explain_plan_table WHERE statement_name = <statement_name>;

Using Explain Plans in SAP HANA Studio

Below are the steps to execute the explain plan table in the SAP HANA studio with the help of SQL statements.

Step 1: Open SAP HANA Studio. We have a SQL statement ready to explain you, how the explain plans work. So, directly go to the SQL Editor.

Step 2: The sample SQL statement fetches a table having four columns. This goes to show that we will be connecting to three tables; a data mention table which provides the year value, a fact table giving NETSALES, and a product table providing category, line and product name.

Step 3: Now, create an explain plan for the execution of the SQL statement. Open the SQL Editor and write the statement:

Select* from explain_plan_table

Then, click on the green execute button.

What we’ll get at this point is an empty explain plan table.

Step 4: To add content into the explain plan table, you need to first write a statement defining an explain plan and then assigning an SQL script to it.

The statement for explain plan that we’ll add on top of the SQL script has a statement name, which is the name of the explain plan we are creating.

And the “for” clause directs the system to evaluate and get an explain plan of the SQL script defined by the select statement. Click on the execute button to execute this statement.

Step 5: Now, go back to the explain plan table and hit the execute button. The table will automatically load the contents of the output of the SQL script evaluation.

What you will see here is an explain plan table. The table has several columns containing details about the SQL statements that were commanded to be evaluated.

Understanding the Explain Plan Table

An explain plan table in the SAP HANA studio has many columns. All of these columns contain different details about the execution plan of the SQL statement. Starting from the left, the columns denote:

• STATEMENT_NAME has the name of the explain plan the user has assigned.
• OPERATOR_NAME is the operation taking place during execution of a statement such as column search i.e. specific columns are being searched in the database to be retrieved in the output. The operator names are displayed in the order of their execution.
• OPERATOR_DETAILS holds the details of the operation taking place.
• EXECUTION_ENGINE indicates the table type.
• SCHEMA_NAME specifies the name of the schema in which table is present.
• TABLE_NAME specifies the name of the table from which the corresponding column retrieves.
• TABLE_TYPE shows the type of table being processed, i.e. column-based or row-based.
• TABLE_SIZE displays the size of the table in use.
• OUTPUT_SIZE shows the number of records fetched from a table.
• SUBTREE_COST is the cost of implementing subtree that starts from an operator. Useful in performance monitoring of the statements.
• OPERATOR_ID shows the rank on which the order of statement execution of a particular operator lies.
• PARENT_OPERATOR_ID is the number of statements on which a particular statement is succeeding. For instance, if parent_operator_id of statement 2 is 1 that means the statement is executed after statement 1. This ID is useful to understand the join clauses where certain statements follow one statement.
• LEVEL indicates the level of the statement from the root operator position.

Other columns are the connection related columns such as HOST, PORT, TIMESTAMP and CONNECTION_ID showing the details of the connection to the database.

You can have a closer look at the execution plan by expanding the OPERATOR_DETAILS column.

You will see the operator name and the details of what is happening at that particular stage of SQL script execution. The first operation taking place is column search of the four columns from respective data tables. It is followed by applying a limit which restricts the number of records to 5.

Then the order by clause applies that descends the records as per the sum of net sales. Then aggregation takes place that groups and squeeze the data, followed by a join clause which applies an inner join between the product table and the fact table. And the operation follows the order.

Summary

This concludes our tutorial on SQL Explain Plans in SAP HANA. We hope the explanation was helpful and gave you a clear understanding of it. Explain plans gives you a detailed account of how we execute a set of statements in an SQL script.

You can analyze different aspects of the execution process and make changes accordingly to optimize the statement execution and the performance of your application.

We hope now you are clear with the concept of SQL explain plans in SAP HANA. If you have any queries, feel free to enter them in the comment section.