PUBLIC

openSAP
Using the Optimizer for SQLScript Programming in SAP
HANA

Week 3 Unit 1(I)

[Link] Hello, and welcome to the first unit of week three. I'm Jin Yeon Lee and I'm very glad to
present
[Link] the content of week three with my colleague Minjeong Lee.
[Link] In week one and two, we studied how SQLScript engine was, and how to use its
supportability features.
[Link] This week we will study useful methods NO_INLINE hint, BIND_AS_PARAMETER function,

[Link] BIND_AS_VALUE function, and SQL hints for SQLScript... By the end of this week, you'll be
familiar
[Link] with using these methods to solve performance issues on your SQLScript procedure.
[Link] Before explaining the INLINE feature, let me tell about usage of table variables first.
[Link] In case we have to make a complex logic in SQL, we usually compose a long and complex
query
[Link] with many relational operations like joins and group bys. Sometimes we use subqueries too.

[Link] If you have to edit this long complex SQL statement, it is certainly very difficult.
[Link] And it sometimes leads to unexpected results. But in SQLScript,
[Link] instead of composing very long and complex single SQL statements,
[Link] we can make several short SQL statements combined with table variables.
[Link] Logically, these are exactly the same, but this way can reduce the complexity of SQL
statements.
[Link] Then it is definitely helpful to comprehend the logic, so then you can easily modify them if
needed.
[Link] For this reason, using table variables is always recommended. And I'm sure you are already
using table variables a lot
[Link] in your SQLScript procedure. But let's think about using table variables
[Link] from a performance perspective. What do you think of executing multiple SQL statements
[Link] instead of a single long complex query in SQLScript calls? As we learned,
[Link] when an SQL statement in SQLScript procedure is executed, SQLScript engine passes that
query to SQL engine,
[Link] then gets the result set. The more result sets, the more interactions
[Link] between SQLScript engine and SQL engine happen, including memory copy operations and
so on.
[Link] And those are expensive from a performance point of view. Instead of multiple short
queries,
[Link] if a single long complex query is passed to an SQL engine from an SQLScript engine,
[Link] then this query can be executed with the optimal query plan by SQL optimizer.
[Link] Then its results set is passed to SQLScript engine one time.
[Link] SAP HANA database provides a feature to take both advantages. If you use table variables
to split the long complex query
[Link] into multiple short ones, then HANA database executes them
[Link] as a single complex one internally. So you don't need to worry about a performance penalty

[Link] by multiple short queries with table variables. SQLScript optimizer tries to combine
[Link] dependent SQL statements, as many as possible, and make them a single query string.
[Link] This is called inlining, one of the optimizations of SQLScript.
[Link] It is beneficial to the performance. For example, materialization,
[Link] materializing results of table variables are omitted,
[Link] and a better join order can be chosen by SQL optimizer. Let me show how the inlining
feature works
[Link] with this example procedure. The procedure "get_results" consists of four SQL statements,

[Link] and they are linked with table variables. Table variable "final_products" contains the result
[Link] of SQL statement one and it is used in SQL statement two.
[Link] Table variable "item" is for the result of statement two and used in statement three.
[Link] Lastly, the table variable "aggregated_items" comes from statement three,
[Link] and it is consumed in statement four. When this procedure is compiled, SQLScript optimizer

[Link] checks the dependency of these four SQL statements, then applies the inlining feature.
[Link] As I explained before, INLINE features rewrite multiple SQL statements
[Link] into an equivalent single SQL statement using WITH clause.
[Link] So once these queries are inline, these SQL statements are totally reshaped.
[Link] Let's check the result. When you use EXPLAIN PLAN FOR CALL,
[Link] you can find which SQL statements are executed, which is the output of SQLScript
optimization,
[Link] without executing the procedure. Since we collected EXPLAIN PLAN FOR CALL with the
statement name "INLINE" before,
[Link] let's delete existing records having STATEMENT_NAME "INLINE" from
EXPLAIN_CALL_PLANS table first.
[Link] Then execute EXPLAIN PLAN FOR CALL for this procedure with input parameters.
[Link] Then check the result from EXPLAIN_CALL_PLANS table. As you can see,
OPERATOR_STRING column,
[Link] there is only one SQL statement though there are four SQL statements in its definition code.

[Link] Now, let's check SQLSCRIPT_PLAN_PROFILER. This shows actual executed SQL
statements,
[Link] whereas "EXPLAIN PLAN FOR CALL" shows an SQLScript execution plan, before actual
execution.
[Link] Unlike the result of "EXPLAIN PLAN FOR CALL" in the previous slide,
[Link] the result of plan profiler for this call statement shows two SQL statements.
[Link] To see executed SQL statements, you should check a value in column
OPERATOR_STATEMENT_STRING
[Link] where the column operator is Execute SQL Statement. Table Assign Op in column
OPERATOR
[Link] is your operation for assigning table variable. And Execute SQL Statement operation

2 / 21
[Link] is next to this Table Assign Op. This means a query result from Execute SQL Statement
operation
[Link] is stored in given table variables, in this Table Assign Op operation.
[Link] Let's check the first SQL statement. The first one starts with a comment.
[Link] This comment shows how the inlining feature is applied. It shows which table variables
[Link] are used in this WITH clause by showing their name and position of the code.
[Link] This information is very important and helpful because you have to modify your SQL script
code.
[Link] You can easily find how your change will be reshaped. At the end of this comment,
[Link] you can see WITH clause follows. Then let me explain the last SQL statement
[Link] which you didn't see in the result of "EXPLAIN PLAN FOR CALL".
[Link] This select statement is for fetching the result to output parameter.
[Link] "EXPLAIN PLAN FOR CALL" does not execute the procedure, so SQLScript engine does
not need to send the result set
[Link] via output parameter. That is why select statement for
[Link] this output parameter is not shown,
[Link] but SQLScript Plan Profiler shows actual execution of call statement.
[Link] When call statement is executed, output parameter is actually selected and returned
[Link] as the result of call statement. That's why this last SQL statement is shown here.
[Link] Let me show the output of inlining. In the code, four SQL statements are associated
[Link] with table variables. After SQLScript optimizer confirms dependency
[Link] of these four SQL statements and their table variables, it combines them into a single SQL
statement.
[Link] The new single SQL statement starts from WITH clause and three SQL statements
[Link] and their table variables are now represented as a subquery in that WITH clause.
[Link] When checking the logic of this new SQL statement, you can see its logic is exactly the
same as the one
[Link] from four SQL statements in the code. In WITH clause, statement one is merged
[Link] as a subquery with query name "_SYS_FINAL_PRODUCT_2".
[Link] Statement two is merged with query name "_SYS_ITEMS_2" and statement three is done
with query name
[Link] "_SYS_AGGREGATED_ITEMS_2". During the execution of this single statement,
[Link] values in table variable, final products, items, and aggregated items are not materialized.
[Link] Instead, join and group by operations run on their intermediate result and only the final
results set of aggregated item
[Link] is materialized and copied to SQLScript engine.

