Applying Tools and Views

1. Monitoring Views

   These views are in Schema 'SYS' and can be identified by the M_ at the beginning of the view name. SAP HANA includes a set of useful runtime views called monitoring views. These views are useful for monitoring and troubleshooting SQL performance. They provide actual SAP HANA runtime data, including statistics and status information related to the execution of DML statements. At the time of writing there are 385 monitoring views. The data in monitoring views is not stored; the view is calculated when you select it. Nevertheless, the result sets can be materialized in tables if needed. Further information can be looked up in the SAP HANA SQL Reference Guide.

   Examples: M_SQL_PLAN_CACHE, M_EXPENSIVE_STATEMENTS

2. System Views

   Main focus area is administration and system state.

   System views are stored in the SYS schema. In tenant databases, each database has a SYS schema with system views that contain information focused on the tenant. System views allow you to evaluate information about the system state itself and not the application code like SQL. The System Privileges CATALOG READ or DATABASE ADMIN are needed to use them.

   Examples: CS_JOIN_TABLES, EFFECTIVE_PRIVILEGES

3. Embedded Statistics Service Views

   Tables and views are stored in the _SYS_STATISTICS schema. It is implemented by a set of tables and SQLScript procedures in the master index server and by the statistics scheduler thread that runs in the master name server. The statistics service is continuously collecting and evaluating information about status, performance, and resource usage from all components of the SAP HANA database.

   Examples: HOST_CS_UNLOADS, HOST_RS_INDEXES

The M_SQL_PLAN_CACHE view shows statistics for individual execution plans. It shows which part of the execution is dominant. For each plan in the cache, it delivers statistics from execution and technical details such as related objects and object IDs, updated objects, and much more.

M_SQL_PLAN_CACHE shows views that can be reset. This is beneficial to only see results after the point in time when it was reset.

To reset the view, execute the following statement: ALTER SYSTEM RESET MONITORING VIEW SYS.M_SQL_PLAN_CACHE_RESET;

In SAP HANA SPS07, this view has 108 columns. When you scroll with the bar at the bottom to the right, you can see more or you can choose to let the tool create the select statement on the view so as to select the information you would like to see.

If you have the TRACE ADMIN privilege, then you can view expensive statements trace information in the following ways:

• In the Expensive Statements app of the SAP HANA Cockpit
• In the Statement Library in the SAP HANA database explorer by searching for Expensive Statements Analysis
• In the M_EXPENSIVE_STATEMENTS system view

Expensive statements are SQL statements with execution times exceeding a configured threshold. The thresholds should be selected in a way that these thresholds are reached only in critical situations that are abnormal. Never set the threshold too high that it can never be reached. Threshold values could be found and fixed when testing applications. The expensive statements trace records information about these statements for further analysis and is inactive by default.

If, in addition to activating the expensive statements trace, you can also enable per statement memory tracking, the expensive statements trace will show the peak memory size used to execute the expensive statements.

The SQL optimization step debug trace (SqlOptStep) logs the query optimization (QO) tree in each step of the optimization.

Two types of optimization exist:

• Rule-based rewriting
• Cost-based enumeration

Rule Based shows QO tree before and after each rule has been applied

Cost Based shows:

• Logical tree of the final execution plan
• Physical tree of the final execution plan
• Type of operators chosen

There are two main categories of optimization, rule-based rewriting and cost-based enumeration. In the case of rule-based rewriting, the SQL optimization step trace shows what the QO tree looks like after each rule has been applied. For example, you can see that the QO trees are different before and after calculation view unfolding. In the case of cost-based enumeration, the SQL optimization step trace shows the logical tree and the physical tree of the final execution plan. While the logical execution plan indicates which operators are located where, the physical execution plan tells you which types of operators were selected.

The trace also provides more detailed information at the debug level. This includes estimated sizes, estimated subtree costs, applied rule names, applied enumerator names, partition information, column IDs, relation IDs, and much more.

The fastest and most effective way to learn about the trace is to use it yourself. This means working through all the activities required to collect the trace and analyze it.

SQL Optimization Time Debug Trace:

• Measures duration of the optimization and intermediate steps in between start and end of compilation.
• Measures how much time it takes to finish each step of the optimization process.
• The SQL optimization time trace is primarily a compilation trace not a execution time trace.
• This trace can be enabled by using the ALTER SYSTEM statement and set the parameter (trace, sqlopttime).

The SQL optimization time trace (SqlOptTime) can be compared to a stopwatch. It measures how much time it takes to finish each step of the optimization process. It records not only the time required for the overall rule-based or cost-based optimization process but also the time consumed by each writing rule in rule-based optimization and the number of repeated enumerators during cost-based optimization.

