Comparing Parameterized Query Plans in SAP HANA Cloud with SQL Analyzer

Context: Parameterized Queries and Performance Fluctuation

A parameterized query can run fast for some input values and slow for others because data distribution is skewed. The optimizer compiles one plan per statement, and that plan may be optimal for a small selectivity range but poor for a large one. For example, a table function that accepts a region parameter may return 50 rows in 20 ms for region DE but 2 million rows in 4 s for region GLOBAL. The difference is not necessarily a bug in the query; it is often a selectivity-dependent plan choice. The first step is to capture the execution plan for each parameter set and compare them with SQL Analyzer. The goal is to decide whether the fluctuation is caused by plan selection and whether enabling Plan Variants or rewriting the query is the right fix.

SQL Analyzer is a set of views, tables, and graphs that lets you analyze any SQL query. You can drill into a graph execution, analyze the timeline of query compilation and execution, visualize which tables were used, see how many records each step processed, and check the sequence of operators. It is not limited to calculation views; it can analyze SQL defined in table functions and SQL Console statements. To use it, you must add the SAP HANA Performance Tools extension to the development space, and that can only be done when the development space is stopped. The analyzing user needs the system privileges TRACE ADMIN and INIFILE ADMIN. The workflow starts by placing the parameterized SQL in an SQL Console, then generating a plan file with Analyze Generate SQL Analyzer Plan File, and finally opening the plan file in the HANA SQL Analyzer view.

Plan files can be generated from the embedded SAP HANA Database Explorer, where the plan file opens directly in the SQL Analyzer view, or from external tools, where the plan file must be downloaded and uploaded to the Explorer view of SAP Business Application Studio. The location of generated plan files is fixed and cannot be changed. If you want to analyze a plan file later, you can access it from the Explorer view or from the external database explorer under Catalog Database Diagnostic Files DB Instance ID other. The key point is that you need a separate plan file for each parameter value you want to compare. One cached plan is not enough when selectivity varies across parameter values.

The comparison should focus on step-level execution time, record counts processed, and operator sequence. A step that takes significantly longer than others, or a step that processes far more records than expected, is a bottleneck. The operator sequence tells you whether the optimizer chose a different join order, a different access path, or a different processing engine. If the plan differs for a slow parameter value, you can either enable Plan Variants so HANA caches multiple plans based on filter selectivity clusters, or rewrite the query and restructure the model so the optimizer produces a stable plan across all selectivity ranges. The decision depends on whether the optimizer can distinguish the selectivity clusters and whether the query structure is fundamentally efficient.

Plan Variants allow SAP HANA to cache multiple execution plans for a parameterized query based on the selectivity of table filters. The optimizer evaluates predicates, compiles plans, and associates filter values with clusters. This reduces performance fluctuations and ensures more stable query performance. However, Plan Variants only help when the optimizer can group parameter values into distinct selectivity clusters. If the query structure is inherently inefficient for all inputs, or if the optimizer cannot distinguish the clusters, plan management alone will not solve the problem. In that case, rewriting the query or restructuring the model is the remaining option.

The monitoring views M_SQL_PLAN_VARIANTS and M_SQL_PLAN_VARIANT_STATISTICS can be used to monitor active Plan Variants and view corresponding execution statistics. These views help you verify that the optimizer created the expected clusters and that the plans are being used. They also help you measure the performance improvements achieved with Plan Variants, including reduced performance fluctuations and ensured stable query performance. The decision to rewrite should be based on the evidence from the plan files, not on assumptions about the data distribution.

The concrete example is a table function that accepts a region parameter. For region DE the query returns 50 rows in 20 ms; for region GLOBAL it returns 2 million rows in 4 s. Place both parameterized calls in the SQL Console, generate two plan files, open them side by side in SQL Analyzer, and observe that for GLOBAL the optimizer chose a full column-store scan with a nested-loop join instead of the index-seek-plus-hash-join it used for DE. This confirms the plan differs by parameter value and suggests either enabling Plan Variants so both plans are cached, or rewriting the join order and filter pushdown to make the optimizer produce a stable plan across all selectivity ranges.

SELECT * FROM TABLE(TABLE_FUNCTION(:region)) WHERE region = :region;

Reasoning: Plan Variants versus Query Rewriting

A parameterized query is cached with a single execution plan, but that plan is a compromise. When the selectivity of a filter changes, the optimal plan can change too. If the optimizer cannot distinguish the selectivity clusters, it may choose a plan that is good for some values and bad for others. This is the root cause of SQL performance fluctuation. Plan Variants address this by allowing SAP HANA to cache multiple plans for parameterized queries based on the selectivity of table filters. The optimizer evaluates predicates, compiles plans, and associates filter values with clusters. This means the system can keep a plan for a small, selective range and a different plan for a large, unselective range.

