Unit 1: Course Introduction: Week 1: SAP HANA Query Processing

Week 1: SAP HANA Query Processing
Unit 1: Course Introduction

Course Introduction
Course overview
▪ Week 1: SAP HANA Query Processing

▪ Week 2: Column Search and Analysis Tools
▪ Week 3: Methods for Query Performance Analysis
▪ Week 4: Case Studies
© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 2

Course Introduction
openSAP course overview
Weeks 1 through 4 Video 1 Self-test 1

Week 1
▪ 5-7 video units and self-tests
SAP HANA Video 2 Self-test 2
▪ 1 weekly assignment
Query Video n Self-test n
▪ Online discussion forum (collaborate, ask questions)
Processing
▪ ~3-4 hours of effort each week Weekly assignment
Week 5 Week 2 Column Search and Analysis Tools

▪ Final exam
Week 3 Methods for Query Performance Analysis
Record of achievement
▪ Collect at least 50% of the total points available in Week 4 Case Studies
all online tests during the course
Week 5 Final exam

Course Introduction
Target audience and course requirements
Target Audience
▪ SAP HANA developers, SAP HANA consultants, and
anyone interested in learning about SAP HANA query
performance
Course Requirements
▪ Basic knowledge of the SQL language

Course Introduction
Course structure
Column Search &

Analysis Tools Case Studies
2 4
WEEK WEEK WEEK WEEK
START
1 3
SAP HANA Query Processing Methods for Query
Performance Analysis

Thank you.
Contact information:
[email protected]
Follow all of SAP
www.sap.com/contactsap
© 2020 SAP SE or an SAP affiliate company. All rights reserved.

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of
SAP SE or an SAP affiliate company.
The information contained herein may be changed without prior notice. Some software products marketed by SAP SE and its
distributors contain proprietary software components of other software vendors. National product specifications may vary.
These materials are provided by SAP SE or an SAP affiliate company for informational purposes only, without representation or
warranty of any kind, and SAP or its affiliated companies shall not be liable for errors or omissions with respect to the materials.
The only warranties for SAP or SAP affiliate company products and services are those that are set forth in the express warranty
statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional
warranty.
In particular, SAP SE or its affiliated companies have no obligation to pursue any course of business outlined in this document or
any related presentation, or to develop or release any functionality mentioned therein. This document, or any related presentation,
and SAP SE’s or its affiliated companies’ strategy and possible future developments, products, and/or platforms, directions, and
functionality are all subject to change and may be changed by SAP SE or its affiliated companies at any time for any reason
without notice. The information in this document is not a commitment, promise, or legal obligation to deliver any material, code, or
functionality. All forward-looking statements are subject to various risks and uncertainties that could cause actual results to differ
materially from expectations. Readers are cautioned not to place undue reliance on these forward-looking statements, and they
should not be relied upon in making purchasing decisions.
SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered
trademarks of SAP SE (or an SAP affiliate company) in Germany and other countries. All other product and service names
mentioned are the trademarks of their respective companies.
See www.sap.com/copyright for additional trademark information and notices.
Unit 2: SQL Query Processing in SAP HANA
SQL Query Processing in SAP HANA
SAP HANA Query Processor components
SAP HANA Server SAP HANA Query Processor

Application
SQL
ABAP
SQL Plan
Cache
JAVA
SQL Front
End
SQL Optimizer
Calculation Engine
SQL Engine
Optimizer & Executor
(Row Engine)
Row Store OLAP JOIN
Engine Engine
Column Store

Simple SQL statement processing overview
SELECT
A.COL1,
B.COL2
FROM TABLE1 A
INNER JOIN TABLE2 B ON A.COL3 = B.COL3;

Simple SQL statement processing overview

Application
SELECT
ABAP A.COL1,
B.COL2 SQL Plan
FROM TABLE1 A
Cache
JAVA INNER JOIN
TABLE2 B ON
A.COL3 = B.COL3;
SQL Front
End
SQL Optimizer
QO
QE
Calculation Engine
SQL Engine
(Row Engine)
Row Store OLAP JOIN
Engine Engine *QO: Query Optimizer Tree
Column Store
*QE: Query Execution Plan
Simple SQL statement processing – Cache not found