The stopwatch for the SQL optimization time trace starts when compilation begins and ends when it finishes. It is primarily a compilation trace rather than an execution trace and can even be obtained by compiling a query and canceling it immediately before execution. There is no point in collecting this trace if your aim is to improve execution time, particularly when this involves an out of memory condition or long running thread caused by a large amount of materialization. The SQL optimization time trace is helpful when there is a slow compilation issue.

With regard to execution time, there could be cases where long execution times can only be reduced at the expense of longer compilation times, so the total time taken from compilation to execution remains the same irrespective of which one you choose to reduce. Although these are execution time-related performance issues, you would still need the SQL optimization time trace to investigate whether the compilation time could be improved. Many cases involving long execution times could be closely related to compilation, such as when the optimizer reaches the enumeration limit, which causes it to stop and generate a plan. Or there might simply be a slow compilation issue where the purpose of the investigation is to reduce the compilation time.

The SQL optimization time trace can also be used in a general sense to get a summarized report of the rules and enumerators used to construct the final plan. The SQL optimization step trace is usually very long, so a brief version that just tells you what choices the optimizer made rather than how they were made might be preferable. In that case, the SQL optimization time trace would be the appropriate tool.

However, execution time and compilation time can be mutually dependent and closely connected to execution time.

The SQL optimization time trace is used in as a general summary report of the rules and enumerators used in the execution plan.

Explain Plan delivers a rough overview of the execution plan chosen from the Optimizer.

In the result set of Explain Plan, the operators are listed in a hierarchy and can be read from bottom up.

Explain Plan provides information about the compiled plan of a given procedure. Explain Plan generates the plan information by using the given SQLScript Engine plan structure. It traverses the plan structure and records each information corresponding to the current SQLScript Engine operator.

In the case of invoking another procedure inside of a procedure, Explain Plan inserts the results of the invoked procedure (callee) under the invoke operator (caller), although the actual invoked procedure is a sub-plan which is not located under the invoke operator.

Another case is the ELSE operator. Explain Plan generates a dummy ELSE operator to represent alternative operators in the condition operator.

Analyze SQL creates a plan graph in a new tab within the Database Explorer. With this plan, you can start analyzing your SQL.

This plan lets you thoroughly analyze your SQL, detect details like involved tables, materializations, execution engine usage, and a convenient timeline of execution steps.

The Plan Graph provides three main tabs and seven sub-tabs. The Overview tab provides a good starting point for exploring the Plan Graph. On sub-tabs Operators, tables in use and more can be filtered.

In the sub-tab Operators, the sum of involved operators are presented and their systems.

The Plan Graph shows each execution step with inclusive and exclusive time measures in microsecond units. One can read this plan from bottom up. It is shown initially folded but each step can be unfolded to analyze processing of the Optimizer substeps. The engine usage chosen by the Optimizer can be detected and then you can compare the estimated rows with the real rows that were evaluated.

You can revise the execution plan of a SQL statement. It displays a visualization of a critical path based on inclusive execution time of operators and allows you to identify the most expensive path in a query execution plan.

In case of a SQLScript, the Plan Graph displays its complete definition. To retrieve the information in a text format, you can copy the definition by clicking the copy icon.

In the Plan Graph tab, you can open the Detail Properties view by clicking one of the operators. This view offers detailed information on the operator such as name, location, ID, summary.

You can open the edge information detail by clicking one of the links between the operators. This view offers further information on the edge values, such as target, source, output cardinality, fetch call count, and estimated output cardinality.

Furthermore, you can configure plan graph settings. You can set the color of the nodes by type or location, and choose to show either physical or logical inner plans.

The Timeline sub-tab shows each execution step and its duration.

Each row represents an execution step with its duration.

The result hierarchy can be unfolded to drill down.

• The timeline can be read from the left to the right. The result steps are on the left side (operator tree table) and are organized hierarchically. You get a complete overview of the execution plan based on the visualization of sequential time-stamps. The operator tree table displays hierarchical parent-child relationships and container-inner plan relationships. Based on the operators, the timeline chart shows the operations executed at different time intervals
• The operator tree table initially is shown folded but each step can be unfolded. In doing so, you can analyze the processing of Optimizer sub-steps so as to find out the stages of performance and starting point for tuning activities.

Generated plans can be saved (suffix: .plv). For instance, they can be saved on the client and loaded and analyzed into another application like SAP Business Application Studio, which is a cloud tool.

In that way different execution plans on different environments (for example, hybrid scenarios) can be compared to reveal and solve performance breaks within SAP HANA.