3 / 21
Week 3 Unit 1(II)

[Link] Even though inlining is beneficial to performance in most cases, there may be a rare case
[Link] where combined SQL statements lead to a non-optimal query plan.
[Link] In this case, blocking a combination of specific SQL statements will help for better
performance.
[Link] And you can do that by using the NO_INLINE hint. For example, a procedure has a lot of
dependencies
[Link] from many SQL statements. The inlining feature combines them into a very complex query.

[Link] SQL optimizer needs more time to optimize the SQL query due to it having so many things
to be checked.
[Link] Then it finally results in long compilation time. Normally long compilation is not a problem.
[Link] Because once its query plan is stored in SQL plan cache, next time the cached plan is
reused without compilation.
[Link] But, in case the long compilation time may affect overall performance,
[Link] so you want to reduce the compilation time, then you can use NO_INLINE hint to avoid
inlining.
[Link] Let me present an example of this case in the next unit. Another case of blocking inlining
[Link] is when a sub-optimal plan is chosen due to a big join size. Let's assume there are two
queries,
[Link] called query one and query two inside the procedure. Each query has several joins.
[Link] And these two queries are merged into a single query by SQLScript optimizer.
[Link] At this point, there may be a situation where joins become very complex and large
[Link] after merging joins from two queries, and running into an unexpected out-of-memory
situation.
[Link] In this case, we can try to use NO_INLINE hint. Going back to the example, let's try to use
NO_INLINE hint
[Link] at the end of the first SQL statement in procedure get_results. As you know, the first SQL
statement is connected
[Link] with the second one by table variable final_products. Now, due to the added NO_INLINE
hint,
[Link] SQLScript optimizer does not combine the first SQL statement and second one. Even if they
have dependency.
[Link] Since the second and third and fourth SQL statement do not have NO_INLINE hint,
[Link] they are combined just like before. Then, let's check the result of explain plan for CALL
[Link] to see how the execution plan of this procedure is changing. Let's delete the record having
the statement name INLINE
[Link] from EXPLAIN_CALL_PLANS table first, because it contains the result of previous explain
plan for CALL.
[Link] Then execute explain plan for CALL for this procedure with input parameters again.
[Link] Then check the results from the EXPLAIN_CALL_PLANS table. As you can see, two SQL
statements
[Link] are in column OPERATOR_STRING now. The first SQL statement shows WITH HINT
NO_INLINE is added.
[Link] The other SQL statement is output of the inlining optimization from the second, third, and
fourth SQL statements.
[Link] Then now, let's check the result of the SQLScript Plan Profiler. As you expected, it shows
three SQL statements.

4 / 21
[Link] The first one is the SQL statement having NO_INLINE hint. The second one is inlined SQL
statement
[Link] from the second, third, and fourth SQL statement. This is just the same result as explain
plan for CALL shows.
[Link] The last SQL statement is select a statement for output parameters, as you had checked
previously.
[Link] As I explained, Table Assign Op is an operation to assign table variables for the next
operation.
[Link] Its operator statement string also shows SQL strings that are executed in the next
operation.
[Link] But you can see $$_SS_SE_TAB_VAR_FINAL_PRODUCTS_2$$ in FROM clause. The
query looks like it is coming
[Link] from the second SQL statement in the code, and
$$_SS_SE_TAB_VAR_FINAL_PRODUCTS_2$$
[Link] is in the red box. It looks like the name of
[Link] table variable, FINAL_PRODUCTS. It comes from the first SQL statement.
[Link] When you check the executed SQL statement,
$$_SS_SE_TAB_VAR_FINAL_PRODUCTS_2$$
[Link] in the red box is not shown anymore. Instead, you can see SYS_SS2_TMP_TABLE, some
numbers,
[Link] FINAL_PRODUCTS_2 with a long GUID value in the green box here.
[Link] Both point the table variable final product, which is assigned from the first SQL statement.
[Link] But the name having $$ is a kind of a logical name of a table variable.
[Link] And its physical name is the long name having GUID value.
[Link] This GUID value is generated newly whenever an SQL script is compiled.
[Link] This naming rule for local temp table was explained in unit two of week two before.
[Link] And it'll be explained again in the last unit of week three.
[Link] So far, we checked what happened when NO_INLINE hint is added at the end of the first
SQL statement.
[Link] This time, let's try to break all dependency by adding this hint at the end of the first,
[Link] second, and third SQL statements. Let's repeat the step to see the result of explain plan for
CALL.
[Link] As you expected, the SQLScript execution plan contains four SQL statements.
[Link] Three SQL statements have WITH HINT NO_INLINE. SQLScript Plan Profiler shows the
same result.
[Link] You can also see logical temp table with GUID values in their names, from the executed
SQL statement.
[Link] It means the results of each SQL statement are stored in corresponding local temp as table
variables.
[Link] As I said, in this case values of result sets are materialized. In column OPERATOR
DETAILS, you can see the size
[Link] of the table variable where the result of the query is stored. Its unit is bytes.
[Link] Okay, so in the previous example, we added NO_INLINE hint
[Link] at the end of the first, second, and third SQL statements. So, four SQL statements were
executed separately.
[Link] Then, can you guess what will happen if NO_INLINE hint is added to the last SQL
statement?
[Link] We learned the procedure flattening optimization rule in week one. It means a procedure A
is called inside another procedure B
[Link] by a CALL statement. This CALL statement for procedure A