Application
SELECT
ABAP A.COL1,
B.COL2 SQL Plan
FROM TABLE1 A
Cache
JAVA INNER JOIN
TABLE2 B ON
No Plan Cache Found!
A.COL3 = B.COL3;
SQL Front
End
SQL Optimizer
SQL Optimizer:
QO Query Optimizer Tree The SQL Optimizer decides the best way
to call the engines depending on the
involved models and queries.
QE Query Execution Plan
Calculation Engine
SQL Engine
(Row Engine)
Row Store OLAP JOIN
Engine Engine
Column Store

Simple SQL statement processing – Plan cache hit

Application
SELECT
ABAP A.COL1,
B.COL2 SQL Plan
FROM TABLE1 A
Cache
JAVA INNER JOIN
TABLE2 B ON
Plan Cache Hit!
A.COL3 = B.COL3;

Simple SQL statement processing – Plan cache hit

Application
SELECT
ABAP A.COL1,
B.COL2 SQL Plan
FROM TABLE1 A
Cache
JAVA INNER JOIN
TABLE2 B ON
A.COL3 = B.COL3;
SQL
Front End
SQL Optimizer
Plan
Calculation Engine
SQL Engine
(Row Engine)
Row Store OLAP JOIN
Engine Engine
Column Store

SQL plan cache

Application
SELECT Plan cache identifiers
ABAP A.COL1,
B.COL2 Check plan cache entry
SQL Plan ▪ USER_NAME
FROM TABLE1 A
Cache
JAVA INNER JOIN
TABLE2 B ON ▪ SESSION_USER_NAME
A.COL3 = B.COL3;
▪ SCHEMA_NAME
SQL
Front End ▪ STATEMENT_STRING
▪ STATEMENT_HASH
Plan cache SQL Optimizer

entry found
Plan
Info columns
▪ EXECUTION_COUNT
▪ PREPARATION_COUNT
Execution
Engines ▪ LAST_EXECUTION_TIMESTAMP
▪ LAST_PREPARATION_TIMESTAMP

Useful columns in M_SQL_PLAN_CACHE
LAST_INVALIDATION_REASON
PLAN_ID
USER_NAME LAST_EXECUTION_TIMESTAMP
STATEMENT_STRING
IS_VALID
M_SQL_PLAN_CACHE EXECUTION_COUNT
LAST_PREPARATION_TIMESTAMP
STATEMENT_HASH
PREPARATION_COUNT

Example of usage of M_SQL_PLAN_CACHE
PM PM
PM PM

Thank you.
[email protected]
Follow all of SAP

warranty.
Unit 3: SQL Queries – Order of Execution
SQL Queries – Order of Execution
General SQL query execution order
1. FROM & JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY
Choose and join tables Aggregate the base data Return the final data Sort the final data
to get base data
Filter the base data Filter the aggregated Remove duplicated values
data marked as DISTINCT

Check SQL query execution order with an example
SELECT /*literal*/ A.COL1, A.COL2

FROM TABLE1 A
INNER JOIN ( SELECT B.COL1, B.COL2
FROM TABLE2 B
LEFT OUTER JOIN (SELECT E.COL1, E.COL2, G.COL2
FROM TABLE3 E
INNER JOIN (SELECT F.COL1, F.COL2
FROM TABLE4 F
GROUP BY F.COL1,F.COL2) G
ON E.COL2=G.COL2) H
ON B.COL1 =H.COL1) D ON A.COL2 = D.COL2
WHERE A.COL3 =20 WITH HINT (NO_USE_HEX_PLAN);
SQL Query Execution Order


From FROM TABLE1 A
Join 1 FROM TABLE2 B
Join 2 FROM TABLE3 E
Join 3 FROM TABLE4 F
ON E.COL2=G.COL2) H


FROM TABLE1 A
FROM TABLE2 B
FROM TABLE3 E
FROM & JOINs
FROM TABLE4 F
ON E.COL2=G.COL2) H


FROM TABLE1 A
FROM TABLE2 B
FROM TABLE3 E
FROM TABLE4 F
ON E.COL2=G.COL2) H
WHEREWHERE
A.COL3 =20 WITH HINT (NO_USE_HEX_PLAN);