The limitation of caching only one execution plan is that it cannot adapt to data distribution changes. A plan that is optimal for a small result set may be terrible for a large result set because it uses a nested-loop join or a full scan. Plan Variants solve this by creating separate plans for each selectivity cluster. The optimizer decides which cluster a parameter value belongs to and uses the corresponding plan. This reduces performance fluctuations and ensures more stable query performance. The monitoring views M_SQL_PLAN_VARIANTS and M_SQL_PLAN_VARIANT_STATISTICS show the active Plan Variants and their execution statistics, so you can verify that the clusters were created correctly and that the plans are being used.

However, Plan Variants are not a universal fix. They only help when the optimizer can distinguish selectivity clusters. If the query structure is inherently inefficient for all inputs, or if the optimizer cannot separate the parameter values into distinct clusters, plan management will not improve performance. In that case, the remaining option is to rewrite the query or restructure the model. Rewriting can change the join order, push filters down, or replace a table function with a more efficient construct. The decision should be based on the plan files from SQL Analyzer, not on assumptions about the data distribution.

The workflow is to generate plan files for each parameter value, compare them in SQL Analyzer, and then decide whether Plan Variants or rewriting is needed. If the plan files show different operator sequences, record counts, or step timings, the optimizer is already distinguishing the parameter values. Enabling Plan Variants may then reduce fluctuation. If the plan files show the same plan but that plan is slow for a specific parameter value, the optimizer is not distinguishing the clusters, and rewriting is likely required. The monitoring views help you verify that the Plan Variants are active and that the execution statistics match the expected selectivity clusters.

The key distinction is between plan management and query structure. Plan Variants manage multiple plans for a parameterized query, but they do not fix a query whose structure is inefficient for all inputs. If the optimizer cannot distinguish selectivity clusters, or if the data skew is extreme, the query must be rewritten. The evidence from SQL Analyzer should guide the decision. The plan files show whether the optimizer is choosing different plans for different parameter values, and whether those plans are stable. If the plans are stable but slow for a specific parameter value, the query structure is the problem. If the plans differ but the slow plan is chosen for a large selectivity range, Plan Variants may be sufficient.

The monitoring views provide the evidence needed to make this decision. M_SQL_PLAN_VARIANTS shows the active Plan Variants, and M_SQL_PLAN_VARIANT_STATISTICS shows the corresponding execution statistics. You can use these views to verify that the optimizer created the expected clusters and that the plans are being used. This is important because Plan Variants can add overhead, and you need to confirm that the benefit outweighs the cost. The decision to rewrite should be based on the plan files and the monitoring views, not on assumptions about the data distribution.

The concrete example is a table function that accepts a region parameter. For region DE the query returns 50 rows in 20 ms; for region GLOBAL it returns 2 million rows in 4 s. Place both parameterized calls in the SQL Console, generate two plan files, open them side by side in SQL Analyzer, and observe that for GLOBAL the optimizer chose a full column-store scan with a nested-loop join instead of the index-seek-plus-hash-join it used for DE. This confirms the plan differs by parameter value and suggests either enabling Plan Variants so both plans are cached, or rewriting the join order and filter pushdown to make the optimizer produce a stable plan across all selectivity ranges.

SELECT * FROM M_SQL_PLAN_VARIANTS WHERE SCHEMA_NAME = '<container_schema_name>' AND OBJECT_NAME = '<calculation_view_or_function>';

Concrete Illustration: Comparing Plan Files in SQL Analyzer

The workflow starts by placing the parameterized SQL in an SQL Console. You do not need to execute the query; you only need to generate the plan file. Use the menu option Analyze Generate SQL Analyzer Plan File, choose a file name prefix, and save. The location is already set and cannot be changed. If the SQL Console was opened from the embedded SAP HANA Database Explorer, the plan file opens immediately in the HANA SQL Analyzer view. If the plan file was generated from another tool, such as Data Preview or the external SAP HANA Database Explorer, you must download the plan file and upload it to the Explorer view of SAP Business Application Studio. The extension SAP HANA Performance Tools must be added to the development space, and that can only be done when the development space is stopped.

Once the plan file is uploaded, select it to display the results in the main window. The SQL Analyzer view shows the execution plan as a graph, with step-level execution time, record counts processed, and operator sequence. You can drill down into a graph execution, analyze the timeline of query compilation and execution, and visualize how many tables were used. The goal is to identify steps that take significantly longer than others, steps that process far more records than expected, and operator sequences that differ between parameter values. These are the signs of a selectivity-dependent plan choice.