5 / 21
[Link] is replaced with the body of procedure A. Once procedure A is flattened, then all SQL
statements of
[Link] procedure A can be inlined with SQL statement of procedure B.
[Link] Let's check this example. Procedure get_results_wrapper has a CALL statement
[Link] of procedure get_results and one simple SQL statement. Since procedure get_results
already has four SQL statements,
[Link] so let's say this simple SQL statement, SELECT * FROM RESULTS_TAB, is statement five.

[Link] And all four SQL statements in procedure get_results have NO_INLINE hint.
[Link] Here is the result of explain plan for CALL. You can see five SQL statements in the
execution plan,
[Link] because NO_INLINE hint at the end of the fourth SQL statement. blocks combining
statement four and statement five.
[Link] If you don't add NO_INLINE hint at the end of fourth SQL statement,
[Link] then this is explain plan for CALL shows only four SQL statements,
[Link] because statements four and five are combined. The last case is adding NO_INLINE hint
[Link] at the end of inner CALL statement. Here's an example.
[Link] Procedure get_results_wrapper consists of CALL procedure, CALL procedure get_results,
and a simple SELECT statement.
[Link] What will happen if NO_INLINE hint is added at the end of the CALL statement of the
procedure get_results?
[Link] Let's check the result of explain plan for CALL. As you can see here, CALL statement of
procedure get_results
[Link] is executed via operator call. This means procedure get_results is not flattened.
[Link] So NO_INLINE hint blocks not only inlining of SQL statement, but also flattening inner CALL
statement.
[Link] In week one, we learned four SQL Script optimization rules. Constant propagation,
procedure flattening,
[Link] SQL statement inlining, and control flow simplification. You can control inlining optimization

[Link] and procedure flattening by using NO_INLINE hint. This hint is very simple to use, but very
powerful.
[Link] So that your procedure can be executed in totally different ways. So you always have to
check the result of
[Link] explain plan for CALL when you added this NO_INLINE hint. With that, we come to the end
of unit one.
[Link] We learned about inline features and NO_INLINE hint. Inlining optimization combines
multiple short queries
[Link] associated with table variables into a single query. It can take more advantage of SQL
optimizer,
[Link] and lead to less materialization of the result of table variables.
[Link] NO_INLINE hint is a way of controlling inlining optimization. It can also control procedure
flattening.
[Link] Those are the key takeaways of this unit. In the next unit, I will present a case study of
NO_INLINE hint.
[Link] Thank you for your attention. See you.

6 / 21
Week 3 Unit 2

[Link] Hello, my name is Jin Yeon Lee. Today, I'm going to explain a case study
[Link] of using the INLINE feature. Let us show we have a SQLScript performance issue.
[Link] A slow running procedure is observed in the system. It takes about 18 seconds.
[Link] That is much longer than expected. The problematic statement is CALL statement
[Link] of procedure CL_ABC_READ_DEF_BASIC=>GET_MRP_ELEMENTS3, with the integer
value 717 as an input parameter.
[Link] Due to the business application logic, this input parameter value is given
[Link] as a constant value, but it keeps changing. In the example, this time it is 717,
[Link] or the next time it can be 718, or any other value. When executing the same CALL
statement
[Link] with the same input parameter value, it runs quickly, not taking 18 seconds.
[Link] But when changing the parameter value, I can see slow response time again.
[Link] In this situation, which supportability feature do you think would help to identify
[Link] where the most time is spent? Yes, since we know the problematic statement,
[Link] let's collect the Plan Visualizer trace first. So the collected Plan Visualizer trace looks like
the following:
[Link] The OVERVIEW page shows compilation time is 218 milliseconds and execution time is
about 18 seconds,
[Link] under EXECUTION SUMMARY box. DOMINANT PLAN OPERATORS box shows
[Link] what is the most expensive operator in this trace. Let's click the most expensive operator
here.
[Link] Then you can see the box having number 1 in the blue circle
[Link] in the PLAN GRAPH page. And you can also see
[Link] its inclusive time is 17.9 seconds. This operator definitely takes a long time,
[Link] because the inclusive time of the next operator is just 3.5 seconds. Now let's check the
STATEMENT_STATISTICS page.
[Link] The execution time of the CALL statement is about 17,000 milliseconds,
[Link] but it's compilation time is 218 milliseconds. When you check which inner SQL statement
takes long,
[Link] the second inner SQL statement from the top looks suspicious.
[Link] Because each execution time is 423 milliseconds and its compilation time is about 14
seconds.
[Link] But here, you might wonder why compilation time of this CALL statement is just 218
milliseconds
[Link] though the compilation time of the second inner SQL statement is 14 seconds.
[Link] The execution time of the CALL statement is 17,983 milliseconds.
[Link] Though the sum of execution time of all inner SQL statements is about only 600
milliseconds.
[Link] On the other hand, the compilation time of the CALL statement is 218 milliseconds,
[Link] but the sum of the compilation time of inner SQL statement is 17,207 milliseconds.
[Link] Where does such a big difference between execution time of CALL statement and the sum
of execution time of all inner statements come from?
[Link] In week one, we learned SQLScript engine compiles
[Link] SQLScript procedure code. Then it generates its execution plan.

7 / 21
[Link] During SQLScript engine executing its execution plan, it passes inner SQL statement string
to SQL engine.
[Link] Then SQL engine processes that SQL statement string. So compilation of inner SQL
statements happens
[Link] in SQLScript execution step, not SQLScript compilation step.
[Link] That is 17,207 milliseconds of the sum of compilation time
[Link] of inner SQL statements belong to execution time of CALL statements.
[Link] Then we can now see the compilation of the second inner SQL statement is most dominant.