SELECTA.COL1, A.COL2
SELECT /*literal*/
FROM TABLE1 A
FROM TABLE2 B
FROM TABLE3 E
FROM TABLE4 F
ON E.COL2=G.COL2) H

SELECT

FROM TABLE1 A WHERE
FROM TABLE2 B
FROM TABLE3 E
FROM TABLE4 F
GROUP BY F.COL1,F.COL2) G FROM & JOINs
ON E.COL2=G.COL2) H

Convert query into optimizer tree

FROM TABLE1 A
FROM TABLE2 B
FROM TABLE3 E
FROM TABLE4 F
ON E.COL2=G.COL2) H
ON B.COL1 =H.COL1) D
ON A.COL2 = D.COL2


FROM TABLE1 A
IJ A.COL2 = D.COL2
FROM TABLE3 E
FROM TABLE4 F
ON E.COL2=G.COL2) H
ON A.COL2 = D.COL2


FROM TABLE1 A
IJ A.COL2 = D.COL2
FROM TABLE3 E
T1
FROM TABLE4 F
ON E.COL2=G.COL2) H
ON A.COL2 = D.COL2


FROM TABLE1 A
IJ A.COL2 = D.COL2
T1 LOJ B.COL1 = H.COL1
FROM TABLE4 F
ON E.COL2=G.COL2) H T2
ON A.COL2 = D.COL2


FROM TABLE1 A
IJ A.COL2 = D.COL2
ON E.COL2=G.COL2) H T2 IJ E.COL2 = G.COL2
ON A.COL2 = D.COL2
T3


FROM TABLE1 A
IJ A.COL2 = D.COL2
ON A.COL2 = D.COL2
T3 G
T4

WHERE A.COL3 = 20
FROM TABLE1 A
IJ A.COL2 = D.COL2
ON A.COL2 = D.COL2
T3 G
WHERE
T4

Thank you.
[email protected]
Follow all of SAP

warranty.
Unit 4: Hands-On: Simple Query Execution
Hands-On: Simple Query Execution
Run simple query
1. Create sample tables

2. Run simple query
3. Check EXPLAIN_PLAN
4. Check M_SQL_PLAN_CACHE

Create sample tables
CREATE SCHEMA <username>;

SET SCHEMA <username>;
CREATE COLUMN TABLE TABLE1 (COL1 INT, COL2 INT, COL3 INT);
INSERT INTO TABLE1 VALUES(1,10, 20);

CREATE COLUMN TABLE TABLE2 AS (SELECT * FROM TABLE1);


Execute SQL statement