For the concrete example, generate two plan files: one for region DE and one for region GLOBAL. Open both files in SQL Analyzer and compare them side by side. For region DE, the plan should show an index seek and a hash join, with a small number of records processed. For region GLOBAL, the plan should show a full column-store scan and a nested-loop join, with a large number of records processed. The step-level execution time and record counts will confirm the difference. This evidence shows that the optimizer chose a different plan for the slow parameter value, and it suggests that Plan Variants or query rewriting is needed.

The comparison should focus on the operator sequence, because that reveals whether the optimizer chose a different join order, access path, or processing engine. A full column-store scan instead of an index seek is a common sign of a selectivity mismatch. A nested-loop join instead of a hash join is another sign. The record counts processed at each step tell you how much data the plan is handling. If a step processes millions of rows when it should process dozens, that step is a bottleneck. The timeline of query compilation and execution helps you see whether the slowness is in compilation, execution, or a specific operator.

After the comparison, you can decide whether to enable Plan Variants or rewrite the query. If the plan files show different plans for different parameter values, and the optimizer can distinguish the selectivity clusters, enabling Plan Variants may reduce fluctuation. If the plan files show the same plan but that plan is slow for a specific parameter value, the optimizer is not distinguishing the clusters, and rewriting is likely required. The monitoring views M_SQL_PLAN_VARIANTS and M_SQL_PLAN_VARIANT_STATISTICS can verify that the Plan Variants are active and that the execution statistics match the expected selectivity clusters.

The concrete example is a table function that accepts a region parameter. For region DE the query returns 50 rows in 20 ms; for region GLOBAL it returns 2 million rows in 4 s. Place both parameterized calls in the SQL Console, generate two plan files, open them side by side in SQL Analyzer, and observe that for GLOBAL the optimizer chose a full column-store scan with a nested-loop join instead of the index-seek-plus-hash-join it used for DE. This confirms the plan differs by parameter value and suggests either enabling Plan Variants so both plans are cached, or rewriting the join order and filter pushdown to make the optimizer produce a stable plan across all selectivity ranges.

EXPLAIN PLAN FOR SELECT * FROM TABLE(TABLE_FUNCTION(:region)) WHERE region = :region;

Applicability Limits

SQL Analyzer requires the SAP HANA Performance Tools extension in the development space, and that extension can only be added when the development space is stopped. This is a prerequisite that must be verified before starting the workflow. The analyzing user needs the system privileges TRACE ADMIN and INIFILE ADMIN. These privileges are system-level and may not be granted to application users in production, so the workflow may not be available to all users. Plan files generated outside the embedded SAP HANA Database Explorer must be manually downloaded and uploaded, adding a step to the workflow. The location of generated plan files is fixed and cannot be changed, which can complicate the process.

Plan Variants help only when the optimizer can group parameter values into distinct selectivity clusters. They do not fix a query whose structure is inherently inefficient for all inputs. If the optimizer cannot distinguish the selectivity clusters, or if the data skew is extreme, plan management alone will not solve the problem. In that case, rewriting the query or restructuring the model is the remaining option. The monitoring views M_SQL_PLAN_VARIANTS and M_SQL_PLAN_VARIANT_STATISTICS can help you verify that the optimizer created the expected clusters and that the plans are being used, but they do not change the underlying query structure.

The decision to enable Plan Variants or rewrite the query should be based on the evidence from the plan files. If the plan files show different plans for different parameter values, and the optimizer can distinguish the selectivity clusters, enabling Plan Variants may reduce fluctuation. If the plan files show the same plan but that plan is slow for a specific parameter value, the optimizer is not distinguishing the clusters, and rewriting is likely required. The monitoring views provide the evidence needed to make this decision, but they do not replace the need to understand the query and the data distribution.

The concrete example is a table function that accepts a region parameter. For region DE the query returns 50 rows in 20 ms; for region GLOBAL it returns 2 million rows in 4 s. Place both parameterized calls in the SQL Console, generate two plan files, open them side by side in SQL Analyzer, and observe that for GLOBAL the optimizer chose a full column-store scan with a nested-loop join instead of the index-seek-plus-hash-join it used for DE. This confirms the plan differs by parameter value and suggests either enabling Plan Variants so both plans are cached, or rewriting the join order and filter pushdown to make the optimizer produce a stable plan across all selectivity ranges.

SELECT * FROM M_SQL_PLAN_VARIANTS WHERE SCHEMA_NAME = '<container_schema_name>' AND OBJECT_NAME = '<calculation_view_or_function>';