[Link] And it is located in line number 295. Timeline view also shows the same information that we
found.
[Link] The compilation time of the second inner SQL statement is long. In the STATEMENT
STATISTICS page, you can see the open link
[Link] in the Deep Dive field in this inner SQL query. When clicking it, you can get a Plan
Visualizer trace
[Link] of this inner SQL statement in a separate window. In that separate Plan Visualizer trace
window
[Link] of this inner SQL statement, you can find compilation summary information.
[Link] This information is a new feature of SQL analyzer tool, which SAP HANA Studio PlanViz
doesn't have.
[Link] Here the rewriting time is 10 seconds. It means this SQL plan is too complex.
[Link] So it needs some time to consider many rule-based optimizations.
[Link] Let's continue checking more information in STATEMENT STATISTICS page. When inlining
optimization is applied,
[Link] a string of inner SQL statement in a SQLScript code is reshaped.
[Link] SQLScript engine leaves information about which table variables are used
[Link] to combine queries as a comment. Click Show in New View,
[Link] after selecting the problematic line. Then you can see comments value
[Link] of the long compiled inner query. You can also find the same comment information
[Link] in the PROPERTIES tab of the most dominant operator BOX in the PLAN GRAPH page.
[Link] According to the comment of the problematic query, it is the output of the inlining
optimization,
[Link] by merging 17 inner SQL statements. This inlined query is very complex.
[Link] So reducing its complexity looks like a key to solve this performance issue.
[Link] Let's add NO_INLINE HINT at the end of these 17 SQL statements
[Link] where the comment points. Then, execute this procedure
[Link] with a different parameter value to check how fast it is now.
[Link] Plan Visualizer trace for the modified procedure shows CALL statement of this procedure
having NO_INLINE HINT
[Link] takes now about 2.6 seconds. That is a big improvement
[Link] from the previous response time of about 18 seconds. Let's check the STATEMENT
STATISTICS page
[Link] of the newly collected Plan Visualizer trace. As you expected,
[Link] the long running compiled inner SQL statement is replaced with 17 statement by adding
NO_INLINE hint now.
[Link] Previously, the single inlined query took 423 milliseconds for its execution.
[Link] And about 14 seconds for its compilation. In the new trace, the sum of execution time
[Link] of the new 17 statement is 3,646 milliseconds and the sum of their compilation time is
16,424 milliseconds.

8 / 21
[Link] Their total execution and compilation time is actually bigger than ones of
[Link] the previous problematic inline inner SQL statement. Then how can this CALL procedure
run faster than before?
[Link] To find out the reason why the modified SQLScript procedure is faster,
[Link] let's check the TIMELINE view. We see these 17 statements are compiled in parallel,
[Link] and they are executed independently. Due to this parallel compilation and execution,
[Link] more CPU resources are used than before. But the entire response time
[Link] of this CALL statement becomes very short. This is an acceptable trade off,
[Link] since the system has enough CPU power. The PLAN GRAPH page also shows 17
statements
[Link] are compiled and executed in parallel. In summary, this case shows how we approach a
long
[Link] compilation problem of SQLScript procedure. As a solution, we use NO_INLINE hint at the
end of each statement
[Link] to block inlining optimization. There is no perfect answer for when NO_INLINE hint must be
used.
[Link] Also there are other ways to solve this issue. When you use this NO_INLINE hint in your
production system,
[Link] please check how this hint affects your procedure as well as resource and workload of the
system first.
[Link] This is the end of unit two. Let me repeat key takeaways.
[Link] As I explained, the advantage of inlining feature, it is helpful to run your procedure fast in
most cases.
[Link] So if you want to use NO_INLINE hint, please understand the output
[Link] of SQLScript optimization first. Then use this hint properly,
[Link] only when the output of SQLScript optimization causes an issue. In the next unit
[Link] I will explain BIND_AS_PARAMETER and BIND_AS_VALUE SQLScript functions. Thank
you for your attention.
[Link] See you.

9 / 21
Week 3 Unit 3

[Link] Hello, and welcome to unit three of week three. I am Jin Yeon Lee.
[Link] Last time, we learned the inline feature and NO_INLINE hint, which can control statement
inlining and procedure flattening.
[Link] Today, we are going to study features that can control constant propagation.
[Link] This constant propagation means evaluating SQL expression during compile time,
[Link] and if a result can be known, then it is used as a constant value for execution time.
[Link] In HANA 2, we can control parameterization behavior of scalar parameters explicitly
[Link] by using BIND_AS_PARAMETER and BIND_AS_VALUE SQLScript functions.
[Link] In a nutshell, BIND_AS_PARAMETER treats a scalar variable as a query parameter.
[Link] On the other hand, BIND_AS_VALUE treats a scalar variable as a constant value.
[Link] Here is a procedure get_results having four input parameters and one output parameter.
[Link] Out of the four input parameters, please keep your eyes on the input parameters
[Link] im_category in the red box, and im_typecode in the blue box. They are used in the first SQL
statement.
[Link] And we will observe how they will be changing. Let's collect Explain Plan for CALL of this
procedure
[Link] with constant values "Notebooks" and "PR" for im_category and im_typecode.
[Link] From the results of Explain Plan, you can see CAST (Notebooks AS NVARCHAR(40))
[Link] and CAST (PR AS NVARCHAR(2)) are used instead of im_category and im_typecode.
[Link] This means when you use constant values for input parameters, by constant propagation
rule, SQLScript optimizer
[Link] changes these input parameters to given constant values. This time, let's use question
marks for input parameters.
[Link] When you use a question mark, it means values for input parameters will be given later
[Link] after preparing our CALL statement. We call it a parameterized query.
[Link] Explain Plan for CALL does not execute our CALL statement. We don't need to bind values
to parameters this time.
[Link] The result shows a different SQL statement from the previous result.
[Link] Previously im_category and im_typecode were replaced with constant values.
[Link] But this time, they were represented as query parameters like __typed_NString_($1, 40).
[Link] Here __typed_NString means data type of the parameter, and $1 means parameter
position,
[Link] and 40 is the length of the data type. Though it is unknown which value will be given
[Link] for im_category at this moment. It comes from the first parameter and it's 40 length
NVARCHAR value.
[Link] When a query parameter is given for the input parameter of a procedure, the SQL statement
becomes a parameterized query.
[Link] As you can see, this inner SQL statement is changing depending on what is given for input
parameter.
[Link] When you use constant value, the constant propagation rule optimizes our query as a literal
one.
[Link] If you use query parameters, then these parameters are used in the query
[Link] so it becomes a parameterized query. The advantage of using a parameterized query
[Link] is that the query plan can be reused, even when parameter values are changing.