FROM TABLE1 A
INNER JOIN (
SELECT B.COL1, B.COL2
FROM TABLE2 B
LEFT JOIN (SELECT E.COL1, E.COL2, G.COL2
FROM TABLE3 E
INNER JOIN (SELECT F.COL1, F.COL2 FROM TABLE4 F GROUP BY F.COL1, F.COL2) G
ON E.COL2=G.COL2) H
ON A.COL2 = D.COL2

Check EXPLAIN PLAN
Go to SAP HANA studio > Explain Plan

Check M_SQL_PLAN_CACHE
SELECT
STATEMENT_STRING,
STATEMENT_HASH,
EXECUTION_COUNT,
PREPARATION_COUNT,
PARAMETER_COUNT,
LAST_EXECUTION_TIMESTAMP,
FROM "M_SQL_PLAN_CACHE"
WHERE SCHEMA_NAME = '<username>'
AND STATEMENT_STRING LIKE '%/*literal*/ A.COL1, A.COL2 … A.COL3=20'
ORDER BY LAST_PREPARATION_TIMESTAMP DESC;

PM PM
Literal query is compiled once, compiled plan is stored in plan cache and is reused

How does the hint IGNORE_PLAN_CACHE work?
1. Run the query with hint (IGNORE_PLAN_CACHE)

2. Check M_SQL_PLAN_CACHE

FROM TABLE1 A
INNER JOIN (
FROM TABLE2 B LEFT JOIN TABLE3 C ON B.COL1 =C.COL1) D
ON A.COL2 = D.COL2
WHERE A.COL3=20 WITH HINT (NO_USE_HEX_PLAN, IGNORE_PLAN_CACHE);

Application SAP HANA Query Processor SAP HANA Query Processor

SELECT SELECT
A.COL1, A.COL1,
ABAP B.COL2 B.COL2
FROM TABLE1 A
INNER JOIN TABLE2 B SQL Plan Check plan cache entry FROM TABLE1 A
SQL Plan
ON A.COL3 = B.COL3;
INNER JOIN TABLE2 B ON
A.COL3 = B.COL3 WITH Skip plan cache lookup
JAVA Cache HINT (IGNORE_PLAN_CACHE); Cache
SQL SQL
Front End Front End
Plan cache entry found
SQL Optimizer SQL Optimizer
Not stored
Plan Plan
in M_SQL_PLAN_CACHE
Execution Execution
Engines Engines

There is no entry found in M_SQL_PLAN_CACHE

Thank you.
[email protected]
Follow all of SAP

warranty.
Unit 5: SQL Optimizer – Architectural Overview
SQL Optimizer – Architectural Overview
SAP HANA SQL Optimizer
Application SAP HANA Query Processor

SELECT
A.COL1,
ABAP B.COL2
FROM TABLE1 A
INNER JOIN TABLE2 B SQL Plan Check plan cache entry
ON A.COL3 = B.COL3;
JAVA Cache
SQL
Front End
Plan cache entry found
SQL Optimizer
Plan
Execution
Engines

SAP HANA SQL optimization process
SELECT /*lit eral*/ A .COL1, A .COL2

FROM TABLE1 A
FROM TA BLE2 B
LEFT OU TER JOIN (SELECT E.COL1, E.COL2, G.COL2
SQL Plan
FROM TABLE3 E
INNE R JOIN ( SELECT F .COL1, F .COL2
FROM TAB LE4 F
GROUP BY F.COL1, F.COL2) G
ON E .COL2=G. COL2) H
ON B.CO L1 =H.CO L1) D ON A.COL2 = D.COL2
WHERE A.COL3 =20 WIT H HINT ( NO_USE_H EX_PLAN) ;
Cache
SQL Front
End
SQL
Optimizer

SAP HANA SQL optimization process

FROM TABLE1 A
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
SQL Front
End
SQL
Optimizer

Rule-based optimization and cost-based optimization

FROM TABLE1 A
▪ Predefined, mathematically proven rules are
FROM TA BLE2 B
applied, which by their nature would simplify

SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache the plan and lower the costs

Rule-Based ▪ Size estimation and cost comparison are not
Optimization
SQL Front required
End ▪ Filter push-down, join removal, simplify group
by
SQL
Optimizer
▪ Cost-based optimization finds candidates from

Cost-Based
the plan generated in rule-based optimization
Query
Optimization ▪ Cost of every alternative is calculated and one
with the best cost is chosen

Rule-based optimization

FROM TABLE1 A
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front
End
SQL
Optimizer
Cost-Based
Query
Optimization


FROM TABLE1 A
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front
End
SQL
Optimizer
Cost-Based
Query
Optimization


FROM TABLE1 A
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front
End
SQL
Optimizer
Cost-Based
Query
Optimization

J12

FROM TABLE1 A
F J24
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache G T2 T4
Rule-Based
Optimization J13
SQL Front
End
G T3
SQL
Optimizer
T1
*Predefined rules are sequentially

Cost-Based
applied to the initial tree.
Query
Optimization

J12

FROM TABLE1 A
J24
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache G T2 T4
Rule-Based
Optimization J13
SQL Front
End
G F Filter Pushdown
SQL
Optimizer
T1 T3

Cost-Based
Query
Optimization

J12

FROM TABLE1 A
J24
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache Simplify G T2 T4
Rule-Based Group By
Optimization J13
SQL Front
End
G F
SQL
Optimizer
T1 T3

Cost-Based
Query
Optimization

J12
Remove
Unnecessary Join
FROM TABLE1 A
J24
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache JG
13 T2 T4
Rule-Based
Optimization
SQL Front G F
End
SQL T1 T3
Optimizer

Cost-Based
Query
Optimization

J12