10 / 21
[Link] A potential disadvantage is that the query plan can be suboptimal for some parameter
values.
[Link] Because the query plan is generated with the first given parameter value.
[Link] For a literal query when different values are given, the SQL statement is always changing.
[Link] So, it needs to compile a new optimal query plan. So when parameter values are changing
frequently,
[Link] compilation costs can be bigger than in the case of a parameterized query.
[Link] And if a similar query plan having different values are generated a lot in a short time,
[Link] SQL plan cache can flood. Which I said, BIND_AS_PARAMETER and BIND_AS_VALUE
[Link] SQLScript functions can control parameterization behavior of scalar parameters explicitly.
[Link] Let's check how they work. In this example,
[Link] BIND_AS_PARAMETER is applied to im_category and BIND_AS_VALUE is applied to
im_typecode.
[Link] Let's collect Explain Plan for CALL with constant value 'Notebooks' and 'PR' as parameter
values.
[Link] Though constant value 'Notebooks' is given for input parameter im_category. Im_category is
replaced with
[Link] query parameter __typed_NString_($1, 40). SQLScript function BIND_AS_PARAMETER
always
[Link] uses a query parameter to represent a scalar variable. So constant propagation rule
[Link] could not apply to input parameter im_category with constant value 'Notebooks'.
[Link] On the other hand, im_typecode is replaced with constant value 'PR'.
[Link] Constant propagation rule applies to im_typecode as well. Let me explain
BIND_AS_VALUE further in the next slide.
[Link] This time let's check Explain Plan for CALL of this procedure with parameters.
[Link] Can you guess how im_category and im_typecode will be changed?
[Link] There is no difference in BIND_AS_PARAMETER im_category because a query parameter
is already given.
[Link] But BIND_AS_VALUE, im_typecode is replaced with CAST function with some long name.

[Link] Since BIND_AS_VALUE always uses a value to represent a scalar variable during
compiling SQL script,
[Link] BIND_AS_VALUE im_typecode should become a constant value. But at this moment,
Explain Plan for CALL
[Link] does not execute this procedure yet. That is, a value for im_typecode is not yet given.
[Link] So SQLScript engine leaves it as a scalar variable name of im_typecode.
[Link] Once this call procedure is actually executed, this scalar variable name will be replaced with

[Link] a real value again. We will check this part in the next slide again.
[Link] Let's check the result of SQLScript Plan Profiler for this case, and check if constant value
'PR'
[Link] is used for BIND_AS_VALUE im_typecode. Since Plan Profiler executes a CALL statement,

[Link] you have to enter values for parameters. Then the scalar variable name of
[Link] im_typecode is replaced with 'PR' now. So you can see CAST (PR AS NVARCHAR(2)).
[Link] in the actual executed SQL statement. So far we studied BIND_AS_PARAMETER
[Link] and BIND_AS_VALUE SQLScript functions. They can control the parameterization behavior

[Link] of scalar variables explicitly. BIND_AS_PARAMETER treats a scalar variable

11 / 21
[Link] as a query parameter, so affected SQL statements become parameterized queries.
[Link] BIND_AS_VALUE treats scalar variables as a constant value,
[Link] so affected SQL statements become literal queries. In the next unit, we will check the
[Link] case study of using BIND_AS_VALUE. Looking forward to meeting you there.
[Link] Thank you, and goodbye.

12 / 21
Week 3 Unit 4

[Link] Hello, welcome to unit four of week three. I'm Jin Yeon Lee.
[Link] Let me present a case study of using BIND_AS_VALUE
[Link] that shows you one of our SQLScript procedures runs very slow,
[Link] very long in the production system. We couldn't see any performance issues
[Link] during developing and testing this SQLScript procedure
[Link] by executing statements one by one. But now, the application canceled
[Link] this very long-running procedure by its timeout logic. To find out the root cause,
[Link] we have to narrow down this issue. First of all, we have to find
[Link] which procedure is problematic. As a first step, let's collect the SQL Trace
[Link] in the production system, when the issue happens. Don't forget to set parameter internal as
true
[Link] in the SQL Trace section. Then let's check the collected SQL Trace.
[Link] In this scenario, if SQL Trace is collected successfully
[Link] while producing the issue, then we can see a trace message
[Link] or cancelation of the problematic query. We can find it by using keywords,
[Link] "cancelled by request". When scrolling up a little bit,
[Link] we can see a CALL statement having an error message. If the statement-execution-id
[Link] of that CALL statement is the same as the one in the error message,
[Link] then that CALL statement is the problematic one. Then let's find which SQL statement
[Link] in that procedure was canceled. When scrolling on SQL Trace
[Link] we can find the error query having the same statement-execution-id.
[Link] Of course, at this moment we cannot say this canceled SQL query
[Link] is our root cause of the timeout issue, because another SQL Trace might take too long
[Link] and this query could be canceled as a victim. Since we found the problematic CALL
statement,
[Link] then as our next step we have to find out which inner SQL statement takes too long.
[Link] Of course, we can also find information of elapsed time of each SQL statement in SQL
Trace.
[Link] But this time let's find that information in M_ACTIVE_PROCEDURES monitoring view.
[Link] Let's exclude the problematic procedure first. The procedure is not executed
[Link] by the application server, but us manually. It will not be canceled by timeout logic.
[Link] Instead, we have to check M_ACTIVE_PROCEDURES monitoring view after an amount of
timeout time.
[Link] Then the monitoring view will show us information of executed SQL statements
[Link] under this CALL procedure. M_ACTIVE_PROCEDURES monitoring view
[Link] shows this query takes too long. And this is the canceled query shown in the previous SQL
Trace.
[Link] The problematic query is Insert into the select statement on the calc view,
[Link] and it is a parameterized query. Parameter values are shown in the SQL Trace file.
[Link] Let’s assume we collect a Plan Visualizer Trace for this query,
[Link] with those parameter values. Then we find this calc view
[Link] has a multi-store table linked with dynamic tiering,
[Link] as a base table, and filters are now pushed down

13 / 21
[Link] to a remote source in dynamic tiering. Though they are given as parameters.
[Link] Since we know each parameter's values are used from an SQL Trace,
[Link] let's execute the same query as a literal one. It is executed quickly
[Link] and its Plan Visualizer Trace shows filters, given as constant values,
[Link] works well for the remote source this time. So it looks like converting
[Link] this parameterized query to a literal one can be a solution.
[Link] Let's try to use BIND_AS_VALUE. The comment determines which line we should modify.
[Link] The problematic SQL statement is located in line 95
[Link] of the definition of the procedure. Then we can see three scalar variables are shown
[Link] where parameters are located. Apply BIND_AS_VALUE functions
[Link] to these scalar variables. Then when you launch this procedure
[Link] in the production system, it works very well. Now the performance problem is solved
successfully.
[Link] Here are the key takeaways. SQL Trace shows information
[Link] about the executed inner SQL statements during execution of the SQLScript procedure.
[Link] Don't forget to configure parameter internal as true in the SQL Trace section.
[Link] M_ACTIVE_PROCEDURE shows information about all executed inner SQL statements
[Link] of an actively running procedure on the fly. Comment in the running SQL statement shows
the position
[Link] of its definition code. You can change or parameterize the query
[Link] to a literal one or vice versa by using BIND_AS_VALUE
[Link] or BIND_AS_PARAMETER SQLScript function. With that,
[Link] we've come to the end of this unit. In the next unit,
[Link] Minjeong will tell you about using SQL hints in SQLScript.
[Link] Thank you for your attention. See you.

14 / 21
Week 3 Unit 5

[Link] Hello, and welcome to unit five of week three. I’m Minjeong Lee and I am going to explain
how to use SQL hint in SQLScript.
[Link] SQL hint is an instruction for the SAP HANA database and it influences the way an SQL
request is processed.
[Link] HANA SQL optimizer determines the access path of a query, but you can override the
optimizer
[Link] by specifying hints in the query to enforce a certain access path. Hints are typically used to
optimize performance or memory consumption of a query
[Link] without any effect on the query result set. If you find that the query is running slow due to a
sub-optimal plan,
[Link] then you can apply SQL hints on that SQL statement to improve its performance. Since the
performance of inner SQL statements
[Link] affects the entire performance of SQLScript procedure a lot, using SQL hint for inner SQL
statements
[Link] can be a powerful solution to improve your SQLScript procedure performance. So, I am
going to explain how to use SQL hint for SQLScript procedure
[Link] during units five and six as the last topic of this course. Like ways to apply SQL hint on SQL
query, using hints for SQLScript is very similar.
[Link] You can add an SQL hint at the end of an inner SQL statement of an SQLScript procedure
code directly.
[Link] Or you can use statement hint feature or pinning hint feature. Regarding statement hint
feature,
[Link] this can be done by executing statement ALTER SYSTEM ADD STATEMENT HINT and
also by using the Statement Hints app of SAP HANA cockpit.
[Link] The pinning hint feature can be done by using the statement ALTER SYSTEM PIN SQL
PLAN CACHE ENTRY.
[Link] This ALTER statement links SQL hint to a certain execution plan stored in SQL plan cache.

[Link] Let’s see one by one. Here is the procedure HINT_TEST, which we are going to apply SQL
hint to.
[Link] This procedure has three inner SQL statements and they have dependency with table
variables.
[Link] The result of the first statement is stored in table variable RESULT1 and is consumed in the
second statement.
[Link] Table variable RESULT2 is used for storing a result of the second statement and it is
consumed in the last SQL statement.
[Link] As you learned, these three statements can be merged into a single statement by
SQLScript optimization rule inlining.
[Link] You can check the combined statement string via Explain Plan for CALL. Then, in this
procedure,
[Link] let’s add USE_HEX_PLAN SQL hint at the end of the third statement. Can you imagine how
this hint will be applied?
[Link] To check the answer, let’s collect Explain Plan for CALL. When clicking operator string
column of Explain Plan for CALL result,
[Link] you can see where the SQL hint is added. As you can see, in this SQLScript code,
[Link] USE_HEX_PLAN hint is added at the end of the third statement. But, SQLScript optimizer
combines the three inner SQL statements into a single one first,
[Link] then added SQL hint USE_HEX_PLAN at the end of the combined statement. That means
the added hint works for the combined statement,

15 / 21
[Link] not for the third SQL statement. So, it’s important to keep SQLScript optimization rules in
mind
[Link] when you are going to add SQL hints somewhere in your SQLScript procedure. Once
adding SQL hints in SQLScript procedure,
[Link] you should check how they will be applied by using Explain Plan for CALL, before executing
it. If you miss these two points,
[Link] you could be surprised because of unexpected impact of SQL hints. Then, let’s see another
example.
[Link] Unlike the previous example, USE_HEX_PLAN hint is added at the end of the second
statement.
[Link] Can you guess what will happen? Explain Plan for CALL shows two SQL statements
[Link] as the result of SQLScript optimization. The first and second statements are merged into a
single one,
[Link] then SQL hint USE_HEX_PLAN is added here. Then, can you expect what the last
statement looks like?
[Link] Yes, the last statement comes from the third statement in the code where RESULT2 is
consumed.
[Link] Here, what we should know is, adding SQL hint has also the same effect as NO_INLINE
hint
[Link] as well as the SQL hint functionality. So, the result of SQLScript optimization is the same
[Link] as when NO_INLINE hint is added at the end of the second statement in the code. Simply
speaking, adding SQL hint in SQLScript code
[Link] means that SQL hint is added at this position, not for a certain SQL statement only. Then,
inlining optimization stops
[Link] between the SQL statement where SQL hint is added and the next statement. I believe you
understand how SQLScript optimizer handles SQL hints.
[Link] Then, let me give you another quiz. Going back to the procedure HINT_TEST not having
any SQL hint,
[Link] what will happen if USE_HEX_PLAN hint is added at the end of a CALL statement? Where
do you expect this USE_HEX_PLAN hint will is applied?
[Link] When checking the Explain Plan for the CALL, you can see this SQL hint is at the CALL
statement itself.
[Link] But when checking the operator string of the inner SQL statements, you cannot see this
SQL hint.
[Link] This means SQL the hint at CALL statement is not propagated to inner SQL statements.
Another example is SQL hint IGNORE_PLAN_CACHE.
[Link] SQL hint IGNORE_PLAN_CACHE is frequently used when you want to ignore the existing
plan cache entry
[Link] and enforce the query to compile and execute. In case you need to clean all cached query
plans
[Link] including execution plan of CALL statement, you might use this IGNORE_PLAN_CACHE
hint at the end of the CALL statement.
[Link] But then, this IGNORE_PLAN_CACHE affects only the CALL statement. I mean that the
Execution Plan of CALL statement is compiled again,
[Link] but the cached SQL query plans of inner SQL statements are still used. With this
HINT_TEST procedure,
[Link] if you execute “CALL HINT_TEST WITH HINT (IGNORE_PLAN_CACHE)”, then, SQLScript
engine doesn’t look up its execution plan in SQL plan cache,
[Link] SQLScript engine compiles the execution plan again for a CALL statement. This means that
this hint works for a CALL statement itself.
[Link] But when SQLScript engine passes the inner SQL statement to SQL engine, this
IGNORE_PLAN_CACHE hint isn’t applied for that inner statement.