FROM TABLE1 A
FROM TA BLE2 B
T2
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache JG
13
Rule-Based
Optimization
SQL G F
Frontend
SQL T1 T3
Optimizer
REWRITE TREE
Cost-Based
Query
Optimization


FROM TABLE1 A
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front
End
SQL
Optimizer
Cost-Based
Query
Optimization

Cost-based optimization

FROM TABLE1 A
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front
End
SQL
Optimizer
Cost-Based
Query
Optimization


FROM TABLE1 A
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front
End
SQL
*The tree from the rule-based
Optimizer
optimization is sent to cost-based
query optimization
Cost-Based
Query
Optimization

Plan 1

FROM TABLE1 A
FROM TA BLE2 B
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front
End
SQL
Optimizer
_THRU_
&
PRE_BEFORE_
Cost-Based
Query
Optimization

Plan 1

FROM TABLE1 A
FROM TA BLE2 B
Plan 2
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front
End
SQL
Optimizer
_THRU_
&
PRE_BEFORE_
Cost-Based
Query
Optimization

Plan 1

FROM TABLE1 A
FROM TA BLE2 B
Plan 2
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front Plan 3
End
SQL
Optimizer
_THRU_
&
Cost-Based
Query PRE_BEFORE_
Optimization

Plan 1

FROM TABLE1 A
FROM TA BLE2 B
Plan 2
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front Plan 3
End
SQL
Optimizer
Plan 4
_THRU_
Cost-Based
Query &
Optimization PRE_BEFORE_

Plan 1

FROM TABLE1 A
FROM TA BLE2 B
Plan 2
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front Plan 3
End
SQL
Optimizer
Plan 4
Cost-Based
Query _THRU_ Plan 5
Optimization
&
PRE_BEFORE_

Plan 1
Enumerators

FROM TABLE1 A
FROM TA BLE2 B
Plan 2
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
A_THRU_B PREA_BEFORE_B
Optimization
SQL Plan 3
Frontend
SQL
Optimizer
Plan 4
▪ AGGR_THRU_JOIN ▪ PREAGGR_BEFORE_JOIN
▪ JOIN_THRU_JOIN ▪ PREAGGR_BEFORE_UNION
Cost-Based
▪ JOIN_THRU_AGGR …
Query Plan 5
Optimization _THRU_
&
PRE_BEFORE_
Plan 6
Plan 1
Final plan selection among alternatives

FROM TABLE1 A
FROM TA BLE2 B
Plan 2
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front Plan 3
End
SQL
Optimizer
Plan 4
Cost-Based
Query Plan 5
Optimization
Plan 6
Plan 1
Final plan selection among alternatives

FROM TABLE1 A
FROM TA BLE2 B
Plan 2
SQL Plan
FROM TABLE3 E
FROM TAB LE4 F
Cache
Rule-Based
Optimization
SQL Front
End
SQL
Optimizer
Plan 4
Cost-Based
Query Plan 5
Optimization
Plan 3 Plan 6
Thank you.
[email protected]
Follow all of SAP

warranty.
Unit 6: Parameter-Aware Optimization
Parameter-Aware Optimization
Check points of parameterized query
1. Parameterized query is compiled twice

I. 1st Compilation: Precompiled Plan
II. 2nd Compilation: Recompiled Plan
➔ Precompiled Plan ≠ Recompiled Plan
➔ Important to check Recompiled Plan
2. Different plan is generated depending on which values are used for the compilation
SELECT
Bind variable = ‘A’ COL1, Bind variable = ‘B’
COL2
FROM VIEW1
WHERE COL3 = ?;

Parameterized query processing
SELECT
COL1,
COL2
FROM VIEW1
WHERE COL3 = ?;

Parameterized query processing – Cache not found
Application SAP HANA Server

SAP HANA Query Processor
ABAP SELECT
COL1,
COL2 SQL Plan
JAVA FROM VIEW1
WHERE COL3 = ?; Cache No Plan Cache Found!


ABAP SELECT
COL1,
COL2 SQL Plan
JAVA FROM VIEW1
WHERE COL3 = ?; Cache No Plan Cache Found!
SQL Front
End


ABAP SELECT
COL1,
COL2 SQL Plan
JAVA FROM VIEW1
WHERE COL3 = ?; Cache
SQL Front
End
SQL Optimizer
QO
QE