16 / 21
[Link] So, SQL engine will look up SQL plan cache for its query plan first, then reuse it without
query compilation.
[Link] But, since HANA 2.0 SPS07 and HANA Cloud QRC 4/2022, a new feature for hint
propagation has been introduced.
[Link] If you want to propagate SQL hint from CALL statement to inner SQL statements, you can
use the CASCADE feature,
[Link] which allows propagation of a hint into internal SQL statements within a procedure. Here’s
the procedure HINT_TEST again.
[Link] This time, it has NO_INLINE hint at the end of the second statement. You know the first two
statements will be combined
[Link] and the last statement will be executed separately. For this procedure, what will happen
[Link] if you execute this procedure with USE_HEX_PLAN hint with CASCADE option? This
USE_HEX_PLAN hint is propagated
[Link] to all statements after SQLScript optimization. As the output of SQLScript optimization,
[Link] two statements are generated, as you expected. One of them already has a hint
NO_INLINE.
[Link] And USE_HEX_PLAN hint is added additionally for this one. USE_HEX_PLAN is also
added to another statement.
[Link] In summary, the hint is applied to both the CALL statement itself and any inner SQL
statements.
[Link] When there are nested CALL statements, CASCADE hints are also propagated into the
nested procedures.
[Link] This CASCADE is only supported in CALL statements and cannot propagate hints into user-
defined functions.
[Link] If CASCADE is used for non-CALL statements, a FEATURE NOT SUPPORTED message
appears.
[Link] This is the last page of unit five. Today, we learned about how to use SQL hints in
SQLScript.
[Link] As you learned, to apply SQL hints, you can simply modify SQLScript code. But, don’t forget
to check how SQL hints are applied
[Link] by checking Explain Plan for CALL before executing it. Because adding hints affects
SQLScript optimization,
[Link] for example, inlining optimization stops at the statement with an SQL hint. Also, you learned
that SQL hint at the end of the call statement
[Link] is not propagated into inner statements by default. And only the CASCADE feature allows it.

[Link] In the next unit, I will continue explaining how to use SQL hint in SQLScript without
modifying SQLScript code.
[Link] Thank you for your attention. See you.

17 / 21
Week 3 Unit 6

[Link] Hello, and welcome to unit six, the final unit of this week and this course.
[Link] I’m Minjeong Lee and today’s topic is pinning SQL hints for SQLScript. As I explained, there
are two way to use SQL hints in SQLScript.
[Link] In the previous unit, we learned about using SQL hints by modifying SQLScript code
directly.
[Link] In this unit, I would like to talk about how to use SQL hints when you cannot modify
SQLScript code directly.
[Link] Without modifying SQL statements, you can add SQL hints by using
[Link] ALTER SYSTEM ADD STATEMENT HINT statement or ALTER SYSTEM PIN SQL PLAN
CACHE ENTRY statement.
[Link] Statement ALTER SYSTEM ADD STATEMENT HINT is called as "add statement hint"
feature.
[Link] This is a feature to add an SQL hint for a certain statement string or its hash value.
[Link] Statement ALTER SYSTEM PIN SQL PLAN CACHE ENTRY is called as "pinning
statement" feature.
[Link] This feature is originally for pinning a cached query plan in plan cache pool, but it also has
an option to add SQL hints on it.
[Link] In this case, Plan ID or its query plan in SQL plan cache is required. Here’s the procedure
HINT_TEST.
[Link] As you see, there is no SQL hint in SQLScript code. Let’s check the result of SQLScript
Plan Profiler this time.
[Link] It shows a single statement is executed because the three inner SQL statements are in-
lined.
[Link] If you’re using HANA Cloud QRC 4/2022, you can find STATEMENT_HASH value of the
executed SQL statements.
[Link] With this STATEMENT_HASH value, let’s add an SQL hint on this query.
[Link] To add SQL hint USE_HEX_PLAN, you can execute this statement:
[Link] ALTER SYSTEM ADD STATEMENT HINT (USE_HEX_PLAN) FOR STATEMENT HASH.
This means the SQL statement having this STATEMENT_HASH value
[Link] will be compiled with a given SQL hint when it's executed next time.
[Link] So, to do that, already-cached plan for this statement will be evicted.
[Link] In case your SQLScript Plan Profiler result doesn’t show STATEMENT_HASH value, then
you need to find it from M_SQL_PLAN_CACHE monitoring view.
[Link] Find hash values in M_SQL_PLAN_CACHE by using a proper STATEMENT_STRING
value
[Link] as a filter condition. After adding SQL hint,
[Link] SQLScript Plan Profiler or Explain plan for CALL result doesn’t show the added hint
[Link] since this hint is applied in SQL engine layer, not SQLScript engine.
[Link] Then, let’s check sqltrace to confirm if the hint is applied well. Turn on sqltrace with
parameter internal as true
[Link] and parameter query_plan_trace as on, then execute CALL HINT_TEST procedure.
[Link] Then you can find SQL hint USE_HEX_PLAN is attached well to the query in the collected
sqltrace result.
[Link] Here’s an example of pinning SQL hints. Pinning hints feature is a little bit different from
ADD STATEMENT HINT feature
[Link] as I explained before. When adding a hint by using statement ALTER SYSTEM ADD
STATEMENT HINT