ABAP SELECT
COL1,
COL2 SQL Plan
JAVA FROM VIEW1
WHERE COL3 = ?; Cache
SQL Front
End
SQL Optimizer
QO
QE Precompilation


ABAP SELECT
COL1,
COL2 SQL Plan Recompile for Once
JAVA FROM VIEW1
WHERE COL3 = ?; Cache (and Never)
Bind Variable = ‘A’
SQL Front
VIEW1
End
SQL Optimizer
QO
QE
Calculation Engine
SQL Engine Optimizer &
(Row Engine) Executor
Row Store OLAP JOIN
Engine Engine
Column Store

Parameterized query processing – Plan cache hit (bind variable = A)

ABAP SELECT
COL1,
COL2 SQL Plan
JAVA FROM VIEW1
WHERE COL3 = ?; Cache Plan Cache Hit!
Bind Variable = ‘A’
SQL Front
VIEW1
End
SQL Optimizer
A plan compiled with

Plan bind variable = ‘A’
Calculation Engine
Row Store OLAP JOIN
Engine Engine
Column Store

Parameterized query processing – Plan cache hit (bind variable = B)

ABAP SELECT
COL1,
COL2 SQL Plan
JAVA FROM VIEW1
WHERE COL3 = ?; Cache Plan Cache Hit!
Bind Variable = ‘B’
SQL Front
VIEW1
End
SQL Optimizer
A plan compiled with

Plan bind variable = ‘A’
Calculation Engine
Row Store OLAP JOIN
Engine Engine
Column Store

Check parameterized query via M_SQL_PLAN_CACHE
1. PRECOMPILATION 2. RECOMPILATION 3. EXECUTION
SELECT
COL1,
COL2 VIEW1
FROM VIEW1
WHERE COL3 = ?;
2020.03.01 PM 2020.03.01 PM
2020.03.01 PM 2020.03.01 PM
2020.03.01 PM
2020.03.01 PM 2020.03.01 PM
2020.03.01 PM 2020.03.01 PM
2020.03.01 PM 2020.03.01 PM
2020.03.01 PM 2020.03.01 PM

Parameterized query with bind variable = 20
SELECT /*Parameterized*/ A.COL1, A.COL2 FROM TABLE1 A INNER JOIN

(SELECT B.COL1, B.COL2 FROM TABLE2 B LEFT JOIN (SELECT E.COL1,
E.COL2 FROM TABLE3 E INNER JOIN (SELECT F.COL1, F.COL2 FROM
TABLE4 F)G ON E.COL2=G.COL2)H ON B.COL1 =H.COL1) D ON A.COL2 =
D.COL2 WHERE A.COL3 =?
--Bind Variable: 20


--Bind Variable: 20
AM AM


--Bind Variable: 30


--Bind Variable: 30
AM AM

Thank you.
[email protected]
Follow all of SAP

warranty.
Unit 7: Hands-On: Understand Parameter-Aware
Optimization
Hands-On: Understand Parameter-Aware Optimization
Run a parameterized query
▪ Run a parameterized query

▪ Check EXPLAIN PLAN
▪ Check M_SQL_PLAN_CACHE