18 / 21
[Link] with a certain statement string or hash value, this command clears the existing cached
query plan of that statement,
[Link] then once the same SQL string is requested, SQL engine compiles it with a given SQL hint.

[Link] So, if you already know the SQL statement to add the SQL hint, it doesn’t need that query
must be executed first
[Link] before executing our ALTER SYSTEM ADD STATEMENT HINT STATEMENT command.
Just before executing it,
[Link] you can add the SQL hint by using ALTER SYSTEM ADD STATEMENT HINT. But the
pinning hint works differently.
[Link] Pinning hints is using Plan ID of SQL PLAN CACHE ENTRY. This means this SQL
statement must be executed first,
[Link] so its query plan must be cached in SQL plan cache. Once executing procedure
HINT_TEST,
[Link] you can find the PLAN_ID of query plan of inner SQL statements in
M_SQL_PLAN_CACHE.
[Link] Then, you can execute ALTER SYSTEM PIN SQL PLAN CACHE ENTRY with that
PLAN_ID.
[Link] In this example, USE_HEX_PLAN hint is added by using the pinning hints feature. You can
use this pinning statement feature to add the SQL hint,
[Link] but the original pinning function is pinning a query plan in a plan cache not to be evicted,
[Link] until the plan becomes unpinned or the system restarts. Here’s again the procedure
HINT_TEST.
[Link] This time, the first statement has NO_INLINE hint. When you check the executed SQL
statement via sqltrace or SQLScript Plan Profiler,
[Link] you can see hexadecimal numbers, which is not in the original SQLScript code.
[Link] In unit two of week two, we learned it comes from a naming rule
[Link] for local temporary table for table variable or table parameter. Since table parameter is also
a table variable,
[Link] from now on, I will skip mentioning the table parameter. In this example, two SQL
statements are actually executed in procedure HINT_TEST.
[Link] The last one has a hexadecimal number and it is the real name of table variable RESULT1.
[Link] As you can see, its alias is RESULT1 This hexadecimal number is a global unique identifier,
GUID.
[Link] And it is used for a name of a local temporary table for table variables. The important thing
you must be aware of is
[Link] the real name of table variable RESULT1 is generated when the procedure is compiled. So,
if you compile CALL HINT_TEST statement again,
[Link] then the real name of table variable RESULT1 will be changed. Here’s an example of a
different name by changing the GUID of table variable RESULT1.
[Link] I get it by executing CALL HINT_TEST with hint IGNORE_PLAN_CACHE. You might
wonder why this is important and mentioned here.
[Link] If the real name of a table variable is changed with a new GUID value due to recompilation,
then the query strings with that table variable are also changed,
[Link] and the corresponding STATEMENT_HASH values too. So, it’s useless the SQL hints
which were added before
[Link] by using "add hint statement" feature or "pinning hint" feature
[Link] because their SQL statements have changed, so will not be executed anymore.
[Link] Instead, new SQL statements with a new actual name of table variable will be executed for
the CALL statement.
[Link] So, it is important to keep the real name of the table variable as long as possible

19 / 21
[Link] when you are using the "add hint statement" feature or the "pinning hint" feature.
[Link] To do that, pinning both a CALL statement and inner SQL statements with hints can be a
solution,
[Link] since the real name of a table variable is generated during compilation of a CALL statement.

[Link] Here’s an example of pinning both a CALL statement and its inner SQL statement
[Link] with SQL hint USE_HEX_PLAN. Check PLAN_ID in M_SQL_PLAN_CACHE after running
this procedure HINT_TEST once.
[Link] Then, you can find two Plan ID values, Plan ID of CALL HINT_TEST statement,
[Link] and Plan ID of target inner SQL statement,
[Link] Then you execute ALTER SYSTEM PIN statement for these two Plan ID values.
[Link] For the inner one, with the SQL hint USE_HEX_PLAN clause is added because you want to
add this hint for this inner statement.
[Link] For CALL statement, you don’t need any hint clauses because it is just for pinning plan in
SQL plan cache.
[Link] Here are the key takeaways of this unit. As I mentioned in the beginning,
[Link] adding or pinning statement hints can be a good solution when you cannot modify
SQLScript code directly to inject SQL hints.
[Link] When adding or pinning SQL hints, you must apply them on actual executed SQL
statements.
[Link] You can use STATEMENT_HASH value or PLAN ID depending on which feature is used.
[Link] Lastly, I explained the real names of table parameters or table variables have a GUID,
[Link] and this name is changed whenever SQLScript procedure is compiled. So, pinning the
CALL statement itself is very important
[Link] to keep SQL hints for inner statements referring to the actual name of a table variable.
[Link] And with that, we came to the end of this course unit and also to the end of our course.
[Link] During the course, we learned how SQLScript engine works with SQL engine and SQL plan
cache.
[Link] Also we learned SQLScript optimization rules. Then, we studied supportability features of
SQLScript,
[Link] which can help you to drill down your SQLScript code by yourself. Lastly, we reviewed
useful NO_INLINE hint and bind_as_parameter, bind_as_value functions,
[Link] and their usages. We hope you enjoyed this content
[Link] and will use tips immediately for your SQLScript codes. Before closing this course,
[Link] I would like to thank you, the learners, for your attention and my course expert colleagues
for their contribution to this course.
[Link] Good luck for the weekly assignment and the final exam. Goodbye.

20 / 21
© 2023 SAP SE or an SAP affiliate company. All rights reserved.
See Legal Notice on [Link]/legal-notice for use terms,
disclaimers, disclosures, or restrictions related to SAP Materials
for general audiences.