Before executing a parameterized query
Please note that our recommendation for parameterized query execution is to make the
parameterized query into a single line. This is because different carriage returns across the
interfaces often interfere with the usage of the plan cache.
SELECT /*Parameterized*/ A.COL1, A.COL2
FROM TABLE1 A
INNER JOIN (
FROM TABLE2 B
LEFT JOIN (SELECT E.COL1, E.COL2, G.COL2
FROM TABLE3 E
INNER JOIN (SELECT F.COL1, F.COL2 FROM TABLE4 F GROUP BY F.COL1, F.COL2) G
ON E.COL2=G.COL2) H
ON A.COL2 = D.COL2
WHERE A.COL3 =? WITH HINT (NO_USE_HEX_PLAN);
SELECT /*Parameterized*/ A.COL1, A.COL2 FROM TABLE1 A INNER JOIN (SELECT B.COL1, B.COL2 FROM TABLE2 B LEFT JOIN
(SELECT E.COL1, E.COL2, G.COL2 FROM TABLE3 E INNER JOIN (SELECT F.COL1, F.COL2 FROM TABLE4 F GROUP BY F.COL1,
F.COL2) G ON E.COL2=G.COL2) H ON B.COL1 =H.COL1) D ON A.COL2 = D.COL2 WHERE A.COL3 =? WITH HINT (NO_USE_HEX_PLAN)

Execute SQL statement
SELECT /*Parameterized*/ A.COL1, A.COL2 FROM TABLE1 A INNER JOIN (SELECT

B.COL1, B.COL2 FROM TABLE2 B LEFT JOIN (SELECT E.COL1, E.COL2, G.COL2 FROM
TABLE3 E INNER JOIN (SELECT F.COL1, F.COL2 FROM TABLE4 F GROUP BY F.COL1,
F.COL2) G ON E.COL2=G.COL2) H ON B.COL1 =H.COL1) D ON A.COL2 = D.COL2
WHERE A.COL3 =? WITH HINT (NO_USE_HEX_PLAN) --Bind Variable: 20

Check EXPLAIN PLAN

SELECT
STATEMENT_STRING,
STATEMENT_HASH,
EXECUTION_COUNT,
PREPARATION_COUNT,
PARAMETER_COUNT,
LAST_EXECUTION_TIMESTAMP,
FROM "M_SQL_PLAN_CACHE"
WHERE SCHEMA_NAME = '<username>'
AND STATEMENT_STRING LIKE '%/*parameterized*/ A.COL1, A.COL2 … A.COL3= ?'
ORDER BY LAST_PREPARATION_TIMESTAMP DESC;

PM PM
The parameterized query is compiled twice: precompilation and recompilation.

Make sure you see the recompiled plan for performance issue analysis.

Thank you.
[email protected]
Follow all of SAP

warranty.

Unit 1: Course Introduction: Week 1: SAP HANA Query Processing

Uploaded by

Copyright:

Available Formats

Unit 1: Course Introduction: Week 1: SAP HANA Query Processing

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Unit 1: Course Introduction: Week 1: SAP HANA Query Processing

Uploaded by

Copyright:

Available Formats

Week 1: SAP HANA Query Processing

Unit 1: Course Introduction

▪ Week 1: SAP HANA Query Processing

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 2

Weeks 1 through 4 Video 1 Self-test 1

Week 5 Week 2 Column Search and Analysis Tools

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 3

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 4

Column Search &

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 5

© 2020 SAP SE or an SAP affiliate company. All rights reserved.

SAP HANA Server SAP HANA Query Processor

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 2

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 3

SAP HANA Server SAP HANA Query Processor

SAP HANA Server SAP HANA Query Processor

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 5

SAP HANA Server SAP HANA Query Processor

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 6

SAP HANA Server SAP HANA Query Processor

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 7

SAP HANA Server SAP HANA Query Processor

Plan cache SQL Optimizer

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 8

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 9

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 10

© 2020 SAP SE or an SAP affiliate company. All rights reserved.

1. FROM & JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 2

SELECT /*literal*/ A.COL1, A.COL2

1. FROM & JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY

SQL Query Execution Order

SELECT /*literal*/ A.COL1, A.COL2

1. FROM & JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY

SQL Query Execution Order

SELECT /*literal*/ A.COL1, A.COL2

1. FROM & JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY

SQL Query Execution Order

SELECT /*literal*/ A.COL1, A.COL2

1. FROM & JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY

SQL Query Execution Order

1. FROM & JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY

SQL Query Execution Order

SELECT /*literal*/ A.COL1, A.COL2

1. FROM & JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY

SQL Query Execution Order

SELECT /*literal*/ A.COL1, A.COL2

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 9

SELECT /*literal*/ A.COL1, A.COL2

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 10

SELECT /*literal*/ A.COL1, A.COL2

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 11

SELECT /*literal*/ A.COL1, A.COL2

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 12

SELECT /*literal*/ A.COL1, A.COL2

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 13

SELECT /*literal*/ A.COL1, A.COL2

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 14

© 2020 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 15

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /literal/ A.COL1, A.COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2

SELECT /lit eral/ A .COL1, A .COL2